Excel formula to calculate percentage of grand total (4 Easy Ways)

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 Excel File

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, 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 a 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%

Percentage in Excel

In Excel, we get percentage 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 Sales Target and the year-end Achievement.

To find out the Percentage (%) of Achievement, follow these steps:

  • In the cell D2, input this formula: =C2/B2 and press Enter key on your keyboard.

calculate percentage of grand total in excel using formula directly

  • Then copy the formula for other cells (D3:D11) in the column.

copy formula in other cells in excel

  • Now select the cell range (D2:D11) and apply Percent Style format (keyboard shortcut: CTRL + SHIFT + %) from the Number group of commands in the Home

applying percent style in the cells

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

How to calculate the percentage of grand total in Excel

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

Calculating the percentage of Grand Total can be two types:

  • First type: 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.
  • Second type: In the Grand Total column, the items can repeat. But Grand Total will be at the end of the column and in a fixed cell.

1) First type: Items don’t repeat in the column

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

Sales from different marketing channels

Follow these steps to calculate the percentage of Grand Total:

  • At the end of the year, the company has calculated their total sales (Grand Total) from different channels. In the cell B10, we have used this formula: =SUM(B2:B9) to calculate the total sales (Grand Total).

calculating grand total in excel

  • The performance of any channel can be evaluated in this way: (The sales generated by a channel / Total Sales generated by all the channels) x 100%. That is, you’re seeing in the following image.

percentage of grand total in excel

You see from the above image that the Grand Total cell (B10) is an absolute reference in the formulas. This is because, when we will copy the formula for other cells in the column, the cell reference B10 will be unchanged.

I have kept some rows (row 6, 7, 8, and 9) blank. This is because: later when you would use more channels to generate sales, you would be able to add them in those blank rows.

2) Second type: Items can repeat in the column

In this example, I will show how to calculate the percentage of Grand Total when the item can repeat in the column.

See the data below. The marketing channels are repeating 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.

Sales data from different marketing channels

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

Step 1: Calculate Grand Total

In the cell B21, calculate the total sales using this formula: =SUM(B2:B20).

Step 2: Find out the unique records in the column

Now let’s find out how many unique records are in the column.

  • Select the range A1:A17 -> Open the Data ribbon -> Click on the Advanced command in the Sort & Filter group of commands

advanced filter command in excel

  • Advanced Filter dialog box will appear. Choose Copy to another location option -> In the List range field, range $A$1:$A17 is already set -> In the Copy to field, input a cell where you want to place the unique records (I choose G1) -> Check mark Unique records only option -> Finally click on the OK

advanced filter excel options

  • This is what you get.

finding unique records using advanced filter in excel

Step 3: Make a named range with the unique records

  • Now select the unique records (created in step 2) -> Open the Formulas ribbon -> click on the Define Name command in the Defined Names group of commands.

define name command in excel

  • New Name dialog box will appear. Keep the options as it is. Just click on the OK

New Name dialog box in Excel

Step 4: Hide the unique records column

The data of column G (where unique records are placed) are intermediary. So, we hide the G column. This action is not required but it is a good practice.

Hiding a column in excel

Step 5: Make a drop-down list with the unique records

  • Select the cell E1 -> open the Data ribbon -> click on the Data Validation command in the Data Tools group of commands

data validation in excel

  • Data Validation dialog box appears. From the Allow drop-down, select the List option -> In the Source field, input this formula: =Marketing_Channel -> Finally, click on the OK

data validation dialog box in excel

  • In the cell E1, you see a drop-down has been created.

creating a drop down list in excel

Step 6: Calculate the percentage of Grand Total

In the cell E2, input this formula: =SUMIF(A2:A20,E1,B2:B20)/$B$21

using sumif function in excel

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 have to understand how SUMIF function works.

SUMIF Function adds the cells specified by a given condition or criteria.

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

In our example, range is A2:A20, the criterion is the cell reference E1 and sum range is B2:B20.

Total sales using the Facebook channel is: $18400

Using F9 key in excel to see the output value of an excel formula

How did I find that?

I have just selected the SUMIF function part in the formula and then have pressed the F9 key in the keyboard.

Internally, Excel divides the 18400 value by the total sales 61700. As the cell is formatted with Percent Style, we see the percentage value in the cell E2.

percentage of grand total in excel

This happens when you change the Marketing Channel.

calculate percentage of grand total in excel using formula

3) Calculating the percentage of Grand Total using Excel Table

You can also use an Excel table to calculate the percentage of Grand Total.

Step 1: Converting the range into a table

  • Select a cell in the range -> In the Insert ribbon, click on the Table command from the Tables group of commands

sales data from different marketing channels

  • Create Table dialog box will appear -> Keep the options as it is and click on the OK

create excel table dialog box

  • Your table is created. Selecting a cell within the table, choose the Total Row option from the Design Design tab appears only when a table is selected.

showing total row in excel table

  • Now show the total values under the column.

sum total row in excel table

  • This is what we get.

excel table with total row

Step 2: Calculating the percentage of grand total

Select the first cell (C2) of the % of Grand Total column -> Input an equal sign (=) -> Select the first cell (B2) of the Sales column -> Input the division symbol (/) -> then select the Total cell (B6) of the Sales column -> Finally, click on the OK button.

input formula in excel table

This is what we get after applying the Percent Style in the column % of Grand Total.

applying percent style in excel table

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 Tab key on your keyboard. A new row will be created with the formula.

4) How to calculate the percentage of grand total in Excel Pivot Table

Here is some dummy data.

excel pivot table sample data

Let me show you how to calculate the percentage of grand total in Excel pivot table. Follow these steps:

Step 1: Creating the pivot table

  • Select a cell within the range -> Open the Insert tab -> and choose the PivotTable command from the Tables group of commands.

excel pivot table command

  • Create PivotTable dialog box appears. Keep the options as it is. If you want to know more about creating a pivot table, read this guide. Click on the OK

create pivot table dialog box in excel

  • I arrange the Pivot Table fields in the following way (image below). Notice that I have placed the Revenue field two times in the Values

excel pivot table example

Step 2: Calculating percentage of grand total

  • Click on the Sum of Revenue2 field in the Values A drop-down will appear. Choose the Value Field Settings… option from the menu.

value field settings command in excel pivot table

  • Value Field Settings dialog box will appear. Change the Custom Name field as % of Grand Total -> Choose the Show Values As tab -> Select the option % of Grand Total from the Show values as drop down -> Finally, click on the OK

value field settings dialog box excel

  • You see % of Grand Total is created in the pivot table.

percent of grand total in excel

Note: In Excel 2016, % of Grand Total column shows the values in percentages. If you’re using a version where it does not show the percentages automatically, here is the solution.

Open the Value Field Settings dialog box of the % of Grand Total field -> Click on the Number Format command

number format in excel pivot table

Format Cells dialog box will appear. Open the Percentage window and make changes as per your need.

format cells dialog box in excel

Related Readings

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 formula. I hope this tutorial has added some value to your Excel journey. Let me know the typos and feedbacks in the comment section.

Happy Excelling!


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply