How to Use Excel Formula to Calculate Percentage of Grand Total

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?

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?

In our above example, our dollar gain was:

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

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

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.

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

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

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.

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:

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

In the above formula, we have calculated the total sales using the Facebook channel and divided it by the 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!

Related Articles

• Show Grand Total with Secondary Axis in Pivot Chart
• How to Add Grand Total to Stacked Column Pivot Chart
• Remove Grand Total from Pivot Table (4 Quick Ways)
• How to Add Grand Total to Bar Chart in Excel (With Easy Steps)