When you create a chart from a data table, Excel does its best to translate the numeric values into data points on a chart, according to the specifications you provide. One area where Excel doesn't quite know what to do, however, is empty cells. If a cell is empty, it could be for any number of reasons—the value isn't available, the value isn't important, or the value is really zero.
You can instruct the program how you want it to treat empty cells by following these steps:
Figure 1. The Select Data Source dialog box.
Figure 2. The Hidden and Empty Cells dialog box.
The option buttons at the top of the Hidden and Empty Cell Settings dialog box (step 5) provide the following three settings:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6289) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Controlling the Plotting of Empty Cells.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
You want your chart to display information as clearly and succinctly as possible. Modifying the labels used to indicate ...
Discover MoreExcel allows you to add all sorts of objects to your worksheets. Among the objects you can add are text boxes, shapes, ...
Discover MoreOne type of chart that Excel allows you to create is one that occupies an entire worksheet. When it comes time to print ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-02-19 14:38:46
Craig Brummell
If you have formulas in the blank cells this will not work and the graphs take it as a zero and display the line, no matter what the setting.
2017-01-07 08:02:50
Walt
That solution might work for some but my MS Office Excel which has all the same icons or boxes as in the example doesn't cooperate.
The details are: Windows 7 Home Premium.
Any suggestions?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments