Microsoft Excel allows you to quickly work with percentages: find them, sum them up, add them to a number, calculate percentage growth, percentage of a number, of a sum, etc. These skills can be useful in a variety of areas of life.

In everyday life, we are increasingly confronted with interest: discounts, loans, deposits, etc. Therefore, it is important to be able to calculate them correctly. Let's take a closer look at the techniques offered by the built-in spreadsheet toolkit.

How to calculate percentage of a number in Excel

The mathematical formula for calculating interest is as follows: (search part / integer) * 100.

To find the percentage of a number, the following version of the formula is used: (number * percentage) / 100. Or move the comma as a percentage by 2 digits to the left and perform only multiplication. For example, 10% of 100 is 0.1 * 100 = 10.

Which formula to apply in Excel depends on the desired result.

Task #1: Find how much is 20% of 400.

1. We make the cell in which we want to see the result active.
2. In the formula bar or directly into the cell, enter =A2*B2.

Since we immediately applied the percentage format, we did not have to use a mathematical expression in 2 steps.

How to assign a percentage format to a cell? Choose any method convenient for you:

• immediately enter a number with the sign "%" (the cell will automatically set the desired format);
• right-click on the cell, select "Format Cells" - "Percentage";
• select a cell and press the hot key combination CTRL+SHIFT+5.

Without using the percentage format, the usual formula is entered into the cell: \u003d A2 / 100 * B2.

This option for finding a percentage of a number is also used by users.

Task #2: 100 items ordered. Delivered - 20. Find how many percent of the order is completed.

1. Set the required cell format to percentage.
2. Enter the formula: =B2/A2. Press ENTER.

In this problem, we again managed with one action. The quotient did not have to be multiplied by 100, because the cell is formatted as a percentage.

It is not necessary to enter percentages in a separate cell. We can have a number in one cell. And in the second - the formula for finding the percentage of the number (= A2 * 20%).

How to add percentage to a number in Excel?

In mathematics, we first find percentages of a number, and then we perform addition. Microsoft Excel does the same. We need to enter the formula correctly.

Task: Add 20 percent to the number 100.

1. We enter the values ​​in cells with the appropriate formats: number - with a numeric (or general), percentage - with a percentage.
2. Enter the formula: =A2+A2*B2.

Another formula can be used to solve the same problem: =A2*(1+B2).

Difference between numbers as a percentage in Excel

The user needs to find the difference between the numerical values ​​as a percentage. For example, calculate how much the price of the supplier has increased / decreased, the profit of the enterprise, the cost of utilities, etc.

That is, there is a numerical value that has changed over time, due to circumstances. To find the percentage difference, you must use the formula:

("new" number - "old" number) / "old" number * 100%.

Task: Find the percentage difference between the "old" and "new" prices of the supplier.

1. Let's make the third column "Dynamics in percent". Assign percentage format to cells.
2. Put the cursor in the first cell of the column, enter the formula: = (B2-A2) / B2.
3. Let's press Enter. And drag the formula down.

The percentage difference has a positive and a negative value. The establishment of the percentage format made it possible to simplify the original calculation formula.

The percentage difference between two numbers in the default cell format ("General") is calculated using the following formula: =(B1-A1)/(B1/100).

How to multiply by percentage in Excel

Task: 10 kg of salt water contains 15% salt. How many kilograms of salt are in the water?

The solution comes down to one action: 10 * 15% = 10 * (15/100) = 1.5 (kg).

How to solve this problem in Excel:

1. Enter the number 10 in cell B2.
2. Place the cursor in cell C2 and enter the formula: \u003d B2 * 15%.
3. Press Enter.

We didn't have to convert the percentage to a number because Excel recognizes the "%" sign perfectly.

If the numeric values ​​are in one column, and the percentages are in another, then it is enough to make cell references in the formula. For example, =B9*A9.

Calculation of interest on a loan in Excel

Task: They took 200,000 rubles on credit for a year. Interest rate - 19%. We will repay over the entire term in equal installments. Question: what is the amount of the monthly payment under these credit conditions?

Important conditions for choosing a function: the constancy of the interest rate and the amounts of monthly payments. A suitable variant of the function is "PLT ()". It is located in the section "Formulas" - "Financial" - "PLT"

1. Rate - the interest rate on the loan divided by the number of interest periods (19%/12, or B2/12).
2. Nper is the number of loan payment periods (12).
3. PS - loan amount (200,000 rubles, or B1).
4. The fields of the arguments "BS" and "Type" will be ignored.

The result with the "-" sign, because the borrower will repay the money.

In life, sooner or later, everyone will face a situation where it will be necessary to work with interest. But, unfortunately, most people are not ready for such situations. And this move is problematic. This article will show you how to subtract percentages from a number. Moreover, various ways of solving the problem will be analyzed: from the simplest (with the help of programs) to one of the most difficult (with the help of a pen and a piece of paper).

We take away manually

Now we will learn how to take away with a pen and a piece of paper. The actions that will be presented below are studied by absolutely every person at school. But for some reason, not everyone remembered all the manipulations. So, what you will need, we have already figured out. Now we will tell you what needs to be done. To make it more clear, we will consider an example, taking specific numbers as a basis. Let's say you want to subtract 10 percent from the number 1000. Of course, it is quite possible to perform these actions in your mind, since the task is very simple. The main thing is to understand the essence of the solution.

