Resources

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.

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`

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`

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`

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)`

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.

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)

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)

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)

)

)

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

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.

## Conversation

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

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.