A guide to percentages in Excel

Do you know your percentages?

Whether it's interest rates, profit margins, restaurant bills, or possession in football matches, you haven't got to look far for examples of where percentages are used.

In this blog post, we're going have a look at the most common ways of calculating percentages in Excel.

Before we begin, It's worth noting these examples all assume the use of the Percentage number format (found on the Home tab), removing the need to multiply by 100. That's because percentages are actually stored as decimals under the bonnet, so 10% is 0.1, 50% is 0.5, and 100% is 1, for example.

Calculate the percentage of a total

Divide the part by the total to return it as a percentage.

part ÷ total = %

For example, 58 marks out of a total of 100 represents 58%.‍

58 ÷ 100 = 58%

Formula in C9
‍=A9:A14/B9:B14

Calculate the percentage change of a value

Divide the new value by the old one, and subtract 1 from the result to return the percentage change. 

(new value ÷ old value) − 1 = % change 

For example, the jump from £1,000 last month to £1,125 this month is a 25% increase.

(£1,125 ÷ £1,000) − 1 = 25%

Formula in I9
‍=(H9:H14/G9:G14)-1

Calculate the percentage change of a percentage

Subtract the old percentage from the new one, and divide the result of this by the old percentage to return the percentage change.

(new % – old %) ÷ old % = % change

For example, the jump from 5% yesterday to 10% today is a 100% increase.

(10% − 5%) ÷ 5% = 100%

Formula in O9
=(N9:N14-M9:M14)/M9:M14

Calculate the value after a percentage change

Add 1 to the percentage change, and multiply the result of this by the old value to return the new value.

old value × (1 + % change) = new value

For example, after a current price of £50 is increased by 10%, the new price becomes £55.

£50 × (1 + 10%) = £55

Formula in U9
‍=S9:S14*(1+T9:T14)

Watch the video

‍

Calculate the percentage share of a single column

So far, all the examples you've seen have been based on two columns. In this section, let's look at how to calculate the percentage a value represents based on the column's total. 

The calculation, number÷(53+72+18+90+23), is the same for each method; however, the techniques vary.

Method 1

This old-style way divides each number by the sum of the range. It requires five formulas for five results.

Formulas in C3:C7
=A3/SUM($A$3:$A$7)
=A4/SUM($A$3:$A$7)
=A5/SUM($A$3:$A$7)
=A6/SUM($A$3:$A$7)
=A7/SUM($A$3:$A$7)

Method 2

The new PERCENTOF function allows you to reference the part and the whole in separate arguments without explicitly dividing. However, it also requires five formulas.

Formulas in F3:F7
=PERCENTOF(A3,$A$3:$A$7)
=PERCENTOF(A4,$A$3:$A$7)
=PERCENTOF(A5,$A$3:$A$7)
=PERCENTOF(A6,$A$3:$A$7)
=PERCENTOF(A7,$A$3:$A$7)

Method 3

By combining PERCENTOF with MAP and LAMBDA, the numbers can be processed individually in a single formula, allowing the results to be spilled.

Formula in  I3
=MAP(A3:A7,
LAMBDA(x,
PERCENTOF(x,A3:A7)
)
)

Method 4

PERCENTOF can be avoided completely by storing the numbers inside BYROW and specifying SUM in the [function] argument. This takes advantage of Excel's new eta lambda capabilities, preventing the need to write LAMBDA(x,SUM(x)) instead.

Formula in L3
=BYROW(A3:A7,SUM)
/
SUM(A3:A7)

At the time of writing, the PERCENTOF function and eta lambda support are only available to Microsoft 365 Insiders.

Watch the video

‍

Conclusion

As you've seen, there are numerous ways to calculate percentages in Excel. With all the main methods covered, hopefully this blog post will serve as a useful guide you can refer to when necessary. After all, it's hard to memorise everything!

For more Excel tips and tricks like this, check out our Video Tutorials page.