How to Create Subtotals and Grand Totals in Excel
Question details
The user needs to calculate subtotals for grouped categories in a spreadsheet and generate a grand total for the entire dataset.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing financial trial balances or reports into specific categories to automatically calculate category-level totals and a final grand total for audits.
- Observed behavior
- The goal state is a dataset properly sorted by category, featuring collapsible outline controls to display detail or summary rows, and a final grand total row at the bottom.
Ensure your dataset has clear column headers and always sort your data by the target category column before applying any subtotal functions.
Use the Built-in Subtotal Command
The most efficient way to automatically insert subtotal and grand total rows based on data categories in Excel.
Excel's built-in Subtotal command automatically calculates subtotals and grand totals in a list or database. It works seamlessly when your data is correctly grouped and sorted by a specific category.
Select any cell in the category column (e.g., Cash, Receivables) and go to the Data tab. Click the Sort A to Z or Sort Z to A button so that all identical categories are grouped consecutively.
Highlight your entire dataset, making sure to include the column headers in your selection.
Navigate to the Data tab on the ribbon. Locate the Outline group on the far right and click the Subtotal button.
In the Subtotal dialog box, set 'At each change in' to your category column. Under 'Use function', choose 'Sum'. In the 'Add subtotal to' section, check the boxes for the numerical columns you want to calculate (e.g., Year 2022, 2023). Click OK.
Use the 1, 2, and 3 outline buttons on the left margin of your worksheet to collapse or expand the data, allowing you to quickly switch between detailed views and high-level summaries.
Create Subtotals and Grand Totals Effortlessly in WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools, including an intuitive Subtotal feature that mirrors Microsoft Excel perfectly. It allows you to quickly group your financial records, generate category subtotals, and calculate grand totals for seamless reporting.
- 1. Sort your category data: Open your spreadsheet in WPS Office, select your target category column, and click the Sort button in the Data tab.
- 2. Access the Subtotal tool: Navigate to the Data tab on the top ribbon and select 'Subtotal' located in the Outline group.
- 3. Apply grouping and sum: Select the column to group by, choose the 'Sum' function, check the columns you wish to total, and click OK to generate the report.
Frequently Asked Questions
Why is the Subtotal button greyed out in Excel?
The Subtotal feature is not available if your data is formatted as an official Excel Table. You must convert the table back to a normal range first. Right-click anywhere in the table, select Table, and click 'Convert to Range' before using the Subtotal command.
How do I completely remove subtotals from my worksheet?
To remove subtotals, select any cell within your subtotaled dataset, go to the Data tab, and click Subtotal. In the dialog box that appears, click the 'Remove All' button at the bottom left.
Why are my subtotals inserting in the wrong places or creating too many groups?
This happens when your data is not properly sorted before applying the subtotal. You must sort the category column first so that all identical items appear in consecutive rows; otherwise, Excel will create a new subtotal every time the category name changes from one row to the next.
Can I use functions other than Sum for subtotals?
Yes, you can use various functions. When configuring the Subtotal dialog box, click the 'Use function' dropdown menu to select other calculation types such as Count, Average, Max, Min, or Product depending on your reporting needs.
