How To Do Subtotals in Excel (And Why They’re Important)

1. On the Data tab, in the Outline group, click Subtotal. The Subtotal dialog box is displayed.
2. In the At each change in box, click the nested subtotal column. …
3. In the Use function box, click the summary function that you want to use to calculate the subtotals. …
4. Clear the Replace current subtotals check box.

Worksheets with a lot of information can occasionally seem overwhelming and even challenging to read. Fortunately, Excel can group data, enabling you to quickly show and hide various worksheet sections. Using the Subtotal command, you can also sum up various groups and make an outline for your worksheet.

The Subtotal command enables you to automatically create groups and summarize your data by using standard operations like SUM, COUNT, and AVERAGE. For instance, using a large inventory order, the Subtotal command could assist in calculating the cost of office supplies by type. It will produce an outline, a hierarchy of groups, to assist you in organizing your worksheet.

In our example, we’ll use a T-shirt order form and the Subtotal command to count how many T-shirts in each size (Small, Medium, Large, and X-Large) were ordered. With a group for each size of T-shirt created in this way, our worksheet will have an outline, and we can then count how many shirts are in each group.

Your worksheet is divided into different levels when you create subtotals. By clicking the Level buttons to the left of the worksheet, you can quickly change between these levels to regulate how much information is displayed in the worksheet. We’ll use an example where we alternate between the three levels of our outline. Even though this example only has three levels, Excel can support up to eight.

How to use the Subtotal Feature and the SUBTOTAL Function in Excel

Why is calculating subtotals in Excel important?

Calculating subtotals is important for many reasons, including:

Separates totals

You can make categories of subtotals using the subtotal function that are independent of one another. For instance, if there is a donation competition between two teams, you can use the subtotal function to separate the donation sums for each team. This can speed up the process of combining totals and maintain an accurate record of donations that both teams can understand during each update.

Helps categorize totals

Over time, subtotal functions can be used to group totals on the same sheet. This is beneficial for a variety of Excel document projects, including those that measure financial performance or charitable contributions. Subtotals can be used in Excel projects to distinguish results in a document when multiple totals are needed from various categories. To compare some results to others, for instance, you can divide the totals for a contest. Additional instances of when categorizing is beneficial include departmental KPI or project progress monitoring

Enables further calculations

Utilizing the data you already have rather than making plans based on information you don’t yet have can help you make more progress. You can plan for future categories by computing subtotals as opposed to adding all the numbers together in a grand total. Even if you are unsure of the categories you might add, it allows you to categorize your data in order to make it simpler. By only adding information as a subtotal rather than updating the total each time you make a change, you can save time.

What are subtotals in Excel?

In Excel, subtotals are groups of numbers that you add together to produce totals that you can then add together to produce a final total. To create an initial number for later averages, you can add a sum or an average by using the subtotal function. More than three different summary functions can be displayed inside any column.

Instead of using the individual items from the subtotals, users create grand totals from detailed data. For instance, if you calculate an average using a summary function, any grand total rows will display the average of all the detail rows in the list rather than the values in the subtotal rows. When editing the detail data, the subtotal command can recalculate both values if your workbook is calibrated to automatically calculate formulas. A list is outlined when you create a command so you can show and hide rows.

When to calculate subtotals in Excel

You might compute subtotals in Excel for a variety of reasons, including:

Using separate totals

Consider using the subtotal function to separate totals for various sums, categories, dates, or other dividing elements. When generating subtotals for various categories, one thing to keep in mind is that each category uses the same unit of measurement as the others. This is done so that you can use a final total that is accurate and free of any overlapping measurements.

Having a grand total

Using subtotals can make your grand total easier to calculate if you know your total will include different categories of measurements over time. Your subtotal calculations might change if, for example, a category is lost during documentation or if any of the subtotals change over time. As these modifications take place in your system, subtotals can assist you in adapting and ensuring accuracy.

Documenting results over time

You can categorize your results and maintain an accurate record of them by creating subtotals. The effectiveness of sub-categorizing your results documentation using subtotals increases with the length of your results documentation. Subtotals allow you to compare one section to another in situations where you may want to.

How to calculate subtotals in Excel

You can use Excel to calculate subtotals by following these steps:

1. Label your first row

Make sure that each column in the range of data you want to calculate has a label in the first row before you can create subtotals in Excel. Similar information, such as the same number total and cell alignment, can be found in each column you want to subtotal. Make sure that there are no blank rows or columns in your range for the subtotals to calculate correctly. Choose a cell in that range once your first row has been labeled.

2. Use the “Data” tab to sort your data

Select the column, then select the “Data” tab to sort the data in that column. Click “Sort Z to A” or “Sort A to Z” in the “Sort & Filter” section. Sorting your data before subtotaling it can make your spreadsheet’s flow plan more logical. You can skip this step if you document your data using different criteria, such as date or quarter.

3. Use the outline section to subtotal your data

Use the “Outline” section in the “Data” tab to select the column you want to subtotal after sorting and arranging your subtotal data. To start subtotaling your data, you can find them in this menu depending on the labels you select. Select the summary box in the “Use Function” box to start calculating your subtotals.

4. Allocate your subtotal to a cell

Next, select each column that you want to subtotal. This can be one or multiple columns at a time. Select the “Page Break Between Groups” check box to organize your data and add a page break after each column. Check the “Summary Below Data” section and choose the cell where you want to display your data to see which row your summary appears in.

5. Add additional subtotals as needed

Repeating the preceding steps will allow you to create an average if you want to include more subtotals. Different summary, placement, and display functions can be used with each subtotal function. Uncheck “Replace Current Totals” if you want to keep the current subtotals in place.

FAQ

How do you add subtotals in Excel?

1. Apply a filter to the list. In this example, the Region column is filtered for “West”.
2. Choose the cell where you want the total to appear in the row below the list.
3. On Excel’s Home tab, click the AutoSum button.

How do you subtotal multiple rows in Excel?

1. Apply a filter to the list. In this example, the Region column is filtered for “West”.
2. Choose the cell where you want the total to appear in the row below the list.
3. On Excel’s Home tab, click the AutoSum button.

How do I sum a column with subtotals?

1. Apply a filter to the list. In this example, the Region column is filtered for “West”.
2. Choose the cell where you want the total to appear in the row below the list.
3. On Excel’s Home tab, click the AutoSum button.