Not Show Hidden Rows in Column Chart?

I have Excel 2003.

In past versions I was able to hide rows and the resulting column chart would not display the data point that was hidden.

My horizontal axis is made up of dates and the vertical axis is the value.

What do I need to do to force the column chart to not "skip" for the dates that have no data?

Select the chart. And then uncheck Tools > Options > Chart > Plot visible cells only

This didn't seem to work for me.

I added another column and used the formula

Please Login or Register  to view this content.

and copied it down. Then changed the series category to reflect this new column.

Now, there are no gaps in the chart.

difficult to tell what you have done without an example.

Had to copy the workbook, and copy > paste values of the linked formulas to get down below the size limit for attachments, but here is the essence of one of the charts and what I have done.

We only want to show the same date between the two years if both dates had production; thus the formulas in columns G & H.

Column B was inserted as a helper column to avoid gaps in the columns of the chart.

The reason it did not exclude the rows is because they are not empty.

Cells with formula are not empty as far as charts are concerned. The best you can do is use NA() instread of the 0 in your formula. This only makes the chart interpolate between valid points. In a line chart this means markers are not displayed. In a column chart it really has no effect on the number of categories display but instead sets the column value to zero.

Rather than a macro you could just use Autofilter.

Good Tip on the autofilter. I had not thought of that.

I will not be the one maintaining the workbook and was unsure of the expertise of the final user, so wanted to make it as easy for them as possible to only plot the "same day production".

I am also thinking of using some named dynamic ranges to limit how many actual data points will be displayed to the last 30 to 60...