Column Chart displaying blank columns

[excel challenge]

Sample data set:

January February March

Speed 1 2 3
Run <blank> <blank> 4


How do I prevent <blank> columns from showing in column chart?

[dilipandey]

Hi excelchallenge,

Welcome to the forum.

You can use =if(isblank(.... ) function in your data set... or you can use =na() ...
If this is not as what you are looking for... suggest you to upload the excelworkbook.. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[npamcpp]

How exactly do the empty cells show in the column chart?

Dilipandey, how exactly is Isblank() going to help here? If a cell is blank, then it won't show in the chart. If you manipulate it with formulas, it will only get worse, not better. I wish you would back up your suggestion with a concrete example on what exactly to do.

[dilipandey]

Hi ExcelChallenge,

Below I have explained use of Isblank and NA() while doing charts..
Isblank & NA use in chart.xlsx

I visualize excel better than beer

If this is not helpful, suggest you to upload your excel workbook to help me understand better

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[Andy Pope]

The use of NA() in the data source of a chart only suppresses the data marker in a line chart. The points either side of cell(s) with NA() will have the line interpolated.

For column charts the column will be treated as zero but the space for the column will remain. The only way to get zero columns removed from the chart is to remove them from the data source. This can be done with dynamic named ranges IF the data is sorted.

[npamcpp]

dilipandey, the title of the thread, and the question is about a column chart, not a line chart. Andy has it nailed.

[excel challenge]

I greatly appreciate the willingness to help. Attached please find spreadsheet. Any tips on how not to have blank / empty column in chart for Score 4 from December to February?


Thanks

[npamcpp]

The chart shows five data series as a clustered column chart. Each cluster (month) has slots for each of the five values. If the underlying cells are blank, zero or NA, then no column will be plotted.

I don't think that you can have a chart where clusters have variable numbers of columns.

[Andy Pope]

You can alter the layout of your chart data.

But I think it maybe confusing to see Score 4 removed from first few months of data.

[excel challenge]

Thank you!