With a Pivot Chart, how can I avoid plotting missing data?

Question

Please see my problem illustrated here:

I have received forecasting information from a customer, telling us roughly what they intend to order from us in a given month. The column months are the "source" data, i.e. reading down the second column (May): in May, they told us that they would spend £10,465 in May and also predicted that they would be spending £8,500 in June.

Each month, they provide a forecast for the upcoming months, so in June, there will be no data included for May. This is shown in the table as a zero, when in reality, it doesn't actually exist in the data and should be blank or N/A. As the pivot table automatically sums the sales for each month, I cannot adjust the formula to show errors instead of zeros to avoid it being plotted.

As you can see in the graph, these zeros are plotted before/after each month and making it incredibly messy.

I have the data in a Pivot Chart because, with slicers, I will analyse the data from multiple months pertaining to many months in the future. I would not be able to keep up if I replicated the data in some way to create a regular excel chart. Is there any way to stop this missing data from being plotted?

Answer 1

Do the followings:

1. Click anywhere on the chart, & find the Chart Tools group on the ribbon.
2. From Design Tab, Select Data.
3. From the Select Data Source, hit the Hidden and Empty Cells.
4. You find Gaps is selected by default,, select either Zero or Connect Data points with Line.