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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents

## 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-

`=SUBTOTAL(9,F5:F7)`

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).

`=SUBTOTAL(9,F5:F16)`

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).

`=SUM(F5:F7)`

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

â§¬ The formula for calculating grand total is-

`=SUM(F6:F16)/2`

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).

### 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

<< Go Back To Subtotal in Excel | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF