In this tutorial, you will learn several methods of using **Excel formula **to **calculate percentage **of **grand total**.

## Download Practice Workbook

It will be easy for you if you download the working file that I have used to write this tutorial. Please download the file, at first, by clicking on the link below.

## Basics of Percentage (%)

**Percentage (%)** is one of the most important **mathematical **concepts that we use in our daily life. The percentage is level playing ground. On the basis of this ground, we compare every performance.

Every **fraction **can be converted to a **percentage **by **multiplying **the **fraction **with **100%**. **100%** is actually **1**.

**How?**

`100% = 100 x (1/100); [% = 1/100] = 1`

We can **multiply **any value by **1**, so we can **multiply **any value or fraction by **100%** as **100%** is actually **1**.

__An Example__

Suppose you **bought **a **stock **at **$63** and **sold **it at **$89**. How much did you **gain **from this **stock**?

`($89 - $63)/$63 = $26/$63 = 0.4127 = 0.4127 x 100% = 41.27%`

In our above example, our dollar gain was:

`$89 - $63 = $26`

When we compare this **gain ($26)** with our** base value $63**, our **gain ratio **is:

`$26/$63 = 0.4127`

To convert this **fraction **(or ratio) to a **percentage**, we have multiplied it by **100%**

`0.4127 = 0.4127 x 100% = 41.27%`

## How to Calculate Percentage in Excel

In Excel, we get **percentages **using the following formula:

**Part/Whole** >> Apply **Percentage Style** on the cell

*Note:** Keyboard Shortcut to Apply Percent Style on a cell: CTRL + SHIFT + %*

Let me explain this concept with an example.

Suppose, you are the manager of several projects. Every project has a **Sales Target** and the year-end **Achievement**.

To find out the **Percentage (%) of Achievement**, follow the steps given below.

**Steps:**

- Firstly, select Cell range
**E5:E14**. - Then, go to the
**Home tab**>> click on**Number Format**.

- After that, change the
**format**to Percentage.

You can also use the keyboard shortcut **CTRL+SHIFT+%** to change the **Format**.

- Next, select Cell
**E5**. - Then, insert the following Formula.

`=D5/C5`

- Now, press
**ENTER**and**copy**the formula for other cells (**E5:E14**) in the column.

- If necessary, you can increase or decrease the decimal points using two commands in the same group (
**Home -> Number**):**Increase Decimal**and**Decrease Decimal**. - You’re done!

So, this is how the percentage works in the Excel environment.

## 4 Ways to Calculate Percentage of Grand Total in Excel

In many cases, you might need to **calculate **the **percentage **of **Grand Total **using **Formula** In Excel, you can do it in the blink of an eye.

### 1. Using Arithmetic Formula to Calculate Percentage of Grand Total for Non-Repetitive Items

In the first method, we will show you how to **calculate **the **percentage **of **grade total **for **non-repetitive** **items **using the **Arithmetic Formula**. Here, **items **will **not repeat **in the column. The **Grand Total** will be at the **end **of the **column **and in a **fixed cell**. We will calculate the **percentage** based on that cell.

To explain this scenario, we have taken the example of a **digital company **that generates **sales **in more than one way.

Follow the steps given below to **calculate **the **percentage **of **Grand Total**.

**Steps:**

- In the beginning, select Cell
**C9**. - Then, insert the following formula.

`=SUM(C5:C8)`

- Next, press
**ENTER**to get the value of the**Grand Total**of**Sales**.

- After that, change the
**Format**of Cell range**D5:D8**going through the steps shown**above**. - Now, select Cell
**D5**. - Then, Insert the following formula.

`=C5/$C$9`

Here, we **divided **the value of Cell **C5 **by **Grand Total **to get the value of **Percentage **of **Grand Total**. You see from the above image that the **Grand Total** cell (**C9**) is an absolute reference in the formulas. This is because, when we copy the formula for other cells in the column, the cell reference **C9** will be unchanged.

- Now, press
**ENTER**. - Then, drag down the
**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Finally, you will get all the values of the
**percentage**of**grand total**in Excel using**Arithmetic Formula**.

**Read More:** **How to Collapse the Table to Show the Grand Totals Only (5 Ways)**

### 2. Use of Advanced Filter Feature and Name Box to Calculate Percentage of Grand Total for Repetitive Items

In this example, we will show how to **calculate **the **percentage **of **Grand Total **using the **formula **in Excel when the **item **can **repeat **in the **column**.

See the data below. The **marketing channels** are repeated in the column. For example, **Facebook **has repeated **5 **times, Google **AdWords **has repeated **4 **times, and so on. At the end of the column, you’re seeing the **total sales **from all the channels.

Follow these steps to get the percentage of **Grand Total** from the above data:

__Step-01:__ Calculating Grand Total to Calculate Percentage of Grand Total

Firstly, we will **calculate **the **Grand Total **of the **Sales**.

- In the beginning, In Cell
**C21**, insert the following formula.

`=SUM(C5:C20)`

Here, we used **the SUM function**, to **add **the values of Cell range **C5:C20**.

- Then, press
**ENTER**to get the value of the**total sales**.

__Step-02:__ Finding Out Unique Records in Column

Now, let’s find out how many **unique records **are in the **column** to **calculate the percentage **of **grand total **in Excel.

- Firstly, select Cell range
**B4:B20**>> open the**Data**ribbon >> click on the**Advanced**command in the**Sort & Filter**group of commands.

- Now, the
**Advanced Filter**dialog box will appear. - After that, choose
**Copy to another location**option. - Then, in the
**List range**field, range**$B$4:$B$20**is already set. - Next, in the
**Copy to**field, input a cell where you want to place the unique records (we will choose**E4**). - Afterward, check mark the
**Unique records only**option. - Finally, click on
**OK**.

- Then, this is what you will get.

__Step-03:__ Making Named Range with Unique Records

Next, we will show you how to **make **a **named range **with **unique records **to **calculate the percentage **of **grand total **in Excel.

- Firstly, select the unique records (created in step 2).
- Then, open the
**Formulas**ribbon. - After that, click on the
**Define Name**command in the**Defined Names**group of commands.

- Now, the
**New Name**dialog box will appear. Keep the options as it is. - Next, click on
**OK**.

__Step-04:__ Hiding Unique Records Column

Here, the data of **Column E **(where unique records are placed) are intermediary. So, we will **hide **the **E column**. This action is not required but it is a good practice.

- In the beginning, select
**Column E**and**Right-click**on it.

- Then, click on
**Hide**.

__Step-05:__ Making Drop-Down List with Unique Records

Now, we will **make **a **drop-down list** with the **unique records **to **calculate percentage **of **grand total **in Excel. Go through the steps given below to do it on your own.

- Firstly, select cell
**G4**>> open the**Data**ribbon >> click on the**Data Validation**command from the**Data Tools**group.

- Now, the
**Data Validation**dialog box appears. - After that, from the
**Allow**drop-down, select the**List**option >> In the**Source**field, input this formula:

`=Marketing_Channel`

- Finally, click on
**OK**.

- Now, In Cell
**G4**, you see a**drop-down**has been created.

__Step-06:__ Calculating Percentage of Grand Total

In the last step, we will show you how you can **calculate **the **percentage **of the **grand total **in Excel using** Formula**.Follow the steps given below to do it on your own.

- In the beginning, select Cell
**G5**. - Then, insert the following formula.

`=SUMIF(B5:B20,F4,C5:C20)/$C$21`

In the above formula, we have calculated the total sales using the **Facebook** channel and divided it by the **Grand Total**:

`=SUMIF (range, criteria, sum_range) / Grand Total`

To understand this formula, you must understand how **the SUMIF function** works. The **SUMIF Function adds **the **cells **specified by a **given condition **or **criteria**.

Syntax of **SUMIF** function: **SUMIF(range, criteria, [sum_range])**

In our example, the **range **is **B5:B20**, the **criterion **is the **Cell **reference **F4 **and the **sum range **is **C5:C20**.

- Now, if we select
**Facebook**in Cell**G4**, we will get the ratio of**Facebook**and the**Grand Total**in Cell**G5**.

- Then, to
**change**the**Number Format**of Cell**G5**, select Cell**G5**. - After that, go to the
**Home tab**>> click on**Number**>> select**Percentage**.

- Finally, we can get the
**percentage**of the**Grand Total**of any**Marketing Channel**.

**Read More:** **How to Make Subtotal and Grand Total in Excel (4 Methods)**

### 3. Using Excel Table to Calculate Percentage of Grand Total in Excel

You can also use an **Excel table **to **calculate **the **percentage **of **Grand Total**. Follow the steps given below to do it on your own dataset.

__Step-01:__ Converting Range into Table

Here, we will show you how to **convert **a **range **into a **table **to **calculate **the **percentage **of **Grand Total**.

- Firstly, select Cell range
**B4:D8**. - Then, in the
**Insert**ribbon >>click on the**Table**command from the**Tables**group of commands.

- Now, the
**Create Table**dialog box will appear. - Then, keep the options as it is and click on the
**OK**

- After that, Your table will be created. Selecting a cell within the table, choosing the
**Total Row**option from the**Design**Design tab appears only when a table is selected.

- Now, show the
**total values**under the**column**.

- Finally, This is what we get.

__Step-02:__ Calculating Percentage of Grand Total

Now, we will show you how to **calculate **the **percentage **of the **grand total **using an Excel **Table**.

- In the beginning, select the first cell (
**D5**) of the**% of Grand Total**column. - After that, input an
**equal sign (=)**>> select the first cell (**C5**) of the**Sales**column. - Then, input the
**division symbol (/)**. - Next, select the Total cell (
**C9**) of the**Sales**column.

- Finally, this is what we get after applying the
**Percent Style**in the column**% of Grand Total**.

*Important: Benefits of using a Table instead of using a range*

*Adding a new row is easy in a table. Select the last cell in the table (excluding the total row) and press the Tab key on your keyboard. A new row will be created with the formula.*

**Read More:** **[Fixed!] Pivot Table Grand Total Column Not Showing (6 Solutions)**

### 4. Inserting Pivot Table to Calculate Percentage of Grand Total in Excel

Here, we have some data of some **Company**, **Workers**, **State, **and their **Revenue**. Now, we will show you how to **calculate **the **percentage **of the **grand total **in the Excel **pivot table** using this dataset.

__Step-01:__ Creating Pivot Table

Here, we will show you how to **create a Pivot** **Table **in Excel to **calculate **the **percentage **of **Grand Total**.

- Firstly, select Cell range
**B4:E15**. - Then, open the
**Insert**tab >> click on**PivotTable**>> select**From Table/Range**.

- Now, the
**PivotTable from table or range**dialog box appears. - Next, keep the options as they are and click on
**OK**.

- Then, we arrange the
**Pivot Table fields**in the following way (image below). Notice that we have placed the**Revenue**field two times in the**Values**.

__Step-02:__ Calculating Percentage of Grand Total

Now, we will show you how to **calculate **the **percentage **of the **grand total **using the **Pivot Table**.

- Firstly, click on the
**Sum of Revenue2**field in the**Values.** - Now, a drop-down will appear.
- Then, click on the
**Value Field Setting**option from the menu.

- Next, the
**Value Field Settings**dialog box will appear. - After that, change the
**Custom Name**field as**% of Grand Total**. - Then, choose the
**Show Values As**tab. - Now, select the option
**% of Grand Total**from the**Show values as**drop-down. - Finally, click on
**OK**.

- Now, you will see the
**Percentage (%) of Grand Total**in the**pivot table**.

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

## Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these explained methods.

## Conclusion

Those are my methods of calculating the percentage of grand total in Excel. I have shown several methods. Most of the methods use Excel formulas. I hope this tutorial has added some value to your Excel journey. Let me know the typos and feedback in the comment section. And, visit **ExcelDemy** for many more articles like this. Thank you!