When calculating in MS Excel, sometimes it may be necessary to add a percentage of a certain number to the same or another number. This technique is often used in accounting, for example, to calculate an employee's salary allowance, compare profitability indicators with different periods. All of these operations may require the ability to add percentages in Excel.

There are several ways to help realize what you have planned. Let's look at them in more detail in the context of this article.

How to add a percentage to a number in MS Excel

There are two ways to do this. Both involve using a special formula. Only in the first case, you work in one cell and, as a rule, with some specific numbers. In the second case, the work goes on with cells in which some data has already been set.
Let's consider these two cases on specific examples.

Example 1: Calculations in a cell

Provided that you do not have a table with filled data or this data has some other form, then it is better to use this method. It is implemented as follows:

Example 2: Working with cells

If you already have a table with data filled in, then this will even be a little easier. The calculation will take place according to the same formula, only instead of numbers, cell numbers will appear there. Here is a good example:

1. We have a table that shows that there is such and such revenue for a certain period for such and such a product. We need to find the same revenue, but increased by a certain percentage. Select the cell that is located in the same line with the desired product. There will be a formula.
2. The formula in our case will look like this: "= (cell number, where the amount of revenue for the product) + (cell number, where the amount of revenue for the product) * (percentage)%". In our case, the formula looks like this: "= C2 + C2 * 20%".
3. You don't need to remember cell numbers. In the formula editor, when you click on the desired cell, it is inserted into the formula automatically.
4. Press Enter to complete the calculation.

There is one serious note to this example - if the desired percentage is located in a cell, then the numbers in these cells must be converted to the appropriate format. Consider how to bring the numbers in the percentage column to the desired format for the correct calculation:

By displaying the result of one addition in this way, you can fill in the cells for the remaining goods in automatic mode. Select the cell in which you have already calculated everything and stretch it to the remaining products using the special manipulator in the lower right part. The data for the rest of the cells will be inserted automatically in accordance with the adjacent cells and columns that appeared in the formula.

As you can see, it is very easy to add a percentage of itself to a number in the Excel working interface. If necessary, you can check the resulting result for reliability using a calculator.

A simple but very common task that can be encountered in everyday work. How to add percentages in Excel using a formula?

Video: How to quickly calculate percentages in Excel 2007

Let's look at a simple example again and look at this problem in detail. Let's say we have a table with the price of goods to which we need to add 18% VAT.

If you write the formula in cell "C2"

B2 + 18%, then such a formula will be incorrect, since we need to add not just 18%, but 18% of the initial price. Therefore, to the initial price of 320 rubles, we need to add another 18% of 320 rubles, that is, 320 * 0.18 = 57.6 or B2 * 0.18

So the final formula will look like this

B2+B2*0.18 or =B2+B2*18%

Note! If you initially added percentages incorrectly, that is, by simply writing B2 + 18%, then this cell will automatically receive the percentage format, that is, it will automatically be multiplied by 100 and the% sign will be added. If this happened, then in the end, when you write down the correct formula, you can get this result.

To display the correct result, you need to change the cell format from percent to numeric. Select all the cells with the price including VAT, click on the right mouse button and select "Cell Format", in the window that opens, select "Numeric" and click on "OK"

Video: How to calculate percentages in Excel?

A simple task, but many people ask similar requests, so we decided to cover it as a separate article and describe it in such a way that it is clear why this is so.

I hope that the article helped you in solving your problem. We will be grateful if you click on the social buttons +1 and "Like" below this article.

Hi all! Do you know how to calculate percentages in Excel? In fact, interest in life accompanies us very often. After today's lesson, you will be able to calculate the profitability of an idea that has suddenly arisen, find out how much you actually get by participating in store promotions. Master some points and be with percentages on "You".

I'll show you how to use the basic calculation formula, calculate percentage growth, and other tricks.

Such a definition as a percentage is familiar to everyone from the school bench. It comes from Latin and literally means “out of a hundred”. There is a formula that calculates percentages:

Consider an example: there are 20 apples, 5 of which you treated your friends. Determine in percentage, what part did you give away? Through simple calculations, we get the result:

