Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Average Filtered Cells/List in Excel?
You could have applied the average function to tables in Excel, but have you ever tried to use the average in filtered cells? When we apply the direct formula to the filtered cells, the result is an error. There is a special method for finding the average for filtered cells. This tutorial will help you understand how we can average filtered cells in Excel. We can complete this process just by using the simple formulas supported by Excel.
Average Filtered Cells/List in Excel
Here we will first create the table, then insert the slicer and use the SUBTOTAL formula to complete the task. Let us see a straightforward process to see how we can average filtered cells or lists in Excel.
Step 1
Consider an Excel sheet containing data similar to the data shown in the image below.
As we all know, the filter can be applied to the tables. To create the table, select the data, then click on the table under Insert and click on OK to get the table as shown in the below image.
Step 2
Now to apply the filter, click on "Insert" and select "Slicer" under the filter. The slicer will then be inserted into the sheet, as shown in the below image.
Step 3
Then click on any button in the slicer to apply the filter, and the list will be filtered and the result will look like the below image. We will be filtering based on country, which is India.
Step 4
To get the average of filtered cells, click on an empty cell and enter the formula =SUBTOTAL(1,B2:B15) and press Enter. We will be using this formula because if we use the AVERAGE function, then the value will return an error, so we need to use the abovementioned formula.
Conclusion
In this tutorial, we used a simple example to demonstrate how we can average filtered cells or lists in Excel.
14K+ Views
