• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A bar graph chart that leaves out bars if value is zero / empty


Excel Ninja
Staff member

So if you have 6 bars and 1 data point is zero you only want to have 5 bars ?

So you don't want a gap

Is that correct?


Excel Ninja
Staff member
Have you tried manually hiding the rows with the zero data?

As well as hiding you can also Group Rows to hide them,

Select the Row with the zero data and Alt Shift Right Arrow

Once the row is grouped

Click on the - on the left margin to hide the row

Click on the + to unhide the row

Alt Shift Left Arrow to remove the group

This way you have a visual clue that there are hidden rows


New Member
Hi Hui

I am trying to avoid doing that as I have a full sheet of columns and the data changes monthly - something that was zero this month may be populated next month etc etc - the data is organised like this:


12 0 33 44 .... etc etc across an entire sheet of columns, 7 x sheets of this.

So manually hiding columns is not an option really!


Excel Ninja
Staff member
Have you tried Grouping Columns as mentioned above ?

Is automatically hiding Columns an option ?

can you post your data somewhere ?


New Member
How do you automatically hide columns? If there is some way that a column automatically hides when there is only zeros / blanks that could work.

Where/how could I post my data? Can you post data on this site?



Excel Ninja
Staff member

I have posted below 2 macros

They need to be pasted into a Code Module in VBA

The Names are self explanatory

To use assign each to a shape or button on your worksheet or you can run them manually


Select a cell in a left hand cell of a row which contains your data

eg: in your case 12 0 33 44 .... select cell 12 and run the Hide_Cols macro

Sub Hide_Cols()

Dim c As Range

Range(ActiveCell, Selection.End(xlToRight)).Select
For Each c In Selection
If c.Value = 0 Or c.Value = "" Then c.EntireColumn.Hidden = True

End Sub

The Unhide_All_Cols macros does just that

Sub Unhide_All_Cols()

Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = False

End Sub
You can't post files here on Chandoo.org but have a read of


for some ideas