17.6 C
London
Tuesday, July 22, 2025
HomeSoftware TutorialsExcelHow to Average Filtered Rows in Excel (With Example)

How to Average Filtered Rows in Excel (With Example)

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

The easiest way to take the average of a filtered range in Excel is to use the following syntax:

SUBTOTAL(101, A1:A10)

Note that the value 101 is a shortcut for taking the average of a filtered range of rows.

The following example shows how to use this function in practice.

Example: Sum Filtered Rows in Excel

Suppose we have the following dataset that shows the number of sales made during various days by a company:

Next, let’s filter the data to only show the dates that are in January or April.

To do so, highlight the cell range A1:B13. Then click the Data tab along the top ribbon and click the Filter button.

Then click the dropdown arrow next to Date and make sure that only the boxes next to January and April are checked, then click OK:

The data will automatically be filtered to only show the rows where the dates are in January or April:

If we attempt to use the AVERAGE() function to find the average value in the Sales column, it will actually return the average of all of the original values:

Instead, we can use the SUBTOTAL() function:

Excel average filtered rows

This function takes the average of only the visible rows.

We can manually verify this by taking the average of the visible rows:

Average of Sales in visible rows: (9+13+14+14+19) / 5 = 13.8.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Delete Filtered Rows in Excel
How to Count Filtered Rows in Excel
How to Sum Filtered Rows in Excel

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories