Are you preparing a report from an Excel data sheet? Or are you learning MS Excel 2016? Since you are here, I am pretty much sure you are experiencing some issues to outline your MS Excel document. Or you are looking for how to quickly insert subtotals in Excel. OK be with me and I will guide on “Excel How to Add New Data Subtotals” throughout this article. Assuming that you are familiar with MS Excel 2016 Group & Ungroup command to categorize your data. At this point, just remember that we do not need these commands. Because MS Excel gives us a complete “Subtotal” command under Data TAB. So if you think you have to Group or Ungroup data manually then this is not the case. The “Subtotal” command will automatically group your data, based on your selection. Let’s do an example to understand about Subtotaling data.
Excel subtotal is an automated way to add subtotals and grand totals in your data report. It’s also a popular practice to dig down your data in many different ways to present. Sometimes you need detail data and sometimes aggregated data or summary data – Excel subtotals give you the freedom to quickly switch to those level data view. Convinced? Just bear with me and I will show you step by step how to do this.
- How to insert subtotals in excel 2016
- How to sum subtotals in excel
- Subtotal formula in excel
- How to add second or multiple Data Subtotals
- How to remove Data Subtotals
Plotting Data to Create Subtotals in Excel 2016:
If you have any specific set of data then you can use your data. If you are here for learning purpose then follow the below Excel data sheet example. In this example we will use the Subtotal command with an inventory data sheet. The data sheet contains category wise product received qty, dispatched qty and finally items in-stock. Our target is to outline a report in Excel sheet with a group for each product category and then count the total number of items in each group.
10 Steps of Excel How to Add New Data Subtotals:
- First you have to sort your data based on the column on which you want to subtotal. Category wise data should be sorted because we need category wise subtotal.
- Go to “DATA TAB” and click on the “Subtotal” command. If you found an error like “This can’t be applied to the selected range. Select a single cell in a range and try again” then click on any single data cell first and click on “Subtotal” command again.
- Excel will popup Subtotal property dialog box.
- The first option is “At each change in:” denotes each change in the mentioned column will have a subtotal for this column. So in our case the column should be “Category”. So the category column must be sorted before applying subtotal command. Otherwise you will be ended up with multiple subtotals for the same values.
- The second option is “Use function:” denotes which aggregate function you want to use. The commonly used functions are:
In our case we will use Sum() built in function to get category wise & in-total items stock in hand.
- The third option is “Add subtotal to:” list box which denotes in which column we want to display the subtotal. Since we need categorized item stock hence we will select “In Hand” column.
- The fourth option is “Replace current subtotals” checkbox which denotes whether you want to remove any existing subtotals or not? Since we have no define subtotals yet so leave it as unchecked.
- The fifth option is “Page break between groups” checkbox which denotes whether you want to put a page break after each new category or not? Leave it as unchecked.
- The sixth option is “Summary below data” checkbox which denotes whether you want to display a grand total at the bottom or top (based on Outline property) of the data or not? In this example we want to consider it. Hence check this field.
- Once you are satisfied with your options click OK, to apply the Subtotals to achieve Excel how to add new data subtotals.
Now look at the sheet. All your data have been grouped & showing subtotals accordingly. A grand total figure also displayed at the bottom of your data. According to our example data now you are getting category wise stock in hand as well as a grand total of stock in hand. Key point to remember is in the first occurrence of any new category Excel automatically grouped the prior category & inserted a subtotal. Up to this stage we have learned Excel how to add new data subtotals in a single column.
- 1 – Display Grand Total only.
- 2 – Display Grand Total and Subtotals.
- 3 – All data including both Subtotals & Grand Total.
If you look at the left side of your data sheet, you will found + and – buttons for each subtotal. You can toggle the view by clicking on these buttons. “-” button denotes that the respective category section is already expanded. If you want to collapse this section then click on the “-” button. Hope you are now familiar with Excel how to add new data subtotals. Let’s explore more on this.
Excel Subtotal in Multiple Columns:
The process is almost same. Start from #2. At #4 stage select your second column on which you want to apply the subtotal. The same is also true for this second level subtotal is whenever a new value comes up Excel will put subtotal data for the prior value. The grand total value remain same. If we consider our current data set then you can choose the product column. Don’t forget to sort both category and product column together like below.
Repeat this until your all subtotals requirement fulfilled.
- Make sure that “Replace current subtotals” checkbox not checked. Because if checked then Category subtotals will be removed that you configured earlier.
- Another grouping button will be added at the top left corner. Keep experimenting by clicking on those buttons. Hope you can understand the presence of the additional button.
How to Remove Subtotals:
If you want to remove subtotals then Go to “DATA TAB” on the ribbon and click on the “Subtotal” command. If you found an error like “This can’t be applied to the selected range. Select a single cell in a range and try again” then click on any single data cell first and click on “Subtotal” command again. At the bottom of the “Subtotal” dialog box you will find a “Remove All” button. Just click to remove all of your subtotals at once.
How to apply outline presets in Excel:
The outline preset property is available just beneath the subtotal command. There are 3 directions available.
- Summary rows below detail
- Summary columns to right of detail
- Automatic styles
Play around with those options to find the perfect fit of your report style.
Download the Exercise File:
If you have different ways or tips on Excel how to add new data subtotals then please share with me through comment. I will try to publish for readers. Otherwise don’t forget to share this with your friends & colleagues through social media. You will find media buttons below to this post.