Subtotal Ribbon in Excel

In this article, we will learn Subtotal Ribbon in Excel.

Scenario :

Subtotal means grouping data based on column and its respective values. For example summing up the stock exchange data from 10 sheets of a workbook. Or getting the percentage Average of Income grouping by different departments. For problems like these we use the Subtotal option in excel. Let's understand where to find subtotal and how to use over a set of data explained illustrating an example below.

Subtotal Ribbon in Excel

Select the table > Go to Data > Subtotal > Select the first grouping column > first aggregate function > first subtotal column > Click Ok.

Select the table > Go to Data > Subtotal > Select the second grouping column > second aggregate function > second subtotal column > Untick Replace current subtotal box > Click Ok. And Continue till the data is grouped with different subtotals.

Note : The Subtotal dialog box reads columns. So select the grouping column and aggregate column separately.

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have product sales and profit data to subtotal.

We will first group data based on the month. Select the table and Go to Data > Subtotal.

Subtotal dialog box appears in front.

Select the Category column name > Choose Sum function > Profit column to subtotal > Click OK.

You can see the category wise subtotal data in Excel. To add more subtotal.

Select the table again and Go to Data> Subtotal > Select Sub-category column name > Choose Average function > Sales column to subtotal > Untick Replace current Subtotal > Click Ok.

Your data is categorized into sub groups sum category group and average subcategory group.

Above snapshot, green coloured are subcategory grouped subtotal and orange ones are Category grouped subtotal. You can also perform this using the pivot tables. Learn more about pivot tables here.

Here are all the observational notes using the formula in Excel
Notes :

1. Select the data before proceeding to the subtotal option. Or else the function throws an error.
2. To remove subtotal. Select table and Go to Data > subtotal > click Remove all button (adjacent to Ok button).

Hope this article about Subtotal Ribbon in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to get subtotal grouped by date using the GETPIVOTDATA function in Excel : This is a special function that is specially used to work with data from pivot tables. It is used to retrieve values from pivot tables using the table columns and rows headers.

Relative and Absolute Reference in Excel : Understanding of Relative and Absolute Reference in Excel is very important to work effectively on Excel. Relative and Absolute referencing of cells and ranges.

All About Excel Named Ranges : excel ranges that are tagged with names are easy to use in excel formulas. Learn all about it here.

How To Count Unique Values in Excel With Criteria | To count unique values in excel with criteria we use a combination of functions. The FREQUENCY function is core of this formula

How to use the SUBTOTAL function in Excel: Returns the SUM, COUNT, AVERAGE, STDEV or PRODUCT on applied filtered data in Excel.

How to use the SUMPRODUCT function in Excel : Returns the SUM after multiplication of values in multiple arrays in excel.

How to Dynamically Update Pivot Table Data Source Range in Excel : To dynamically change the source data range of pivot tables, we use pivot caches. These few lines can dynamically update any pivot table by changing the source data range. In VBA use pivot tables objects as shown below.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.