Average Filtered Column in Excel: Formula and Tips

Formulas & functions · Updated November 18, 2026

To average only the visible rows in a filtered Excel column, use =SUBTOTAL(1,B2:B100), replacing B2:B100 with your data range. SUBTOTAL recalculates when you change the filter, so the result reflects the records currently shown rather than every value in the column.

Why a filtered average needs a special function

Excel’s ordinary AVERAGE function calculates across values in its range whether or not some of their rows are hidden by a filter. That can make a result misleading: the screen may show a small subset of records, while the displayed average still includes the records filtered out of view. SUBTOTAL is designed to work with lists and filtered data. Its average option leaves out rows excluded by a filter.

This distinction matters in everyday analysis. You might filter a sales table to one region, a date range, or a product category and expect the average to describe only that selection. A filter changes which rows are displayed; it does not delete the other records. SUBTOTAL responds to that displayed selection while keeping the full source data intact.

How the SUBTOTAL formula works

SUBTOTAL takes a function number and one or more references. The function number 1 means average. The reference identifies the values to calculate. In a range containing a header, the text header is ignored, but using just the data cells makes the formula easier to understand and maintain.

For a standard filtered list, the key behavior is that SUBTOTAL ignores rows removed from view by AutoFilter. It also ignores other SUBTOTAL results inside its reference, which helps prevent a subtotal from being counted again in a larger subtotal. Empty cells and text entries are not included in the average; numeric zeroes are included.

Place the formula outside the rows being filtered, such as above the table or in a separate summary area. If you put a result in the source list itself, it can be harder to interpret and may be affected by how the list is arranged. Keep the result label clear, for example, Average of visible sales.

Worked example with visible rows

Suppose B2:B7 contains the sales values 12, 100, 18, 24, 6, and 30. A filter leaves the rows with 12, 18, and 24 visible. The average of those visible values is 18: their sum is 54, divided by 3. The filtered-out values do not contribute to this SUBTOTAL result.

With those sales values in B2:B7 and the filter showing only the rows containing 12, 18, and 24, enter this formula in a cell outside the filtered list:

=SUBTOTAL(1,B2:B7)

The result is 18. If you change the filter so that different records are visible, Excel recalculates the result. Clear the filter and the same formula averages all numeric values in B2:B7: the total is 190 across six values, so the result is about 31.67.

Set up and apply a filter

In Excel for Microsoft 365 on Windows, click a cell in the data range and choose Data > Filter. Excel adds filter arrows to the header row. Open the arrow on the column you want to filter and select or search for the values to display. Then enter the SUBTOTAL formula using the numeric column you want to average.

For reliable results, make sure the source data has one header row and no completely blank rows splitting the list. Each column should contain one kind of information. If the data is already formatted as an Excel table, its header typically has filter controls available. On Excel for Mac, use the Data tab and choose Filter as well; the ribbon layout can vary slightly by version, but the formula works the same way.

A quick process is:

  • Identify the column of numeric values to average and note its data range.
  • Turn on the filter and select the records you want to analyze.
  • Enter SUBTOTAL with function number 1 and the numeric range.
  • Change the filter and confirm that the result updates to match the visible rows.

Filtered rows versus manually hidden rows

SUBTOTAL has two sets of function numbers for the same calculations. Numbers 1 through 11 include rows hidden manually, while numbers 101 through 111 exclude manually hidden rows. Filtered-out rows are excluded by either set. For an average, use 1 when manually hidden rows should still count, or 101 when they should be left out too.

With values in B2:B100, use this version when the average should exclude both filtered-out rows and rows hidden manually with Excel’s Hide command:

=SUBTOTAL(101,B2:B100)

This choice does not change the treatment of rows hidden by the filter. It only affects rows hidden manually. That distinction is useful when a worksheet combines filtering with deliberate row hiding, though hiding rows can make an analysis less transparent to other people using the workbook.

Using a table and its Total Row