In this way, interest is calculated both on the school bench and in ordinary life. Thanks to Excel, such calculations become even easier, because everything happens automatically. There is no single formula for calculations. The choice of calculation method will depend on the desired result.

How to calculate percentages in Excel: the basic calculation formula

There is a basic formula that looks like:
Unlike school calculations, this formula does not need to be multiplied by 100. Excel takes care of this moment, provided that the cells are assigned a certain percentage format.

Let's consider a specific situation. There are products that are in the order stage, and there are goods that have been delivered. The order in column B, named Ordered. Delivered products are listed in column C named Delivered. It is necessary to determine the percentage of the delivered fruit:

• In cell D2, write the formula =C2/B2. Decide how many lines you need and use autocomplete to copy it.
• In the "Home" tab, find the "Number" command, select "Percent Style".
• Look at the decimal places. Adjust their number if necessary.
• Everything! Let's see the result.

The last column, D, now contains values ​​that represent delivered orders as a percentage.

How to calculate percentage of a total in excel

We will now look at some more examples of how to calculate percentage in excel of the total amount, which will allow you to better understand and assimilate the material.

1. The calculated amount is located at the bottom of the table

At the end of the table, you can often see the "Total" cell, where the total is located. We have to count each part to the final value. The formula will look like in the previously considered example, but the denominator of the fraction will contain an absolute reference. The $ sign will be in front of the row and column names.

Column B is filled with values, and cell B10 contains their total. The formula will look like:
Using a relative link in cell B2 will allow it to be copied and pasted into cells in the Ordered column.

2.Parts of the sum are located on different lines

Suppose we need to collect data that is in different rows and find out what part is occupied by orders for a particular product. Adding specific values ​​is possible using the SUMIF (SUMIF) function. The result that we get will be necessary for us to calculate the percentage of the amount.

Column A is our range, and the summation range is in column B. We enter the name of the product in cell E1. This is our main criterion. Cell B10 contains the sum of the products. The formula looks like this:

In the formula itself, you can place the name of the product:

If you need to calculate, for example, how much cherries and apples take as a percentage, then the amount for each fruit will be divided by the total. The formula looks like this:

Calculating Percentage Changes

Calculating data that changes can be expressed as a percentage and is the most common task in Excel. The formula for calculating the percentage change is as follows:

In the process of work, you need to accurately determine which of the values ​​which letter occupies. If you have more of any product today, this will be an increase, and if less, then a decrease. The following scheme works:

Now we need to figure out how we can apply it in real calculations.

1. Count the changes of two columns

Let's say we have 2 columns B and C. In the first one, we display the prices of the last month, and in the second - this month. To calculate the resulting changes, we enter a formula in column D.

The results of the calculation using this formula will show us the presence of an increase or decrease in price. Fill in the formula with all the lines you need using autocomplete. For cells with a formula, be sure to activate the percentage format. If you did everything correctly, you get such a table, where the increase is highlighted in black, and the decrease is in red.

If you are interested in changes over a certain period and the data is in one column, then we use the following formula:

We write down the formula, fill it with all the lines that we need and get the following table:

If you want to count the changes for individual cells, and compare them all with one, use the absolute reference we already know using the $ sign. We take January as the main month and calculate the changes for all months as a percentage:

Copying the formula over other cells will not change it, but a relative reference will change the numbering.

Calculating the value and the total amount from a known percentage

I clearly demonstrated to you that there is nothing complicated in calculating percentages through Excel, just like in calculating the amount and values ​​​​when the percentage is already known.
1. Calculation of the value by a known percentage

For example, you buy a new phone that costs $950. You are aware of the 11% VAT surcharge. It is required to determine the surcharge in monetary terms. This formula will help us with this:

In our case, applying the formula =A2*B2 gives the following result:

You can take as a decimal value, and using a percentage.

2. Calculation of the total amount

Consider the following example where the known starting amount is $400 and the salesperson tells you that the price is now 30% less than last year. How can I find out the original cost?

The price decrease was 30%, which means that we need to subtract this indicator from 100% to determine the desired share:

The formula that determines the initial cost is:

Given our task, we get:

