I received a call from my daughter yesterday. She was totally frustrated as she was trying to plot data for a class project that contained a lot of empty cells. I told her how to fix it and then learned that these suggestions would not work on a MAC. As I helped her figure how to do it, I tripped across a lot of information on plotting empty cells and realized that a lot of people struggled with this. So, of course, here is a post on how to handle plotting data when some of the data is missing.
Here is a simple example. Below is twelve months data on beverage sales. Almond milk sales did not begin until June. Soy milk proved unpopular and they stopped selling it in May. The company introduced goat milk in January and then decided it needed more work and reintroduced it in November.
Let’s start with looking at the chart. Right off the bat, you can see there is something wrong with the top gray line for organic milk as there is a break in the line. Cider looks to be starting in March even though there is January data. January goat sales do not even display on the chart.
First, sometimes people want to add zeroes. Lets’ see what it looks like if I do that for February cider sales.
Well, now the January sales show but look at that plunge. Adding zeroes is not recommended. Let’s see what alternatives there are.
In this example, below I have replaced the 0 with #N/A.
As you can see, it smoothed out without that excessive plunge where it was trying to display a zero.
Let’s try the same thing for the goat milk sales line.
Hmm.. not as perfect as it look as though. Goat milk was sold all year but better than what it was. Also, let’s face it, no one wants a lot of error values in their spreadsheets.
Another alternative that is the best, I think, is just show the empty cells as gaps or connect data points with a line.
To do this, right-click on the data in your chart and select Data…
Click on Hidden and Empty Cells.
If you look at the goat milk, when the cells are blank and I use Connect data points with line, it resembles the #N/A version but at least I do not have #N/A errors throughout my spreadsheet.
So, really you have a choice between showing gaps or connecting the lines.
I was reading Jon Peltier’s blog and he mentioned that Microsoft had updated the Hidden and Empty Cell Settings to handle #N/A. So, of course I immediately updated my Excel version and the new update suddenly included the Show #N/A as an empty cell. If you do not see that option, then you either have an earlier version of Excel or you need to update.
So, to finish this off and see how it looked, I put #N/A in all the empty cells, and then selected Connect Data points and put a checkmark in Show #N/A as an empty cell.
The results are below.
So, if you do have #N/As in cells, that option is a nice workaround.
Jon Peltier goes into much more detail and shows how the different choices look in a variety of chart formats so if you are looking for more information on this, I recommend you stop by Peltier Tech Blog.
And for you MAC users, if you right-click and Select Data…, you should see a similar dialog box.
Hopefully, my daughter’s project turned out well. For more information on computer software tips and tricks, see the full catalog of self-study CPE products.
