FREE CHEAT SHEET
All the key points in one handy PDF
Plus, collect a FREE copy of our 30 Excel Power Tips
ENTER YOUR DETAILS FOR IMMEDIATE ACCESS
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?
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!
The first thing to understand is the correlation between percentages and numbers. Think of it like this:
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.
Consider the following range of numbers:
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.
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?’
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:
To combine steps 3 and 4 into a single step:
In cell E1, type the formula:
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:
The formula works like this:
E1 is the total sales figure.
B1 contains 20% or whatever sales tax percentage you used.
We could have also written:
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.
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.