In this tutorial, we will learn a quick and efficient way to calculate percentages in Excel. Whether you need to calculate restaurant tipping, reseller commission, income tax, or interest rate, understanding how to calculate percentages is essential. We will explore the basic percentage formula and a few more formulas for calculating percentage increase, percent of total, and more.
Percentage Basics
Before we dive into the formulas, let's review the basics of percentages. The term "per cent" is derived from the Latin per centum, meaning "by the hundred." As you may remember from high school math class, a percentage is a fraction of 100 that is calculated by dividing the numerator by the denominator and multiplying the result by 100.
The basic percentage formula is as follows:
*Percentage = (Part / Whole) 100**
For example, if you had 20 apples and you gave 5 to your friends, you can calculate the percentage of apples you gave away by performing the following calculation: 5/20 * 100 = 25%.
Basic Excel Percentage Formula
In Excel, the basic formula to calculate a percentage is slightly different from the math formula. The Excel formula is:
Percentage = Part / Total
Unlike the math formula, you do not have to multiply the resulting fraction by 100 in Excel. Excel automatically applies the Percentage format to the cell, which displays the result as a percentage.
Let's see how you can use the Excel percentage formula on real-life data. Suppose you have the number of "Ordered items" in column B and "Delivered items" in column C. To find out the percentage of delivered products, follow these steps:
- Enter the formula
=C2/B2in cell D2, and copy it down to as many rows as needed. - Click the Percent Style button (Home tab > Number group) to display the resulting decimal fractions as percentages.
That's it! The percentage of delivered products will be calculated and displayed in column D.
Calculating Percentage of Total in Excel
Calculating the percentage of a total is a common scenario in Excel. Let's explore a few examples to help you calculate the percentage of a total in different situations.
Example 1: Total at the End of the Table
If you have a total in a single cell at the end of a table, you can use the following formula to calculate percentages of the total:
Percentage = Part / $Total$
For example, if you have values in column B and their total in cell B10, you can use the formula =B2/$B$10 to calculate the percentage of each value compared to the total.
Example 2: Parts of the Total in Multiple Rows
Suppose you have several rows for the same product, and you want to know what part of the total is made up of all orders of that particular product. In this case, you can use the SUMIF function to add up all the numbers relating to a given product and then divide that number by the total.
The formula would look like this:
Percentage = SUMIF(range, criteria, sum_range) / $Total$
For example, if 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 formula may look similar to this: =SUMIF(A2:A9, E1, B2:B9) / $B$10.
By using the SUMIF function, you can calculate the percentage of a specific product compared to the total.
Calculating Percent Difference in Excel
To calculate the percentage difference between two values, you can use the following formula:
Percent Change = (New Value - Old Value) / Old Value
Let's see how you can use this formula to calculate the percent difference between two numbers in Excel.
Example 1: Calculating Percent Difference Between Two Columns
Suppose you have the last month's prices in column B and this month's prices in column C. You can use the formula =(C2-B2)/B2 to calculate the percent change between the two values.
- Enter the formula in any blank cell in row 2, such as D2.
- Select the formula cell and click the Percent Style button on the Home tab to convert the decimal number to a percentage.
- Drag the formula down to copy it to the cells below.
The formula will calculate the percentage of change in this month's prices compared to last month's prices.
Example 2: Calculating Percentage Difference Between Two Numbers
If you have one column of numbers and you want to calculate the percentage change between the previous number and the current one, you can use the formula =(C3-C2)/C2.
Please note that you should skip the first row with data and put your percent difference formula in the second cell, such as D3.
To display decimals as percentages, apply the percent format to the cells containing your formula.
Calculating Amount and Total by Percentage
In addition to calculating percentages, Excel can also help you calculate amounts and totals based on percentages. Let's explore a couple of examples.
Example 1: Calculate Amount by Total and Percentage
Suppose you are buying a new laptop for $950, and there is an 11% VAT charge on this purchase. You can calculate the VAT amount by multiplying the total by the percentage.
The formula would be:
*Amount = Total Percentage**
Assuming the total value is in cell A2 and the percentage is in cell B2, you can use the formula =A2*B2 to calculate the VAT amount.
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.
Example 2: Calculate Total by Amount and Percentage
Suppose your friend offers you his old computer for $400, which is a 30% discount off the original price. You can calculate the original price by dividing the amount by the percentage.
The formula would be:
Total = Amount / Percentage
For instance, to calculate the original price, you can use the formula =A2/(1-B2).
By using these formulas, you can calculate amounts and totals based on percentages in Excel.
How to Increase/Decrease a Number by Percentage
If you need to increase or decrease a number by a certain percentage, Excel can help you with that too. Here's how:
To Increase an Amount by a Percentage:
Use the formula Amount * (1 + %). For example, to increase the value in cell A1 by 20%, the formula would be =A1*(1+20%).
To Decrease an Amount by a Percentage:
Use the formula Amount * (1 - %). For example, to decrease the value in cell A1 by 20%, the formula would be =A1*(1-20%).
You can also apply these formulas to an entire column of numbers by using the Paste Special feature in Excel. Simply enter the formula in an empty cell, copy it, select the range of cells you want to change, right-click, choose Paste Special, select Values under Paste, choose Multiply under Operation, and click OK.
By following these steps, you can easily increase or decrease a column of numbers by a certain percentage.
Conclusion
In this tutorial, we have covered various aspects of calculating percentages in Excel. We explored the basic percentage formula, calculating percentages of a total, calculating percent difference between two numbers, and calculating amounts and totals by percentage. With these formulas, you can efficiently perform percentage calculations in Excel for a wide range of scenarios.
Remember, understanding how to calculate percentages is essential in many areas of life and work. Whether you are analyzing data, making financial decisions, or evaluating discounts, Excel provides powerful tools to help you calculate percentages accurately and efficiently.
That concludes our tutorial on calculating percentages in Excel. We hope you found it helpful and informative. If you have any further questions, feel free to ask. Happy calculating!