Display hidden data in an Excel chart

Excel won't display hidden data in a chart unless you flip the switch. Learn which option controls this behavior.

By default, Excel displays only visible data in a chart. Consequently, if you hide worksheet data, Excel won't display that data in a chart. For instance, it's obvious from a quick look at the following chart's legend that Tuesday's data is missing: Most of the time, that's probably what you'll want. After all, if you went to the trouble to hide the data at the worksheet level, it's doubtful that you'll want to expose the data in a chart. However, it's easy enough to display the hidden data for those times when you do. To display hidden data in a chart, do the following:

  1. Select the chart.
  2. From the Tools menu, choose Options.
  3. Click the Chart tab.
  4. In the Active Chart section, clear the Plot Visible Cells Only Option.
  5. Click OK.

In Excel 2007, do the following:

  1. Select the chart.
  2. Click the Design tab.
  3. Click Select Data in the Data group.
  4. Click the Hidden And Empty Cells button (at the bottom).
  5. Select the Show Data In Hidden Rows And Columns option.
  6. Click OK twice.

Excel displays the hidden data in the chart without unhiding the data in the worksheet. For example, the modified chart below displays Tuesday's data even though that data is still hidden in the worksheet.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks