Excel SUBTOTAL: Does It Ignore Hidden Rows?

How-to basics · Updated November 18, 2026

Yes, Excel’s SUBTOTAL can ignore hidden rows, but the result depends on its function number. Use 101–111 to exclude rows hidden manually; numbers 1–11 include manually hidden rows. Both sets of numbers ignore rows removed by a filter.

How SUBTOTAL treats hidden rows

SUBTOTAL is designed to calculate a result for a range that may be filtered or partially hidden. Its behavior has two parts: filtered-out rows are always excluded, while manually hidden rows are handled according to the function number. This distinction is useful when a worksheet has a filter as well as rows hidden with Excel’s Hide command or an outline control.

For example, function number 9 means SUM and includes manually hidden rows. Function number 109 also means SUM, but excludes manually hidden rows. The leading 1 in the second group changes how hidden rows are treated; it does not change the calculation itself.

  • Function numbers 1–11: Exclude rows hidden by a filter, but include manually hidden rows.
  • Function numbers 101–111: Exclude rows hidden by a filter and rows hidden manually.

In both groups, the final digit identifies the operation. For instance, 1 and 101 calculate an average, 2 and 102 count numeric values, and 9 and 109 sum values. Choose the function number based on whether manually hidden records should remain part of the answer.

Example: sum visible rows only

Suppose a worksheet has sales amounts in B2:B7: 10, 20, 30, 40, 50, and 60. If you manually hide row 3, which contains 20, a subtotal using 9 still includes that amount. To sum the range while including manually hidden rows, with the sales figures in B2:B7, use:

=SUBTOTAL(9,B2:B7)

The result is 210, because 10 + 20 + 30 + 40 + 50 + 60 = 210. To exclude the manually hidden row, with the same figures in B2:B7, use:

=SUBTOTAL(109,B2:B7)

The result is 190: 210 minus the hidden row’s 20. If you instead apply a filter that removes a record from view, both formulas omit that filtered-out record. This makes 109 a useful choice for a visible-rows-only total when users may hide rows manually as well as filter the data.

The word “visible” needs a little care. SUBTOTAL’s 101–111 group excludes rows hidden manually, but it does not turn every kind of visual formatting into a rule for inclusion. A cell with white font, a collapsed column, or a custom number format is not the same thing as a hidden row.

How to enter the formula in Excel

In Excel for Microsoft 365 on Windows, select the cell where the result should appear, type the formula, and press Enter. Replace B2:B7 with the range containing the values you want to summarize. Keep the subtotal cell outside the range being summarized; otherwise the formula may include itself or produce an unexpected result.

To hide a row manually, right-click its row heading and select Hide. To show it again, select the row headings on either side of the hidden row, right-click, and choose Unhide. The equivalent row commands are available in Excel for Mac, although the wording and layout of contextual menus can vary slightly. The SUBTOTAL function numbers work the same way on Windows and Mac.

To filter a range, click in the data and choose Data > Filter. Use the dropdown in a column heading to select which records to display. SUBTOTAL recalculates when the filter changes, so you can use one formula to get a total for the currently displayed records rather than creating a separate total for every filter choice.

Choosing the right function number

Start by deciding what “ignore hidden rows” means for your worksheet. If you mean only records excluded by a filter, either function-number group will omit those records. If you also want to omit rows hidden manually, use 101–111. For a sum, that usually means choosing between 9 and 109.

  • Use 9 when a hidden row should still count in the sum, such as a temporarily concealed detail row that remains part of a report total.
  • Use 109 when the total should reflect only rows currently visible, including when some rows are hidden with Hide or an outline.
  • Use 1 or 101 for the same distinction with an average, rather than a sum.
  • Use the corresponding code for another operation by keeping its final digit and choosing the appropriate group.

Do not select 109 just because it looks like a more advanced version of 9. It deliberately returns a different answer when manually hidden rows contain values. In a financial or operational report, decide which interpretation matches the reporting rule, then make that choice clear to other people who use the workbook.

Filters, manual hiding, and grouped rows

Filtering and hiding are different ways to make rows disappear. A filter hides records that do not meet the selected criteria, such as showing only the East region. SUBTOTAL excludes those records whether its function number is 9 or 109. Clearing the filter makes those records visible again, and the result updates accordingly.

Manual hiding is a separate action: a person selects one or more row headings and chooses Hide. With function number 9, values in those rows remain in the result even though the rows cannot currently be seen. With 109, they do not. Rows hidden by collapsing an outline or grouped section are also treated as hidden rows for the 101–111 behavior.

This can explain an apparent mismatch between a displayed list and its total. If someone hides a row manually but the formula uses 9, the displayed numbers may add to less than the reported total. That is not necessarily a calculation error: the formula is following its function-number rule. Check whether rows are filtered or manually hidden before changing the formula.

SUBTOTAL versus SUM and table totals

SUM adds values in its specified range without adapting its result to filtered-out rows. SUBTOTAL is often a better fit for a filtered list because it excludes filtered records. It also has a useful safeguard: when a SUBTOTAL formula encounters another SUBTOTAL formula within its reference, it ignores that nested subtotal. This helps prevent double-counting in worksheets with several sections and intermediate totals.

An Excel table can make this behavior easier to use. Select a cell in the table and choose Table Design > Total Row to display a total row. In the total-row cell, use the dropdown to choose an operation such as Sum. Excel uses SUBTOTAL for table totals so that the result responds to filtering. If you need a particular hidden-row rule, inspect the generated formula and confirm that its function number matches your intention.

SUM is still appropriate when the result should always include every value in a range, regardless of filtering. The choice is about behavior, not which function is universally better: use SUM for a fixed range total, and SUBTOTAL when filter-aware results or hidden-row handling matter.

Common problems and checks

If a subtotal seems wrong, check the formula number first. A formula using 9 will include manually hidden rows; changing it to 109 changes that behavior. Then check whether the row was filtered out or hidden manually, since both groups exclude filtered rows but only the 101–111 group excludes manually hidden ones.

  • Check the range: Make sure the reference includes the intended data rows and not an unrelated value or the result cell.
  • Check the operation: 9 sums values, while 1 averages them. A correct range with the wrong function number still gives the wrong kind of result.
  • Check the data: Text that looks like a number may not be treated as a numeric value. Confirm that imported or pasted amounts are stored as numbers.
  • Check nested totals: SUBTOTAL ignores other SUBTOTAL results within its range, so a total over section subtotals may be lower than expected by design.
  • Check hidden columns separately: The 101–111 codes are for hidden rows. Do not rely on them to exclude values merely because their columns are hidden.

Also remember that filtering does not delete records. A SUBTOTAL result changes as records are filtered out and restored, but the underlying data remains in the worksheet. If someone copies only the displayed result elsewhere, that copied number will not automatically update when the original filter changes.

Frequently Asked Questions

Does SUBTOTAL 9 ignore hidden rows?
No. SUBTOTAL(9,range) ignores rows excluded by a filter, but includes rows hidden manually. Use function number 109 if manually hidden rows should also be excluded.

Does SUBTOTAL 109 ignore filtered rows?
Yes. Both 9 and 109 omit rows hidden by a filter. The difference is that 109 also omits rows hidden manually.

Does SUBTOTAL ignore hidden columns?
The 101–111 function numbers are intended to ignore manually hidden rows, not to exclude values just because their columns are hidden. Arrange the range or use a different approach if column visibility needs to control a calculation.

Why does my subtotal include a row I cannot see?
The row may have been hidden manually while the formula uses a function number from 1–11. For a sum that excludes manually hidden rows, use 109 instead of 9. If the row was excluded by a filter, both codes should omit it.

Will SUBTOTAL update when I change a filter?
Yes. SUBTOTAL recalculates to reflect rows included in the filtered result. This is one reason it is commonly used for totals above or below a filtered list.

Is SUBTOTAL available in Excel for Mac?
Yes. The function and its hidden-row behavior are the same in current Excel for Mac and Excel for Microsoft 365 on Windows. The steps for filtering or hiding rows may look slightly different, but the function numbers do not change.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.