Chart Hidden Data in Excel

Add to Favorites

How to show data from hidden rows and columns on a chart in Excel.

When you hide rows or columns of data, a chart will, by default, not display that data anymore.  Thankfully, there is a simple way around this.

The problem:

Steps to Show Hidden Data on Charts

  1. Select the chart:
  2. Go to the Design tab that appears when you select the chart and then click Select Data:
  3. In the window that appears, in the lower left corner, click the button Hidden and Empty Cells.
  4. Another smaller window will open.  In this window put a check next to where it says Show data in hidden rows and columns. Then, click OK.
  5. The first window will appear again. Click OK for that window as well and that is it.

We can still see our chart regularly:

But, now, also when the data for the chart is hidden:


This is a great little feature and I don't know why they make you go through so many steps just to enable it.

Download the sample file for this tutorial so you can see this feature in action in Excel.

Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Group Data Together for Increased Readability in Excel
Tutorial: How to group data together or collapse it in order to focus only on the important data in...
Prevent Charts from Printing in Excel
Tutorial: This is how you prevent a chart from appearing when you print from Excel. This is a gre...
Elegant Date and Location Filtering in Excel - A Must See!
Tutorial: Visually nice way to add Date and Location Filters in Excel. This combines Tables with Sl...
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
Macro: This free Excel macro filters data to display only those records or rows that contain a va...
Filter Data in Excel Without the Filter "Arrow" Appearing in the Filtered Column - AutoFilter
Macro: This Excel macro filters data in Excel without the filter "arrow" appearing in the column ...