How to Calculate Percentage In Excel – An Easy Step-By-Step Guide

Updated on July 13, 2024

Article Outline

Learning how to calculate percentage in Excel is crucial to various tasks, whether it has to do with finances, data, or the grades one receives. Excel makes this task easier with its variety of functions and formulas. It is time to get more detailed and find out what steps we need to take to calculate percentages in Excel as fast as possible.

Using Basic Percentage Formula in Excel

Applying Basic Percentage Calculation

Calculating percentages starts with understanding the basic formula:

 

Percentage=(Part)/(Total)×100

 

In Excel, it is unnecessary to multiply by 100 because Excel formats the result of this operation to percentage automatically. Let’s walk through an example:

 

  1. Enter the total value in cell A1 (e.g., 200).
  2. Enter the part value in cell B1 (e.g., 50).
  3. In cell C1, type the formula: =B1/A1.

Excel will show the result in decimal form. To convert this to a percentage, we can format the cell:

 

  1. Select cell C1.
  2. Choose the “Home” tab in the toolbar and press the “%” button.

This action converts the decimal to a percentage. In our example, 50 divided by 200 results in 0.25, which Excel translates into 25 percent.

Formatting Cells to Display Percentages

Appropriate formatting of cells is very important to avoid confusion. To ensure our results are easily understandable, we should always format cells correctly:

 

  1. Choose the cell or range of cells that you want to format.
  2. Navigate to the “Home” tab.
  3. Next to the “Number” group, click the dropdown and choose “Percentage”.

Additionally, we can adjust the number of decimal places:

  1. Once you have the cell selected, go to the “Number” group in the “Home” tab and click the “Increase Decimal” or the “Decrease Decimal” buttons.

For example, if we get a percentage of 25. 6789%, we might want to present it only to two decimal places and format it to be 25. 68%. 

*Image
Get curriculum highlights, career paths, industry insights and accelerate your technology journey.
Download brochure

Calculating Percentages Automatically in Excel

Setting Up Automatic Calculations

Excel allows us to calculate percentages, which makes our work easier and faster. A simple real-life example would be computing the percentage of marks achieved by the students.

 

Assume we have a table of student scores:

 

Student Name Total Marks (B) Marks Obtained (C) Percentage (D)
John 500 450 C2/B2
Jane 500 400 C3/B3
Mark 500 475 C4/B4

 

To calculate the percentage of marks obtained:

 

  1. Type the student names in column A.
  2. Enter the total marks in column B.
  3. Enter the marks obtained in column C.
  4. In column D, we will calculate the percentage using the formula: =C2/B2.

After you fill the formula in D2, click Enter. Drag the fill handle from D2 to D4 to apply the same formula to the other cells. Excel will show the percentages as decimals. We can then proceed to format these cells into percentages, as explained above.

 

Output:

 

Student Name Total Marks Marks Obtained Percentage
John 500 450 90%
Jane 500 400 80%
Mark 500 475 95%

Using Built-In Excel Functions for Percentages

Excel has many built-in functions that have made our calculations even easier. For instance, when calculating the percentage change between two values, we use the formula:

 

Percentage Change=(New Value−Old Value)/(Old Value)

 

Let’s apply this to a practical example:

Assume we have monthly sales data:

 

Month Sales
Jan 1000
Feb 1200
Mar 1100

 

To calculate the percentage change from January to February:

 

  1. Enter the sales data in columns A and B.
  2. In cell C2, type the formula: =(B2 – B1)/B1.

Press Enter, then format cell C2 as a percentage. Use the fill handle to spread the formula in other cells. This method gave a quick view of how sales had transformed from one month to another.

 

Output:

 

Month Sales Percentage Change
Jan 1000
Feb 1200 20%
Mar 1100 -8.33%

Calculating Percentage of a Total in Excel

