The formula for calculating percentages is familiar to us from school. In real life, this skill is rarely required, and if for simple cases a pen and paper are enough, then when calculating margin, VAT, interest on a loan, you have to deal with many numbers. Using the spreadsheet that comes with Microsoft Office makes the task a lot easier. But calculating percentages in Excel requires special skills, which we will talk about today.
Contents
Interest calculation in Excel
In the simplest cases, an online calculator is sufficient to calculate percentages. The main condition is that it has a% button. Professionals can work blindly, but for an ordinary user, such a calculation will only require care.
Percentage is defined as the quotient of dividing a part of a number by an integer multiplied by 100. From school, everyone is familiar with the simple scheme for calculating percentage by making a proportion. Let’s say we need to calculate 10% of 40. The proportion will be as follows:
40 – 100%
X – 10%.
Then X = (40 * 10) / 100
The basic formula for calculating percentages in Excel does not require division by 100, since this action is performed automatically. The main condition is that the percentage format for a cell or a group of cells is specified.
Now let’s look at a simple example of how to calculate percentages in an Excel spreadsheet. For example, in column B, the quantities of paid spare parts are entered, and in column C, those delivered to the warehouse. We need to calculate in column D what percentage of the order for each spare part is completed. Let’s see how to make a formula for calculating interest:
- put the cursor on cell D2 and write down the formula = C2 / B2 for it;
- using the autocomplete marker, we transfer the formula to all lines that the formula should apply to;
- set the “Percentage format” for the column by clicking on the button with the percentage image.
For calculations, column D will contain the percentages already multiplied by 100 and rounded to whole numbers. If desired, we can put the required number of decimal places if high precision is required.
Ways to calculate percentage of a number in Excel
Using the percentage format for numbers is the easiest, but not the only way to calculate percentages in a program. It can be argued that this is an adapted version of the well-known formula, which does not require division by 100 and rounds the result to whole numbers by default.
But no one forbids us to use the usual formula (the part divided by a whole and multiplied by 100) when calculating percentages in Excel. So, if you want to calculate 7% of the numbers available in column B, you can specify the formula = (B1 * 100) / 7. The result will be similar to the above method. Of course, the option = (B1 / 7) * 100 will also be absolutely correct.
And if you need to calculate the number by a known percentage, use the modified formula = (B1 * 7) / 100.
How to calculate the percentage of the amount in Excel
Imagine the following situation: in a database entered in Excel, there are records for sellers indicating sales volumes (it does not matter, in absolute or monetary terms), as well as the total amount for all sales and sellers. We need to calculate the contribution of each of the sellers to the total turnover.
How to implement it:
- add another column for calculating the percentage, the start of this will be C;
- insert into cell C2 the formula = B2 / $ B $ 19 (if the total is written in the 19th cell);
- we extend the formula to all cells by stretching it down.
Some clarification is needed here. The dollar sign is needed to fix column B together with the ninth cell. In this case, when you drag the formula down to extend it to all sellers, the program will automatically substitute the value of cell B to calculate the percentage of sales for each line. Without the “$” icons, when you drag down the formula, cell B9 will move in the same direction. In other words, to keep the cell reference unchanged when you copy a formula, you must precede it with a dollar sign.
The result will be in the form of decimal fractions, to convert it to percent, click on the “%” icon, which will convert the cell format to percentage (multiply the number by 100).
Let’s take a more complex example. Let the table show the sellers and the goods they sell, as well as the volume of their sales. We need to calculate what share of the total sales volume is a particular product, for example, cucumbers.
Add column E, change its format to percentage, insert into E2 a formula preceded by the SUMIF function. As an argument indicating which product needs to be summarized, you can specify either its specific value (in our case – “Cucumbers”, or specify a separate cell in which this value is entered).
Our formula would be: = SUMIF ($ A $ 2: $ A $ 19; Cucumbers; $ B $ 2: $ B $ 19) / B19.
The final result will be calculated in cell E2.
If you need to calculate the share of sales for several products (tomatoes and cucumbers), the formula will be a little more complicated:
= (SUMIF ($ A $ 2: $ A $ 19; “Cucumbers”; $ B $ 2: $ B $ 19) + SUMIF ($ A $ 2: $ A $ 19; “Tomatoes”; $ B $ 2: $ B $ 19)) / B19 …
That is, the principle of drawing up such formulas is clear.
How to add percentage to number in TP Excel
A classic example of such operations is the calculation of a percentage markup. Let’s consider a specific example – there are lists of goods with prices, we need to calculate the cost including VAT (18%). That is, the final formula should look like <New price> = <old price> + <old price * 18%>.
If the names of goods are located in column A, prices are in column B, then the final formula for calculating the margin (not in percent, in price terms) will look like = B2 + (B2 * 18%). Do not forget to place parentheses in accordance with the order in which arithmetic operations are calculated.
How to subtract a percentage from a number in MS Excel
Tasks that require subtracting its percentage from the number are less common, but the implementation principle is the same as in the previous example. Let’s say we have set the price of a product with VAT, and we want to know its initial cost.
To subtract the percentage from the number, we simply replace the plus with a minus, and as a result, in cell C2 we will have the formula = B2- (B2 * 18%).
How to calculate the percentage difference in Excel
Another fairly common practical problem. Let’s give a concrete example. On May 10, a consignment of goods was delivered to the store at the same price, on May 30, a revaluation was made, the cost increased, and we want to calculate by what percentage. It will be different for each product.
The calculation formula is simple: <new price – old price> / <old price> * 100%.
The original data is located in columns B and C, we need to calculate in column D the difference between prices, expressed as a percentage.
The first thing we do is assign cell D2 the format “Percentage. Next, we insert into this cell the formula = (C2-B2) / B2.
Second example. There is a dynamics of sales by months. We need to calculate how the sales volume changed as a percentage in relation to the previous month. If the months are located in column A, sales volumes are in column B, then our formula will be as follows: = (B3-B2) / B2. As always, we stretch the formula down and get the desired result.
And now, in the same table, you need to calculate the dynamics of sales as a percentage in relation to January volumes. In this case, the January cell must be fixed with a dollar sign, and then the resulting formula that calculates the percentage difference between the two sales figures will be: = (B3- $ B $ 2) / $ B $ 2.
Percentage multiplication in Excel
Let’s go back to our VAT example. There is a table with price values, the VAT percentage is known, and we need to get a column with the margin value.
The calculation formula is simple: take the cost, multiply by 18 and divide by 100. Let’s see how to multiply prices by the percentage of VAT in Excel:
If the price is entered in column B, then our formula will look like: = B2 * 18%.
There is no need to divide by 100, since we directly tell the program that we are working with percentages, Excel understands this and takes into account.
If there are numbers in one column and percentages (different) in the other, change the formula to the following: = B2 * A2.
As you can see, operations with percentages in a spreadsheet processor have their own specifics. Firstly, it is necessary to indicate the format of the “Percentage” cell so that the program understands what it is dealing with. You can specify the “%” sign directly in the formula, and then Excel will determine that the result should be divided by 100. In all other respects, the same rules apply as for any other operations and formulas.
And, as always, we leave comments, share experiences, point out inaccuracies.