Calculating percentages in Excel can be extremely useful, regardless of how you use spreadsheets. From organizing personal finances to calculating business profits over time, Excel allows you to see percentages related to a wide range of numerical datasets. But in order to actually use percentages in Excel, you’ll need to know the correct formulas.
In today’s guide, we will give you all of the Excel percentage formulas you need for the most common percentage calculations, along with step-by-step instructions to implement the formulas and format them correctly. And remember, if you need additional help setting up percentage formulas in your worksheet or troubleshooting issues in Excel, feel free to reach out to the experts at Geeker today!
What Is The Percentage Formula In Excel?
If you just want to find a percentage using two data points, you’ll need to use the standard Excel percentage formula:
Percentage = (Part / Whole)*100
As you can see, Excel calculates percentages the same way people do. By simply typing in the correct formula, you can instruct Excel to give you the percentage using two values in your worksheet. More specifically, you’ll be telling Excel to divide a “part” from the “whole,” and then multiply the result by 100. To apply this formula in Excel, just follow these steps:
- Enter the “Part” value in the cell of your choice (Ex. A1)
- Enter the “Whole” value in a different cell (Ex. B1)
- In a third cell (Ex. C1), type this formula: =(A1/B1)*100
- Press “Enter” to complete the formula and see the result in the third cell (C1)
If you’re using different cells, the appearance of your formula will change. For example, if you put the Part in B3 and the Whole in D5, your formula should look like this:
=(B3/D5)*100
Since the result will just give you a number out of 100, you may want to format it so that it actually shows the percent symbol (%) after the number. To format the result as a percentage, follow these steps:
- Select the cell with the percentage result
- Go to the “Home” tab
- In the “Number” group, click the dropdown arrow next to the “Number Format” box
- Select “Percentage” from the list of number formats
Keep in mind that formatting works the same for all of the Excel percentage formulas outlined in this guide, so you may need to reference this section more than once.
How To Calculate Excel Percentages
The formula outlined above is pretty basic math that you can do with a calculator in seconds. Fortunately, Excel has various built-in formulas for calculating percentages using different types of data. Here are the most common ones you’ll need to know:
Percentage Change
This metric refers to the change between two values, represented as a percentage. Essentially, it subtracts the “Old Value” from the “New Value,” divides the result by the “Old Value,” and then multiplies by 100 to calculate the percentage change between the two numbers. Here is the formula you’ll need to use:
Percentage Change = ((New Value – Old Value) / Old Value)*100
To apply this formula in Excel, follow these steps:
- Enter the “Old Value” in a cell (Ex. A1)
- Enter the “New Value” in a different cell (Ex. B1)
- In a third cell (Ex. C1), type the formula: =((B1-A1)/A1)*100
- Press “Enter” to complete the formula
You can format the resulting number as a percentage using the same steps outlined in the previous section.
Percentage Increase Formula
If you’ve already tried out the percentage change formula, then calculating percentage increase will be really easy. Both of these results can be found using the same formula. The only difference is that, when calculating percentage increase, the “New Value” must be higher than the “Old Value.” Here is the formula to calculate percentage increase:
Percentage Increase= ((New Value – Old Value) / Old Value)*100
You can follow the steps outlined in the previous two sections to apply this formula in Excel and then convert the result to a percentage.
Percentage Decrease Formula
The percentage decrease formula is also the same as the percentage change and percentage increase formulas. But the “rules” for calculating percentage decreases are the opposite of the rules for calculating percentage increases. In other words, the “Old Value” needs to be higher than the “New Value” to find the percentage decrease. Here is the formula you’ll need to use:
Percentage Decrease= ((Old Value – New Value) / Old Value)*100
Again, you can follow the steps outlined in the sections above to apply this formula in Excel and then convert the result to a percentage.
Percentage Difference Formula
Calculating the percentage difference in Excel between two numbers is a little more complex than the previous calculations. This is because the percentage difference requires more steps, as you’ll be calculating the absolute difference between two values and the average of the same two values. Here is the formula for calculating percentage difference:
Percentage Difference= (Absolute Difference / Average of the Two Numbers)*100
To apply this formula in Excel, follow these steps:
- Enter the first number in a cell (Ex. A1)
- Enter the second number in a different cell (Ex. B1).
- In a third cell (Ex. C1), type the formula: =(ABS(A1-B1)/((A1+B1)/2))*100
- Press “Enter” to complete the formula
Once again, you can use the same method outlined above to convert the resulting value into a percentage.
How To Calculate Average Percentages In Excel
The simplest way to calculate average percentages in Excel is to use the AVERAGE function. This function finds the mean of a range of cells. You can follow these steps to do it:
- Enter the percentage values in a range of cells (Ex. A1 through A10)
- In a different cell (Ex. B1), type this formula: =AVERAGE(A1:A10)
- Press “Enter” to complete the formula
Keep in mind that the AVERAGE function will correctly calculate the average even if the values in the range do not have the percent symbol. Excel internally stores percentage values as decimals, so the AVERAGE function will calculate the mean of the decimal values whether or not you’ve formatted them as percentages.
How To Show Percentages In Excel Charts
Adding percentages to a chart in Excel is a great way to get a visual representation of your data. You can show percentages in Excel charts by following these steps:
- Select your data range
- Go to the “Insert” tab and select the type of chart you want from the “Charts” group
- Go to the “Chart Design” tab and click on “Add Chart Element” in the “Chart Layouts” group
- Select “Data Labels” and choose the desired position for your data labels
- Right-click on any of the data labels in the chart and select “Format Data Labels” from the menu
- In the “Format Data Labels” pane, check the “Percentage” box under the “Label Options” section
- (Optional) Uncheck the “Value” box if you only want to display percentages
The data labels on your chart will now display the percentages. If you want to further format the data labels by font, size, or color, you can do so using the options available in the “Format Data Labels” pane.
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!