It is a very general task to calculate percentages in Excel. It can help us to smoothly complete any task related to datasets like sales data and exam scores. It can be used to find out the portion each entry is contributing to the whole.

Calculating with Total at the End of the Table

Often, the total is listed at the end of a table. Let’s consider an example:

 

Product Sales
Apples 150
Oranges 200
Bananas 100
Total 450

 

We want to find out what percentage of total sales each product represents. Here’s how:

 

  1. Enter the data in columns A and B.
  2. In cell C2, type the formula to calculate the percentage: =B2/$B$5.

The dollar signs create an absolute reference, ensuring the total remains fixed when we drag the formula.

 

  1. Press Enter, and drag the fill handle from C2 to C4.

Excel will display the percentages as decimals. We can format these cells as percentages:

 

  1. Select cells C2 to C4.
  2. Click the “%” button in the toolbar under the “Home” tab.

Now, we can see what percentage of total sales each product represents:

 

Output:

 

Product Sales Percentage of Total Sales
Apples 150 33.33%
Oranges 200 44.44%
Bananas 100 22.22%
Total 450

Utilising Absolute References in Formulas

Even when copying the formula to other cells, if you want a part of the formula to remain unchanged, absolute reference can be helpful. It is highly efficient in many scenarios. Let’s consider an example: we are finding out the target percentage achieved by each salesperson. This percentage will directly affect the total commission they will receive.

 

Salesperson Sales Target Percentage of Target Achieved
Alice 5000 20000
Bob 3000 20000
Carol 7000 20000

 

To calculate the percentage of the target each salesperson achieved:

 

  1. Enter the sales data in columns A, B, and C.
  2. In cell D2, type the formula: =B2/$C$2.
  3. Press Enter, and drag the fill handle from D2 to D4.

Excel will show the percentages of the target each salesperson achieved. Formatting these cells as percentages will make the data more readable:

 

Output:

 

Salesperson Sales Target Percentage of Target Achieved
Alice 5000 20000 25.00%
Bob 3000 20000 15.00%
Carol 7000 20000 35.00%

 

Calculating Percentage Increase or Decrease Between Values

It is essential to know how any value changes with time. This can be very helpful in tracking sales growth, stock prices, or expenses of a particular department. Finding the percentage increase or decrease in data with time can help us understand the trend, predict upcoming events, and develop strategies.

Formula for Percentage Increase

The formula for calculating percentage increase is:

 

Percentage Increase=(New Value−Old Value)/(Old Value)

 

Let’s apply this to a practical example. Assume we have sales data for two months:

 

Month Sales
January 1000
February 1200

 

To calculate the percentage increase from January to February:

 

  1. Enter the data in columns A and B.
  2. In cell C2, type the formula: =(B2 – B1)/B1.
  3. Press Enter and format cell C2 as a percentage.

Excel will display the result as 20%, indicating a 20% increase in sales from January to February:

Output:

 

Month Sales Percentage Increase
January 1000
February 1200 20.00%

Formula for Percentage Decrease

The formula for calculating percentage decrease is similar:

 

Percentage Decrease=(Old Value−New Value)/(Old Value)

 

Consider a scenario where sales drop from March to April:

 

Month Sales
March 1500
April 1300

 

To calculate the percentage decrease from March to April:

 

  1. Enter the data in columns A and B.
  2. In cell C2, type the formula: =(B1 – B2)/B1.
  3. Press Enter and format cell C2 as a percentage.

Excel will show a 13.33% decrease in sales:

 

Output:

 

Month Sales Percentage Decrease
March 1500
April 1300 13.33%

 

Using Advanced Excel Functions for Percentage Calculations

Employing SUMIF for Conditional Percentage Calculations

The SUMIF function is powerful for conditional calculations. It sums values based on specified criteria. Let’s look at sales data across different regions:

 

Region Sales
North 5000
South 3000
East 4000
West 2000
North 6000
South 3500

 

