Using Subtotals in Microsoft Excel
Tips for Designing Powerful Spreadsheets with Easy Commands
Using the Subtotals command in Excel to save time and produce accurate spreadsheets.
Excel has many useful commands that can enhance spreadsheets and enable the user to design them easier and faster. The Data Subtotals command can save several steps and produce spreadsheets that are accurate and easy to read.
The Subtotal command can be a little difficult to learn and frustrating when it goes wrong, but with practice can save many keystrokes and errors.
How Subtotals works can best be described with an example. Consider a spread with a many rows of data and 3 columns. For this example using baseball statistics columns are (1) players, (2) teams and (3) home runs. The goal is to subtotal home runs by team.
The columns can be different descriptions with multiple columns of data. There can be as many columns as needed, but Excel will only sort on one description field.
Arranging Data in Excel
- Begin by sorting the data by players within the teams.
- Once the teams are sorted, click Data Subtotals to bring up the Subtotal box.There are 3 main options. “At each change in” refers to the description column that is being subtotaled, in this case, (2) team.
- Select “Use function” to choose to sum, count or do statistical analysis.
- Finally, choose the data column that is to be subtotaled in “Add subtotal to.”
- When OK is clicked, the data column will have a subtotal and a grand total, and each subtotaled description column will be bolded.
Using a Subtotaled Spreadsheet
Once the spreadsheet has created the subtotals, it also creates a section on the left side with the numbers 1, 2 and 3 near the top. Clicking number 2 will hide the detail and show only the subtotals. Clicking 1 will hide everything but the grand total.
Other options in the subtotal box include setting up a page break between the subtotaled groups and an option to place the subtotals above, rather than below the data.
Things to Know about Subtotals in Excel
Excel will make an assumption as to which column is the description column to subtotal by, and the column that should be subtotal. These are often incorrect depending on how the data is set up. Be sure to verify before sorting.
In this example, Excel will assume that the subtotal should be based on (1) players, which will not subtotal the spreadsheet properly.
If there is already a total in the spreadsheet, Excel will include it in the grand total, which will produce an incorrect result. Make sure to delete any totals that are put in using the SUM command or button.
Data Subtotals is another powerful tool available in Excel to make designing and using spreadsheets easy and accurate.
