How to filter data exclude bottom total row in Excel?
In day-to-day data processing with Excel, it is common to calculate a total row at the bottom of your dataset to summarize key figures such as totals, averages, counts, and other aggregations. However, when you use Excel’s filter feature, the total row is often subject to the same filtering as the rest of your data, meaning it can be hidden when filters are applied. In many scenarios—especially in summary reporting, financial statements, or ongoing data analytics—you may need the total row to always remain visible at the bottom of your data, regardless of filters applied to the rest of your worksheet, as demonstrated in the screenshot below.
This article will walk you through several practical methods to ensure the bottom total row always stays visible during filtering. Each method is suitable for different usage scenarios and user preferences. The article not only covers built-in Excel features but also includes formula approaches and programmatic VBA solutions, providing an array of options depending on your needs.
➤ Filter data exclude bottom total row by creating a table
➤ Filter data exclude bottom total row by inserting a formula
➤ Filter data exclude bottom total row using VBA code
Filter data exclude bottom total row by creating a table
The most straightforward way to keep your total row visible while filtering is to convert your data into an official Excel Table. Excel Tables have a native Total Row function that is intelligently designed to stay visible at the bottom of your data even as you filter other rows. Below are the detailed steps, common scenarios, and notes for this method:
1. If your data already includes a manually calculated total row (i.e., a row with total formulas not built into a table), delete this row temporarily to avoid duplication.
2. Select the range of your actual data (excluding the manually created total row). Then go to Insert > Table. In the Create Table dialog box, ensure My table has headers is checked if your data includes column headers. Review the data range to confirm all your rows are included.
3. After clicking OK, your data will be formatted as an Excel Table. While the table is selected, go to the Design tab (or Table Design in some versions). In the Table Style Options group, check the Total Row box. Excel will automatically add a new total row at the bottom.
4. You can now use the filter arrows on any column to filter your data as needed. The Total Row will always appear as the last row in the table, regardless of the filters you apply.
Applicable Scenarios: This method is ideal for datasets that are frequently updated, shared across teams, or used in recurring reports—especially when you benefit from built-in table features like banded rows, structured references, and automatic formatting.
Advantages:
- Seamless integration with Excel's built-in features
- No need to update formulas when data expands or shrinks
- Total row remains dynamic and automatically updated
Limitations:
- Custom formulas in the total row need to be reconfigured after converting to a Table
- Large datasets may experience minor performance slowdowns due to real-time formatting
- Make sure to delete any manually created total row before converting to a Table, or you may see duplicated totals.
- Clear filters before adding or removing data to keep the table structure consistent.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Filter data exclude bottom total row by inserting a formula
If you don’t want to use an Excel Table, you can retain a manual total row at the bottom of your data by applying the SUBTOTAL formula. The SUBTOTAL function is specifically designed to work dynamically with Excel filters—its calculation will reflect only visible (filtered-in) rows, but the formula itself won't be hidden or excluded when filtering. This approach is flexible and works well with classic data ranges.
1. In the first cell of your intended total row, directly below your data table, enter the following formula (for example, if your values are in column B from row 2 to 13):
=SUBTOTAL(9, B2:B13)Replace B2:B13 with the range corresponding to your data’s actual column and rows. The number 9 in the formula specifies the SUM function; you can use different function numbers as needed (for example, 1 for AVERAGE, 2 for COUNT, etc.).
2. Press Enter to confirm. The total row will immediately display the calculated sum based on visible (unfiltered) rows. When you apply filters using Excel’s built-in filter buttons, the total row remains visible at the bottom and always shows the current subtotal for only the visible rows.
SUBTOTAL formula if your data grows or shrinks. This formula won’t auto-expand unless you use a dynamic range function like OFFSET or INDEX.Summary: The SUBTOTAL formula supports a variety of aggregate functions (like AVERAGE, COUNT, etc.), making it flexible for summary rows used in finance, inventory, and reporting workflows.
Filter data exclude bottom total row using VBA code
For users working with larger datasets, or those seeking an automated approach to ensure the bottom total row is always displayed (and never hidden, regardless of filter settings), you can use VBA (Visual Basic for Applications) to programmatically manage filtering. This solution is especially useful in workbooks where the data range or total row placement changes dynamically, or when you want to enforce consistency for all users.
This VBA example works by checking the last row in your data range and forcing it to remain visible, even after applying filters. It ensures the total row is always shown and can be reused by assigning the macro to a button.
Steps to use this VBA solution:
1. In Excel, go to the Developer tab. If you do not see this tab, you may need to enable it from Excel Options by checking "Developer" in the Customize Ribbon section.
2. Click Visual Basic to open the VBA editor. In the new window, click Insert > Module, and paste the following code into the new module:
Sub AlwaysShowTotalRow()
Dim ws As Worksheet
Dim lastRow As Long
On Error Resume Next
Set ws = ActiveSheet
' Get last used row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Ensure total row is visible before applying filter
ws.Rows(lastRow).Hidden = False
' If filtering is active, reapply filter
If ws.FilterMode Then
ws.ShowAllData
End If
' Make sure total row remains visible
ws.Rows(lastRow).Hidden = False
End Sub3. After entering the code, close the VBA editor. In Excel, press Alt + F8, select AlwaysShowTotalRow, and click Run. The macro will make sure the last row (your total row) is visible even after filters are applied.
• The macro assumes the total row is the last used row in column A. You may adjust the logic if your data includes blank rows or multiple summaries.
• Consider assigning this macro to a button for easy reuse if your data changes frequently.
• If your workbook contains multiple sections, use a named range or a specific column marker to locate the correct total row more reliably.
Advantages: Automates visibility of total row regardless of user actions or filtering. Ideal for dashboards, reports, or shared workbooks.
Limitations: Requires macro-enabled workbook and proper macro permissions. May need customization for more complex sheet layouts.
Summary: This VBA approach is recommended for automated environments where manual control of total rows is unreliable or inconvenient. It helps maintain visibility of summary data across all use cases.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in