Cell PhonesComputersConsumer ElectronicsGraphic Design & Video EditingHome Theater & AudioIndustrial TechnologyInternet

How to Get Excel 2007 and 2010 to Ignore Empty Cells in a Chart or Graph

Updated on May 10, 2016

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)

Example of data with many blank cells (left), the initial chart created (top right) and the final corrected chart (bottom right).
Example of data with many blank cells (left), the initial chart created (top right) and the final corrected chart (bottom right). | Source

If you would like to learn more about creating charts and graphs, I have a beginner's guide that you can find here that will assist you with all aspects of creating graphs and charts:

http://robbiecwilson.hubpages.com/hub/Creating-charts-and-graphs-in-Excel-2007

A stunning Thermometer chart created using Excel 2007 or Excel 2010.
A stunning Thermometer chart created using Excel 2007 or Excel 2010. | Source

In addition to a general guide on how to create charts and graphs, I have also written articles on how to create step by step, specific types of charts. Creating spectacular graphs can really make your presentations or worksheets stand out from the crowd.

Firstly, I have an article that shows you how to create a beautiful thermometer chart (right) which is perfect for illustrating progress towards a goal (they are particularly popular for fund raisers). My article on thermometer charts can be found here:

I have also created an article which illustrates how to create Gantt charts in Excel. A Gantt chart is a Project Management tool used to show progress both for the project overall as well as the individual tasks that make up the overall project. My article introducing the Gantt chart can be found here:

A beautiful Gantt chart created using Excel 2007 or Excel 2010.
A beautiful Gantt chart created using Excel 2007 or Excel 2010. | Source

Configuring how Excel treats Hidden and Empty Cells to create a chart that correctly ignores zero values and empty cells in Excel 2007 and Excel 2010

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

Illustration of a chart using the default settings created from a data range containing many gaps in Excel 2007 and Excel 2010.
Illustration of a chart using the default settings created from a data range containing many gaps in Excel 2007 and Excel 2010. | Source

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

Configuring how Excel treats gaps in data when creating charts in Excel 2007 and Excel 2010.
Configuring how Excel treats gaps in data when creating charts in Excel 2007 and Excel 2010. | Source
  • 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.

Chart created using the Zero option (left) and the Connect data points with line (right) option when configuring how Excel 2007 and Excel 2010 deals with empty cells.
Chart created using the Zero option (left) and the Connect data points with line (right) option when configuring how Excel 2007 and Excel 2010 deals with empty cells. | Source
  • 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.

Using the IF function in a formula to convert empty cells to #N/A in Excel 2007 and Excel 2010.
Using the IF function in a formula to convert empty cells to #N/A in Excel 2007 and Excel 2010. | Source
  • 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

Chart created with the data range containing #N/A in cells rather than being blank in Excel 2007 and Excel 2010.
Chart created with the data range containing #N/A in cells rather than being blank in Excel 2007 and Excel 2010. | Source

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)

The finished graph, created in Excel 2007 and Excel 2010 with Excel configured to ignore gaps in our data.
The finished graph, created in Excel 2007 and Excel 2010 with Excel configured to ignore gaps in our data. | Source

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 hub useful and informative. Please feel free to leave any comments you may have below and many thanks for reading!

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      hey 9 months ago

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

    • profile image

      J Reilly 11 months ago

      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.

    • profile image
      Author

      Robbie C Wilson 4 years ago

      Hi Haseena,

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

    • profile image
      Author

      Robbie C Wilson 4 years ago

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

    • jabelufiroz profile image

      Firoz 4 years ago from India

      Impressive. Voted up.

    • Haseena Firdousia profile image

      Haseena 4 years ago from India

      really nicely expalined..voted up and useful.

    Click to Rate This Article