Excel SUBTOTAL AVERAGE: Average Filtered Data

How-to basics · Updated November 18, 2026

To average values in Excel while excluding rows removed by a filter, use =SUBTOTAL(1,B2:B100), assuming the values are in B2:B100. SUBTOTAL recalculates the average from the visible rows, so the result changes as you filter the list.

What SUBTOTAL AVERAGE means in Excel

SUBTOTAL is a worksheet function that performs a selected calculation on a range. Its first argument is a number identifying the calculation; its second argument is the range to calculate. For an average, the function number is 1 or 101. This makes SUBTOTAL useful when you want a summary that responds to filtering rather than averaging every record in the original list.

The most common version for filtered data is =SUBTOTAL(1,range). Excel excludes rows hidden by a filter regardless of whether you choose 1 or 101. The difference matters for rows hidden manually: 1 includes manually hidden rows, while 101 excludes them too. Choose 101 if you want the average to reflect only rows that are currently visible on the worksheet.

SUBTOTAL is not a special kind of average. It uses the same basic averaging principle as AVERAGE: add the included numeric values and divide by the number of included numeric values. Its key distinction is which rows it includes.

How the function responds to filters

When you filter a list, Excel hides records that do not meet the filter criteria. SUBTOTAL recognizes those filter-hidden rows and leaves their values out of the calculation. Change the filter, and Excel recalculates the result using the newly visible records. This is handy for reports where someone might select one region, date range, product, or status at a time.

For example, imagine a table with a Region column and an Amount column. Filtering Region to East hides the West records. A SUBTOTAL average over the Amount column averages the visible East amounts, not all amounts in the original range. Clearing the filter makes all eligible records visible again, and the result changes accordingly.

Filtering is different from deleting rows. A filter preserves the underlying records and temporarily hides some of them; SUBTOTAL can therefore give a changing summary without altering the source data. If you copy a filtered range elsewhere, check whether you need only visible rows or the full dataset: the function’s behavior does not remove hidden records from the workbook.

Choose function number 1 or 101

The two average options differ only in how they treat rows hidden manually. Rows excluded by a filter are ignored by both. Rows hidden with the worksheet’s Hide command, or by collapsing an outline, are included by function number 1 and excluded by function number 101.

  • 1 means AVERAGE and includes manually hidden rows. Filter-hidden rows are still excluded.
  • 101 means AVERAGE and excludes manually hidden rows as well as filter-hidden rows.

Use 1 if manual hiding is just a visual choice and the hidden values should remain part of the summary. Use 101 if the number is intended to represent only records visible to the reader. Neither option is universally better; the right choice depends on what your report means by included data.

For example, suppose B2:B7 contains 12, 18, 25, 40, a blank, and 100, and the row containing 25 is hidden manually. With the layout B2:B7 containing those six entries, =SUBTOTAL(1,B2:B7) returns 39: the five numeric values total 195, and 195 divided by 5 is 39. With the same layout and hidden row, =SUBTOTAL(101,B2:B7) returns 42.5: the four visible numbers total 170, and 170 divided by 4 is 42.5. The blank is not counted as a number in either calculation.

Worked example: average a filtered list

Consider a small sales list with regions in A2:A7 and amounts in B2:B7. The rows contain East 12, West 18, East 25, West 40, East with a blank amount, and West 100. If you filter the Region column to show East only, the visible numeric amounts are 12 and 25. The blank amount is not part of the average, so the expected result is 18.5.

With regions in A2:A7 and the corresponding amounts in B2:B7, enter this formula in a cell outside the filtered list: =SUBTOTAL(1,B2:B7). With the East filter active, it returns 18.5 because (12 + 25) divided by 2 equals 18.5. The blank cell is ignored, and the West amounts are excluded because their rows are filtered out.

If you clear the filter, the five numeric amounts are 12, 18, 25, 40, and 100. Their total is 195, and 195 divided by 5 is 39, so the formula returns 39. This before-and-after check is a simple way to confirm that the range includes the intended records and that the filter is working.

Set up the filter and place the formula

In Excel for Microsoft 365 on Windows, select a cell in the list and choose Data > Filter. Filter arrows appear in the header row; use the arrow in the relevant column to choose which records to display. The same Data > Filter command is available in Excel for Mac, although ribbon layout and shortcuts can vary by release.

  1. Keep one clear header row above the records. Avoid blank rows or columns inside the data area, since they can make it harder for Excel to identify the complete list.
  2. Turn on the filter using Data > Filter and select the criteria you want to view.
  3. Put the SUBTOTAL formula outside the records it summarizes, such as above the list or in a separate summary area.
  4. Check the result with at least one filter selection and again with the filter cleared. Confirm that the range begins and ends at the correct data rows.

