How To Calculate a Percentage In Excel. A Comprehensive Guide

How To Calculate a Percentage In Excel. A Comprehensive Guide

Many people can’t do percentages.

Not to be unkind, but it’s one of those areas where, mathematically, it can be hard to get your head around, let alone putting together a formula in Excel to do it for you!

How many of these question have you asked yourself?

  1. “How do I write percentage formulas?”
  2. “How do I calculate percentages?”
  3. “How do I increase x by y percent?”
  4. “How do I add x% to y?”
  5. “How do I subtract x% from y?”

These are some of the most common questions and challenges that people have. I get asked so

Today I have simplified it and laid it all out in a post for you. You're welcome!

Feature Download: Grab 2 fantastic takeaways (both free). Watch a free video demonstrating everything discussed below and download a handy cheat sheet (printable reference) summarising all the key steps.

Percentages vs Numbers

The first thing to understand is the correlation between percentages and numbers. Think of it like this:

Percentage sliding scale

Percentage sliding scale

Imagine a scale that goes from 0 to 1, where 0 = 0% (i.e. nothing) and 1 = 100% (i.e. everything).

Now travel half way along that scale and you have 0.5 or 50%.

Travel three quarters of the way along the scale and you have 0.75 or 75%.

So the relationship can be expressed like this:

the percentage = the decimal number multiplied by 100,
with a % symbol added to the end.

Try this.

  1. Enter 0.5 into a cell and press Enter.
  2. Re-select the cell
  3. Click the % icon in the Number group on the Home ribbon. This converts the decimal number to a percentage.
  4. Click the comma stye icon (next to the % button) to convert back to a number. The formatting defaults to 2 decimal places. You can adjust this by clicking the increase/decrease decimal places icons.
  5. Formatting icons at your fingertips on the Home tab

    Formatting icons at your fingertips on the Home tab

Common mistake: Formatting percentages rather than calculating percentages

Consider the following range of numbers:

Starting values

Starting values

This question is often asked:

What is the percentage of each number?

If you select the range of cells and click the % button mentioned above, you're in for a surprise!

Excel is doing exactly what it is supposed to to. It has multiplied each number by 100 and added the % symbol on the end.

Shock! Horror!

Shock! Horror!

How to calculate percentages the proper way

The original question - ‘What is the percentage of each number?’ - doesn’t actually make sense.

The correct question is - ‘What percentage is each number of the total?’

Try this:

  1. Select cells A1:A5, if necessary.
  2. Click the comma style icon to change the formatting back to ordinary numbers.
  3. Decrease the decimal places until you are showing whole numbers again.
  4. Select cell A6.
  5. Click the AutoSum button on the right side of the Home ribbon, to calculate a total.
  6. The AutoSum Button

    The AutoSum Button

  7. In cell B1, type the formula: =A1/A6.
  8. Before you press Enter, press the F4 key to make the call reference absolute (A6 becomes $A$6 which means it is now a fixed reference).
  9. Percentage formula with absolute cell reference

    Percentage formula with absolute cell reference

  10. Press Enter.
  11. Re-select cell B1.
  12. Grab the auto fill handle (the small block on the bottom right corner of the cell) and double-left-click. This will copy the formula down to cell B5. You now have 5 decimal results.
  13. Results currently showing as decimal figures

    Results currently showing as decimal figures

  14. Select cells B1:B5, if necessary.
  15. Click the % icon in the Number group on the Home tab, then increase the decimal places to show all figures to 1 decimal place.
  16. Results now displayed as percentages

    Results now displayed as percentages

  17. Select cell B6.
  18. Click the AutoSum icon. You will see that the total of all the percentages is 100%.
  19. Individual percentage figures will total 100 percent

    Individual percentage figures will total 100 percent

How to add 20% Sales Tax

Or, to put it another way - How to increase a figure by 20%.

Most countries have some form of sales tax. In the UK , its called VAT (Value Added Tax). In Australia it’s called GST (General Services Tax). In the US it’s just called Sales Tax.

Excel doesn’t care what you call it. It just sees a percentage!

So how do you add a percentage of sales tax to a base figure to reach a total?

Try this on a fresh sheet:

  1. In cell A1, type your base figure (e.g. 1,000). NB. Don’t type currency symbols or commas, just the plain number.
  2. In cell B1, type the sales tax percentage (e.g. 20%).
  3. In cell C1, type the formula: =A1*B1. This calculates the amount of sales tax.
  4. In cell D1, type the formula: =SUM(A1, C1). This is your total.

To combine steps 3 and 4 into a single step:

In cell E1, type the formula:

=SUM(A1,A1*B1)

Adding sales tax to a base figure to calculate a total

Adding sales tax to a base figure to calculate a total

Using percentages to remove sales tax from a total

Let’s say that you have a total figure of $1,200 which comprises $1,000 plus $200 sales tax. How do get back to the base figure of $1,000.

The maths goes like this: Divide the total figure by 120% (assuming a sales tax of 20%).

It’s bad practice to use fixed figures like 120% so here’s how to calculate it using a formula that makes use of the 20% cell.

Continuing from the previous example, try this:

In cell F1, type the formula:

=E1/(1+B1)

The formula works like this:

E1 is the total sales figure.

B1 contains 20% or whatever sales tax percentage you used.

  • 20% is the same as 0.2, and
  • 120% is the same as the value 1.2, so
  • 1+0.2 = 1.2, which is the same as saying
  • 100% + 20% = 120%

We could have also written:

=E1/(100%+B1)

Using percentages to calculate interest on a loan

If cell A1 contains a loan amount, let’s say 200,000, and cell A2 contains an interest percentage, let’s say 5% (per annum), you simply multiply one by the other to calculate the amount of interest due per annum, i.e.

=A1*A2

Let’s wrap

I hope you’ve found this useful. It’s surprising how many people struggle with percentages and I hope I’ve simplified the process for you.

Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature

Jason Morrell photo and bio

cheat-sheet-percentages

FREE CHEAT SHEET & VIDEO

Watch the video to see it done and download a printable PDF cheat sheet of all the key steps

PLUS, GET A FREE COPY OF MY 30 EXCEL POWER TIPS

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz