excel

How To Keep Total When Filtering In Excel

Jump to

    Filtering in Excel helps you display only the data that meets certain criteria, but keeping a running total while filtering can be complex. This guide provides clear steps to ensure your totals adjust dynamically as you apply or remove filters.

    By the end of this tutorial, we will also explore why Sourcetable offers a more straightforward approach for maintaining totals during filtering compared to Excel.

    Keeping Totals When Filtering in Excel

    To maintain accurate totals when applying filters in Excel, utilize the SUBTOTAL function. This function ensures the summation of only visible cells, disregarding those that are hidden manually or through filters.

    Using SUBTOTAL for Visible Cells

    Employ the SUBTOTAL function by inserting =SUBTOTAL(109, your_range) into a cell. The '109' argument specifies that you want to sum the visible cells. Replace 'your_range' with the actual range of cells you wish to sum.

    Advantages of SUBTOTAL

    The SUBTOTAL function is superior when filtering data because it automatically adjusts the total to only include visible cells, providing an accurate sum despite the applied filters.

    Issues with SUM in Filtered Data

    Avoid using the SUM function alone for filtered data, as it includes both visible and hidden cells in the total. This can lead to incorrect totals when rows are hidden by filters.

    Formatting and Errors

    Ensure that numbers are not formatted as text, as this can cause the SUM function to malfunction. Additionally, a #Name error may indicate a misspelling in your SUM formula.

    Common Use Cases

    • excel

      Tracking monthly expenses while filtering out non-essential purchases

    • excel

      Calculating total sales for specific product categories

    • excel

      Summing up employee hours worked by department

    • excel

      Monitoring inventory levels of selected items

    • excel

      Aggregating customer feedback scores for service improvement analysis

    Excel vs. Sourcetable: A Comparative Overview

    Discover the uniqueness of Sourcetable, a modern spreadsheet solution designed for data consolidation. Unlike traditional tools, Sourcetable aggregates data from various sources, enabling seamless data analysis within a single interface.

    Excel, the classic spreadsheet software, is powerful for data manipulation but lacks integrated capabilities for pooling data from multiple sources. Sourcetable fills this gap, offering a centralized data management experience.

    Sourcetable's AI copilot distinguishes it from Excel, providing an intuitive chat interface to assist users in formula creation and template generation, thus enhancing productivity and user experience.

    While Excel relies on user proficiency for complex tasks, Sourcetable's AI streamlines the process, offering a more accessible platform for users at different levels of technical expertise.

    Opt for Sourcetable when in need of a collaborative, data-centric spreadsheet tool with AI-enabled assistance. When traditional spreadsheet functionalities suffice, Excel may be the go-to option.

    Recommended Reading

    • How to... how to total filtered data in excel
    • How to... excel how to sum only filtered cells
    • How to... how to count rows in excel with filter
    • How to... how to add filtered cells in excel
    • How to... how to save filtered data in excel
    • How to... how to auto number or renumber after filter in excel
    • How to... how to display total row in excel


    Sourcetable Logo

    Try Sourcetable To Get Insights From Your Data

    Ask questions about your data in a powerful spreadsheet that your whole team knows how to use.

    Drop CSV