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:


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


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


The default for Excel in this instance is Gaps:


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”:


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:



Connect data point with line:


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


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:


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



Connect data point with line:


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:


Now, go out there and make great charts!

Happy Excelling!

Free Download!

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

No comments yet.

Leave a Reply