How to Average Filtered Rows in Excel

To average filtered rows in Excel, you can use the SUBTOTAL function, which can perform calculations on only the visible cells after applying a filter. This function works with different types of calculations, including average, sum, and count.

Follow these steps to average filtered rows in Excel:

1. Organize your data in a table or range with headers if you haven't already.
2. Click anywhere within your data range.
3. Go to the "Data" tab on the Excel ribbon.
4. Click on the "Filter" button to apply the filter to your data. You will see small drop-down arrows appear in your header row.
5. Click on the arrow in the header of the column you want to filter.
6. Choose the filtering criteria by checking or unchecking the boxes or selecting the filtering options, then click "OK."
7. Now that your data is filtered, you can use the SUBTOTAL function to calculate the average of the filtered rows.

Example: Averaging Filtered Rows

Let's say you have a dataset with the following columns: Product ID, Category, and Sales. You want to calculate the average sales for a specific category.

1. Apply a filter to your data following steps 1-6 above.
2. Click on the arrow in the "Category" header and choose the category you want to calculate the average sales for.
3. In a blank cell, use the SUBTOTAL function to calculate the average of the filtered rows. The function syntax is:

=SUBTOTAL(function_num, range)

For averaging, the function_num is 1 for AVERAGE when including hidden rows, and 101 for AVERAGE when excluding hidden rows (filtered out rows). In this example, we will use 101 to exclude hidden rows.

=SUBTOTAL(101, C2:C100)

Replace 'C2:C100' with the actual range of the Sales column in your dataset.

4. Press "Enter" to get the average of the filtered rows. The result will update automatically when you change the filter criteria.

That's it! You have successfully calculated the average of filtered rows in Excel using the SUBTOTAL function.