How to enter a formula with percentages in Excel. How to calculate the percentage of a number in Excel. How does this formula work?

Just today I encountered an error when calculating the difference between two values as a percentage. This error led to quite serious consequences, and correcting these consequences took several hours. So that in the future I can refer to this article, and not write explanations every time, this article is intended. How to calculate percentages in Excel? Calculation of percentages of a number. The difference in numbers as a percentage and correct formatting.

First, a little theory. A percentage is, first of all, a fraction, or rather a hundredth part, denoted by the sign “%” and is used to indicate the share of something in relation to the whole. If we go really deep into the school course, then the decimal fraction 0.07 is equal to 7%. In our culture, it is convenient and customary to compare values as percentages, for example, the concept “agree 100%” means complete approval.

As we said above, a percentage is just a share. Therefore, in Excel, I do not recommend using divisions by 100 when highlighting percentages, but using a special percentage format. As well as the number format “ ” or “ “. This format is available on the Home panel as a separate button:

Below I show the correct use of this format. Cell A4 uses Percentage format.

The fact is that the method of writing percentages shown in line 5 complicates subsequent calculations - you need to divide the data by 100 each time in new formulas. There is a high probability of forgetting about this or mistaking the data in the cell for a regular number.

It is worth noting that if you enter the text 5% in a cell, the program itself will recognize and accept the percentage format. And the number in the cell will be written as 0.05.

Accordingly, if we need to understand what percentage (profitability) is the profit of 12 thousand rubles. in revenue 70 thousand rubles. we need 12 divided by 70.

Most often, confusion arises when calculating the included VAT. For clarification, I have .

Proportion in Excel and calculating numbers from percentages

What if you are given the number 250 and you know that it is 7% of some number. How to find this number, i.e. 100%.

To find a question mark we need? = 250 * 100% / 7%. Those. it will be 3,571, which will be 100% for this task.

In the same way, you can find any unknown value out of 4 and easily calculate percentages in Excel. For example, if 100% is specified and the percentage of the number is not specified.

250 * 7% / 100%.

Don’t judge harshly who considers such information elementary - someone will definitely need a description of the proportion.

Difference of numbers in percentage

Another popular question: “How to find the difference between numbers as a percentage?” or “How to find the percentage increase in an indicator from year to year?”

The rule of subtracting the smaller from the larger does not work here, since the difference can be negative. There is also a common question here: what number should you divide by? It all depends on the conditions of the problem. If you need to find an increase in the indicator compared to the previous year, 2016, you need to divide by the 2016 data.

Of course I do.

Share our article on your social networks:

When working with numbers in Excel, it is often necessary to calculate the percentage of a number. For users with extensive experience in Excel, this is not a problem, but if you have just started learning this program, then such a simple task may lead you to a dead end.

If you are faced with just such a problem, then this article should help you. Here you will learn how you can perform this operation in Excel 2003, 2007, 2010, 2013 or 2016.

If you need to calculate the percentage of a number in Excel, then the easiest way is to use the school program, dividing the number by 100 and multiplying by the desired percentage. For example, in order to get 15 percent of the number in cell A2, you need to use the following formula: =A2/100*15. The screenshot below shows how this formula will look in Excel.

If necessary, the percentage can not be written directly into the formula, but taken from the table. In this case, instead of a specific percentage value in the formula, you need to specify the address of the cell with the percentage. The resulting formula will look something like this: =A2/100*B2

It should be noted that the formula with which you can calculate the percentage of a number can be written a little differently. Instead of dividing by 100, as in school, you can immediately multiply by the desired percentage. In this case, the formula will look like this: =A2*B2%, where A2 is the original number and B2 is the percentage to be calculated. The main thing is not to forget to insert a percent sign at the end of the formula.

But, if the cell in which the percentage is indicated has a percentage format rather than a numeric one, then in this case there is no need to indicate the percentage sign at the end of the formula, and the formula looks like this: = A2*B2, where A2 is the original number and B2 is the percentage to be calculated.

How to calculate the percentage of a number and immediately add it

Also, sometimes it becomes necessary to calculate the percentage of a number and immediately add it to this number. In this case, it is convenient to use the second way of writing the formula, without dividing by 100. If you write the percentage directly into the formula, it will look something like this: =A2+A2*18%, where A2 is the original number, and 18 is the percentage , which needs to be counted and added.

For five years, calculate the tax on the sale of goods or find out how much it will cost to take out a loan. In any of these cases you will have to deal with interest. A calculator is a good thing, but it cannot provide clarity and recalculate the result when the source data changes, just as Excel easily does. In addition, calculations can always be saved in a file (it is clear that the calculator does not have such functionality).

