Hi Brian,

You need to use a PivotTable to return the results you want to see in the chart. The Excel Table will always include suppliers that do or don't have data, whereas a PivotTable will only return suppliers that do have data.

Mynda

Thanks, Mynda!

I am almost there. The data labels have dropped off but the chart still shows gaps where the labels with no data would be (if they had any data). Is there any way to make the chart more dynamic?

Hi Brian,

Without an example file I'm not sure exactly what you're referring to, sorry . If you're strictly talking about data labels and they show zero where there is no data, then you can use a custom number format to hide the zeros e.g

#,##0;-#,000;

The last semi-colon is the zero format, which is blank, therefore returning a blank data label.

Mynda

Hi Mynda,

Sorry, I'm not explaining this very well. I have attached a file with a mock-up of what I am trying to do.

Thanks for your help.

Brian

Hi Brian,

Yes, you need to summarise your data in a PivotTable (not a table), but first you need to unpivot it, which I've done in the attached file with Power Query.

Mynda

So I need to unpivot the data and include only the suppliers that have data in the table. That makes sense - I knew there had to be a simple solution!

Thanks so much!

The PivotTable will automatically only include the suppliers that have data according to the filters set in the Slicer.