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.