Creating "regular" chart and a pivot table on data with gaps

Question

I have a lot of data in an excel file that I am trying to display in 2 charts: Regular graph chart (all data) Pivot table (averages) The problem is that I have a lot of gaps in my data that i dont know how to display to work with both charts. If I display the gaps as NA(), the pivot table average return NA(). If I display the gaps as empty cell (""), the graph shows this values as 0.

Simplified example:

Problem with NA(): Problem with NA()

Problem with blank (""): Problem with blank ("") *the "Hidden and Empty cells" option in Select Data has no impact (I tried all 3 options).

any reccomended solution? (beside copy all the data twich, one with blanks ("") and one with NA()).

Thank you.

Answer 1

As you've discovered, "" is NOT an empty cell, but a short piece of text which is plotted as zero. So it might be fine for your pivot table source but not for your chart source.

Make two data ranges, one for the pivot table which uses "" and the other for the chart which uses #N/A.

Answer 2

Use the empty cell, but for the chart showing all data - create a scatterplot then add a solid line.

Select the data.

Use Insert>Charts>Scatter:

Select the series by clicking one of the dots. Right click on a selected dot, then choose 'Format Data Series'.

In the options pane that appears, click the slanted paint bucket, then under 'Line', choose 'Solid line':

This will ensure you have a gap in the chart where you have a gap in your data.

Answer 3

1. TRUE EMPTY CELL OPTION

The best way to handle this is a true and proper empty cell (no value, no formula), but understandably this isn't always practical with continuous running formulas per column.

This method has the added advantage (disadvantage for some?) of a discontinuous line in your normal scatter plot.

2. FILTER "" BLANKS OPTION

Using ="" in your formulae will make your pivots work as expected. You can then add a normal filter to your data and filter out the blanks. The normal graph will ignore the hidden rows and plot a continuous line.

Unfortunately this option doesn't work if you have multiple series with "blanks" scattered unevenly across the rows.

3. PIVOT REFRESH OPTION

You could also take advantage of the fact that pivot table don't automatically refresh. Name a cell "plotforpivot" and give it the value TRUE. Then use this formula for your "blank" cells:

=IF(plotforpivot,"",NA())

Now refresh you pivot table. Next change the "plotforpivot" cell to FALSE. Your pivot and normal plot will now reflect correctly until the next pivot refresh.

4. AGGREGATE function option

You could opt for calculating the average yourself (i.e. not using a pivot table). If you use NA() the normal AVERAGE function (also used by the Pivot) will return #N/A as well. However, the AGGREGATE function can be used to compute the average whilst ignoring errors like so =AGGREGATE(1,7,B2:B7). Sadly there is no option to use AGGREGATE functions to summarise pivot values.

1. FILTER THE PIVOT

Last but not least, you can filter the #N/A values in the pivot. Firstly use =NA() for your blanks so that the normal graphs plot nicely. Next set up your pivot as you like, but also drag your series data in under the pivot "filter" section. Now click the down filter arrow next to the series at the top of the pivot table, click "select multiple items" and uncheck "#N/A". Unfortunately this suffers the same problem as filtering with option #2, as multiple series will also filter whole rows at a time. I guess fundamentally this is a problem, as Excel cannot average say 10 items for one series, and then only 9 items for another. It cannot hide just one cell, only a whole row or whole column. Perhaps you can do multiple pivot tables instead? One for each series?

In conclusion

In the end if you have multiple series you might have no other option than to duplicate each series column, one with #N/A and the other with "". You can at least do this easily starting with say NA() in your original formula (column B) and use =IFERROR(B2,"") in the duplicate.

Ideally one should be able to use the AGGREGATE(1,7...) in a pivot. Googling if that is possible, I did get a few hits using PowerPivot / PowerQuery. You can try if you like, but its not my strong suite.

I think I personally prefer the pivot refresh option (#3). If the client is smart enough to refresh the pivot table they can surely toggle a cell value. You can even make the "plotforpivot" cell a dropdown list to only allow TRUE or FALSE, and you can include a warning formula in A1 that detects if you have #N/A errors in the pivot and "" blanks in the data e.g. =HYPERLINK("#plotforpivot",IF(ISNA(GETPIVOTDATA("Data",$D$1)), "WARNING #N/A ERROR: PLEASE TOGGLE PLOTFORPIVOT AND REFRESH THE PIVOT TABLE", IF(COUNTIF(B2:B7,"")>0,"WARNING PLOTTING ERROR: PLEASE TOGGLE PLOTFORPIVOT","ALL GOOD!")))