Excel how to add percentages. How to add percentages in Excel using a formula

Very simple adding procedure. Select the desired cell and add the required amount of percent according to a simple scheme (this is the simplest): multiply by the number in cell 1X - where x, the number, is the number of tens of percent. For example: 26% - write *12.6.

In order to add exactly the given percentages to a given number, you need to create a formula:

1. Select the cell where the result will be located and put there = (equals)...
2. Select the cell where the given number is located...
3. We put a + (plus) sign...
4. Open the bracket (...
5. Repeat step 2...
6. We put the * sign (multiply)...
7. Select the cell where the specified percentage is located or simply enter the percentage number...
8. We put the % (percentage) sign...
9. Close the bracket)...
10. Press Enter...

The formula is ready, the result is obtained in the form of a number, this formula looks something like this:

=K22+(K22*20%)

=K22+(K22*L22%)

To add a percentage to a number, you need to select the cell with the original data and enter the number with the percentage sign in another column. And in the third, write in the formula line: P11+P11*18%.

For example, P11 is equal to 15. Then the final number is - 17.7 Using the same anthology, the other cells in the column are drawn and Excel will calculate everything. Autumn is a good time for stores to do promotions. For example, all vegetables are 20% more expensive than the purchase price.

To add percentage to a number, you need to multiply this number by (1+%), that is, for example, to 200 you need to add 10% = 200*1.1=220. If such an action needs to be done with a column, then simply grab the cell by the cross in the lower right corner of the cell and pull it down.

Adding percentages in the Excel editor will be quite simple. First, you need to select an empty cell and put an equal sign (=) there. Then take the cell where the desired number is shown. Click plus, open the bracket, again select the given number and insert the multiply sign, then select the cell with our percentage and enter the value and the percent sign itself. Then we close the bracket. Well, press enter

Add interest to spreadsheet it will be possible as follows:

First, put the = sign in the cell with the result,

Select the cell with the original number, then press +,

select the cell again, then press the * sign,

the next step is to press the number sign (how many percent) and the % sign itself

The bracket is closed and completed by pressing the Enter key.

A percentage is a number that means a part of something whole. To calculate interest in school curriculum there is a universal formula

(required part / (divide) the whole)* (multiply)100=%

Let's take a simple example

I have 30 apples, 10 of which I gave away. Question: what percentage of apples did I give away? We get simple formula calculations

Calculate interest in Microsoft Excel It turns out faster and easier, since many actions are performed automatically. Your task is only to correctly enter the formula and enter the numbers.

We enter the formula and set the cells with the result, percentage format.

PART/WHOLE=PERCENTAGE

Now in detail

In column D we have the result obtained, it is expressed as a percentage, since we set the format in column D as a percentage.

We enter the formula in the column with the part (in our in this case column C) is divided by a column with an integer (in this example it is column B), we get the result.

if there are a lot of such calculations to be made, i.e. this is several lines (we have 9 in this case), then for convenience you can automate everything - select the entire column D into as many lines as necessary and click autofill (this way all the lines in column D will acquire the formula specified by it). And then you just need to substitute the necessary numbers.

To get the number exactly as a percentage, i.e. set the percentage format, then you need to select:

Home - Number - Percentage format (if the Russian version of the program)

Home - Number - Percent Style (if English version programs)

In formulas, all letters are typed in English language, regardless of the program language.

There are many options for working with percentages in Microsoft Excel, all of them depend on the task you are faced with. For clarity, you can watch this video:

in the first video you can watch how the process of working in Excel with percentages and data visualization (visual representation of percentages) goes on

the second video shows a lesson in Russian, where they explain how to substitute percentages and how to calculate percentages

In order to add percentages to any number in Excel, you need to write in the cell the formula corresponding to this action. As usual, we start all formulas with the equal sign, and then select the cell with the number you need, now put a plus and after the plus, open the bracket and select the same cell, and now the multiply sign and enter the number, and then the percent sign, it will look like this :

= A44+(A44*50%)

Also, the number for percentage can be taken from any cell, then the formula will be like this:

= A44+(A44*B44%)

Finish by pressing enter.

Adding percentages in Excel is a simple procedure. You just need to write mathematical formula. For example, you want to increase the number 30 by 20%, as you would count on a piece of paper: =30*1.2 or =30+30*0.2=30*(1+0.2). You can enter these same formulas in Excel, but here you can make more universal formulas, for example, in your cell A1=30 in cells B1=20%

You write =A1*(1+B1) in any cell, and now you can choose any value for A1 and B1.

A similar problem can be solved in the following ways.

First, you need to determine that cell A1 will contain the initial number, for example, 30.

