In the "At each change in" box, select the first column that you want to base the subtotals on -- Category in this example. Description. This example teaches you how to create a running total (cumulative sum) in Excel.A running total changes each time new data is added to a list. That's an example report. 1. Thus, the SUBTOTAL function in Excel helps in analyzing the data provided. You may think of a sum of selected values. If you are using the SUBTOTAL, you do not need to worry about duplicate calculations. To insert this feature you need to follow these quick steps: STEP 1: Highlight your data and go to Data > Subtotal Where the SUBTOTAL function shines is when you need to calculate all values. The SUBTOTAL function in Excel is made up of 2 things: a math calculation (in the form of a function) and the data to be subtotaled. To insert subtotals first you have to sort your table. Note: 109 is the argument for Sum if you use the SUBTOTAL function. The SUBTOTAL function is a built-in function in Excel that is categorized as a Math/Trig Function. Fortunately, the AutoComplete feature in Excel helps you with this. See at the table below. Sort column in which you want to use subtotals. The tutorial explains the specificities of Excel SUBTOTAL function and shows how to use Subtotal formulas to calculate data in visible cells – sum, count, average, find the … 5. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. The SUBTOTAL function ignores rows hidden by a filter and calculates the correct result. The function ignores its counterparts in the selected range. The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database. Calculating the subtotal in Excel. The Subtotal feature requires that your values be grouped. This Excel tutorial explains how to use the Excel SUBTOTAL function with syntax and examples. The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data.For example, the Subtotal command could help to calculate the cost of office supplies by type from a large inventory order. The SUBTOTAL function in Excel allows users to create groups and then perform various other Excel functions such as SUM, COUNT, AVERAGE, PRODUCT, MAX, etc. As a worksheet function, the SUBTOTAL function can be entered as part of a formula in a cell of a worksheet. How to use Excel function SUBTOTAL Written by co-founder Kasper Langmann, Microsoft Office Specialist.. Select the function that you want to use when totaling the columns. Thus, you will get the sum of all data without issues. Excel Data Analysis - Subtotals with Ranges - If you have a list of data that you want to group and summarize, you can use Excel Subtotal and Outline to display … Excel offers a number of features for helping in this area; you can use data validation, data types, and even data entry forms. You can do many various calculations based on subtotals. Select cell B9 and enter a simple SUM function. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function. Excel´s Subtotal feature is a great way to automatically insert a Sum/Count/Average/Max/Min subtotal to your data set with a press of a button. Excel tutorial on how to use Subtotal in Excel. Select a cell in the list, and on the Excel Ribbon, click the Data tab, then click Subtotals. This article describes the formula syntax and usage of the SUBTOTAL function in Microsoft Excel.. 4. It's not easy to remember which argument belongs to which function. The example below shows how the SUBTOTAL and the SUM functions work in unison. It will create a hierarchy of groups, known as an outline, to help organize your worksheet. In this tutorial, we turn our attention to the SUBTOTAL function in Excel. The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows. It will help you dealing with data in Excel and creating reports. Creating subtotals. Returns a subtotal in a list or database. It can be used as a worksheet function (WS) in Excel. We’ll use the Subtotal button, found in the Data tab, and then go over how to use the SUBTOTAL function. Subtotals allow you to create summary of the groups list. This feature is located under the Excel Data tools menu: Data > Subtotal.