Learn how to sum only visible cells in Excel using the SUBTOTAL function. Discover how to calculate totals for filtered data, ignore manually hidden rows, and use practical formulas with step-by-step examples.
How to Sum Only Visible Cells in Excel (Formula & Examples)
When working with large Excel spreadsheets, you may often need to filter data to focus on specific records. For example, you might have a sales report containing hundreds of transactions but want to calculate the total sales for only the rows currently visible after applying a filter.
A normal SUM formula may not give you the result you expect because it includes values from hidden rows. This can make your calculations inaccurate, especially when preparing filtered reports, monthly summaries, or business performance sheets.
Fortunately, Excel provides a simple solution. You can use the SUBTOTAL function to add only visible cells while ignoring filtered-out rows.
In this guide, you'll learn how to sum visible cells in Excel using practical examples, understand the difference between SUM and SUBTOTAL, and discover alternative methods for handling hidden rows.
What Does Sum Only Visible Cells Mean in Excel?
Summing only visible cells means calculating the total of values that are currently displayed in your worksheet while excluding rows hidden by filters or manual hiding.
For example, imagine you have a sales report containing transactions from January, February, and March. If you apply a filter to display only February transactions, you may want the total sales for February without including the other months.
This is where the SUBTOTAL function becomes useful.
Unlike the regular SUM function, SUBTOTAL can ignore filtered-out rows and, depending on the function number you choose, manually hidden rows as well.
SUM vs SUBTOTAL: What's the Difference?
Before learning the formulas, it's important to understand how SUM and SUBTOTAL behave.
Feature | SUM Function | SUBTOTAL Function |
|---|---|---|
Adds numerical values | Yes | Yes |
Ignores filtered rows | No | Yes |
Ignores manually hidden rows | No | Depends on function number |
Updates when filters change | No | Yes |
Supports different calculations | No | Yes |
Best for visible-cell totals | No | Yes |
If your goal is to calculate totals based on filtered data, SUBTOTAL is generally the better choice.
How to Sum Only Visible Cells Using SUBTOTAL
The easiest way to add visible cells in Excel is by using the SUBTOTAL function.
The formula is:
=SUBTOTAL(9,B2:B10)Here's what each part means:
SUBTOTAL: Excel function used to perform calculations on a range of data.
9: Tells Excel to perform a SUM calculation while ignoring filtered-out rows.
B2:B10: The range containing the numbers you want to add.
For example, suppose you have the following sales data:
Product | Sales |
|---|---|
Laptop | 15,000 |
Mobile | 12,000 |
Keyboard | 2,000 |
Monitor | 8,000 |
Mouse | 1,000 |
Printer | 6,000 |
If you enter the formula below:
=SUBTOTAL(9,B2:B7)Excel calculates the total of all visible sales records.
Example: All rows are visible
Total visible sales
₹44,000
15,000 + 12,000 + 2,000 + 8,000 + 1,000 + 6,000
Now, if you apply a filter and display only Laptop, Mobile, and Monitor, Excel automatically recalculates the result.
Example: After applying a filter
Total visible sales
₹35,000
15,000 + 12,000 + 8,000
You don't need to change the formula whenever you apply a different filter. Excel updates the total automatically.
How to Sum Visible Cells While Ignoring Manually Hidden Rows
Sometimes, you may hide rows manually instead of using Excel's filter feature.
In this situation, the function number you choose makes a difference.
Excel provides two useful options:
Formula | Behaviour |
|---|---|
| Ignores filtered rows but includes manually hidden rows |
| Ignores both filtered and manually hidden rows |
If you want to calculate the total of only the cells visible on your screen, use:
=SUBTOTAL(109,B2:B10)This formula is particularly useful when preparing reports where you manually hide certain rows to simplify the presentation.
Practical example:
Suppose your monthly expenses are:
Expense | Amount |
|---|---|
Rent | 10,000 |
Electricity | 2,000 |
Internet | 1,000 |
Transport | 3,000 |
Office Supplies | 2,000 |
The total is ₹18,000.
If you manually hide the Internet and Office Supplies rows, the formula:
=SUBTOTAL(109,B2:B6)returns ₹15,000.
It excludes both manually hidden rows from the calculation.
How to Sum Visible Cells After Applying Filters
If you're working with a large dataset, Excel's Filter feature makes it easy to calculate totals for selected records.
Follow these steps:
Step 1: Select your dataset, including the column headings.
Step 2: Go to the Data tab in the Excel ribbon.
Step 3: Click Filter to enable dropdown arrows in your column headings.
Step 4: Open the filter dropdown for the column you want to filter.
Step 5: Select the records you want to display and click OK.
Step 6: Enter the following formula below your data:
=SUBTOTAL(9,B2:B100)Excel will calculate the total using only the visible rows.
You can change the filter criteria whenever needed, and the subtotal will adjust automatically.
How to Use SUBTOTAL with an Excel Table
If you regularly work with sales reports, expense trackers, or inventory lists, converting your data into an Excel Table can make calculations easier.
Here's how:
Select your dataset.
Press
Ctrl + Ton Windows orCommand + Ton Mac.Confirm that your table has headers.
Click OK.
Select any cell inside the table.
Open the Table Design tab.
Enable the Total Row option.
Excel adds a total row at the bottom of your table.
Click the dropdown in the total cell and select Sum. Excel automatically uses the SUBTOTAL function to calculate the visible total.
For a table named SalesTable, you can also use a structured reference:
=SUBTOTAL(109,SalesTable[Sales])This makes your formula easier to understand because it refers to the Sales column by name instead of using cell references.
Common Mistakes When Summing Visible Cells in Excel
Even though SUBTOTAL is straightforward, a few mistakes can lead to unexpected results.
Using SUM instead of SUBTOTAL
The SUM function includes values from hidden rows. If you want filter-aware calculations, use SUBTOTAL.
Choosing the wrong function number
Function number 9 ignores filtered rows but includes manually hidden rows. Use 109 when you want to exclude both.
Including the formula cell in the calculation range
Avoid placing your subtotal formula inside the range it calculates. Doing so can create unnecessary calculation issues. Place the formula outside the data range.
Expecting SUBTOTAL to ignore every type of hidden data
SUBTOTAL ignores rows hidden by filters. It also ignores manually hidden rows when using function numbers 101–111. However, it does not ignore individual hidden columns or cells.
Using the wrong data range
Always check that your formula includes the correct column and all relevant rows. Missing even a few cells can affect your final total.
Other Useful SUBTOTAL Function Numbers in Excel
The SUBTOTAL function is not limited to addition. It can also calculate averages, counts, maximum values, and minimum values.
Here are some commonly used function numbers:
Function Number | Calculation |
|---|---|
1 | AVERAGE |
2 | COUNT |
3 | COUNTA |
4 | MAX |
5 | MIN |
9 | SUM |
101 | AVERAGE (ignores manually hidden rows) |
102 | COUNT (ignores manually hidden rows) |
103 | COUNTA (ignores manually hidden rows) |
104 | MAX (ignores manually hidden rows) |
105 | MIN (ignores manually hidden rows) |
109 | SUM (ignores manually hidden rows) |
These options make SUBTOTAL useful for more than just calculating totals.
SUMPRODUCT Alternative for More Advanced Calculations
For more complex spreadsheets, you may sometimes need to calculate visible values using additional conditions.
The SUBTOTAL function is usually the simplest choice for standard filtered totals. However, SUMPRODUCT can be combined with SUBTOTAL and OFFSET to create more advanced calculations.
For example:
=SUMPRODUCT(SUBTOTAL(109,OFFSET(B2,ROW(B2:B10)-ROW(B2),0,1)))This formula evaluates the visibility of individual rows and adds their corresponding values.
It can be useful when building advanced reports, but it is more complicated than a standard SUBTOTAL formula.
For everyday spreadsheets, start with SUBTOTAL unless you have a specific requirement that needs a more advanced formula.
Frequently Asked Questions (FAQs)
Can Excel SUM only visible cells?
Yes. Use =SUBTOTAL(9,B2:B10) to add visible cells while ignoring rows hidden by filters.
Which formula ignores manually hidden rows in Excel?
Use =SUBTOTAL(109,B2:B10). It ignores both filtered-out and manually hidden rows.
Why does SUM include hidden rows?
The SUM function adds all numerical values in its selected range, regardless of whether their rows are hidden or filtered.
Does SUBTOTAL update automatically when I change filters?
Yes. Excel recalculates the result when you change the filter criteria, showing the total for the currently visible rows.
Can I use SUBTOTAL in an Excel Table?
Yes. You can enable the Total Row option in Table Design or use a structured reference such as =SUBTOTAL(109,SalesTable[Sales]).
Final Thoughts
Summing only visible cells in Excel is much easier when you use the SUBTOTAL function instead of SUM.
For filtered datasets, =SUBTOTAL(9,range) is a simple and reliable option. If you also want to exclude manually hidden rows, use =SUBTOTAL(109,range).
Whether you're managing sales figures, tracking monthly expenses, preparing inventory reports, or analysing business data, these formulas can save time and help you avoid incorrect totals.
The best approach is to use SUBTOTAL for filtered reports and Excel Tables when you regularly work with changing datasets.



COMMENTS