Did you ever think about grouping similar data when you are having a lot of data?
Your sheets could appear inappropriate when your data is not organized and sorted in order. Your data will not be comprehendible because of the mess in it. However, with Excel, you can easily add subtotals to manage similar data types in one group. But the thing is how to add subtotals in Excel, do you have any idea about it?
This powerful feature can generate an outline for the sheets and in this post, you will get to know how simply you can perform this task.
What is Subtotal in Excel?
Subtotal in Excel is an outstanding feature used for the sum of a set of numbers that can be added to another set of numbers. It ultimately makes a grand total. Not only totaling values within a set, but the subtotal feature also lets you even group as well as summarize data with the help of SUM, COUNT, AVERAGE, MIN, MAX, and other functions.
When you subtotal in Excel, it will look like this:
How to Add Subtotals in Excel?
Adding subtotals in Excel is not rocket science, all you need to know is to understand the process thoroughly and then follow it:
Manage the Source Data:
With the Excel Subtotals feature, you need to organize the source data properly as there should not be an empty row. First, you have to sort the column in which you need to group the data. For this,
Click on the Filter tab given on the Data tab.
Now, click on the filter arrow and choose to sort in order from A to Z or Z to A:
Add Subtotals:
Now, select any cell from the data and open the Data tab.
From there click on the Outline group and choose Subtotal.
In case you need to add subtotals for a particular data, you need to select that range of data before clicking on the Subtotal button.
Classify the Subtotal Options:
You have to identify three main things in the Subtotal dialog box:
- Which column to group by,
- What summary function to use,
- And which columns to subtotal:
In the example here, you can notice the data is grouped by the Region column, and in the Sales and Profit columns the SUM function is used to total numbers.
Furthermore, choose any option given below:
- By selecting the Page break between Groups option, you can add an automatic page break right after each subtotal.
- By clearing the Summary below data option, you can showcase a summary row above the details row.
- By selecting the Summary below data option, you can display the summary row below the details row.
- Check the box Replace current subtotals to overwrite the already present subtotals.
- Press the OK button. You will see subtotals below each data group and the grand total will be inserted at the end of the table.
Once you are done with adding the subtotals in the sheet, you will see by default they get recalculated when you edit the source data.
Things You Need to Consider about Excel Subtotal Feature
As you are now familiar with a subtotal feature in Excel, which is highly versatile and specific in terms of calculating data, now let’s figure out a thorough description of its characteristics.
- Only Visible Rows can be Subtotal
If your data contains some filtered rows, the Excel subtotal feature will simply ignore them and calculates only visible cells. Filtered rows include values of manually hidden rows such as the rows that get hidden by the following process:
Open the Home tab and choose the Hide Rows command.
Now, click on the Cells group and then select Hide & Unhide option from the Format menu.
Let’s see the following mentioned details:
With the SUBTOTAL feature you can by default create SUBTOTAL formulas that help in certain calculations such as SUM, COUNT, AVERAGE, etc. This function identifies in the first argument (function_num), which belongs to one of the following sets:
- 1 – 11 ignored filtered cells and manually hidden rows are added.
- 101 – 111 ignored all hidden rows including all manually hidden and filtered cells.
You will notice this function adds a formula with function numbers 1 – 11.
Following is the formula that appears by adding subtotals with the SUM function:
SUBTOTAL(9, C2:C5)
In this formula:
9 shows the SUM function
C2:C5 shows the first group of cells to the subtotal.
Filtered-out rows will by default removed from the subtotals and in the case when you hide those rows manually, subtotals will be added.
- Grand Totals are Calculated from the Original Data
By using the original data, the Excel Subtotal feature lets you calculate grand totals. For instance, if you use the AVERAGE function to calculate the Grand Average as an arithmetic mean in cell C2:C19 subtotals are added but the values in the subtotal rows will get ignored.
- Subtotals are not Applicable in Tables
With Excel tables, this outstanding feature is not allowed to use. You will notice the Subtotal button not working on the ribbon because you are trying to apply it on the tables. If you really want to apply this feature to a table, you have to convert the table data into an ordinary range. Only then you can apply it to the table data.
To Wrap Up:
In this post, you have learned how versatile the subtotal feature is and how to add subtotals in Excel with simple-to-follow steps. Hopefully, the above-mentioned data is useful to you. keep exploring Excel and continue spreading knowledge!
