How to Calculate Average in Excel with Filtered Data

Calculating an average in Excel seems simple until you filter a list and your answer doesn't update. It’s a frustratingly common problem: you filter your campaign data to see just your Facebook Ads, but the 'Average Cost Per Click' cell at the top stubbornly shows the average for all your campaigns. This article explains why the standard AVERAGE function doesn't work on filtered data and teaches you how to use two powerful functions, SUBTOTAL and AGGREGATE, to get the right answer every time.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why the Normal AVERAGE Function Fails with Filtered Data

The core of the issue lies in how Excel handles filtering. When you apply a filter, Excel doesn't delete the data that doesn't match your criteria, it simply hides the rows. Think of it like pulling down a window shade. The furniture is still in the room - you just can't see it.

Most standard Excel functions, including AVERAGE, SUM, COUNT, and MAX, are designed to look at a range of cells and include all of them in their calculation, whether they are visible or hidden. So when you filter a list, the AVERAGE function still sees - and calculates - all the values in the hidden rows, giving you an incorrect total for your visible data.

Let’s look at a quick example. Imagine you have this simple table of sales data:

Sales Report - Q1

• Region | Sale Amount
• North | $200
• South | $150
• North | $250
• West | $300
• South | $125

If you use the formula =AVERAGE(B2:B6), you’ll get $205. But what happens if you filter the 'Region' column to only show "North"? Your table will look like this:

Sales Report - Q1 (Filtered for North)

• Region | Sale Amount
• North | $200
• North | $250

The standard AVERAGE(B2:B6) formula will still return $205, even though the correct average for the visible "North" region sales is clearly $225. This is where you need a smarter function that knows to ignore hidden cells.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Solution: Excel's SUBTOTAL Function

The secret weapon for working with filtered lists is the SUBTOTAL function. Unlike AVERAGE, SUBTOTAL can be configured to perform calculations exclusively on the visible cells within a range. It’s the perfect tool for creating dynamic summary metrics that update automatically as you filter your data.

Understanding the SUBTOTAL Syntax

The syntax for the function looks like this:

=SUBTOTAL(function_num, ref1)

• function_num: This is a special code that tells SUBTOTAL which type of calculation to perform (like averaging, summing, or counting). This is the key part of the formula.
• ref1: This is the range of cells you want to calculate, like B2:B50.

The function_num seems intimidating at first, but it’s actually quite straightforward. For each type of calculation, there are two different codes you can use. The codes from 1 to 11 include manually hidden rows, while the codes from 101 to 111 exclude them - which is exactly what we need for filtered data.

For calculating an average, the magic number is 101.

• 1: Regular AVERAGE.
• 101: AVERAGE that ignores hidden (filtered) rows.

So, our new formula will be =SUBTOTAL(101, B2:B6).

How to Use SUBTOTAL with Filtered Data: Step-by-Step

Let's walk through how to set this up using our earlier product sales example.

Here's our dataset of monthly ad campaign performance:

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 1: Set Up your Filters

First, if you haven't already, turn on filtering for your dataset. Click any cell within your data range, go to the Data tab in the Excel ribbon, and click the Filter icon. Little dropdown arrows will appear in your header cells.

Pro Tip: A faster way to do this is to format your data as an Excel Table. Select your data and press Ctrl+T (Cmd+T on a Mac). This not only adds filters but also provides other benefits like auto-expanding formula ranges.

Step 2: Enter the SUBTOTAL Formula

Now, click on the cell where you want to display your dynamic average (for this example, let's say it's a cell above your spending, so D1 if following that table above).

Instead of =AVERAGE(D2:D6), you’ll type the SUBTOTAL formula:

=SUBTOTAL(101, D2:D6)

• 101: This is the special code that tells SUBTOTAL to calculate the average and, crucially, ignore any hidden rows from filtering.
• D2:D6: This points to the data you want to average (in this case, the Conversions column).

Now when you filter the 'Channel' column to only display "Facebook Ads," your table will instantly update to show just these rows:

The moment you apply that filter, the result in any cell containing your SUBTOTAL formula will immediately recalculate. Instead of showing the average for all conversions, which is 27, it will now show the correct average of just the visible Facebook Ads campaigns: (25 + 20 + 40) / 3 = 28.33.

The Alternative: Using AGGREGATE Function

While SUBTOTAL is fast and gets the job done, Excel also offers an even more powerful alternative for specific scenarios: the AGGREGATE function. It can do everything SUBTOTAL does, but it’s capable of handling other, more complex problems, such as ignoring error values in your calculations.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Understanding the AGGREGATE Syntax

The syntax for AGGREGATE can seem a bit complex, but it is very flexible:

=AGGREGATE(function_num, options, ref1, [ref2], ...)

• function_num: Like SUBTOTAL, this is a code telling AGGREGATE what type of calculation to perform. For an average, it’s 1.
• options: This tells AGGREGATE how to handle things like hidden rows or errors. For our purposes, "ignore hidden rows" is represented by 5.
• ref1: The array or range you want to calculate over, similar to the SUBTOTAL function’s use.

So to achieve the same thing we did with the SUBTOTAL, our formula would be:

=AGGREGATE(1, 5, D2:D6)

• 1: The code for average.
• 5: The code to ignore hidden rows (filtered data).
• D2:D6: The range you're averaging.

This will calculate the average of visible cells in D2:D6, just like our SUBTOTAL(101, D2:D6) example.

Final Thoughts

Calculating averages on filtered data is a common point of friction in Excel, but it doesn't have to be. By swapping the standard AVERAGE function for the smarter SUBTOTAL(101, ...) or AGGREGATE(1, 5, ...) formulas, you can create dynamic, accurate reports that update in real time as you slice and dice your data.

While mastering these functions is a huge step up from manual spreadsheet work, at some point the spreadsheets themselves become the bottleneck. For me, that came when I realized how much time I was still spending just connecting to and managing different data sources. That's why we created Graphed. We wanted to eliminate the busywork of exporting CSVs from Google Analytics, Facebook Ads, Shopify, and a half-dozen other platforms just to get them into one place. With Graphed, I can connect my data sources once, then just ask questions in plain English, like, "What was my average cost per conversion from Facebook ads in June?" and get back a live dashboard instantly.