Robbie mostly writes about Skyrim but also occasionally sheds light on the oddities of Microsoft applications such as Excel and Outlook.

How to Configure Excel 207 and Excel 2010 to Ignore Empty Cells When Creating a Chart or Graph

Hi, and welcome to my latest article on creating charts in Excel. Today, I will look at a problem that I had myself, how to create a chart when most of the cells in the range that you want to chart are blank (contains empty cells).

In order to resolve this and create a chart that displays the data correctly, you may be tempted as I have done in the past to copy the data values to a new range so that the data is contiguous (without gaps). This can be extremely time consuming and very annoying, especially if the amount of data is large. Excel is supposed to make our collective lives easier, so I decided to look for a solution to this problem that involved as little extra manual work as possible.

In my case, and the example I will use for today’s article, I am interested in charting the weekly averages of the number of daily sales that my team made. When I created a chart by selecting the data range, all I got was chart with no data.

We will look at two methods to resolve this:

1. First, how to change the way the chart deals with blank or empty cells
2. Secondly, how to use a formula to change the contents of empty cells to #N/A (Excel ignores cells with #N/A automatically when you create a chart)

In the figure below, you can see:

• The data I use to create the charts (on the left)
• The blank initial chart (top right)
• The completed chart (bottom right)

Configuring How Excel Treats Hidden and Empty Cells

The first method we are going to use to create a chart that displays the data correctly is to change the way Excel 2007 and Excel 2010 treat empty cells in graphs.

By default in a chart, when Excel sees an empty cell it creates a gap. When you have a data range with more empty cells than cells with data this means that Excel effectively creates a graph full of gaps.

To begin, we need to create a chart of the average weekly sales.

• We create a line graph by selecting the data in B3:B31 and D3:D31 (click and hold the Ctrl key to select columns that are not adjacent)
• Click the Insert tab and select the Line button in the Charts group and select Line

Now that we have the graph created, we need to change the way Excel treats empty cells. To do this:

• Right click the chart and choose Select Data
• Click Hidden and Empty Cells

• Gaps is the default setting as we discussed before
• Zero in our example will result in data being charted as value, zero, zero, zero, zero, zero, zero, value
• Connect data points with line does exactly as it says, connects data points together

The below figure shows our graph created using Zero (left) and Connect data points with line (right). Gaps we already saw with the blank chart above.

• I also tidied up the horizontal axis by right clicking it and selecting Format Axis
• Under the Axis Options tab, I changed the Major unit to Fixed 7 Days
• I changed the Position Axis to On tick marks so that it showed the full date

Using a formula to convert empty cells to #N/A so that Excel 2007 and Excel 2010 charts data correctly

The second method of ensuring that Excel charts data containing large numbers of gaps or empty cells correctly is to use a formula. To achieve this, we will use the fact that when Excel sees #N/A in a cell, it does not include it in a chart. The formula we will use in E3 is:

=IF(D3="", #N/A,D3)

The IF function looks at the contents of cell D3 and IF D3 equals “” (in other words a blank cell) then it changes the current cell to #N/A. If Excel finds something in the cell, it will simply copy the cell. For example, it will copy the contents of D3 to E3.

I have an article that discusses the IF function in greater detail. I also cover using IFERROR to suppress expected errors and using IF with the logical functions AND, OR and NOT. It can be found here:

• Next, I copy the formula down the E column.

• I then create a chart exactly as we did above using the dates in column B and the results of the formulas in column E

As you can see from the figure above, the chart correctly displays the data, even though Excel is using Show empty cells as Gaps (the default setting for all charts created in Excel 2007 and Excel 2010).

We would then tidy up the chart as we did above,

• Tidying up the horizontal axis
• Deleting the Legend
• Adding a Title (click the Layout tab with the graph selected, choose the Chart Title button and select the title type you prefer)

Conclusion

Often data comes into you from spreadsheets that you have no control over and is not always formatted exactly how you would like it. Rather than re-edit the data to fit what you want it to do, it is always much better and quicker to have Excel work around any formatting challenges the data may have and produce the results that you want without significant manual editing on your part.

In today’s article, we looked at an issue that I come across often:

How to create charts using data that contains large numbers of blank cells. We looked at two methods of how to resolve this:

• Changing the way Excel treats empty cells in the chart itself
• Using IF statements to change blank cells to #N/A (Excel 2007 and Excel 2010 ignore cells containing #N/A when creating charts)

Using IF statements in a formula is more flexible than changing Excel’s way of dealing with empty cells in a chart as you can ask it to change anything to #N/A, not just empty cells. This means that if you receive spreadsheets with junk or data you don’t want you can also get Excel to ignore it for the purposes of creating a chart from the data.

I do hope that you have found this article useful and informative. Please feel free to leave any comments you may have below and many thanks for reading!

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

© 2013 Robbie C Wilson

Comments

Mitja on January 14, 2019:

Hi. This works, however my problem is that after all of this is done, my chart displays NA() in the chart legend which is really anoying. How do you remove it?? That only happens at my work where we have 2013 office. At home where I use 2019, it works as it should.

Farzad on September 30, 2018:

Thank you, it was straightforward

Srikanth on January 22, 2018:

the #N/A is really helpful, the show hidden button in the select data is not helpful enough

Femme on November 02, 2017:

In my case it worked like a charm in a scatter plot with one set of X-values and many Y-series. The outcome of a formula being "" is treated as a zero value, not as an empty cell. With the outcome #N/A the line stops at the last generated valid value, which is just the behavior I needed. But I agree with J Reilly, it should be possible to generate an empty cell. Sometimes you want the line to be interrupted for empty cells which is the "Gaps" behavior for cells that are really empty. The example above shows the "Connect data points with line" behavior.

hey on October 13, 2016:

The #NA trick does not work for my charts. It is still desplaying the #NA cells as blank

J Reilly on August 15, 2016:

Formula =IF(D3="", #N/A,D3) is incorrect, should read =IF(D3="", #N/A,E3).

Also you micely avoid the stupid behaviour of Excel relating to Gaps. What I would see is that if there is a null cell (impossible to get with a formula) the graph should show nothing (not a joining line). However, even with NA() function, it still shows a value of zero.

Robbie C Wilson (author) on June 10, 2013:

Hi Haseena,

Thanks so much for your feedback, I am really glad that you found it useful.

Robbie C Wilson (author) on June 10, 2013:

Thanks for your kind comment Jabelufiroz, glad that you enjoyed it.

Firoz from India on June 10, 2013:

Impressive. Voted up.

Haseena from India on June 10, 2013:

really nicely expalined..voted up and useful.