First of all, you need to write down the proportion. Let's say you have two columns with two rows. You need to remember one thing: numbers fit into the left column, and percentages into the right. Two values ​​​​will be written in the left column - 1000 and X. X is entered because it is he who symbolizes the number that needs to be found. In the right column will be entered - 100% and 10%.

Now it turns out that 100% is the number 1000, and 10% is X. To find x, you need to multiply 1000 by 10. Divide the resulting value by 100. Remember: the required percentage must always be multiplied by the number taken, after which the product should be divided by 100%. The formula looks like this: (1000*10)/100. The picture will clearly show all the formulas for working with percentages.

We got the number 100. That is what lies under the very X. Now all that remains to be done is to subtract 100 from 1000. It turns out 900. That's all. Now you know how to subtract percentages from a number using a pen and notebook. Practice on your own. And over time, you will be able to perform these actions in your mind. Well, we move on, talking about other ways.

Subtracting with the Windows Calculator

It's clear: if there is a computer at hand, then few people want to use a pen and a notebook for calculations. It's easier to use technology. That is why now we will consider how to subtract percentages from a number using the Windows calculator. However, it is worth making a small remark: many calculators are capable of performing these actions. But the example will be shown using the Windows calculator for better understanding.

Everything is simple here. And it is very strange that few people know how to subtract percentages from a number in a calculator. First, open the program itself. To do this, go to the Start menu. Next, select "All Programs", then go to the "Accessories" folder and select "Calculator".

Now everything is ready to start the solution. We will operate with the same numbers. We have 1000. And we need to subtract 10% from it. All you need to do is enter the first number (1000) into the calculator, then press the minus (-), and then click on the percentage (%). As soon as you have done this, the expression 1000-100 will immediately appear to you. That is, the calculator automatically calculated how much it is 10% of 1000.

Now press Enter or equals (=). Answer: 900. As you can see, both the first and second methods led to the same result. So it's up to you which method to use. Well, in the meantime, we are moving on to the third, last option.

Subtract in Excel

Many people use Excel. And there are situations when it is vital to quickly make a calculation in this program. That is why now we will figure out how to subtract a percentage from a number in Excel. In the program, this is very easy to do using formulas. For example, you have a column with values. And you need to subtract 25% from them. To do this, select the column next to it and enter equals (=) in the formula field. After that, click LMB on the cell with the number, then put "-" (and again click on the cell with the number, then enter - "*25%). You should get, as in the picture.

As you can see, this is the same formula that was given for the first time. After pressing Enter, you will receive a response. To quickly subtract 25% of all numbers in a column, you just need to hover over the answer, placing it in the lower right corner, and drag it down to the required number of cells. Now you know how to subtract a percentage from a number in Excel.

Conclusion

Finally, I would like to say only one thing: as you can see from all of the above, in all cases only one formula is used - (x * y) / 100. And it was with her help that we managed to solve the problem in all three ways.

Literally today I encountered an error when calculating the difference between two values ​​as a percentage. This error led to quite serious consequences, and the correction of 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 percentage in Excel? Calculate percentage of a number. Percentage difference and correct formatting.

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

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

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

The fact is that the method of writing percentages shown in line 5 makes subsequent calculations difficult - you need to divide the data by 100 each time in new formulas. There is a high probability of forgetting about it or taking 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 calculation of a number from a percentage

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 the question mark we need? = 250 * 100% / 7%. Those. it will be 3571, it 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%.

Do not judge strictly who considers such information elementary - someone will definitely need a description of the proportion.

Percentage difference

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

The rule from the big we subtract the smaller does not work here, since the difference can be negative. There is also a common question here, what number should be divided by. It all depends on the conditions of the task. If you need to find an increase in the indicator in relation to the previous year, 2016, you need to divide it by the data of 2016.

Of course, I am applying.

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 can drive you into 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, to get 15 percent of the number in cell A2, you would 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. As a result, the 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. You can not divide by 100, as in school, but 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 the percent sign at the end of the formula.

But, if the cell in which the percentage is indicated does not have a numeric, but a percentage format, then in this case the percent sign at the end of the formula does not need to be indicated, 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 the number and immediately add it to this number. In this case, it is convenient to use the second way of writing the formula, without school division by 100. If you write the percentage directly into the formula, then it will look something like this: = A2 + A2 * 18%, where A2 is the original number, and 18 is the percentage to be calculated and added.

When working with tabular data, you often need to calculate a percentage of a number or calculate a percentage of a total. This feature is provided by Microsoft Excel. But unfortunately, not every user knows how to use the tools for working with percentages in this program. Let's find out how to calculate percentage in Excel.

Excel can perform many mathematical tasks, including the simplest calculation of interest. Depending on the needs, it will not be difficult for the user to calculate the percentage of the number and the number of the percentage, including in tabular data options. To do this, you just need to use certain formulas.

Option 1: Calculate percentage of a number

Now let's take a look at how you can calculate percentages by working with tabular data in cells.

Option 2: Calculate a number by percentage

As you can see, the program provides an opportunity to conveniently work with percentage values. The user can calculate both the share of a certain number as a percentage, and the number of the total amount by percentage. Excel can be used to work with percentages like a regular calculator, but it can also be used to easily and automate the work of calculating percentages in spreadsheets.