How to Calculate Percentages in Excel: Formulas & Examples

Microsoft Excel is a powerful tool for working with numbers and performing mathematical calculations. One of the most common operations in Excel is calculating percentages. Whether you’re analyzing sales data, creating a budget, or tracking student grades, knowing how to calculate percentages in Excel is essential. In this guide, we’ll cover the key formulas you need to master percentage calculations in Excel.

Calculating the Percentage of a Total

Imagine you’re a teacher who wants to calculate the percentage of students who passed a test. Let’s say 36 out of 50 students passed. Here’s how you can find the percentage in Excel:

1. Select an empty cell.
2. Enter the formula: =36/50
3. Press Enter. The result will be 0.72.
4. To convert the decimal to a percentage, click the Percent Style (%) button on the Home tab.

The cell will now display 72.00%, indicating that 72% of the students passed the test.

Tip: Use the Increase Decimal or Decrease Decimal buttons to control the number of decimal places shown.

Finding the Percentage Change Between Two Values

Calculating the percentage change is useful for tracking growth or decline over time. For instance, let’s say your company’s revenue was $850,000 in 2021 and $920,000 in 2022. To find the percentage change:

1. Select a blank cell.
2. Type the formula: =(920000-850000)/850000
3. Press Enter. You’ll see the result 0.0823529411764706.
4. Format the result as a percentage by clicking the Percent Style button.

The percentage change is 8.24%, indicating an increase in revenue from 2021 to 2022.

If revenue decreased to $890,000 in 2023, you can calculate the percentage decrease from 2022 using the same formula:

=(890000-920000)/920000

This returns -3.26%, showing the percentage drop in revenue.

Determining the Original Value from a Percentage

Sometimes you’ll encounter a situation where you know a percentage and need to work backward to find the original value. For example, suppose a store is offering a 30% discount on a jacket. The sale price is $70. To calculate the original price before the discount:

1. Select an empty cell.
2. Enter the formula: =70/0.7
3. Press Enter. The result is 100.
4. Format the cell as currency using the Accounting Number Format button on the Home tab.

The original price of the jacket was $100.00 before the 30% discount.

Calculating an Amount from a Total and Percentage

In this scenario, you know the total and percentage and want to find a specific amount. Let’s say you’re purchasing a $1,200 laptop and need to pay a 6.5% sales tax. To calculate the tax amount:

1. Select a blank cell.
2. Type the formula: =1200*0.065
3. Press Enter. The result is 78.
4. Apply the Accounting Number Format to display the result as currency.

The sales tax on the $1,200 laptop is $78.00.

Increasing or Decreasing a Value by a Percentage

You can also use Excel formulas to increase or decrease a number by a certain percentage. For example, if your monthly rent is $1,500 and your landlord plans to raise it by 5%, you can find the new rent amount like this:

1. Select an empty cell.
2. Enter the formula: =1500*(1+0.05)
3. Press Enter. The result is 1575.
4. Format the cell as currency.

Your new monthly rent after the 5% increase will be $1,575.00.

To decrease a value by a percentage, simply replace the plus sign (+) with a minus sign (-) in the formula. If your goal is to reduce your monthly grocery budget of $600 by 15%, the formula would be:

=600*(1-0.15)

The result, formatted as currency, is $510.00, your new grocery budget after the 15% reduction.

Calculating Percentage of Completion

Excel can also help you track progress on projects by calculating the percentage of completion. For example, let’s say you’re writing a book with 20 chapters. You’ve finished 7 chapters so far. To find the percentage of the book you’ve completed:

1. Select a blank cell.
2. Enter the formula: =7/20
3. Press Enter. The result is 0.35.
4. Format the result as a percentage.

You’ve completed 35% of the book so far.

Finding Percentage Allocation

When working with budgets or investment portfolios, you may need to allocate funds based on percentages. For instance, suppose you have a total investment of $10,000 and want to allocate 60% to stocks and 40% to bonds. To calculate the dollar amounts for each allocation:

1. For stocks, enter the formula: =10000*0.6
2. For bonds, enter the formula: =10000*0.4
3. Press Enter after each formula. The results are 6000 and 4000.
4. Format the cells as currency.

You should invest $6,000 in stocks and $4,000 in bonds to achieve the 60/40 allocation.

Calculating Weighted Average with Percentages

Weighted averages are useful when some values contribute more to the average than others. For example, let’s say you have three exams with different weights: Exam 1 (30%), Exam 2 (50%), and Exam 3 (20%). Your scores are 85, 92, and 78, respectively. To calculate your weighted average:

1. Multiply each score by its weight:

• Exam 1: 85 * 0.3 = 25.5
• Exam 2: 92 * 0.5 = 46
• Exam 3: 78 * 0.2 = 15.6

1. Add the weighted scores: 25.5 + 46 + 15.6 = 87.1

Your weighted average score is 87.1.

Converting Between Percentages, Decimals and Fractions

A percentage in Excel is just a number shown with a % format: 75% is stored as 0.75. So you convert by formatting, not with a function:

• Decimal to percentage: select 0.65 and press Ctrl + Shift + %; it shows 65%.
• Percentage to decimal: set the cell to General or Number; 75% shows 0.75.
• Whole number like 40 to 40%: use =A1/100 and format it as a percentage.
• Percentage as a fraction: format the cell as Fraction (Ctrl + 1 > Fraction); 40% shows 2/5.

(The CONVERT function is for units such as miles and kilometres. It has no percentage units.)

More Tips for Working with Percentages in Excel

• When creating formulas, make sure to use parentheses to control the order of operations. For instance, =(A1/B1)*100 is different from =A1/(B1*100).
• If you’re working with large datasets, consider using absolute cell references ($A$1) instead of relative references (A1) to keep formulas consistent when copied to other cells.
• Customize the number of decimal places displayed by using the Increase Decimal and Decrease Decimal buttons, or click the dialog launcher in the Number group on the Home tab for more options.
• Use the Percentage number format to display values as percentages without multiplying by 100 in your formulas.

Final Thoughts

Excel provides a wide range of tools and functions for calculating percentages, including basic formulas and percentage formatting. By mastering these techniques, you’ll be able to efficiently analyze data, create budgets, track progress, and make informed decisions. Whether you’re working with simple percentages or more complex weighted averages, Excel has the tools you need to get the job done.

FAQs