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.


Download Practice Workbook


4 Methods to Make Subtotal and Grand Total in Excel

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.

Dataset

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!

Preparation of Dataset

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.

Using the Excel Function

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

Using the Excel Function

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.

Using the Excel Function

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

=SUBTOTAL(9,F5:F16)

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

How to Make Subtotal and Grand Total in Excel Using the SUBTOTAL Function

So, the output will be as follows.

How to Make Subtotal and Grand Total in Excel Using the SUBTOTAL Function

Read More: How to Use SUBTOTAL in Excel with Filters (With Quick Steps)


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.

How to Make Subtotal and Grand Total in Excel Using SUM Function


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 Data tab > Data Tools ribbon > click on the drop-down list of the Outline option > choose the Subtotal feature.

How to Make Subtotal and Grand Total in Excel Using 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).

Using the Subtotal Feature

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

How to Make Subtotal and Grand Total in Excel Using the Subtotal Feature

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

Using the Subtotal Feature

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

How to Make Subtotal and Grand Total in Excel Using the Subtotal Feature


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.

Creating Pivot Table

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

Creating Pivot Table

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

Creating Pivot Table

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

How to Make Subtotal and Grand Total in Excel Creating Pivot Table

Read More: How to Subtotal Multiple Columns in Excel Pivot Table (with Easy Steps)


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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo