A guide to percentages in Excel
Resources

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.

About the Author

Andrew is a technical writer who specialises in Microsoft Excel and data analysis. He has published hundreds of articles and social media posts aimed at helping people enhance their skills and spreading good practices.

Conversation

0 Comments

As you comment, let's keep the discussion friendly and respectful to add value to the conversation.

Thank you! Your comment has been sent!
Oops! Something went wrong while submitting the form.
Commenter profile pic.
Author Name
Comment Time

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

Reply
Delete
Commenter profile pic.
Author Name
Comment Time

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

Reply
Delete

REGISTER YOUR INTEREST

Our next event takes place in February 2025. Register your interest in attending and be the first to know when registration is live!