How to Calculate Percentages in Excel: Formulas & Examples

Sharing is caring!

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.

Using the CONVERT Function for Percentage Conversions

Excel’s CONVERT function allows you to easily convert values between different measurement systems, including percentages. This can be particularly useful when working with different units or when you need to quickly convert percentages to decimals or fractions.

The syntax for the CONVERT function is:

=CONVERT(number, from_unit, to_unit)

• number: The value you want to convert.
• from_unit: The unit of the original value.
• to_unit: The unit you want to convert to.

For percentage conversions, use the following units:

• “%” for percentage
• “1” for decimal
• “1/100” for fraction

Let’s look at some examples.

Converting Percentage to Decimal

To convert a percentage to a decimal, use the formula:

=CONVERT(A1, “%”, “1”)

If cell A1 contains the value 75%, the formula will return 0.75.

Converting Decimal to Percentage

To convert a decimal to a percentage, use the formula:

=CONVERT(B1, “1”, “%”)

If cell B1 contains the value 0.65, the formula will return 65%.

Converting Percentage to Fraction

To convert a percentage to a fraction, use the formula:

=CONVERT(C1, “%”, “1/100”)

If cell C1 contains the value 40%, the formula will return 40/100, which can be simplified to 2/5.

The CONVERT function makes it easy to switch between different representations of percentages, saving you time and effort when working with various 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/(B1100).
• 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, the CONVERT function, and various formatting options. 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

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.