How to Make Subtotal and Grand Total in Excel (4 Methods)

Whenever you have a larger dataset and you want to compute subtotal for a specific field or some specific group and grand total covering the entire dataset. In this instructive session, I’ll demonstrate 4 methods on how to make subtotal and grand total in Excel.

How to Make Subtotal and Grand Total in Excel: 4 Methods

Let’s introduce today’s dataset (which belongs to the B4:F13 cell range) as shown in the following screenshot. Here, the sales report is given along with the necessary information e.g. Product Items, States, Price, Quantity, and lastly Sales.

Now, you’ll explore the methods to make subtotal and grand total (for the Sales column) in the case of the above dataset. The first two methods use Excel functions and the rest methods focus on the use of some fruitful features of Excel.

1. Using SUBTOTAL Function to Make Subtotal and Grand Total

Luckily, there is a dedication function i.e. SUBTOTAL function to calculate subtotal covering 11 mathematical operations e.g. sum, average, maximum, and so on.

⧬ Let’s assume that you want to find the subtotal for the group of products (i.e. Product A, Product B, and Product C). That means you can find the subtotal for only Product A (in the F8 cell). In addition, you can determine the grand total (in the F17 cell) for all groups of products. That’s the main difference between subtotal and grand total!

Let’s move on to the point!

Now, you have to insert the SUBTOTAL function.

➜ While entering the formula, you’ll see some groups of functions. From there, you need to select 9 as you’re going to compute the sum of the Sales.

➜ Then, select the cells (F5:F7) of Product A.

So, the formula for the F8 cell will be like the following-

Here, the SUBTOTAL function returns the sum values of the Sales in the case of Product A.

The below image displays the formula with respect to the dataset.

After pressing the ENTER key and copy-pasting (press CTRL + C to copy the formula and CTRL + V to paste it) the formula, you’ll get the following output.

⧬ Furthermore, you can use the SUBTOTAL function to make grand total. Insert the following formula (in the F17 cell).

Here, the function automatically excludes the computed subtotal of Products A, B, and C.

So, the output will be as follows.

2. Using SUM Function

Another simple way to make subtotal and the grand total is the application of the SUM function.

⧬ The formula for computing subtotal (for Product A).

Note: Just copy and paste the formula for Product B and Product C.

⧬ The formula for calculating grand total is-

Here, the SUM function returns all the sum values. When I divided it by 2, it would return the actual grand total as subtotal values are available there.

Read More: How to Insert Subtotals in Excel

3. Utilizing Subtotal Feature to Make Subtotal and Grand Total

Now, I am going to show you an amazing Subtotal feature available in Excel. You can do some special tasks using this feature. Follow the steps to utilize this feature.

➜ Initially, select the dataset or keep the cursor over any cell within the dataset.

➜ Then, go to the Data tab > Data Tools ribbon > click on the drop-down list of the Outline option > choose the Subtotal feature.

➜ Immediately, you’ll see a dialog box namely Subtotal. Now, pick the Product Items from the drop-down list of the At each change in: option.

➜ Later, fix the Sum function and check the box before Sales (from the options of Add subtotal to:).

➜ Also, check the box before the Replace current subtotals and Summary below data options (these are checked by default).

Eventually, you’ll get the subtotal and grand total together after pressing OK.

Let’s explore some optional tasks that you can do for a better presentation.

⧬ If you want to display only the Grand Total, just click over the 1 (located on the upper-left side as depicted in the following image).

⧬ Similarly, if you feel necessary to represent the subtotal and grand total instead of the entire dataset, click over 2 (right side of 1).

Read More: How to Use SUBTOTAL in Excel with Filters

4. Pivot Table to Bring Subtotal and Grand Total

Though the Subtotal feature performs awesome, it doesn’t work in the case of the Excel table.

In such a situation, you may create a Pivot Table, a powerful tool to summarize a larger dataset.

➜ Firstly, select the entire dataset and pick the From Table/Range option by clicking over the drop-down list of the Pivot Table in the Insert tab.

➜ Then, press OK (keep the default options as these show).

➜ After pressing OK, you have to drag the Fields to their relative areas. For example, the Product Items to Rows area and Sales to Values area.

Finally, you’ll get the following output where the C5, C6, and C7 cells show the subtotal of Product A, B, and C respectively. And the C8 cell displays the Grand Total of the entire Sales.

Download Practice Workbook

Conclusion

That’s all for today’s session. I firmly believe that the above-discussed method will help you to make subtotal and grand total in Excel. If you find this article useful, share it with your community. And, don’t forget to share your thoughts.

Related Articles

• How to Sort Subtotals in Excel
• How to Remove Subtotals in Excel
• VBA Code for Subtotal in Excel

<< Go Back To Subtotal in Excel | Learn Excel