How to Calculate Percentage of Total in Excel

You can calculate percentages in Excel using basic multiplication and division. I’ll show you how to use this to make quick work of calculating a column of percentages.

In the example below, column B shows travel expenses for the year, and a yearly total (using the SUM function). In column C, we want to know the percentage that each month contributed to the total. Think of a pie chart: the total in B18 is the whole pie, and the percentages in column C will be the slices.

In C5, this formula divides the January amount by the total:
=B5/B18

But I won’t enter it, yet! In order to save time and minimize data entry, I want to auto-fill the formula down to row 18. This will change the numerators (January through December), but leave the denominator fixed on B18 because I want to show each month divided by the same total.

To do this, I’ll make the denominator an absolute reference by clicking it and pressing the F4 key on the keyboard. That makes the formula look like this:

=B5/$B$18

Tip: $B tells Excel to not rewrite the columns when I fill, and $18 tells Excel not to rewrite the rows. In this instance, it doesn’t matter if I have a dollar sign before the B or not, since I’m going to fill straight down the column, and it wouldn’t change, anyway.

Now I’ll enter the formula, but the result is a decimal:

Instead of showing this long decimal, I want a percentage and I want to round it off after two digits. This takes just one click:

Select the long decimal in C5, then click the Percent Style button on the Home tab of the Ribbon bar.

Tip: To increase or decrease the number of decimal places showing, click the Increase Decimal or Decrease Decimal button on the Ribbon bar.

This gives me the percent that I want:

Now I’ll auto-fill the formula down to row 18. When you click any cell, do you notice the small dot in the lower-right corner?

When you roll the mouse over the dot, the mouse pointer becomes a crosshair.

Now I’ll drag the crosshair downwards:

Now the entire column is filled with percentages (I deleted the zero in C17).

If you’d like to examine this worksheet, download Percentage Formula 1_travel expenses.xlsx. You can test yourself by deleting all the numbers down column C and trying it on your own.