Excel: Hide Columns Without Affecting Your Chart View

Yes. In Excel for Microsoft 365 on Windows, select the chart, go to Chart Design > Select Data > Hidden and Empty Cells, and select Show data in hidden rows and columns. That lets a standard chart continue plotting values from columns you hide; if you also want the chart object itself to stay in place, adjust its cell-positioning properties separately.

What hiding columns does to a chart

Hiding a worksheet column does not delete its contents or remove the cells from a chart’s source range. However, standard Excel charts normally omit data located in hidden rows or columns. This default can make it look as though hiding a column has changed the chart, even though the source values and chart references remain in the workbook.

The chart option changes what the chart displays, not the worksheet. With Show data in hidden rows and columns enabled, the chart can plot data from cells that are still hidden. You can hide columns to make a sheet easier to read or to keep supporting calculations out of view without having to move the chart’s source data elsewhere.

There are two possible meanings of “affecting the chart”: changing the plotted data, or changing the chart’s position and size on the sheet. The hidden-data setting addresses the first. If the chart shifts or resizes when you hide columns, its object-positioning setting is the relevant control.

Turn on plotting for hidden columns

Use these steps for a regular chart in Excel for Microsoft 365 on Windows:

Click the chart whose data should remain visible. Selecting it reveals the Chart Design tab on the ribbon.
Choose Chart Design > Select Data.
In the Select Data Source dialog box, choose Hidden and Empty Cells.
Select Show data in hidden rows and columns, then choose OK. Close the Select Data Source dialog box if it remains open.
Hide the worksheet columns as usual. For example, select the column headings, then use Home > Format > Hide & Unhide > Hide Columns.

To undo the behavior, return to the same dialog and clear the checkbox. The setting is associated with the chart, so check each chart that needs the same treatment; do not assume changing one chart updates every other chart in the workbook.

If you cannot see Chart Design, first click a chart rather than a cell. If the chart is selected but the command is unavailable, check whether it is a PivotChart or whether you are using a version or platform with a different feature set. Excel for the web may not expose every chart-formatting dialog available in the Windows desktop app, so use desktop Excel when you need the full hidden-cell setting.

Keep the chart object from moving or resizing

A chart placed over worksheet cells is a floating object. Its relationship to the cells beneath it can determine what happens when columns are hidden, inserted, or resized. To change that relationship in desktop Excel, right-click the chart border, choose Format Chart Area, and open Size & Properties in the formatting pane. Under Properties, choose the behavior that suits the layout.

Move and size with cells: the chart can move and change size along with the cells it is positioned over.
Move but don’t size with cells: the chart can move with worksheet cells but retains its size.
Don’t move or size with cells: hiding or resizing worksheet columns will not move or resize the chart object.

These choices concern the chart’s position and dimensions, not which values it plots. For example, a chart can be set not to move or resize and still omit data from hidden columns if Show data in hidden rows and columns is unchecked. Conversely, it can display hidden-column data while still shifting position when a column is hidden. Set both controls if both outcomes matter.

A chart that does not move may end up visually covering a different part of the sheet after columns are hidden. That is not a data error; it is a layout consequence. Review the sheet after changing column visibility, especially if colleagues use the chart and source table together.

Choose the right setting for the job

Whether a chart should plot hidden columns depends on why you are hiding them. If the columns contain legitimate data that should remain part of a report, turn on the hidden-data option. If you are hiding columns specifically to exclude them from the chart, leave the option off. The worksheet visibility setting is not a substitute for deliberately selecting the chart’s data series.

Keep a complete trend chart: If monthly values are in columns B through M and you hide columns containing earlier months to declutter the worksheet, enable plotting hidden data when the full-year trend should remain visible.
Show only a selected period: If hiding earlier months is intended to narrow the chart to the visible period, leave the option disabled or change the chart’s source range intentionally.
Hide helper data: If a chart uses a calculation column that supports the display, but the calculation should remain plotted, enable the setting. If it is only an intermediate result and should not appear, remove it from the chart series instead.

For an important report, it is usually clearer to control inclusion through the chart’s source range or series than to rely on hiding columns as a way to change the message. Worksheet visibility is easy for another editor to change, and the chart may show different information depending on its hidden-data setting.

