Contents
In this lesson, you will see how to quickly calculate percentages using Excel, get acquainted with the basic formula for calculating percentages, and learn a few tricks that will make your work with percentages easier. For example, the formula for calculating the percentage increase, calculating the percentage of the total amount, and something else.
The ability to work with percentages can be useful in various areas of life. This will help you estimate the amount of tips in a restaurant, calculate commissions, calculate the profitability of any enterprise and the degree of your personal interest in this enterprise. Tell me honestly, will you be delighted if they give you a promotional code for a 25% discount for buying a new plasma? Sounds tempting, right?! And how much do you actually have to pay, can you calculate?
In this tutorial, we’ll show you a few techniques that will help you easily calculate percentages with Excel, as well as introduce you to the basic formulas that are used to work with percentages. You will learn some tricks and you will be able to hone your skills by deciphering solutions to practical percentage problems.
Basic knowledge about percentages
Term Percent (per cent) came from Latin (per centum) and was originally translated as FROM A HUNDRED. In school, you learned that a percentage is a part of 100 parts of a whole. The percentage is calculated by dividing, where the numerator of the fraction is the desired part, and the denominator is the whole, and then the result is multiplied by 100.
The basic formula for calculating interest looks like this:
(Часть/Целое)*100=Проценты
Example: You had 20 apples, of which you distributed 5 to your friends. What percentage of your apples did you give away? Having made simple calculations, we get the answer:
(5/20)*100 = 25%
This is how you were taught to calculate percentages in school, and you use this formula in your daily life. Calculating percentages in Microsoft Excel is an even easier task, since many mathematical operations are performed automatically.
Unfortunately, there is no universal formula for calculating interest for all occasions. If you ask the question: what percentage formula to use to get the desired result, then the most correct answer will be: it all depends on what result you want to get.
I want to show you some interesting percentage formulas. These are, for example, the formula for calculating the percentage increase, the formula for calculating the percentage of the total amount, and some other formulas that you should pay attention to.
The basic formula for calculating interest in Excel
The basic formula for calculating interest in Excel looks like this:
Часть/Целое = Процент
If you compare this formula from Excel with the familiar formula for percentages from the math course, you will notice that it does not multiply by 100. When calculating percentage in Excel, you do not need to multiply the result of division by 100, as Excel will do this automatically if for a cell given Percent Format.
Now let’s see how the calculation of percentages in Excel can help in real work with data. Let’s say that in column B you have a certain number of ordered products (Ordered), and in column C you have entered data on the number of delivered products (Delivered). To calculate what proportion of orders have already been delivered, we will do the following:
- Write down the formula =C2/B2 in cell D2 and copy it down as many lines as needed using the autofill handle.
- Push command Percent Style (Percentage Format) to display division results in percent format. It’s on the tab Home (Home) in a command group Number (Number).
- If necessary, adjust the number of displayed characters to the right of the decimal point.
- Done!
If you use any other formula to calculate percentages in Excel, the general sequence of steps will remain the same.
In our example, column D contains values that indicate, as a percentage, what percentage of the total number of orders are orders already delivered. All values are rounded to whole numbers.
Calculate percentage of total amount in Excel
In fact, the example above is a special case of calculating a percentage of the total. To better understand this topic, let’s look at a few more tasks. You will see how you can quickly calculate the percentage of the total in Excel using various data sets as an example.
Example 1. The total amount is calculated at the bottom of the table in a specific cell
Very often, at the end of a large table of data, there is a cell labeled Total, in which the grand total is calculated. At the same time, we are faced with the task of calculating the share of each part relative to the total amount. In this case, the formula for calculating the percentage will look the same as in the previous example, with one difference – the reference to the cell in the denominator of the fraction will be absolute (with $ signs before the row name and column name).
For example, if you have some values u10buXNUMXbwritten in column B, and their total is in cell BXNUMX, then the percentage calculation formula will be as follows:
=B2/$B$10
We use a relative reference for cell B2 so that it changes when we copy the formula to other cells in column B. The cell reference in the denominator must remain the same when the formula is copied, so we wrote it as $B$10.
Tip: There are two ways to make the cell reference in the denominator absolute: either enter the sign $ manually, or select the desired cell reference in the formula bar and press the key F4.
The figure below shows the result of calculating the percentage of the total. Percentage format with two decimal places is selected for data display.
Example 2: Parts of the grand total are on multiple lines
Imagine a data table like the previous example, but here the product data is spread across multiple rows of the table. It is required to calculate what part of the total amount is the orders of a particular product.
In this case, we use the function SUMIF (SUMIS). This function allows you to summarize only those values that meet some specific criterion, in our case, this is a given product. The result is used to calculate the percentage of the total.
=SUMIF(range,criteria,sum_range)/total
=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)/общая сумма
In our example, column A contains the names of products (Product) – this is range. Column B contains quantity data (Ordered) – this is sum_range. In cell E1 we enter our criterion — the name of the product for which you want to calculate the percentage. The total amount for all products is calculated in cell B10. The working formula will look like this:
=SUMIF(A2:A9,E1,B2:B9)/$B$10
=СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10
By the way, the name of the product can be entered directly into the formula:
=SUMIF(A2:A9,"cherries",B2:B9)/$B$10
=СУММЕСЛИ(A2:A9;"cherries";B2:B9)/$B$10
If you need to calculate how much of the total amount is made up of several different products, then you can sum the results for each of them, and then divide by the total amount. For example, this is how the formula would look if we want to calculate the result for cherries и apple:
=(SUMIF(A2:A9,"cherries",B2:B9)+SUMIF(A2:A9,"apples",B2:B9))/$B$10
=(СУММЕСЛИ(A2:A9;"cherries";B2:B9)+СУММЕСЛИ(A2:A9;"apples";B2:B9))/$B$10
How to calculate percentage change in Excel
One of the most popular tasks you can do with Excel is calculating the percentage change in data.
Excel formula that calculates percentage change (increase/decrease)
To calculate the percentage change between values A и B, use the following formula:
(B-A)/A = Изменение в процентах
When using this formula in real data, it is very important to correctly determine which value to put in place. A, and which one is in place B.
Example: Yesterday you had 80 apples and today you have 100 apples. This means that today you have 20 more apples than yesterday, that is, your result is an increase of 25%. If yesterday there were 100 apples, and today 80, then this is a decrease of 20%.
So, our formula in Excel will work as follows:
(Новое значение – Старое значение) / Старое значение = Изменение в процентах
Now let’s see how this formula works in Excel in practice.
Example 1: Calculate percentage change between two columns
Suppose that column B contains the prices of the last month (Last month), and column C contains the prices that are current this month (This month). In column D, we will enter the following formula to calculate the price change from the previous month to the current one as a percentage.
=(C2-B2)/B2
This formula calculates the percentage change (increase or decrease) in the price this month (column C) compared to the previous month (column B).
After you write the formula in the first cell and copy it to all the necessary lines by dragging the autofill marker, do not forget to set Percent Format for cells with a formula. As a result, you should get a table similar to the one shown in the figure below. In our example, positive data, which shows an increase, is displayed in standard black, and negative values (percentage decrease) are highlighted in red. For details on how to set up such formatting, read this article.
Example 2: Calculate percentage change between rows
In the case where your data is located in one column, which reflects sales information for a week or a month, the percentage change can be calculated using the following formula:
=(C3-C2)/C2
Here C2 is the first value and C3 is the next value.
Note: Please note that, with this arrangement of data in the table, the first line with data must be skipped and the formula must be written from the second line. In our example, this will be cell D3.
After you write down the formula and copy it into all the necessary rows of your table, you should get something similar to this:
If you need to calculate for each cell the percentage change compared to the value of one particular cell, use a signed absolute reference to that cell $, for example, so $C$2.
For example, this is how the formula for calculating the percentage change for each month in comparison with the indicator would look like January (January):
=(C3-$C$2)/$C$2
When you copy your formula from one cell to all the others, the absolute reference will remain the same, while the relative reference (C3) will change to C4, C5, C6, and so on.
Calculation of the value and the total amount by a known percentage
As you can see, calculating percentages in Excel is easy! It is just as easy to calculate the value and the total amount by a known percentage.
Example 1: Calculate a value from a known percentage and total amount
Let’s say you buy a new computer for $950, but add another 11% VAT to that price. The question is how much do you need to pay? In other words, 11% of the indicated value is how much in currency?
The following formula will help us:
Total * Percentage = Amount
Общая сумма * Проценты = Значение
Let’s pretend that total amount (Total) is written in cell A2, and Interest (Percent) – in cell B2. In this case, our formula will look quite simple = A2 * B2 and give the result $ 104.50:
It is important to remember: When you manually enter a numeric value into a table cell followed by a % sign, Excel interprets this as hundredths of the entered number. That is, if you enter 11% from the keyboard, then in fact the value 0,11 will be stored in the cell – this is the value Excel will use when making calculations.
In other words, the formula =A2*11% is equivalent to the formula = A2 * 0,11. Those. in formulas, you can use either decimal values or values with a percent sign – as you prefer.
Example 2. Calculate the total amount from a known percentage and value
Let’s say your friend offered to buy his old computer for $400 and said it was 30% off its full price. Do you want to know how much this computer originally cost?
Since 30% is a price reduction, the first step is to subtract this value from 100% to calculate how much of the original price you need to pay:
100%-30% = 70%
Now we need a formula that will calculate the initial price, that is, find the number, 70% of which is equal to $400. The formula will look like this:
Amount/Percentage = Total
Значение/Процент = Общая сумма
To solve our problem, we get the following form:
=A2/B2 or =A2/0,7 or =A2/70%
How to increase/decrease value by percentage
With the onset of the holiday season, you notice some changes in your usual weekly spending items. You may want to make some additional adjustments to how your spending limits are calculated.
To increase a value by a percentage, use the following formula:
=Значение*(1+%)
For example, the formula =A1*(1+20%) takes the value contained in cell A1 and increases it by 20%.
To decrease a value by a percentage, use the following formula:
=Значение*(1-%)
For example, the formula =A1*(1-20%) takes the value contained in cell A1 and reduces it by 20%.
In our example, if A2 is your current expenses, and B2 is the percentage by which you want to increase or decrease their value, then you need to write the following formula in cell C2:
Increase by percentage: =A2*(1+B2)
Decrease by percentage: =A2*(1-B2)
How to increase/decrease by percentage all values in a column
Suppose you have an entire column filled with data that needs to be incremented or decremented by some percentage. However, you do not want to create another column with a formula and new data, but change the values in the same column.
We need only 5 steps to solve this problem:
- Let’s enter the data that needs to be increased or decreased in a column. In our example, this is column B.
- In an empty cell, write one of the formulas:
To increase by a percentage:
=1+20%To decrease by a percentage:
=1-20%
In both formulas, we took 20% as an example, and you can use the percentage value that you need.
- Select the cell with the formula (C2 in our example) and copy it by pressing Ctrl + C.
- Select the range of cells in which you want to make a change. Right-click on it and in the context menu that appears, select paste Special (Special insert).
- In the opened dialog box paste Special (Paste special) in section paste (Paste) select item Values (Values), and in the section Surgery (Operation) item Multiply (Multiply). Then press OK.
As a result, the values in column B will increase by 20%.
In this way, you can multiply, divide, add, or subtract some percentage from a whole column of data. Just enter the desired percentage in an empty cell and follow the steps described above.
These methods will help you in calculating percentages in Excel. And even if percentages have never been your favorite section of mathematics, knowing these formulas and tricks will make Excel do all the work for you.
That’s all for today, thank you for your attention!