How to Make A Percentage Formula in Excel: Step-by-Step
We use percentages almost everywhere. For example, there’s a 50% off this weekend, monthly sales have risen by 30%, you get an annual increment of 15%, etc.
The percentage is a genius concept, and the best part – they are super easy to calculate.
This guide will help you explore a few ways to calculate and present percentages in Excel. And you’d enjoy it to the core.
Download our free sample workbook here to tag along with the guide as you continue reading.
Table of Contents
Formula for percentage
Back to grade 4 arithmetic, here’s the formula for calculating a basic percentage.
= Value / Total Value * 100
For example, to see what percentage is 30 out of 200, you write it as shown below.
= 30 / 200 * 100
And that’s 15%.
In Microsoft Excel, there is no in-built function for calculating percentages. To reach a percentage in Excel, you need to put together the following formula
= Value / Total Value
And then format it as a percentage.
The only difference between a normal percentage formula and the Excel formula is ‘100’.
Let’s see a quick example of how this works.🙈
1. Here’s an image of the total and secured marks for some students.
Calculate the percentage marks secured by each student.
2. Activate a cell and format it as a percentage by going to Home > Number > Formats > Percentage.
4. In the same cell, now write the percentage formula as below.
= B2 / C2
Don’t forget, always start a formula with an equal sign (=) 😊
Cell B2 contains the value (the secured marks,) and Cell C2 contains the total value (the total marks).
5. Hit ‘Enter’.
Wow! Kevin scored a whopping 88%.✌
6. Drag and drop the same to all the students to calculate percentages for all.
Percentage increase in Excel
Most of the time you’d want to use Excel to calculate the percentage change between two values.
The increase or decrease percentage is calculated by dividing the difference between two numbers by the initial number (the base value).
Calculating percentage increases in Excel is super easy – see for yourself.
1. The data below shows the share prices of some companies over two years.
All the companies show an increase in the share price over the year. But what percent increase is this?
2. To calculate it, write the percentage increase formula shown below:
= (New Share Price – Old Share Price) / Old Share Price
= (C3 – B3) / B3
The above formula gives the change in share price as a percentage of the old share price.
Note the parenthesis to the subtraction function. Adding parenthesis tells Excel to perform subtraction first and the division operation last.
3. Hit ‘Enter’ and drag and drop to have the results, as below.
The shares of Company A show a percentage increase of 12%. This means the share price has increased by 12% since the last year.
If the share prices had declined over the year, the same formula would have been used to calculate the percentage decrease instead.
Showing decimals as percentages
Continuing the same example as above.
Did you see the same upon applying the percentage formula in Excel?
We applied the formula for percentage increase but all that we get is a decimal number.
Don’t fret. This is only because the ‘percentage’ format is yet not applied to the subject cell.
1. Select the cells where the decimals appear.
2. Go to Home > Number > Formats > Percentage.
You may use the shortcut to apply the percentage format by going to the Home tab > Numbers > the percentage Icon.
3. Excel changes the decimal to a percentage.
4. Maybe this percentage is not an exact round number. In most cases, percentages with zero decimal positions are rounded up / down by Excel.
5. To add decimal positions to your percentage, select the cell.
6. Go to Home > Number > Formats > More Number Formats.
7. This launches the ‘Format Cells’ Dialog box as below.
8. Go to Percentage format from the “Format Cells” dialog box.
9. Set the decimal positions to any desired number.
10. Excel adds specified decimal positions to the percentage values, as shown below.
You can also increase or decrease the decimal positions to a percentage by using the shortcut below.
Go to Home > Number > Decimal Increase / Decrease button
That’s it – Now what?
That’s all about how to calculate percentages in Excel.
Very simple but very useful. You’d need percentage calculation almost everywhere. And Excel can help you fast-forward your percentage calculations.
This guide explains how you can calculate percentage change and simple percentages in Excel. It also discusses different ways how you may format and present percentages in Excel. (With many tips to help you save time and hassle).🙌
Calculating percentages in Excel will appreciably help you. However, use it in combination with other major functions of Excel to make the best use of it.
These functions include the VLOOKUP, SUMIF, and IF functions of Excel. My 30-minute free email course takes you through these and many more functions of Excel. Sign up now.
Calculating percentages in Excel can help you with quick analytics. To see articles on more functions of the like nature, check out how to add sparklines in Excel to show trends.