How to remove vertical axis categories where the value is 0 in dynamic chart | Dashboards & Charts

How to remove vertical axis categories where the value is 0 in dynamic chart

susan Evans

Member

Members

Forum Posts: 5

Member Since:
May 9, 2015

Offline

December 8, 2016 - 10:03 pm

I have a graph which shows no of customers in each department for 4 different organisations. I have a tool so you can change the month, and some departments may have some customers on one month, but no customers in other months across the 4 organisations. As there are quite a few departments, my graphs looks quite cluttered.

How do I get a chart to only show those departments (categories) on my Y axis which have a value not equal to 0 in any organisation? Even though there is no value, the Department (Category) title still appears on my Y axis.

I have tried conditional conditioning so that it changes the department name to White Text, but then an empty space appears in my chart Y Axis, where the department is.

Can this be done?

Thanks in advance.

See Answer

Mynda Treacy

Admin

Forum Posts: 732

Member Since:
July 16, 2010

Offline

December 8, 2016 - 10:24 pm

Hi Susan,

I recommend you use a PivotTable to summarise your data, a Pivot Chart to present it and Slicers to control the filtering. This way the PivotTable will do the work in only showing you the departments that have values for the month selected in the Slicer.

I would have shown you an example in your sample file, but the source data is in an external file so I wasn't able to

Here is a tutorial on Slicers: https://www.myonlinetraininghub.com/ill-have-a-slicer-that

And a tutorial on Pivot Charts: https://www.myonlinetraininghub.com/excel-pivot-charts

Please let me know if you have any questions.

Kind regards,

Mynda

Answers Post

susan Evans

Member

Members

Forum Posts: 5

Member Since:
May 9, 2015

Offline

December 8, 2016 - 11:57 pm

Thank you. Further question, how do I include the Slicer selection in my header? Normally I would do ="Title "&Cell Ref, but the slicer doesn't seem to have cell reference.