Then in cell B1 the percentage that needs to be added is, for example, 15%. In our example, the number format of a cell with a percentage must be set to Percentage.

In this case, in cell C1 the final result of adding the percentage to the number will be indicated.

Thus, we can see the calculation formula we need in the formula bar.

In the first case, we multiply the number by the sum of one with a percentage.



In the second case, we add to the original number the product of the same number and the percentage.

Interest in modern world spinning all over the place. Not a day goes by without using them. When purchasing products, we pay VAT. Having taken out a loan from a bank, we repay the amount with interest. When reconciling income, we also use percentages.

Working with percentages in Excel

Before you start working in Microsoft Excel, remember school lessons mathematics where you learned fractions and percentages.

When working with percentages, remember that one percent is a hundredth (1% = 0.01).

When performing the action of adding percentages (for example, 40+10%), we first find 10% of 40, and only then add the base (40).

When working with fractions, do not forget about the basic rules of mathematics:

1. Multiplying by 0.5 is equal to dividing by 2.
2. Any percentage is expressed as a fraction (25%=1/4; 50%=1/2, etc.).

We count the percentage of the number

To find a percentage of a whole number, divide the desired percentage by the whole number and multiply the result by 100.

Example No. 1. There are 45 units of goods stored in the warehouse. 9 units of goods were sold in a day. How much of the product was sold as a percentage?

9 is a part, 45 is a whole. Substitute the data into the formula:

(9/45)*100=20%

In the program we do the following:

How did this happen? Having set the percentage type of calculation, the program will independently complete the formula for you and put the “%” sign. If we set the formula ourselves (with multiplication by one hundred), then there would be no “%” sign!

Example No. 3. Let's try the acquired knowledge in practice. We know the price for the product (see picture below) and VAT (18%). You need to find the VAT amount.

We multiply the price of the product by the percentage using the formula B1*18%.

Advice! Don't forget to extend this formula to the remaining lines. To do this, grab the lower right corner of the cell and lower it to the end. This way we get an answer to several elementary problems at once.

Example No. 4. Inverse problem. We know the amount of VAT for the product and the rate (18%). You need to find the price of a product.

Add and subtract

Let's start with the addition. Let's look at the problem using a simple example:

1. We are given the price of the product. It is necessary to add VAT percentage to it (VAT is 18%).
   
2. If we use the formula B1+18%, then the result we will get is incorrect. This happens because we need to add not just 18%, but 18% of the first amount. As a result, we get the formula B1+B1*0.18 or B1+B1*18%.
   
3. Pull down to get all the answers at once.
   
4. In case you use the formula B1+18 (without the % sign), then the answers will be obtained with “%” signs, and the results will not be what we need.
   
5. But this formula will also work if we change the cell format from “percentage” to “numeric”.
   
6. You can remove the number of decimal places (0) or set it at your discretion.
   

Now let's try to subtract the percentage from the number. Having knowledge about addition, subtraction will not be difficult at all. Everything will work by replacing one sign “+” with “-”. Working formula will look like this: B1-B1*18% or B1-B1*0.18.

Now let's find percentage of all sales. To do this, we sum up the quantity of goods sold and use the formula B2/$B$7.

These are the basic tasks we accomplished. Everything seems simple, but many people make mistakes.

Making a chart with percentages

There are several types of charts. Let's look at them separately.

Pie chart

Let's try to create a pie chart. It will display the percentage of sales of goods. First, we are looking for percentages of all sales.

Schedule

Instead of a histogram, you can use a graph. For example, a histogram is not suitable for tracking profits. It would be more appropriate to use a graph. A graph is inserted in the same way as a histogram. You need to select a chart in the “Insert” tab. Another one can be superimposed on this graph. For example, a chart with losses.

This is where we end. Now you know how to rationally use percentages, build charts and graphs in Microsoft Excel. If you have a question that the article did not answer, . We will try to help you.

Working in Excel program Often there is a need to add or subtract some percentages from a number. This may be due to the need to add a VAT percentage or calculate profit. Whatever the specific task, it can be solved in Excel.

Now we will talk about how to add a percentage to a number in Excel. The material will be useful for users of all versions of Excel, including Excel 2003, 2007, 2010, 2013 and 2016.

To explain how to add a percentage to a number, let's look at a simple example. Let's say you have a number to which you need to add a certain percentage (for example, you need to add 18% VAT). And in the next cell you want to get the value with the percentage already added.

To do this, you need to select the cell where the result should be located and enter the formula into it. As a formula, you can use this simple construction: =A2+A2*18%. Where A2 is the cell containing the original number, and 18 is the percentage you want to add to this original number.

