Creating a dynamic chart which removes all x axis entries when N/A is shown

[sav1979]

Hi all,

I am really hoping that someone can help me out on this as I am really stuck. I have a chart which shows 2 sets of metrics, one is the category and the second is the supplier. I want to create a chart whereby the user can select a supplier and category and see the scores accordingly. In some cases, a supplier does not have an entry for a specific category, so N/A is shown. I want this to reflect in the chart by dynamically plotting only where a score is shown and all the other points on the x axis should be hidden. The user can then easily copy and paste into a report.

I have attached the file.

Many, many thanks in advance.

Sav1979Supplier Performance Comparison Chart_test.xlsx

[teylyn]

Hello,

the chart is a clustered column chart with two series. You cannot hide part of a clustered column set, just because an individual data point is #N/A. Also, each of the X axis category groups will show the space to plot all the series columns.

If you don't want then #N/A to show in the chart, leave the cells in your source data blank. After that, change the selection, so the dynamic ranges recalculate. Then there will be no NA in the chart.

cheers, teylyn

[sav1979]

Thanks but then it just shows a zero and the gaps are still there. From my understanding of your post, it is impossible to hide categories on the x axis which have a zero/blank value?

[teylyn]

Yes. If a column has a 0 value then it will still show as a gap in a clustered column chart.