When you update a PowerPoint chart that pulls data from an Excel worksheet, you may notice that rows or columns you hid in Excel still appear in the chart. This happens because PowerPoint has a specific setting that controls how hidden data is handled. The toggle is called Plot data from hidden cells and it lives inside the Select Data Source dialog. This article explains exactly what this toggle does, how it changes the chart when you turn it on or off, and how to configure it so your charts show only the data you want.
Key Takeaways: Hidden Cells in PowerPoint Chart Data
- Select Data Source > Hidden and Empty Cells > Show data in hidden rows and columns: Controls whether Excel rows or columns you hid are plotted in the PowerPoint chart.
- Unchecking the box hides hidden data from the chart: When unchecked, any row or column hidden in the source Excel sheet is skipped and does not appear as a series or category.
- Checking the box shows hidden data in the chart: When checked, hidden rows and columns are still plotted, which can cause unexpected data to appear if you later hide rows for filtering purposes.
Why PowerPoint Charts Can Show Data You Thought You Hid
PowerPoint charts are linked to an Excel worksheet, either embedded in the presentation or linked to an external file. When you create a chart, PowerPoint reads all the data in the source range, including rows and columns that you later hide in Excel. By default, PowerPoint is set to show data from hidden cells. This means that if you hide a row in Excel to remove it from a printed report or a dashboard view, that same row still appears in the PowerPoint chart. The toggle is not in the main ribbon or the Format pane. It is located inside a secondary dialog called Hidden and Empty Cell Settings, which you reach through the Select Data Source dialog.
The behavior is by design. Many users hide intermediate calculation rows in Excel but still want those values included in the final chart. Other users hide rows to filter data visually and expect the chart to update accordingly. The toggle exists to serve both workflows. Understanding which state the toggle is in is the only way to predict whether hiding a row will remove it from the chart or leave it visible.
How the Toggle Affects Chart Series and Categories
When the Show data in hidden rows and columns box is checked, PowerPoint treats hidden cells the same as visible cells. The chart includes every row and column in the source range regardless of the hidden state. When the box is unchecked, PowerPoint skips any row or column that is hidden in the source worksheet. The chart recalculates and removes the corresponding series or category from the plot. The axis labels and legend update to reflect only the visible data.
Steps to Change the Hidden Cells Toggle in a PowerPoint Chart
Follow these steps to turn the hidden cells toggle on or off for an existing PowerPoint chart.
- Select the chart in the PowerPoint slide
Click once on the chart to activate the Chart Tools contextual tabs on the ribbon. You will see two extra tabs: Design and Format. - Open the Select Data Source dialog
Go to the Chart Design tab on the ribbon. In the Data group, click Select Data. The Select Data Source dialog opens. - Open the Hidden and Empty Cell Settings dialog
In the bottom-left corner of the Select Data Source dialog, click the Hidden and Empty Cells button. A small dialog appears with two options. - Check or uncheck the hidden cells toggle
In the Hidden and Empty Cell Settings dialog, locate the checkbox labeled Show data in hidden rows and columns. Check the box to include hidden data in the chart. Uncheck the box to skip hidden data when plotting the chart. - Confirm and close both dialogs
Click OK in the Hidden and Empty Cell Settings dialog. Then click OK in the Select Data Source dialog. The chart updates immediately to reflect the new setting.
What Happens When You Toggle the Setting
If you uncheck the box, any row or column that is currently hidden in the linked Excel worksheet will disappear from the chart. If you check the box, those hidden rows and columns reappear. The chart does not change the underlying data. It only changes which data is plotted. The source range in Excel remains the same.
Common Issues When Hidden Data Behaves Unexpectedly
PowerPoint Chart Shows Extra Bars or Lines After Hiding Rows in Excel
This is the most common complaint. You hide a row in the source Excel worksheet expecting the chart to update, but the bar or line remains. The cause is the Show data in hidden rows and columns checkbox being checked. Uncheck it using the steps above, and the chart will match what you see in the filtered Excel view.
PowerPoint Chart Disappears or Shows Gaps After Unchecking the Toggle
If you uncheck the toggle and the chart loses most of its data, it means many rows or columns in the source range are hidden. This can happen if you used Excel grouping or filters that hide rows visually. To fix this, go back to Excel and unhide the rows or columns that should appear in the chart. Then refresh the chart in PowerPoint by clicking the chart and pressing Ctrl+Shift+F9 to refresh the link.
Hidden Cells Toggle Is Grayed Out or Unavailable
The toggle is only available when the chart is linked to an Excel worksheet. If your chart uses data typed directly into the PowerPoint datasheet, the hidden cells option does not appear. To use this feature, you must have a chart that references an Excel range, either embedded or linked. If the button is grayed out, recreate the chart using data from an Excel worksheet instead of the internal datasheet.
PowerPoint Chart Hidden Cells Toggle: Show vs Skip Comparison
| Item | Box Checked (Show Hidden Data) | Box Unchecked (Skip Hidden Data) |
|---|---|---|
| Chart behavior | Plots all rows and columns in the source range, including hidden ones | Plots only visible rows and columns; hidden ones are omitted |
| Effect of hiding a row in Excel | Chart does not change; the row still appears | Chart updates immediately; the row disappears |
| Use case | Intermediate calculations or reference rows that must stay in the chart | Filtered views, dashboards, or reports where hidden rows should not appear |
| Impact on chart legend | Legend includes all series even if source rows are hidden | Legend shows only visible series |
| Refresh after unhiding rows | Rows reappear in chart automatically | Rows reappear in chart automatically |
Now you can control exactly how PowerPoint charts react when you hide rows or columns in the source Excel worksheet. Use the Select Data Source dialog and the Hidden and Empty Cell Settings button to check or uncheck Show data in hidden rows and columns. If you frequently hide rows in Excel for filtering, keep the box unchecked so your charts stay in sync. If you hide rows only for calculation purposes and need them in the chart, keep the box checked. As an advanced tip, you can also set this toggle per chart, meaning different charts on the same slide can behave differently with the same source data.