Once you have entered the formula, you just need to press the Enter key on your keyboard and you will get the result. In our case, we added 18 percent to the number 100 and got 118.

If you don’t want to add a percentage, but subtract it, then this is done in a similar way. Only the formula uses a minus rather than a plus.

If necessary, the percentage that you will add or subtract can not be indicated directly in the formula, but taken from the table. For this case, the formula needs to be slightly modified: =A2+A2*B2%. As you can see, in the formula instead of specific meaning percent, the cell address is used, followed by the percent.

After using this formula, you will receive a number with the percentage added to it, which was indicated in the table.

Possible problem when adding interest

It should be noted that when working with percentages, you may end up with some too large numbers, as well as the percentage sign, starting to appear in your cells.

This happens in cases where the user first enters the formula incorrectly and then corrects it. For example, in the case of adding 18 percent, you can make a mistake and enter: =A2+18%.

If after this you correct yourself and enter correct formula=A2+A2*18%, then you will get some implausibly large number.

The problem is that as a result of introducing the first formula, the cell format changed from numeric to percentage. To fix this, right-click on the cell and go to “Format Cells”.

In the window that opens, select the cell format that will suit it.

Most often, it is general or numeric. After selecting the desired format, save the settings using the “Ok” button.

In almost every field of activity we are faced with the need to be able to calculate percentages. Most often, we need interest calculation skills when calculating VAT, margin, markup, loan interest, return on deposits and valuable papers, discounts. All these calculations are made as a percentage of the amount.

Click the button below to download Excel file with examples of percentage calculations in Excel:

What is percentage?

Percent (lat. per cent - per hundred) - one hundredth part. Marked with a “%” sign. Denotes a share of something in relation to the whole. For example, 25% of 500 rubles. means 25 parts of 5 rubles. each, that is, 125 rubles.

The basic formula for calculating the percentage of the amount looks like this:

There is no universal formula that fits every interest calculation scenario. Below you will learn about the most popular calculation methods.

How to calculate percentage of amount in Excel

Video lesson:

(Part/Whole) * 100 = Percentage (%)

But if you use the “Percentage” cell format, then to calculate the percentage of a number you just need to divide one number by another. For example, we have apples that we bought for 100 rubles. in a wholesale warehouse, and the retail price will be 150 rubles. In order to calculate the percentage of the purchase price from the retail price, we need:

• Create a table with prices and add a column to calculate the percentage:

• In cell D2, enter a formula that calculates the percentage of the purchase price from the sales price:

• Apply cell D2 “Percentage” format:

How to calculate the percentage of the sum of values ​​in an Excel table

Video tutorial:

Let's imagine that we have a list of sellers with the sales volume for each and the total amount of all sales for all sellers. Our task is to determine what percentage each seller contributes to the final turnover:

For this we need:

• Add a column to the table with percentage calculation;
• Insert the formula into cell C2:

The $ signs fix column “B” and cell “9” so that when extending the formula to all rows of the table, Excel automatically substitutes the sales volume of each seller and calculates the % of total sales. If you do not put the “$” signs, then when you stretch the formula, the system will shift cell B9 as many cells down as you stretch the formula.

• Extend the formula to all table cells corresponding to the rows with the names of the sellers:

• In the example above, we stretched the formula and got the values ​​in the form of fractional numbers. In order to convert the received data into percentages, select the data with the left mouse button and change the cell format to “Percentage”:

How to calculate the percentage of multiple values ​​from the table sum

Video tutorial:

In the example above, we had a list of sellers and their sales volume. We calculated how much each employee contributed to the final sales volume. But what if we have a list of recurring products with sales volume data and we need to calculate what percentage of all sales a particular product represents?

From the list of products above, we want to calculate the sales volume of tomatoes (they are written in several rows of the table). For this:

• To the right of the table we indicate the product (Tomatoes) for which we want to calculate the share in sales:

• Change the format of cell E2 to “Percentage”;
• In cell E2, insert a formula with the function that will help calculate Tomatoes from the list of products and sum up their sales volume, and then divide it by the total sales volume of goods:

How does this formula work?

For calculations we use the formula. This function returns the sum of the numbers specified as arguments that meet the criteria specified in the formula.

The syntax for the SUMIF function is:

=SUMMIF(range, condition, [sum_range])

• range– the range of cells by which the criteria are evaluated. Arguments can be numbers, text, arrays, or references containing numbers;
• condition– criteria that are checked against a specified range of cells and determine which cells to sum;
• sum_range– summed cells. If this argument is not specified, then the function uses the argument range as sum_range .

