How to use the per­cent­age formula in Excel

The per­cent­age cal­cu­la­tion feature in Excel makes it easier to calculate per­cent­ages. We’ll show you how to use the most important formulas in Excel to calculate per­cent­ages of totals or per­cent­age changes such as growth and decline. You’ll also learn how to calculate pro­por­tions and totals based on per­cent­ages and how to increase and decrease numbers by a per­cent­age.

The basics of cal­cu­lat­ing per­cent­ages in Excel

In this article, we’ll give you an overview of the most important methods for cal­cu­lat­ing per­cent­ages in Excel and show you the most important formulas for doing per­cent­ages. The following in­struc­tions apply to the Excel version of Microsoft 365 as well as to the 2021, 2019 and 2016 Excel versions.

When you calculate per­cent­ages in Excel, you save yourself steps such as mul­tiply­ing by 100 since the format­ting auto­mat­ic­ally takes care of this for you as soon as you apply the per­cent­age format (see below). The basic Excel per­cent­age cal­cu­la­tion formula looks like this:

Part/Whole = Percentage

How to calculate per­cent­ages in Excel – Formulas and format­ting

Let’s look at a practical example of how per­cent­ages are cal­cu­lated in Excel. A retailer keeps track of their inventory with a spread­sheet. The spread­sheet contains in­form­a­tion about the products that they have bought and sold. The number of products that the retailer has bought is in column B, and the number of products that the retailer has sold is in column C. To calculate the per­cent­age of items sold for each product, enter the following formula in the first field of column D (D2):

=C2/B2

Close

In a formatted table, Excel auto­mat­ic­ally transfers the per­cent­age formula to the sub­sequent cells. Al­tern­at­ively, you can transfer the formula by double-clicking in the bottom right-hand corner of the cell (D2).

Display values in Excel as per­cent­ages

The results may not look quite right at first. Instead of per­cent­ages, you’ll see decimal numbers in your Excel spread­sheet.

Close

If you want Excel to round the numbers and display the results with the percent sign, you need to change your default settings:

1. Click on the Home tab.
2. Select the cell range con­tain­ing the values that should be displayed with a percent sign.
3. In the ribbon, click on % in the Number section.

Close

Specify per­cent­age with decimal places in Excel

In Excel’s default settings, per­cent­ages are displayed without decimal values. However, you can easily change this format by clicking on the small arrow at the bottom right of the Number section. In the Format cells menu that opens, you have the option of spe­cify­ing how many decimal places should be displayed.

Close

How to calculate the per­cent­age of a sum of values using Excel formulas

In the example above, we cal­cu­lated per­cent­ages based on how many units a retailer had purchased of a specific product, and how many of those units the retailer was able to sell. In the table, purchases rep­res­en­ted the total amount, and the sales were the per­cent­ages. However, we can also use Excel to do more complex cal­cu­la­tions, for example, to find the per­cent­age of a larger amount that is the sum of different values in a table.

Cal­cu­lat­ing the per­cent­age of a total at the end of an Excel table

Let’s say the retailer in the example above now wants to know how many products they purchased in total, and what the per­cent­age of each in­di­vidu­al product is in relation to the total number of products that were purchased. With the help of per­cent­age equations and the right excel formula, you can determine what per­cent­age of the total result each product rep­res­ents.

First, we need to add an ad­di­tion­al cell (our example uses cell B11) to calculate the total amount of products purchased. To do this, we are going to use the formula =SUM(B2:B10).

In column C, we are going to find out what per­cent­age of the total amount of purchases each product rep­res­ents. To do this, we enter the following formula in cell C2:

=B2/$B$11

Close

Calculate per­cent­age using values from different rows

Let’s now assume that the retailer has several rows for the same product and wants to use Excel to calculate the per­cent­age that this product accounts for. In this case, you can use Excel SUMIF, which auto­mat­ic­ally adds all values that match the criterion you have selected. The formula looks like this:

=SUMIF(range,criteria,sum_range)/total

In our example, we entered the criterion Pants in cell E1. The values that we want to add are in column B and the product names for automated selection are in column A. The cor­res­pond­ing formula in Excel is as follows:

=SUMIF(A2:B10,E1,B2:B10)/$B$11

Close

How to use the Excel per­cent­age formula to calculate per­cent­age change

Per­cent­age change or per­cent­age growth is one of the formulas that you’ll probably use most often when cal­cu­lat­ing per­cent­ages in Excel. Simply use the following formula to calculate the change between the original value (A) and another value (B):

Percentage change=(B-A)/A

Value (A) rep­res­ents the value before the change. For example, if you compare sales between March and April, the number of sales in March would be the original value (A) and sales in April would be B. The following two examples il­lus­trate how to calculate the per­cent­age of growth using this formula.

Calculate the per­cent­age change between two columns

The retailer in our example entered the sales from March in column B and the sales from April in column C. The retailer then enters the following formula in column D to calculate the per­cent­age of growth:

=(C2-B2)/B2

Close

Calculate the per­cent­age growth between two rows

You may want to keep track of total monthly sales in a spread­sheet. In this case, you can calculate the per­cent­age growth between two columns so that you can easily see month-over-month changes in sales.

In the example below, we’ve entered all monthly sales in column B and cal­cu­lated the growth in column C. Start with the sales from January in cell B2 and determine the per­cent­age growth for February in cell C3. To do this, enter the following formula in cell C3:

=(B3-B2)/B2

Close

How to calculate a total/partial amount in Excel based on a per­cent­age

Just as you can use Excel to calculate a per­cent­age using two values, you can also use Excel to calculate the overall total or a pro­por­tion based on a per­cent­age.

Calculate a partial amount

Let’s say you buy new IT equipment for your company and the total price is £1,200. This includes VAT, which is 20% in the UK. For this purpose, you simply calculate the amount of tax based on this per­cent­age.

In the following example, the total price is in cell A2 and the per­cent­age is in cell B2. You need to calculate the absolute value of the VAT in cell C2. Use the following formula:

=A2*B2

Close

Calculate a total amount

Excel also allows you to find the total amount of something based on the per­cent­age of a partial amount. For example, let’s say you attend a multi-day workshop in another city. As a workshop attendee, you receive a special discount and pay only 70 percent of the price of your hotel room as part of your all-inclusive ac­com­mod­a­tion. You are charged £350 and are curious how expensive your stay would have been without the discount.

Enter the amount you paid in cell A2 and the per­cent­age in cell B2. In cell C2, enter the formula for cal­cu­lat­ing the total price based on the per­cent­age that cor­res­ponds to the discount price. Divide your amount by the per­cent­age:

=A2/B2

Close