Converting a value to a percentage

This method of calculation is useful for those who are especially careful about their expenses and want to make some changes to them.

To increase the value by a percentage, we will use the formula:

We need to reduce the percentage. Let's use the formula:

Using the formula =A1*(1-20%) reduces the value that is contained in the cell.

Our example shows a table with columns A2 and B2, where the first is the current expenses, and the second is the percentage by which you want to change the expenses in one direction or another. Cell C2 should be filled with the formula:

Increment by percentage of values ​​in a column

If you want to make changes to the entire data column without creating new columns for this and using an existing one, you need to take 5 steps:

Now we see values ​​increased by 20%.

Using this method, you can perform various operations for a certain percentage by entering it into a free cell.

Today was a big lesson. I hope you made it clear to yourself how to calculate percentage in excel. And, despite the fact that such calculations are not very popular for many, you will do them with ease.

Spreadsheet editor Microsoft Office Excel is often underestimated in vain. It seems to many that it is difficult to understand, so they use a calculator and other available tools to solve their problems. But why do this, if with the help of this editor you can simply recalculate formulas in batches, build graphs, tables almost automatically. And you can master the Excel base in a couple of days. If you want to explore all the functionality of this utility, then visit the site https://tutorexcel.ru/. There you can find any answer to the question regarding Excel.

We add percentages

Often, people need to add interest. To avoid doing this manually, it is enough to use Excel. And we'll tell you how.

Let's say that to a certain number, you need to add some fixed percentage. To do this, in cell A1, enter our amount, from which the percentage will be displayed. It will appear in cell A2. But first, let's do the following. As we said above, the percentage in this example is fixed. First, we determine the value of the multiplier. You can’t just enter 25% (our example) just like that. To do this, use the formula 1+(25/100)=1.25. The resulting value is our multiplier, which must be written in cell A2. To do this, click on it and enter the following: an equal sign, the source cell number (A1), an asterisk and a multiplier. It looks like this: \u003d A1 * 1.25. Now it remains to confirm the result by pressing the Enter key. The program will give you the result in a matter of seconds.

But, it does not always happen that you need to multiply by a fixed percentage. If it changes, then three cells will have to be used.

In the first, as in the previous case, we enter our number. In the second B1 we will enter our percentage. And finally, cell C1 is the result. In order to calculate the percentage, enter the following formula in C1: A1*(1+B1/100). A1 is the original number, and B1 is the percentage. In this case, we write the cell number so that when the percentage value changes, we do not change the formula. It will automatically substitute the number from B1. After that, press Enter and get the finished result.

As you can see, everything is extremely simple and clear. MS Excel is a multifunctional editor that is quite easy to learn, but nevertheless has the best base for working with graphs, tables and formulas.

Excel is used very often because of the ease of building tables. Most seo-specialists use it to group key queries for their semantic core.

The concept of interest

A percentage is one hundredth of a whole. Imagine that each number with which you operate is "cut" into one hundred equal parts. Based on this assumption, further operations become simple and clear.

How to add percentages to a number

Suppose we need to solve a problem.
The goods cost 1600 rubles. How much did the product cost after the price increase by 5%?

Stage one. We find how many rubles account for one percent. That is, we divide the price by 100 parts. We get the "size" of one part.

1600 / 100 = 16 rubles in one percent

Stage two. We find how many rubles are in five percent.

16 * 5 = 80 rubles

Stage three. We find the price that turned out after the rise in price.

1600 + 80 = 1680 rubles

Formula for adding percent to a number

The solution to the problem of how to add 5% to the price of 1600 rubles can be written in one line:
1600 * (100% + 5%) / 100%

Let's analyze this expression. 1600 - the number that needs to be increased by 5% (and in the future - substitute the required percentage here). This number is multiplied by a fraction, in the numerator of which is the percentage change that we need to get (we add 5% increase to 100% which is the original price) and in the denominator - 100%, because the number with which we operate is always 100%.

NUMBER * (100% + INCREASE%) / 100% = AddPercentageTONumber

Based on the above, you can also find how to subtract the required percentage from the number. You just need to put a minus sign in the numerator.

2080.1947