Excel MAX of Filtered Data: Formula, Limits, and Fixes

Formulas & functions · Updated November 18, 2026

If your numbers are in B2:B100, use =SUBTOTAL(4,B2:B100) to return the largest value among rows currently shown by an Excel filter. The key distinction is that this version of SUBTOTAL ignores rows removed by a filter but still includes rows hidden manually; use function number 104 if manually hidden rows should be excluded too.

The formula for the largest filtered value

SUBTOTAL can perform several summary calculations on a range, including a maximum. Its first argument selects the calculation, and its second argument identifies the cells to summarize. For MAX, the function number is 4. When the range contains a filtered list, SUBTOTAL skips rows that the filter has hidden.

For example, if product prices are in D2:D250 and you filter the table to show only one category, the formula should refer to the price cells, not the category labels. With prices in D2:D250, use =SUBTOTAL(4,D2:D250). The result is the highest price in the rows that remain visible after filtering.

This formula updates when you change the filter. It also works when you clear the filter: with all data rows visible, it returns the maximum across the full range. This makes it useful in a worksheet summary cell or a table’s Total Row, where readers may change the displayed records.

How SUBTOTAL responds to filters

A filter does not delete records; it hides rows that do not meet the selected conditions. SUBTOTAL is designed to summarize data that may be filtered, so its result reflects the visible subset. In contrast, the ordinary MAX function evaluates every number in its referenced range, including numbers in rows hidden by a filter.

For instance, with values in B2:B100, the formula =MAX(B2:B100) returns the largest value anywhere in the range, even if the row containing it is excluded by a filter. If the displayed records are the ones you want to analyze, use SUBTOTAL instead. This distinction explains why a MAX formula can appear not to respond when you change a filter.

SUBTOTAL also treats nested subtotals specially. If the referenced range includes other SUBTOTAL formulas, it ignores those nested results to avoid counting the same data twice. That behavior is useful in grouped summaries, but it is another reason to check what cells your range contains if a result seems unexpected.

Choose between function numbers 4 and 104

SUBTOTAL has two number ranges for its calculation choices. Numbers 1 through 11 include manually hidden rows, while numbers 101 through 111 exclude manually hidden rows. Both sets ignore rows hidden by an AutoFilter. For a maximum, the corresponding choices are 4 and 104.

  • Use 4 when the goal is the maximum among filtered results and manually hidden rows should still count.
  • Use 104 when only rows currently visible on the sheet should count, whether other rows were hidden by a filter or hidden manually.

With values in B2:B100, =SUBTOTAL(104,B2:B100) calculates the maximum while excluding both filtered-out rows and manually hidden rows. This difference matters when someone uses Home > Format > Hide & Unhide > Hide Rows, or hides rows from the row-header context menu. If no rows are manually hidden, formulas using 4 and 104 return the same result.

There is no need to use 104 simply because a filter is applied. Both choices respond to AutoFilter results. Choose based on what manually hidden rows mean in your worksheet: if hiding a row is just a visual presentation choice, 4 may be appropriate; if hidden means “do not include in this summary,” choose 104.

Set up a practical filtered-data example

Suppose a small sales list has region names in A2:A7 and sale amounts in B2:B7. The six records are East 120, West 85, East 210, North 175, East 95, and West 160. Select a cell in the list and choose Data > Filter. Use the dropdown in column A to show East only.

With region names in A2:A7, amounts in B2:B7, and the East filter applied, enter =SUBTOTAL(4,B2:B7). It returns 210, the highest amount among the three displayed East records: 120, 210, and 95. If you change the filter to West, the same formula returns 160. Clear the filter and it returns 210, the maximum across all six records.

Now suppose you manually hide the row containing the East sale of 210 while the East filter is still active. With amounts in B2:B7, =SUBTOTAL(4,B2:B7) still returns 210 because function number 4 includes manually hidden rows. With the same range, =SUBTOTAL(104,B2:B7) returns 120 because that choice excludes the manually hidden row as well as the rows hidden by the filter.

To turn on filtering, select a cell in a normal data range and use Data > Filter. In a formatted Excel table, use the header dropdowns to filter. The formula itself is the same in Excel for Microsoft 365 on Windows, Mac, and the web; the ribbon layout can differ slightly by platform, but the Data tab’s Filter command is the relevant control.

Use a table for a range that grows

