# How to Average Filtered Data in Excel (2 Easy Methods)

We will consider an example where we have a list of expenditures within different categories shown in the picture below.

We filtered the list based on the category Food and Health.

### Method 1 – Use of the SUBTOTAL Function to Average Filtered Data in Excel

Steps:

• Select any cell and input the following formula.
`=SUBTOTAL(1,D5:D11)`

Here, 1 is for calculating the Average and D5:D11 is the filtered range from where the average will be calculated.

• Press Enter.

Read More: How to Do Subtotal Average in Excel

### Method 2 – Use of the AVERAGEIF Function to Average Filtered Data in Excel

The problem with the SUBTOTAL function is that when you undo filtering of the range, the average will get changed. To fix the issue, we can use the AVERAGEIF function so that our calculated average remains unchanged. But this time, we can only filter one category. So either Food or Health should be filtered, not both. We will filter only the Food category.

Steps:

• Select any cell and input the following formula.
`=AVERAGEIF(C5:C7,"Food",D5:D7)`

• Press Enter and you will have the average of the filtered cells.

## Things to Remember

• The result from the SUBTOTAL function is subject to change as the filtered range changes.
• The AVERAGEIF function takes only one criterion to calculate the average of filtered cells.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF