Charts will often “lose” their information when the underlying data that drives the chart is hidden. The data can be hidden either by manually hiding rows or columns or may become hidden when using groups.
Let’s create a simple chart by highlighting our data (cells A3:B8) and then press the key combination ALT‑F1.
This inserts the default chart type, most likely the 2D Clustered Column chart.
If we were to select columns A & B, then right-click in the highlighted area and click “Hide”, observe what happens to the chart.
To prevent this from happening, we need to adjust one simple setting.
Click anywhere on the chart and from the ribbon select Chart Tools – Design (tab) -> Data (group) -> Select Data.
In the Select Data Source dialog box, click the button in the lower left labeled “Hidden and Empty Cells”.
In the Hidden and Empty Cell Settings dialog box, check the box labeled “Show data in hidden rows and columns”.
This will return your chart to its former glory.
This feature will also prevent your chart from disappearing if you are working with filtered or grouped data.