If the list is an Excel table, structured references can make the formula easier to read and maintain. Tables typically expand when you add records, and their column names are clearer than fixed cell addresses. To create a table, select the data and choose Insert > Table, making sure the table has headers.

For a table named Sales with a column named Amount, enter =SUBTOTAL(4,Sales[Amount]) in a worksheet cell. It returns the largest visible amount in that table column and adjusts as table records are added or removed. If a header or table name differs in your workbook, use the exact name Excel displays.

A table’s Total Row can also provide a summary. Select a cell in the table, open the Table Design tab, and select Total Row. In the total cell for the relevant column, choose Max from its dropdown. Excel uses a subtotal-style calculation for table totals so the result can respond to filtering. A worksheet formula is often easier to audit when you want to specify explicitly whether manually hidden rows count.

When MAXIFS or FILTER is a better fit

Sometimes “filtered data” means records that meet conditions, not rows hidden with Excel’s filter controls. If the question is “What is the largest sale in East?” and you want a formula based on a region criterion regardless of what is currently displayed, MAXIFS is usually clearer. It evaluates records meeting its criteria; it does not mean “only the rows visible after AutoFilter.”

With regions in A2:A7 and amounts in B2:B7, use =MAXIFS(B2:B7,A2:A7,"East") to return 210. MAXIFS is available in Excel 2019 and later, including Microsoft 365. If you change the worksheet filter to West, this formula still returns the East maximum because its criterion remains East.

For Microsoft 365 or Excel 2021 and later, FILTER can create an array of values that meet a condition, and MAX can find the maximum in that array. With regions in A2:A7 and amounts in B2:B7, use =MAX(FILTER(B2:B7,A2:A7="East")). This also returns 210 for the example. It is useful when you need to build further calculations from a filtered array, but it is not a substitute for SUBTOTAL when your intention is to respond to the worksheet’s AutoFilter selections.

In short, use SUBTOTAL when the displayed rows control the answer, MAXIFS when a stated condition controls it, and FILTER when you need a dynamic array of matching records. Choosing by meaning prevents a formula from silently answering a different question than the one you intended.

Common problems and checks

  • The result does not change with the filter. Check that the formula uses SUBTOTAL rather than MAX, and that its range covers the values in the filtered list.
  • The result includes a hidden row. If the row was hidden manually, function number 4 includes it. Switch to 104 if manually hidden rows should be excluded.
  • The result is zero or unexpectedly small. Check whether the cells contain numbers rather than numbers stored as text, and whether the referenced range includes the intended data rows. Text entries are not treated as numeric maximum candidates.
  • The formula returns an error or excludes the wrong data. Confirm the range starts and ends on the correct rows and does not accidentally include unrelated values. In a copied formula, verify that its references have not shifted.
  • No rows match the filter. A subtotal over a range with no visible numeric records may display 0. That can be valid output rather than a formula error; check the filter selections and whether visible cells contain numeric values.

For a quick audit, clear all filters and compare the result with the maximum you expect in the complete dataset. Then apply a filter that removes the known highest value. A SUBTOTAL result should fall if that value was the only maximum and no other visible record ties it. This check helps distinguish a formula issue from a filter or data-type issue.

Frequently Asked Questions

Does MAX ignore filtered rows in Excel?

No. MAX considers numeric values in its referenced range even when their rows are hidden by a filter. Use SUBTOTAL with function number 4 or 104 to calculate a maximum that responds to filtered rows.

What formula finds the maximum of visible rows only?

For values in B2:B100, =SUBTOTAL(104,B2:B100) excludes rows hidden by filters and rows hidden manually. If manually hidden rows should remain part of the calculation, use function number 4 instead.

Does SUBTOTAL 4 ignore manually hidden rows?

No. Function number 4 ignores rows hidden by a filter but includes manually hidden rows. Function number 104 excludes both types of hidden rows.

Will the formula update when I change an Excel table filter?

Yes. SUBTOTAL recalculates to reflect the rows shown by the table’s filter. A MAXIFS formula based on a criterion does not automatically become a visible-rows calculation just because a table filter changes.

Can I use this formula in Excel for the web or on a Mac?

Yes. SUBTOTAL works in Excel for Microsoft 365 on Windows, Mac, and the web. The interface may look different, but the formula syntax and its filtered-row behavior are the same.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.