To find the total sales for the North region:

 

  1. Enter the data in columns A and B.
  2. In cell C1, type the formula: =SUMIF(A:A, “North”, B:B).
  3. Press Enter.

Excel will sum the sales for the North region. We can use this result to calculate the percentage of total sales:

 

  1. Enter the total sales in cell D1 (e.g., 23500).
  2. In cell E1, type the formula: =C1/D1.
  3. Press Enter and format cell E1 as a percentage.

This gives us the percentage of total sales for the North region:

 

Output:

 

Region Sales Total Sales for North Total Sales Percentage of Total Sales
North 5000 11000 23500 46.81%
South 3000
East 4000
West 2000
North 6000
South 3500

 

Calculating Percentages Across Multiple Rows

It is a very common activity to find out the percentages across multiple rows. Let’s consider an example: we have the sales data of different products, and we want to calculate the contribution of each with respect to the total sales:

 

Product Sales
Apples 3000
Oranges 5000
Bananas 2000

 

To calculate each product’s percentage of total sales:

 

  1. Enter the data in columns A and B.
  2. In cell C1, type the formula: =B1/SUM(B:B).
  3. Press Enter, and drag the fill handle from C1 to C3.

Excel will calculate the percentage of total sales for each product. Formatting these cells as percentages will help visualise the data:

 

Output:

 

Product Sales Percentage of Total Sales
Apples 3000 30.00%
Oranges 5000 50.00%
Bananas 2000 20.00%

 

Using these methods and functions, we can efficiently perform various percentage calculations in Excel. The tools and techniques discussed here are essential for analysing and presenting data accurately and effectively.

Practical Applications of Percentage Calculations

Excel’s percentage calculations are versatile, helping us in various real-world scenarios. Let’s explore some practical applications.

Calculating Discounts and Sale Prices

We often need to calculate discounts during sales. Knowing how to do this in Excel can simplify budgeting and shopping.

 

Assume we have a list of products with their original prices and discount percentages:

 

Product Original Price Discount (%)
Laptop $1000 15%
Smartphone $800 10%
Headphones $200 20%

 

To calculate the discounted price:

 

  1. Enter the product names in column A, original prices in column B, and discount percentages in column C.
  2. In cell D2, type the formula: =B2*(1-C2).
  3. Press Enter, and drag the fill handle from D2 to D4.

Excel will show the discounted prices. To make the prices clear, format these cells as currency:

 

  1. Select cells D2 to D4.
  2. Click the “$” button in the toolbar under the “Home” tab.

The table with discounted prices will look like this:

 

Output:

 

Product Original Price Discount (%) Discounted Price
Laptop $1000 15% $850
Smartphone $800 10% $720
Headphones $200 20% $160

Finding the Original Price from Discounted Price

Calculating the original price from a discounted price is also useful. Let’s consider the same product list but with discounted prices.

 

Product Discounted Price Discount (%)
Laptop $850 15%
Smartphone $720 10%
Headphones $160 20%

 

To find the original price:

 

  1. Enter the product names in column A, discounted prices in column B, and discount percentages in column C.
  2. In cell D2, type the formula: =B2/(1-C2).
  3. Press Enter, and drag the fill handle from D2 to D4.

Excel will show the original prices. Format these cells as currency for clarity:

 

  1. Select cells D2 to D4.
  2. Click the “$” button in the toolbar under the “Home” tab.

The table with original prices will look like this:

 

Output:

 

Product Discounted Price Discount (%) Original Price
Laptop $850 15% $1000
Smartphone $720 10% $800
Headphones $160 20% $200

 

Calculating Tax and Other Percentages on Purchases

Calculating tax on purchases is common. For example, adding sales tax to the price of products can be automated in Excel.

 

Assume we have the following data:

 

Product Price Tax Rate (%)
Laptop $1000 8%
Smartphone $800 8%
Headphones $200 8%

 

