Dealing With Hidden & Empty Cells In #Excel Charts

Here’s how you can adjust your charts in Excel when they have hidden or empty cells:

You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a list of sales by year. Using the keyboard shortcut Alt + F1, I can create a default column chart:

hide1

If there were a couple years with no sales, and those cells were left blank, here is what the chart would look like:

hide2

That looks fine. But if I changed it to a line chart, here’s what happens:

hide3

The default for Excel in this instance is Gaps:

hide4

To access this dialog box, right-click on the chart and click on “Select Data…”. Then in the lower left-hand corner, click on “Hidden and Empty Cells”:

hide5

The 3 choices are:

Gaps: this will leave gaps in your chart as shown above
Zero: this will treat any blank or hidden cell as having a zero value
Connect data point with line: this will ignore the missing or hidden data and draw the line directly between the visible data. See the examples below:

Zero:

hide6

Connect data point with line:

hide7

In the second example, we have monthly and quarterly sales. Creating a similar default chart produces this:

hide8

If I move the quarterly sales to a secondary axis and make it a combination chart using columns for the monthly data and a line chart for the quarterly data, here is the result for each of the 3 options:

Gaps:

hide9
(Since there are so many missing data cells, there are no points for Excel to connect, so the line chart is non-existent)

Zero:

hide10

Connect data point with line:

hide11

The best option is probably the last one. Also, note the option “show data in hidden rows and columns”. This is useful and should be checked when you have data in rows or columns that are hidden and you want them displayed in your chart:

hide12

Now, go out there and make great charts!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

No comments yet.

Leave a Reply