Excel tables can make a filtered average easier to maintain because their ranges expand when you add new records. Select a cell in the table, then choose Table Design > Total Row. In the total row cell for the numeric column, open the drop-down and select Average. Excel uses a subtotal calculation so the displayed result responds to table filters.

A table is especially useful when new data is added regularly: you do not have to revise a fixed range such as B2:B100 each time the list grows. If you prefer to enter the calculation yourself, a structured reference can refer to the table column. For a table named SalesData with a column named Amount, the table’s Amount values are referenced as SalesData[Amount]. Use the table’s Total Row option when you want Excel to manage the calculation in the table itself.

Check the table name and column heading before using structured references, because Excel requires them to match the workbook. If the Total Row is turned on, avoid counting that result as an ordinary data record in a separate calculation. SUBTOTAL is designed to disregard nested SUBTOTAL results, but a clearly separated summary is still easier to audit.

Why the result may look wrong

If the number does not match what you expect, check the filter state and the data type first. A value that looks like a number may actually be stored as text, perhaps because it was imported with a leading apostrophe or an unexpected separator. Text values are not averaged. Convert them to numbers if they are meant to represent quantities, then review the calculation.

  • The result does not change: Confirm that the filter is applied to the rows included in the formula range, and that the formula points to the intended column.
  • The average is unexpectedly low or high: Check for numeric zeroes, which count in an average, and verify that the filter selection includes the intended records.
  • The result is an error: Look for error values in the referenced cells. A SUBTOTAL calculation can return an error when a referenced cell contains one.
  • New records are missing: A fixed range will not automatically include values pasted below its end. Expand the range or convert the data to a table.
  • Hidden rows still count: If rows were hidden manually rather than filtered out, use function number 101 instead of 1 when those rows should be excluded.

Also check that the formula is not inside a filtered-out row. The calculation may still exist there, but you will not see it while that row is hidden. A summary cell above the list or on a separate worksheet is usually easier to find and interpret.

SUBTOTAL compared with AVERAGE and AVERAGEIF

Use AVERAGE when you want every numeric value in a range to count, regardless of whether its row is currently hidden by a filter. Use SUBTOTAL when the calculation should respond to rows shown by a filter. AVERAGEIF is useful for averaging values that meet a stated condition, such as a matching category, but it does not automatically use the visible-row state as its condition.

These functions answer different questions. For example, an AVERAGEIF formula can calculate the average for a named region even if the worksheet filter is set to another region. SUBTOTAL instead describes the visible records in the referenced list. If the goal is to analyze the current filtered view, SUBTOTAL is usually the clearest choice.

Frequently Asked Questions

Does SUBTOTAL ignore rows hidden by a filter?
Yes. Both SUBTOTAL function number 1 and 101 exclude rows hidden by an AutoFilter. The difference is whether manually hidden rows are included: 1 includes them, while 101 excludes them.

Does SUBTOTAL include zeroes in the average?
Yes. A numeric zero is a value and counts in the average. Blank cells and text entries are ignored, so a zero can lower the result even when blanks do not.

Can I average a filtered Excel table column?
Yes. Use SUBTOTAL with the table column as the reference, or turn on the table’s Total Row and select Average for that column. The result responds to filters applied to the table.

Why does AVERAGE show a different result from SUBTOTAL?
AVERAGE includes numeric values in filtered-out rows, while SUBTOTAL excludes rows hidden by the filter. If all rows are visible and the ranges match, the results will generally agree unless other hidden-row or data issues are involved.

Does sorting change the filtered average?
No. Sorting changes the order of records, not which numeric values are in the filtered set. The average should remain the same unless sorting is combined with a change to the filter or data.

What if I want the average of all rows, including filtered-out ones?
Use AVERAGE with the full data range. Unlike SUBTOTAL, it includes numeric values in rows hidden by a filter, which is appropriate when the filter should not affect the calculation.

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.