Calculation Issues

How to Manage Hidden Rows and Columns in Excel Formulas and Charts

Emma Brown Sep 25, 2026 869 views

Question details

Understand the default behavior of Excel when processing hidden rows and columns, and learn how to control whether hidden data is included or excluded in formulas and charts.

How to Manage Hidden Rows and Columns in Excel Formulas and Charts

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating formulas or charts that reference a dataset containing manually hidden rows or columns.
Observed behavior: Standard formulas calculate hidden data by default, while charts automatically exclude hidden data from their display.

Before you start

Determine whether your data analysis requires hidden values to be included in total calculations, or if you need your charts and summaries to reflect only the visible data on your screen.

Solution 1Recommended

Use Functions That Ignore Hidden Rows

Standard functions like SUM or AVERAGE include hidden data. To exclude manually hidden rows from your calculations, use the SUBTOTAL or AGGREGATE functions.

The SUBTOTAL function is designed to ignore rows hidden by a filter. By using specific function numbers (101-111), it can also ignore manually hidden rows. AGGREGATE offers even more advanced options to ignore hidden rows, errors, and nested subtotals.

Select the target cell

Click the cell where you want the formula result to appear.

Enter the SUBTOTAL formula

Type =SUBTOTAL(109, A1:A10) to sum the range A1:A10. The '109' tells Excel to use the SUM function and to ignore manually hidden rows.

Use the AGGREGATE formula (Optional)

Alternatively, type =AGGREGATE(9, 5, A1:A10). Here, '9' specifies the SUM operation and '5' commands Excel to ignore hidden rows.

Execute the calculation

Press Enter. The cell will now display a total that recalculates dynamically, excluding any rows you choose to hide.

Function Numbers Matter: In the SUBTOTAL function, numbers 1-11 include manually hidden rows, while numbers 101-111 exclude them. Both ranges will ignore rows hidden by an AutoFilter.

Solution 2

Configure Charts to Include Hidden Data

By default, Excel charts do not plot hidden data. You can adjust the chart settings to force the chart to display data from hidden rows and columns.

Manage Data with WPS Spreadsheet

Easily Handle Hidden Data and Charts in WPS Office

WPS Spreadsheet provides robust, fully compatible functions like SUBTOTAL and AGGREGATE to handle hidden rows accurately. It effortlessly opens Excel files and offers intuitive chart data controls.

1. Open your data file: Launch WPS Spreadsheet and open your existing Excel workbook containing the hidden rows or charts.
2. Apply SUBTOTAL or AGGREGATE: Enter =SUBTOTAL(109, Range) to sum only the visible cells, exactly as you would in Microsoft Excel.
3. Adjust Chart Settings: Right-click your chart, choose 'Select Data', and toggle the hidden cells option to control exactly how your data is plotted.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Identical SUBTOTAL and AGGREGATE function logic for smooth file transitions.Easy-to-use chart data selection tools for handling hidden and empty cells.Lightweight software with a familiar, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUM formula still count hidden rows?

The standard SUM function is designed to calculate all cells within a specified range, regardless of their visibility. To sum only visible cells, you must use the SUBTOTAL function with function number 109, or use the AGGREGATE function.

Do filters affect formulas the same way as manually hiding rows?

Not exactly. When you use Excel's AutoFilter tool, the SUBTOTAL function (using numbers 1-11) will automatically ignore the filtered-out rows. However, if you manually right-click and hide a row, you must use function numbers 101-111 in SUBTOTAL to ignore them.

Why did my chart line drop or break when I hid a column?

By default, Excel charts exclude hidden rows and columns. When you hide a data point, the chart treats it as non-existent rather than zero, which may alter the line connecting data points. You can change this by going to Select Data > Hidden and Empty Cells and checking 'Show data in hidden rows and columns'.

Can I make PivotTables ignore hidden rows?

PivotTables do not ignore manually hidden rows in their source data during a refresh. To remove specific data points from a PivotTable, you should use the PivotTable's built-in filtering options rather than hiding rows in the raw data sheet.

Excel & Data Problems