Hidden cells, filtered data, and blank cells

Hidden columns and filtered rows are related but not identical situations. The chart dialog’s option explicitly refers to hidden rows and columns. Filtering a table changes which records are displayed, and charts based on filtered data can behave according to their source and chart settings. If your goal is to show only records that meet a condition, use a filter or a deliberately filtered chart source, then check the result rather than assuming that hiding a column and filtering a row have identical effects.

The same dialog also has controls for empty cells. These govern how the chart treats gaps in its source data; for example, a line chart can display empty cells as gaps, zeros, or connected points, depending on the available choice. Those controls are separate from whether hidden data is plotted. Changing the empty-cell treatment does not, by itself, make hidden-column values appear.

For a chart with several series, inspect the source range and each series if the result is unexpected. One series might reference a different set of columns, or a category-label range might include hidden cells even when the values do not. The Select Data Source dialog can help you confirm which worksheet ranges the chart uses.

Common problems and practical checks

The checkbox is on, but the chart still looks wrong: Confirm that the hidden cells are actually part of the chart’s source range. The setting cannot add cells that the chart does not reference.
The chart loses a category or series: Reopen Chart Design > Select Data and inspect the series and category ranges. A range may end before the hidden columns or may point to a different worksheet.
The chart moves after columns disappear: Change the chart object’s Properties setting as described above. This is independent of the hidden-data checkbox.
Only one chart updates: Select each other chart and inspect its own Hidden and Empty Cells options. Chart settings need not be shared across separate charts.
A line has an unexpected break: Check whether the source cells are empty, contain formulas returning an empty string, or are hidden. Empty-cell handling and hidden-data handling are separate, so test the relevant option with the actual source.

A useful check is to make a small temporary change: hide one source column, compare the chart, then unhide it. If the chart changes only when the hidden-data checkbox changes, the issue is the display setting. If the chart stays the same but shifts on the sheet, inspect object positioning. Avoid editing source values just to test visibility; it can create a different problem from the one you are diagnosing.

Alternatives when hiding is not enough

If you need precise control over what a chart shows, adjust the chart data source rather than using column visibility as a proxy. In Chart Design > Select Data, you can edit a series or its range, remove a series, or choose a different source range. This makes the intended chart content more explicit for anyone maintaining the workbook.

Another option is to keep the chart’s source data on a separate worksheet and hide that worksheet if the visible report should be uncluttered. A chart can reference data on another sheet, but the same hidden-data behavior may matter if the source cells or sheet are hidden, so check the chart’s setting. If you need users to choose which values appear, consider an interactive control or a filtered source table rather than manually hiding columns each time.

These approaches serve different purposes. Hiding columns is quick and reversible for worksheet layout. Editing a chart range is more direct when changing the chart’s content. A separate source sheet can make a presentation sheet cleaner, while a filter or interactive design is more suitable when the displayed subset changes regularly.

Frequently Asked Questions

Does hiding a column delete its data from an Excel chart?
No. Hiding a column leaves its cell values and chart references intact. By default, a standard chart does not display data in hidden rows or columns; enable Show data in hidden rows and columns to plot it.

Can I hide columns but keep the chart showing all the data?
Yes. Select the chart and enable Chart Design > Select Data > Hidden and Empty Cells > Show data in hidden rows and columns. Hide the worksheet columns afterward or leave them hidden.

Why does my chart move when I hide columns?
The chart object is positioned in relation to worksheet cells. Right-click its border, choose Format Chart Area, and change Size & Properties > Properties to Don’t move or size with cells if it should stay fixed.

Does the hidden-data setting apply to every chart?
Do not assume it does. Select each chart you need to check and review its Hidden and Empty Cells setting. Separate charts can have different sources and settings.

Can I use this setting in Excel for the web?
Excel for the web may not provide the same chart dialog as Excel for Windows. If you cannot find Hidden and Empty Cells, open the workbook in the Microsoft 365 desktop app, change the chart setting there, and then save the workbook.

Will the setting make filtered-out records appear?
Not necessarily. The option is specifically for data in hidden rows and columns, while filtering changes which records are displayed. Check the chart’s source and behavior with the filter you are using instead of treating filtering and hiding as interchangeable.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.