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

Get FREE Advanced Excel Exercises with Solutions!

Filtering data is one of the most useful tools of Microsoft Excel. But when it comes to find the average of those filtered data, some people may find it problematic as the AVERAGE function considers not only the filtered data but also the hidden data in the specified range. In this article, we will learn how to average filtered data in Excel. So let’s begin!


How to Average Filtered Data in Excel: 2 Easy Methods

We will demonstrate two effective methods to average filtered data in Excel with appropriate illustrations. To demonstrate, we will consider an example where we have a list of expenditures within different categories shown in the picture below.

Suppose we filter the list based on the category Food and Health. So our filtered list would look like the figure below.

How to Average Filtered Data in Excel

Now, we want to determine the average of the filtered data. To do that, let’s look at our first method.


1. Use of SUBTOTAL Function to Average Filtered Data in Excel

In this method, we will use the SUBTOTAL function. This function allows us to calculate the sum, average, maximum, minimum, and many other parameters in a filtered range. It only counts the visible data and ignores the hidden data. The steps for applying this function are given below.

Steps:

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

Here,

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

Use of SUBTOTAL Function to Average Filtered Data in Excel

  • Now press Enter, and you will get the average value of the filtered cells, which is 135.

Use of SUBTOTAL Function to Average Filtered Data in Excel

Read More: How to Do Subtotal Average in Excel


2. Use of 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. Here, we will filter only the Food category.

Utilizing AVERAGEIF Function to Average Filtered Data in Excel

Steps:

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

Here,

  • C5:C7 is the criteria range.
  • Food is the criteria.
  • D5:D7 is the range of cells to be averaged.

Utilizing AVERAGEIF Function to Average Filtered Data in Excel

  • Now press Enter, and you will have the average of the filtered cells.

This time even if you undo the filtering of the range, the average will be unchanged.

Read More: How to Calculate Sum & Average with Excel Formula


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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That is the end of this article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.


Related Articles


<< Go Back to Conditional Average | Calculate Average | How to Calculate in Excel | Learn Excel

Aniruddah Alam
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo