Recently an associate called me with an Excel 2010 problem. She was asked to create an elegant worksheet that pulled data in from an external database, did calculations, and then charted the results as well as creating a columnar report. Sounds pretty easy so far.
The problem she ran into was that when she hid data in the Excel workbook her Excel chart content disappeared. How could she show or hide data and still have the chart show the results? When the data appeared on the sheet, the chart data displayed as in this figure.
When she hid the data by hiding columns K and L, then the chart data disappeared as in this figure.
It turns out it’s a pretty straightforward problem to fix, if you know where the right dialog box is. To fix this, select the chart. A group of Chart Tools menus will display on the ribbon.
- On the Design tab, in the Data group, click Select Data.
- The Select Data Source dialog displays.
- Click the Hidden and Empty Cells button to display the Hidden and Empty Cell Settings.
- In the Hidden and Empty Cell Settings dialog box, click the Show Data in Hidden Rows and Columns box, then click Ok.
Now you can hide or display data in Excel and still the chart.