Automatically create subtotals in Excel

Q. I receive regular spreadsheets with all of our company’s accounts, departments, employees, and balances, and I have to subtotal the amounts by accounts, departments, etc. I have been manually doing this using the SUM function. A colleague suggested I use the SUBTOTAL function, but is there really any difference? It’s just a different function.

A. There are several reasons to use SUBTOTAL instead of SUM when inserting subtotals in your spreadsheet. One reason is that the subtotals are ignored when calculating the grand total, making it much easier to sum the entire spreadsheet. Other benefits to using SUBTOTAL are that you can opt to ignore any numbers that have been hidden, dynamically summarize data, and sum filtered values. And one of the most useful reasons is that you can automatically create the subtotals, saving a considerable amount of time.

You can download an Excel workbook similar to the one described in the question here and access a video demonstration of how to automatically create subtotals at the bottom of this article.

See below for a small snippet of the spreadsheet we will be subtotaling. It contains many account types, account codes, account titles, departments, cost centers, years, months, and budgeted values.

First, let’s subtotal the budgeted values by account type. Place your cursor anywhere in the dataset. Click the Data tab on the Ribbon. Then choose Subtotal from the Outline group, and the Subtotal window will open. Under At each change in:, choose ACCT_TYPE since that is what we decided to subtotal. However, you do not have to subtotal by the first column; you could have picked from any of the columns in the dataset. Under Use function:, choose Sum, which is the default. Notice there are several options you could choose other than SUM. Under Add subtotal to:, choose BUDGET since this is what we want totaled for each account type. There are three checkboxes at the bottom of the Subtotal window. The first one is Replace current subtotals, which will remove any subtotals currently in the dataset with the new ones. By default, this is already checked. Although I did not have any existing subtotals in my spreadsheet, I will leave the option checked. The second checkbox is Page break between groups. This option allows you to separate the subtotaled sections to different pages. This is useful if you want to print the spreadsheet with each account type totaled on a separate page. The third checkbox is Summary below data. This will populate summary data pertaining to the settings you chose. Since we used SUM in our example, the summary data displays a grand total for all budgeted values.

The screenshot below shows a snippet of the spreadsheet with the budgeted values of each account type subtotaled and a grand total of all budgeted values displayed at the end of the spreadsheet. All of this was done with just a few clicks.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.