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**hide

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

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. P*roduct A*,* Product B*,* and Product C*). That means you can find the subtotal for only P*roduct 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 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.*

So, the output will be as follows.

**Read More:** **How to Show Grand Total in Pivot Table (3 Easy Methods)**

### 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 Remove Grand Total from Pivot Table (4 Quick Ways)**

**Similar Readings**

**How to Use SUBTOTAL in Excel with Filters (With Quick Steps)****Add Grand Total to Bar Chart in Excel (With Easy Steps)****How to Use Excel Formula to Calculate Percentage of Grand Total**

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

➜ 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:** **[Fixed!] Pivot Table Grand Total Column Not Showing (6 Solutions)**

### 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 the *Product A*, *B*, and *C* respectively. And the **C8 **cell displays the **Grand Total** of the entire *Sales*.

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