You can add a chart in Excel, based on worksheet data, like this sales chart example.
But, if you filter the data, and rows are hidden, that data also disappears from the chart. You might like that feature, and not mind that the chart changes. In the example shown below, only the West region’s data is visible on the sheet and in the chart.
However, there are some situations when you want the chart to show the data, even if the data is hidden. In the next example, columns A:C are hidden, so that they don’t distract from the chart. Oops! Now there’s nothing in the chart. That’s not the effect that you were going for.
Change a Chart Setting to Show Hidden Data
If you want your chart to show all the data, even if some of the source data is hidden, you can change one of the chart settings. The Excel 2010 instructions are shown below, and you can click here for instructions to show hidden data in Excel 2003 charts.
To change the setting in Excel 2010:
- Click on the chart to select it, and on the Excel Ribbon, under Chart Tools, click the Design tab
- Click the Select Data command
- Click the Hidden and Empty Cells button
- Add a check mark to ‘Show data in hidden rows and columns’
- Click OK, twice, to close the dialog boxes
Now, you can hide rows or columns, and the chart data will remain visible.
Test the Hidden Data Feature
To see the difference this option setting makes, you can test the feature in this embedded Excel file. Filter the Region, and one chart continues to show all the data, but the other chart has hidden data.
Watch the Show Hidden Data in Chart Video
To see the steps for changing the chart settings in Excel 2010, you can watch this short video tutorial.