Fix Excel AutoSum Inserting SUBTOTAL Instead of SUM
Question details
The user is attempting to calculate a total using AutoSum, but the tool is inserting a SUBTOTAL formula (using function number 109) instead of the standard SUM formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using the AutoSum tool inside a dataset formatted as an official Excel Table.
- Observed behavior
- Excel automatically overrides the typical SUM behavior and inserts a SUBTOTAL(109) formula because the active cell is located within an Excel Table.
Before proceeding, determine if you rely on specific Excel Table features like dynamic filtering and automatic row expansion, as resolving this AutoSum behavior requires converting the table back into a standard data range.
Convert the Excel Table to a Standard Range
Converting your formatted table back to a standard data range forces the AutoSum tool to revert to its default behavior and insert a SUM formula.
Excel is programmed to use SUBTOTAL inside tables so that filtered rows update dynamically. By converting the table into a normal range, you remove this dynamic behavior, allowing AutoSum to work normally.
Right-click on any cell inside the Excel table where you are experiencing the AutoSum formatting issue.
In the context menu that appears, hover your cursor over 'Table' and click on 'Convert to Range' from the sub-menu.
A prompt will appear asking if you want to convert the table to a normal range. Click 'Yes' to confirm your decision.
Navigate back to your desired total cell and click the AutoSum button again. It should now insert a standard SUM formula and select the values above the active cell.
Manually Type the SUM Formula
If you wish to retain Excel Table functionalities like automatic filtering, bypass the AutoSum button entirely by manually inputting the SUM formula.
Easily Manage Data and Formulas with WPS Spreadsheet
WPS Spreadsheet provides an intuitive interface for executing mathematical functions like AutoSum. It allows you to effortlessly switch between formatted data tables and standard ranges to ensure your formulas behave exactly as you expect.
- 1. Open your dataset: Launch WPS Spreadsheet and open your existing workbook containing the dataset.
- 2. Convert Table if necessary: Right-click any cell within your data table, select 'Table', and click 'Convert to Range' to prepare for standard formula processing.
- 3. Apply AutoSum: Highlight the empty cell directly below your data, navigate to the Formulas tab, and click 'AutoSum' to instantly generate a standard SUM calculation.
Frequently Asked Questions
Why does Excel use SUBTOTAL instead of SUM inside a table?
When you use AutoSum inside an official Excel Table, the application defaults to the SUBTOTAL function. This is an intentional design choice so that if you apply filters to the table columns, the total will dynamically update to sum only the visible rows. A standard SUM function would incorrectly include hidden rows in its calculation.
What does the 109 mean in the SUBTOTAL formula?
In the formula =SUBTOTAL(109, [Range]), the number 109 represents a specific calculation method. The '9' stands for the SUM operation, and the '100' series instructs Excel to calculate the sum while ignoring any rows that have been manually hidden or filtered out.
Will I lose my data if I convert an Excel table to a range?
No, your data is completely safe. Converting a table to a range only removes the specialized table functionality (like built-in filter drop-downs and automatic formula expansion). All your text, numbers, and even the visual formatting (like cell colors and borders) will remain intact.
