If you’re working with Excel tables and want to calculate totals that adjust automatically when filters are applied, the SUBTOTAL function is the perfect tool. In this guide, we’ll show how to use it effectively with data like Region, Salesperson, Category, Revenue $, and Units.
Table of Contents
📊 Sample Data Table
| Region | Salesperson | Category | Revenue $ | Units |
|---|---|---|---|---|
| North | Aman | Electronics | 5000 | 25 |
| South | Riya | Apparel | 3000 | 18 |
| East | Dev | Electronics | 4500 | 20 |
| West | Pooja | Grocery | 2500 | 15 |
| North | Suresh | Apparel | 3200 | 17 |
🔧 Step-by-Step: Use SUBTOTAL to Get Dynamic Totals
Let’s say you’ve applied filters on Region or Category, and you want Excel to calculate totals only for visible rows.
➤ Total Revenue (Visible Rows Only):
=SUBTOTAL(9, D2:D6)
Explanation:9is the function number for SUMD2:D6is the Revenue $ column
➤ Total Units (Visible Rows Only):
=SUBTOTAL(9, E2:E6)
This will automatically recalculate based on the filtered data.🔢 Common SUBTOTAL Function Numbers
| Function | Includes Hidden Rows | Ignores Hidden Rows |
|---|---|---|
| SUM | 9 | 109 |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
For filtered tables, always use numbers from 101–111 to ignore hidden rows manually.
✅ Why Use SUBTOTAL?
- Dynamic results when filtering data
- Avoids manual updates to formulas
- Works great with Excel Tables and Dashboards
- Helps in cleaner, interactive reporting
💡 Bonus Tip: Use AutoSum with Filtered Data
- Apply filters on your table.
- Click on the AutoSum button in the ribbon.
- Excel automatically uses the SUBTOTAL formula (instead of SUM) for filtered data.
🧠 Final Words
Using the SUBTOTAL function in Excel is a must-know trick for anyone working with filtered datasets like Region-wise sales reports or category-based revenue. It keeps your totals dynamic and smart—saving you time and reducing errors.
