How to calculate percentage in Excel - formula examples

Example 1. The total is at the end of the table in a certain cell

A very common scenario is when you have a total in a single cell at the end of a table. In this case, the percentage formula will be similar to the one we've just discussed with the only difference that a cell reference in the denominator is an absolute reference (with $).The dollar sign fixes the reference to a given cell, so that it never changes no matter where the formula is copied.

For example, if you have some values in column B and their total in cell B10, you'd use the following formula to calculate percentages of the total:

=B2/$B$10

You use a relative cell reference to cell B2 because you want it to get changed when you copy the formula to other cells of column B. But you enter $B$10 as an absolute cell reference because you want to leave the denominator fixed on B10 when auto-filling the formula down to row 9.

The screenshot below demonstrates the results returned by the formula, the Percentages of Total column is formatted as percentage with 2 decimal places showing.

Example 2. Parts of the total are in multiple rows

In the above example, suppose you have several rows for the same product and you want to know what part of the total is made by all orders of that particular product.

In this case, you can use the SUMIF function to add up all numbers relating to a given product first, and then divide that number by the total, like this:

=SUMIF(range, criteria, sum_range) / total

Given that column A contains all product names, column B lists corresponding quantities, cell E1 is the name of the product you are interested in, and the total is in cell B10, your real-life formula may look similar to this:

=SUMIF(A2:A9 ,E1, B2:B9) / $B$10

Naturally, you can put the product name directly in the formula, like this:

=SUMIF(A2:A9, "cherries", B2:B9) / $B$10

If you want to find out what part of the total a few different products make, add up the results returned by several SUMIF functions, and then divide that number by the total. For example, the following formula calculates the percent of cherries and apples:

=(SUMIF(A2:A9, "cherries", B2:B9) + SUMIF(A2:A9, "apples", B2:B9)) / $B$10

For more information about the SUM function, please check out the following tutorials:

• How to use the SUMIF function in Excel
• Excel SUMIFS and SUMIF with multiple criteria

Excel formula for percent increase / decrease

To calculate the percentage of difference between two values A and B, the generic formula is:

When applying this formula to real data, it is important that you correctly determine which value is A and which is B. For example, yesterday you had 80 apples and how you have 100, meaning that now you have 20 apples more than before, which is 25% increase. If you had 100 apples and now you have 80, your number of apples has reduced by 20, which is 20% decrease.

Considering the above, our Excel formula for percentage change takes the following shape:

And now, let's see how you can use this formula for calculating percent difference in your spreadsheets.

Example 1. Calculating percent difference between 2 columns

Suppose you have the last month prices in column B and this month prices in column C. Then your percent change formula takes this form:

=(C2-B2)/B2

To calculate the percent difference between two numbers correctly, carry out these steps.

1. Enter the formula in any blank cell in row 2, say D2. This will output the result as a decimal number.
2. Select the formula cell and click the Percent Style button on the Home tab to convert the decimal number to percentage.
3. Drag the formula down to copy it to the below cells.

As the result, the formula calculates the percentage of change in this month (column C) comparted to last month (column B). Positive percentages that show percentage increase are formatted in usual black, while negative percentages (percent decrease) are formatted in red. To have this done automatically, set up a custom format for negative percentages as explained in this tip.

Example 2. Calculating percentage difference between two numbers

In case you have one column of numbers, say column C that lists weekly or monthly sales, you can calculate the percentage change between the previous week/month and the current one using this formula:

=(C3-C2)/C2

Where C2 and C3 are the numbers you are comparing.

To display decimals as percentages, apply the percent format to the cells containing your formula, and you will get the following result:

To calculate the percentage of change between a certain number and all other numbers, fix the address of that cell by using the $ sign, e.g. $C$2.

For instance, to calculates the percent increase / decrease for each month compared to January (C2), the formula in D3 is:

=(C3-$C$2)/$C$2

When copying the formula to the below cells, the absolute reference ($C$2) will remain the same, while the relative reference (C3) will change to C4, C5, and so on based on a relative position of the row where the formula is copied.

For more formula examples, please see How to calculate percentage change in Excel.

Example 1. Calculate amount by total and percentage

Suppose you are buying a new laptop for $950 and they charge 11% VAT on this purchase. The question is - how much do you have to pay on top of the net price? In other words, what is 11% of $950?

The following formula will help:

Assuming that the total value is in cell A2 and percent in B2, the above formula turns into a simple =A2*B2 and returns 104.50.

Remember, when you type a number followed by the percent sign (%) in Excel, the number is interpreted as a hundredth of its value. For example, 11% is actually stored as 0.11 and Excel uses this underlying value in all formulas and calculations.

In other words, the formula =A2*11% is equivalent to =A2*0.11. Naturally, you are free to use the decimal number corresponding to the percentage directly in a formula if this works better for your worksheets.

Example 2. Calculating total by amount and percentage

For example, you friend offered you his old computer for $400, which is 30% off the original price. You want to know what the original price was.

Since 30% is the discount, you deduct it from 100% first to know what percentage you actually have to pay (100% - 30% = 70%). Now you need the formula to calculate the original price, i.e. to find the number whose 70% equals 400.

The formula goes as follows:

Applied to real data, it may take any of the following shapes:

=A2/B2

or

=A2/0.7

or

=A2/70%

How to increase / reduce an entire column by a percentage

Suppose, you have a column of numbers that you want to increase or reduce by a certain percent, and you want to have the updated numbers in the same column rather than add a new column with the formula.

Here are 5 quick steps to handle this task:

1. Enter all the numbers you want to increase or decrease in some column, column B in this example.
2. In an empty cell, enter one of the below formulas:

   Increase by percentage:

   =1+20%

   Reduce by percentage:

   =1-20%

   Naturally, you are free to replace 20% in the above formulas with the percentage you want.

3. Select the cell with the formula (C2 in our case) and copy it by pressing Ctrl + C.
4. Select the range of cells you want to change, right click the selection and then click Paste Special…
5. In the Paste Special dialog window, select Values under Paste, Multiply under Operation and click OK.

And here's the result - all the numbers in column B are increased by 20%:

In the same fashion, you can multiply or divide a column of numbers by a certain percentage. Simply enter the desired percentage in an empty cell and follow the steps above.