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!

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • profile image

        Farzad 

        2 months ago

        Thank you, it was straightforward

      • profile image

        Srikanth 

        10 months ago

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

      • profile image

        Femme 

        13 months ago

        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.

      • profile image

        hey 

        2 years ago

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

      • profile image

        J Reilly 

        2 years 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 imageAUTHOR

        Robbie C Wilson 

        5 years ago

        Hi Haseena,

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

      • profile imageAUTHOR

        Robbie C Wilson 

        5 years ago

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

      • jabelufiroz profile image

        Firoz 

        5 years ago from India

        Impressive. Voted up.

      • Haseena Firdousia profile image

        Haseena 

        5 years ago from India

        really nicely expalined..voted up and useful.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)