To calculate the tax amount:

 

  1. Enter the product names in column A, prices in column B, and tax rates in column C.
  2. In cell D2, type the formula: =B2*C2.
  3. Press Enter, and drag the fill handle from D2 to D4.

Excel will display the tax amounts. To find the total price, including tax:

 

  1. In cell E2, type the formula: =B2+D2.
  2. Press Enter, and drag the fill handle from E2 to E4.

The table will now look like this:

 

Output:

 

Product Price Tax Rate (%) Tax Amount Total Price
Laptop $1000 8% $80 $1080
Smartphone $800 8% $64 $864
Headphones $200 8% $16 $216

 

Adjusting Amounts by a Percentage

Adjusting values by a percentage is another useful application. Suppose we need to increase employee salaries by 10%.

 

Employee Current Salary
Alice $5000
Bob $4500
Carol $6000

 

To calculate the new salaries:

 

  1. Enter the employee names in column A and current salaries in column B.
  2. In cell C2, type the formula: =B2*(1+0.10).
  3. Press Enter, and drag the fill handle from C2 to C4.

Excel will display the increased salaries. The table will look like this:

 

Output:

 

Employee Current Salary New Salary
Alice $5000 $5500
Bob $4500 $4950
Carol $6000 $6600

 

Tips for Efficiently Working with Percentages in Excel

Increasing or Decreasing a Number by a Percentage

We can adjust a number by a percentage using simple formulas. For instance, decreasing marketing expenses by 15% can be done as follows:

 

Expense Current Amount
Advertising $2000
Promotions $1500
Sponsorships $1000

 

To calculate the reduced amounts:

 

  1. Enter the expense categories in column A and the current amounts in column B.
  2. In cell C2, type the formula: =B2*(1-0.15).
  3. Press Enter, and drag the fill handle from C2 to C4.

Excel will show the reduced amounts. The table will look like this:

 

Output:

 

Expense Current Amount Reduced Amount
Advertising $2000 $1700
Promotions $1500 $1275
Sponsorships $1000 $850

Handling Entire Columns with Percentage Changes

We often need to apply percentage changes to entire columns of data. Excel simplifies this with features like Paste Special.

 

To increase all values in a column by 20%:

 

  1. Enter the values in column A.
  2. In an empty cell, type =1+0.20 and press Enter.
  3. Copy this cell.
  4. Select the range in column A.
  5. Right-click and choose “Paste Special”.
  6. Under “Operation”, select “Multiply”, and click OK.

Excel will adjust all values in the selected range by 20%.

 

Conclusion

Learning how to calculate percentage in Excel helps to obtain a flexible working tool for various activities. We looked at setting up discounts/sale prices, as well as searching for original prices and adding taxes. We also got to know how to practise amounts and manage whole columns with the help of percentages. These techniques can be useful to control and process financial information and identify patterns to make sound decisions quickly. These are made easier by the formulas and functions built into Excel to work with data with minimal instances of errors. These are skills that must be employed when calculating tax, when adjusting the company’s budget, or when analysing the rate of its sales.

FAQs
Use the formula =part/total, then format the cell as a percentage.
For increase: =(new value – old value)/old value. For decrease: =(old value – new value)/old value.
Yes, use the SUMIF function to sum values based on criteria and divide by the total.
Select the cells, go to the “Home” tab, and click the “%” button in the “Number” group.
Yes, use “Paste Special” with the “Multiply” option to adjust all selected values by a percentage.

Updated on July 13, 2024

Link
left dot patternright dot pattern

Programs tailored for your success

Popular

Management

Data Science

Finance

Technology

Future Tech

Upskill with expert articles

View all
Hero Vired logo
Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.
Blogs
Reviews
Events
In the News
About Us
Contact us
Learning Hub
18003093939     ·     hello@herovired.com     ·    Whatsapp
Privacy policy and Terms of use

|

Sitemap

© 2024 Hero Vired. All rights reserved