Thus, in the formula =SUMIF($A$2:$A$8,$E$1,$B$2:$B$8)/B9 we indicated “ $A$2:$A$8” as a range of products among which the function will search for the criterion we need (Tomatoes). Cell “ $E$1” is indicated as a criterion and indicates that we are looking for “Tomatoes”. Cell range “ $B$2:$B$8” indicates which cells need to be summed if the required criterion was found.

How to calculate percentage difference in Excel

For example, to calculate the percentage difference between two numbers A and B, use the formula:

(B-A)/A = Difference between two numbers as a percentage

In practice, when using this formula, it is important to determine which of the numbers you need is “A” and which is “B”. For example, imagine that yesterday you had 8 apples in stock, and today you have 10 apples. Thus, the number of apples you have compared to yesterday has changed by 25% upward. If you had 10 apples yesterday, and today you have 8 apples, then the number of apples compared to yesterday has decreased by 20%.

Thus, the formula to correctly calculate the percentage change between two numbers looks like this:

(New value - Old value) / Old value = Percentage difference between two numbers

Below, with examples, we will look at how to use this formula for calculations.

The most convenient way to calculate percentages and perform actions with them is in Excel in the package Microsoft Office, since all that is required is to indicate the values ​​​​and the desired formula.

A percentage is a hundredth of a whole number, which is denoted on paper by the % sign or decimals(70% = 0.70). The standard expression for calculating percentage is Whole/Part*100, but thanks to Excel you don’t have to calculate anything manually.

How to calculate percentages in Excel

The simplicity of working with the program lies in the fact that the user only needs to enter the values ​​of the whole and its part (or select from previously entered data), and indicate the calculation principle, and Excel will perform the calculations independently. In Excel the percentage is calculated like this: Part/Whole = Percentage, and multiplication by 100 occurs automatically when the user selects the percentage format:

To carry out the calculations, let’s take the calculation of the execution of the work plan:

The program will independently calculate the percentage of plan completion for each product.

Percentage of number

In Excel, you can calculate a number knowing only its fraction: %*Part = Whole. Let's say you need to calculate what is 7% of 70. To do this:

If the calculation is carried out when working with a table, then instead of entering numbers you need to provide links to required cells. You should be careful; when calculating, the format should be General.

Percentage of the amount

If the data is scattered throughout the table, then you need to use the formula SUMIF- it adds the values ​​that correspond to the specified parameters, in the example - the specified products. The formula for the example will look like this - “=SUMSIF (criterion range; addition range)/total amount”:

In this way, each parameter is calculated, i.e. product.

Calculate percentage change

Comparing two shares is also possible using Excel. To do this, you can simply find the values ​​and subtract them (from the larger to the smaller), or you can use the increase/decrease formula. If you need to compare numbers A and B, then the formula looks like this: (B-A)/A = difference" Let's look at an example of a calculation in Excel:

1. Stretch formula to the entire column using the autofill marker.

If the calculated indicators are located in one column for a specific product over a long period of time, then the calculation method will change:

Positive values ​​indicate an increase, and negative values ​​indicate a decrease.

Calculation of value and total amount

Often, it is necessary to determine the total amount knowing only the share. There are two ways to do this in Excel. Consider purchasing a laptop, assuming it costs $950. The seller says that this price does not include VAT, which is 11%. The final markup can be found by making calculations in Excel:

Let's look at the second calculation method using another example. Let’s say that when you buy a laptop for $400, the seller says that the price is calculated taking into account a 30% discount. You can find out the starting price like this:

The starting price will be $571.43.

How to change a value to a percentage value

Often you have to increase or decrease the final number by some fraction of it, for example, you need to increase monthly costs by 20%, 30% and 35%:

The program will calculate the total independently for the entire column if you drag out the expression using the fill marker. The expression for reducing the amount is the same, only with a minus sign - “ =Value*(1-%)».

Operations with interest

You can perform the same operations with fractions as with ordinary numbers: addition, subtraction, multiplication. For example, in Excel you can calculate the difference in indicators between a company's sales using the ABC command, which returns the absolute value of the number:

The difference between the indicators will be 23%.

You can also add (or subtract) a percentage to a number - consider the action using the example of vacation planning:

The program will independently perform calculations and the results will be 26,000 rubles for the vacation week and 14,000 rubles after the vacation, respectively.

Multiplying a number by a fraction in Excel is much easier than manually, since it is enough to indicate the required value and percentage, and the program will calculate everything itself:

All amounts can be quickly recalculated by expanding the formula to cover the entire column F.

To subtract a share, you must indicate a number in cell A1, and a percentage in cell A2. Perform calculations in cell B1 by entering the formula “ =A1-(A1*A2)».