Error Trapping in Excel: How to use NA() to make your Excel chart pretty!

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

Excel NA_1

And here’s how that data is plotted by default.  The Raw Data plot looks great.

Excel NA_2

Unfortunately, the Plot Data that we scaled in Column C has a problem; it has zero’s where they shouldn’t be.

Excel NA_3

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.

Excel NA_4

The Excel file linked below can be downloaded if you’d like to try it on your own.


Excel NA() (485 downloads)

 

Say something

Your email address will not be published. Required fields are marked with a grey bar.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>