For a short list, you can select the range yourself. For a growing dataset, an Excel Table can make the setup easier to maintain: select a cell in the data and choose Insert > Table, making sure the option indicating that the table has headers is correct. A table adds filter controls to its header row. Its Total Row also offers an Average calculation from a dropdown, and Excel uses a SUBTOTAL-based calculation so the summary responds to filtering.

Keep the result cell outside the range being averaged. If you put a formula inside its own input range, it may create a circular reference or include an unintended cell. Also avoid selecting an unnecessarily large range that includes notes or other unrelated numbers below the list.

What SUBTOTAL includes and ignores

When the range is a cell reference, SUBTOTAL’s average counts numeric values and ignores blank cells and text. A cell containing the number 0 is different from a blank: zero is a numeric value and is included in the average. This distinction can matter when a zero represents a real measurement, no sales, or a placeholder used by a reporting system.

Text that looks like a number may also be ignored if it is stored as text rather than as a numeric value. If a result seems too high or too low, check whether the cells are genuinely numbers and whether blanks or zeros have the meaning you intend. Do not remove zeros merely to change the average unless they are genuinely not observations for the question you are answering.

Visible error values can cause the calculation to return an error rather than an average. Check the visible records for errors such as #N/A or #VALUE! if that happens. If no numeric values remain visible—for example, the filter leaves only blank or text entries—the average has no numbers to divide and Excel returns #DIV/0!.

SUBTOTAL also ignores other SUBTOTAL formulas within the reference range. This helps prevent a grand total from counting an intermediate subtotal a second time. It does not mean that every formula result is ignored: ordinary formulas that return numbers are numeric values and can be averaged when their rows are included.

SUBTOTAL versus AVERAGE and AVERAGEIF

=AVERAGE(B2:B100), assuming the values to average are in B2:B100, averages numeric values in the range without adapting to filtered-out rows. It can therefore be the right choice when the desired answer should always cover the entire range, regardless of the current filter. SUBTOTAL is the better fit when the question is specifically about the currently visible records.

AVERAGEIF and AVERAGEIFS calculate averages based on conditions you specify in the formula. For example, they are useful when you want an average for a named region even if no filter is applied. Their criteria are part of the formula, whereas SUBTOTAL responds to which rows are visible after filtering. A formula using an average criterion should not be assumed to have the same visibility behavior as SUBTOTAL.

You can also combine approaches in a report: use a filter to let a reader inspect a subset, and use SUBTOTAL for the visible-row summary; use AVERAGEIFS in a separate cell for a fixed condition-based result. Label the two outputs clearly so readers know whether each number follows the active filter.

Common mistakes to avoid

  • Using 1 when manually hidden rows should be excluded. Choose 101 for an average based only on visible rows, including when rows may be hidden manually.
  • Including the header or summary cells. Point the range at the data values, not a text heading or another summary formula.
  • Expecting zeros to be ignored. Zero is a value, not a blank, and affects the average.
  • Assuming a filtered result changes the source data. A filter hides records; it does not delete or permanently remove them.
  • Using a fixed range that misses new records. Extend it when the list grows, or consider an Excel Table and its Total Row.

Another common misunderstanding is that 101 is required for filter-aware averages. It is not: both 1 and 101 exclude rows hidden by filters. The extra behavior in 101 concerns rows hidden manually. Selecting the function number based on that distinction prevents surprises when someone hides rows for presentation.

Frequently Asked Questions

What is the Excel formula for averaging filtered cells?
Use =SUBTOTAL(1,range), replacing range with the cells that contain the values. For example, if the values are in B2:B100, use =SUBTOTAL(1,B2:B100). Filtered-out rows are excluded.

Does SUBTOTAL 1 ignore manually hidden rows?
No. Function number 1 includes rows hidden manually, but it excludes rows hidden by a filter. Use 101 if manually hidden rows should also be left out.

Does SUBTOTAL count zero values?
Yes. A numeric zero is included in the average. Blank cells and text in referenced cells are ignored.

Why does my SUBTOTAL average show #DIV/0!?
There may be no numeric values in the range that remain visible. Check the active filter and confirm that the range contains numeric values rather than only blanks or text.

Can I use SUBTOTAL with an Excel Table?
Yes. A Table’s Total Row provides an Average option that updates with filtering. You can also enter a SUBTOTAL formula in a separate summary cell, taking care to reference the data column rather than the summary cell itself.

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.