Software

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.

About

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.

1 comments
iltermutlu
iltermutlu

Thank you for your explanation especially for 2007 version one. This method works also in 2010 and 2013

Editor's Picks