How to Get Excel 2007 and 2010 to Ignore Empty Cells in 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)

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

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)

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!