If you’ve ever used Excel to present data with ‘holes’ in it, you might have been frustrated by the options for how those holes should be represented on a chart. Here’s a short summary of one way to make the holes show up as missing data points rather than ‘0’, which might be confusing, inaccurate, or just plain wrong.
On this plot, you can see an imaginary data set plotted for the years 2000 through 2020, but the data for 2006 is missing, and the data for 2014 through 2020 hasn’t been collected yet. Often times, these values will need to be processed to some extent (scaled, added, multiplied, or otherwise) and these missing values will be quietly assumed to be ‘0’ without raising an eyebrow. That is, not until it comes time to make a chart. Then these ‘0’ values show up where we’d really rather they not be.
Here’s our innocent looking data:
Column A: Year
Column B: Raw Data
Column C: Plot Data =B2*1.1
And here’s how that data is plotted by default. The Raw Data plot looks great.
Unfortunately, the Plot Data that we scaled in Column C has a problem; it has zero’s where they shouldn’t be.
We can eliminate these unwanted data points by using IF() and NA(), Excel’s “value not available” error function.
Column D: Data 2 = IF( B2 , B2*1.2, NA() )
Column E: Data 3 = D2 + 1
This is somewhat analogous to using NaN’s (“Not A Number”) in other programming languages and data processing tools. These #N/A messages propagate through to other calculations and correctly omit data points without having to manually delete the formula’s from offending cells, making it much easier to add data later on without having to figure out which formulas need to be repopulated.
The Data 2 plot below shows the results of our work; a finished chart that we can easily add data to, but without the distracting chartjunk.
The Excel file linked below can be downloaded if you’d like to try it on your own.