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.
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
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.
Hi Susan
Just join your header to the filtered Pivot Table's value.
Refer attachment for an example. Hope this helps.
Sunny
SunnyKow said
Hi SusanJust join your header to the filtered Pivot Table's value.
Refer attachment for an example. Hope this helps.
Sunny
Fabulous.
Thank you.
Susan