When percentages are calculated in Excel, the calculation formula is selected depending on what data is known and what specifically needs to be determined. As you know from school, the basic formula can be written like this: percentage = value / total.

Calculating the percentage of the total

For example, if you win 5,000 rubles in the state lottery, you need to pay 15%. How much will you need to pay to the state?

Calculating percentages with a known value and total

For example, if there are 29 students in a class, of which 16 are boys, what is the percentage of boys?

If you use the Percent Format button (Home tab, Number group), you can present the total in percentage terms.

Calculate grand total using value and percentage

For example, jeans in a store cost 2,390 rubles. This week there is a 23% discount in honor of February 23rd. How much will jeans cost next week?

Calculating percentages as the difference of two numbers

For example, the salary in February was 24,780 rubles, and in March - 25. By what percentage did income increase during this period? To solve this example, you need to use the ABC function in addition to the basic division and subtraction operators.

Note. The ABC function is designed to determine the absolute value of a number. In other words, it returns the modulus, that is, an unsigned number.

Decrease or increase a number by a specified percentage

For example, the rate of ordinary securities of Sberbank over the past week increased by 1.521% and amounted to 97.96 rubles per share. What was the initial cost of the securities? Or, conversely, it is expected that Sberbank securities will rise in a couple of days (by at least 2%). What will be their final cost then? For greater convenience, you can copy the contents of the following table into a new Excel sheet. If suddenly an error appears instead of the results, try replacing the commas in the numbers with periods.

As you can probably already see, calculating percentages in Excel is a very simple and convenient thing. It is not for nothing that this program is so loved by accountants, bankers, financiers and simply all those who often have to work with numbers.

In the process of solving various kinds of problems, both educational and practical, users often turn to Excel.

Spreadsheets allow you to analyze data, create charts and graphs, and perform a variety of calculations. One of the common operations is calculating interest. The ability to competently make the necessary calculations is a useful skill that can be successfully used in almost all areas of life. What techniques can help you calculate percentages using Excel tables?

How to calculate percentages in Excel - basic calculation formula

Before you begin calculating percentages, you need to define the terminology. The term "percentage" means the number of shares out of all 100 parts of a whole. The mathematical definition of percentage is a fraction, the numerator of which determines the required number of parts, and the denominator determines the total. The result is multiplied by 100 (since an integer is 100%). Working with a spreadsheet, the formula for determining percentage is as follows:

Part/whole = Percentage

The only difference from the usual mathematical interpretation is the absence of further multiplication by 100. The properties of the table fields will help you obtain the required value format - just activate the Percentage cell format.

Example 1

Here is a series of data entered, for example, in column D (D2, D3, D4, D5, ...). It is necessary to calculate 5% of each value.

Activate the cell adjacent to the first value (or any other) - the result of the calculations will be located in it.
In cell E2 write the expression “=D2/100*5” or “=D2*5%”.
Press Enter.
“Extend” cell E2 to the required number of rows. Thanks to the autofill marker, the above formula will also calculate the remaining values.

Example 2

In front of you are 2 columns of values - for example, sold cakes (D2, D3, D4, D5, ...) and the total number of baked goods (E2, E3, E4, E5, ...) of each type. It is necessary to determine what part of the product has been sold.

In the cell where the result will be calculated (for example, F) write the expression “=D2/E2”.
Press Enter and “stretch” the cell by the required number of lines. Using the autofill marker will allow you to apply this formula to all subsequent cells and make the correct calculations.
To convert the result to percent format, select the required cells and use the Percent Style command. To activate the latter, you can right-click and select “Cell Format” – “Percentage” from the list that appears. In this case, you specify the desired number of decimal places. Or go to the “Home” – “Number” section and select the “Percentage” view.

How to calculate percentages in Excel - percentage of the amount

To calculate the share of each part relative to the total amount, use the expression “=A2/$A$10”, where A2 is the value of interest, the total amount is indicated in cell A10. What if the position you are interested in appears in the table several times? In this case, use the SUMIF function with the parameters:

SUMIF(range,criteria,sum_range)/total

SUMIF(range, criterion, sum_range)/total sum

Move to the cell where the result will be obtained.
Write down the expression “=SUMIF(C2:C10;F1;D2:D10)/$D$14” (or =SUMIF (C2:C10;F1;D2:D10)/$D$14), where

C2:C10, D2:D10 – ranges of values within which calculations occur,

F1 – cell in which the characteristic under study is indicated,

