Home

Learning Hub

Articles

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

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:

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%. 

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:

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:

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:

Let’s apply this to a practical example:

Assume we have monthly sales data:

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:

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:

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:

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.

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:

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:

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

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:

Formula for Percentage Decrease

The formula for calculating percentage decrease is similar:

Consider a scenario where sales drop from March to April:

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:

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:

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:

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:

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:

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:

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:

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.

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:

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:

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:

Adjusting Amounts by a Percentage

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

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:

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:

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:

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.