D14 is the cell in which the amount is calculated.

How to calculate percentages in Excel - percentage change

The need for such calculations often arises in the course of assessing growth or loss based on operating results. So, sales volumes by product category for 2015. entered in column D, similar data for 2016. - in column E. It is necessary to determine by what percentage the sales volume increased or decreased.

In cell F2, enter the formula “=(E2-D2)/D2”.
Convert cell data to Percentage format.
To calculate the gain or loss for the remaining categories (cells), drag F2 for the required number of rows.
Evaluate the result. If the value is positive, you have an increase, if negative, you have a decrease.

Microsoft Excel makes our life easier. It is very easy to perform any operation. To do this, you just need to write the formula on the command line.

To deduct interest, you need to do the following: Place the cursor in the column where the result should be. Then comes the formula - we write down the number from which we need to subtract the percentage or simply point it at the cell with the percentage, if we have them written down. We put a minus sign and open the bracket. In brackets you need to write down the following steps - from the required number we subtract the percentage that needs to be subtracted. It can be written by hand or via a cell.

As a result, we should get, for example, the following formula (number - (number * percentage to be subtracted). Press Enter.

So, first you need to select a cell where the percentage will be calculated and the result will be formatted. In this cell we put the = (equals) sign...
Then we find the cell with the number from which we need to calculate the percentage and enter its address in the selected cell...
We put a * (multiplication) sign after the cell address...
Behind the multiplication sign in numbers we write the percentage that we need to calculate, or select a cell in which this percentage is already written and enter its address...
Following the number or address line we put a % sign (percent)…
Then you just need to press Enter...

You will have a number that will be the specified percentage...

It looks something like this:

=G9*H15%

If in Excel it is necessary to subtract a certain number of percentages from a number, then this action is written in the form of a formula according to the following steps

First, we stand on an empty cell where we would like to see the final result;
We start the formula with the = sign;
indicates the number from which the percentage must be subtracted. If this number is already written in the file, then click on the cell where it appears;
put the subtraction sign -;
open the parenthesis (;
again click on the number from which we subtract a percentage;
put the multiplication sign *;
write the number of percentages, after which you need to indicate the % symbol;
close the parenthesis;
press Enter.

I will try to briefly state your question. The algorithm of actions is as follows:
- put an = sign in an empty cell.
- We write the address of the cell from which we subtract the percentage.
- we write the sign *.
- We write the number of percents and the % sign.
The equation for subtraction is: amount/total=percentage(percentage decimal format)
Let's say you write the formula =A2/C2 in cell D2 and copy it down as many lines as necessary, for this you can use the autofill marker. Next, click the Percent Style command on the Home tab in the Number command group. And, if necessary, you will need to adjust the quantity characters to the right of the decimal point.
In this program, you can find out the percentage of the total amount in two ways;

The first function is fixed in the control panel itself, where at the top, approximately in the middle, there is a mat. panel displays the % icon.
The second option is to write it in a line and subsequently perform the calculation.
If you often have to deal with interest, I can suggest you automate this process. To do this, you will need to create a small template for yourself. Let me explain:
In cell G6 the formula =E6-(E6*F6) is entered. Cell F6 is in percentage format. If, for example (as in the example), you need to subtract 10% from 100, then you enter the number 100 in cell E6, the required percentage in cell F6, and in the final cell G6 you get the result.
Select functions, then mathematical ones, and then %. Although it’s more convenient for me to write the formula myself. % is a part of 100. Therefore, if you need to subtract, for example, 27%, then I will write it as a formula: (column where the required number is) * 0.73.
For example, you need to subtract 10% from 100. We enter the formula =100-(100*0.1) It turns out 90. Well, then it depends on your imagination, you can make this process much easier (if you need to often add and subtract percentages) by dividing these actions into cells and percentages.
Subtracting percentages in Excel is fairly easy. To do this, select an empty cell and put an equal sign there. Then we look at the numeric address of the cell from which the percentage needs to be subtracted. We insert this address after the equals and put the multiply sign. Then we insert or manually enter the percentage that we are subtracting. Then put the percentage icon and press enter.
To subtract a percentage from a number in this program, you need to do the following:
- put the cursor in an empty cell in which we will have the result,
- We begin to compose a formula, first we write the sign =;
- Next we write the number from which we subtract the percentage, you can also click on it if the number is already in the cell,
- then we write the sign -;
- then we put the sign (;
- click on the number from which you want to subtract the percentage,
- then we write the multiplication sign * and indicate the number of percents,
- then we write the % sign, and then );
Now all you have to do is press Enter.