How to subtract a percentage of the number in Excel. Microsoft Excel: Add a Percentage to a Number

In the modern world, percentages are everywhere. Not a day goes by without using them. Buying products - we pay VAT. Taking a loan from the bank, we pay the amount with interest. When we compare income, we also use interest.

Working with percentages in Excel

Before starting work in Microsoft Excel, let's remember school math lessons, where you studied fractions and percentages.

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

Performing the action of adding percent (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 the percentage of a whole number, divide the desired fraction by an integer and multiply the result by 100.

Example # 1. The warehouse contains 45 units of goods. 9 units of the product were sold in a day. How much was sold as a percentage?

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

(9/45)*100=20%

We do the following in the program:

How did it come about? Having set the percentage type of calculations, the program will independently add the formula for you and put a "%" sign. If we set the formula ourselves (multiplied by one hundred), then the "%" sign would not exist!

Example # 2. Let's solve the inverse problem. It is known that there are 45 units of goods in the warehouse. It is also indicated that only 20% were sold. How many units were sold in total?

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

We multiply the price of the product by the percentage, according to the formula B1 * 18%.

Advice! Do not forget to extend this formula to the rest of the lines. To do this, grab the lower right corner of the cell and lower it to the end. Thus, 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 want to find the price of an item.

Add and subtract

Let's start by adding... Let's consider the problem with a simple example:

1. We are given the price of the item. It is necessary to add to it the VAT percentage (VAT is 18%).
   
2. If we use the formula B1 + 18%, then we get the wrong result. This is 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 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 "number".
   
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... With the knowledge of addition, subtraction will not be difficult. Everything will work by replacing one sign "+" with "-". The working formula will look like this: B1-B1 * 18% or B1-B1 * 0.18.

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

These are the basic tasks. It seems simple, but many people make mistakes.

Making a percentage chart

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

Pie chart

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

After that, your diagram will appear in the table. If you are not satisfied with its location, then move it by pulling outside the diagram.

bar graph

For this we need data. For example, sales data. To create a histogram, we need to select all the numerical values ​​(except for the total) and select the histogram in the "Insert" tab. To create a histogram, we need to select all the numerical values ​​(except for the total) and select the histogram in the "Insert" tab.

Schedule

Instead of a histogram, you can use a graph. For example, the histogram is not suitable for tracking profit. It would be more appropriate to use a graph. The graph is inserted in the same way as the histogram. It is necessary to select a chart in the "Insert" tab. Another one can be superimposed on this chart. 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, write to us. We will try to help you.

Percentage formula in Excel

In Excel, you can apply the "Percentage" format to a cell, and depending on whether it is applied to a cell in which percentages are written or not, the type of formula will depend. The whole difference lies in the fact that such a cell is automatically written as a percentage, and there is no need to apply division and multiplication by one hundred in the formula.

For example, we need to get a percentage of a number and we have a general format applied to the cells. In this case, the formula is written as taught at school, divide by one hundred and multiply by a percentage.

You can also simplify the notation a little by using the percent sign in the formula, perhaps it will be more convenient for someone. The seed cell is multiplied by the percentage cell, followed by a percentage sign.

If the "Percentage" format is applied to the cell containing the percentage value, the formula will look like the multiplication of two cells.

How to subtract or add percentages to a number in Excel?

You can subtract percentages in Excel or add percentages to a number by simply writing the formula correctly, taking into account the format applied to the cells. For example, the formulas for subtracting percent from a number may be as shown in the pictures below.

The formula for adding percentages to a number in Excel will look similar.

In various types of activities, the ability to count percentages is required. Understand how they "work out". Trade margins, VAT, discounts, profitability of deposits, securities and even tips - all this is calculated as a part of the whole.

Let's see how to work with percentages in Excel. A program that makes calculations automatically and allows variations of the same formula.

Working with percentages in Excel

Calculating the percentage of the number, adding, subtracting percentages on a modern calculator will not be difficult. The main condition is that there must be a corresponding icon (%) on the keyboard. And then - a matter of technique and care.

For example, 25 + 5%. To find the meaning of an expression, you need to type this sequence of numbers and signs on the calculator. The result is 26.25. You don't need a big mind with such a technique.

To compile formulas in Excel, let's remember the school basics:

Percentage is one hundredth of a whole.

To find the percentage of a whole number, you need to divide the desired fraction by an integer and multiply the total by 100.

Example. They brought 30 units of goods. On the first day, 5 units were sold. How many percent of the product were sold?

5 is part. 30 is a whole. We substitute the data into the formula:

(5/30) * 100 = 16,7%

To add a percentage to a number in Excel (25 + 5%), you must first find 5% of 25. At school, the proportion was:

X = (25 * 5) / 100 = 1.25

Then the addition can be performed.

Once the basic computational skills are restored, the formulas will be easy to figure out.



How to calculate percentage of a number in Excel

There are several ways.

We adapt the mathematical formula to the program: (part / whole) * 100.

Look closely at the formula bar and the result. The result was correct. But we not multiplied by 100... Why?

The format of the cells changes in Excel. For C1 we have assigned the "Percentage" format. It involves multiplying the value by 100 and displaying it with a% sign. If necessary, you can set a certain number of digits after the decimal point.

Now let's calculate how much 5% of 25 will be. To do this, enter the calculation formula in the cell: = (25 * 5) / 100. Result:

Or: = (25/100) * 5. The result will be the same.

Let's solve the example in a different way, using the% sign on the keyboard:

Let's apply the knowledge gained in practice.

The cost of the goods and the VAT rate (18%) are known. You need to calculate the amount of VAT.

Let's multiply the cost of the product by 18%. Let's "multiply" the formula for the entire column. To do this, grab the lower right corner of the cell with the mouse and drag it down.

The amount of VAT, the rate is known. Let's find the cost of the product.

Calculation formula: = (B1 * 100) / 18. Result:

The quantity of goods sold is known, separately and in total. You need to find the share of sales for each unit relative to the total.

The calculation formula remains the same: part / whole * 100. Only in this example we will make the reference to the cell in the denominator of the fraction absolute. Use the $ sign in front of the row name and column name: $ In $ 7.

How to add a percentage to a number

The task is solved in two steps:

And here we have performed the actual addition. Let's omit the intermediate action. Initial data:

The VAT rate is 18%. We need to find the amount of VAT and add it to the price of the product. Formula: price + (price * 18%).

Don't forget about parentheses! With their help, we establish the calculation procedure.

To subtract a percentage from a number in Excel, follow the same procedure. Only, instead of adding, we perform subtraction.

How to calculate percentage difference in Excel?

How much the value has changed between the two percentages.

Let's abstract from Excel first. A month ago, tables were brought to the store at a price of 100 rubles per unit. Today the purchase price is 150 rubles.

Percentage difference = (new data - old data) / old data * 100%.

In our example, the unit purchase cost has increased by 50%.

Let's calculate the percentage difference between the data in two columns:

Do not forget to set the "Percentage" format of the cells.

Let's calculate the percentage change between the lines:

The formula is (next value is previous value) / previous value.

With this data arrangement, skip the first line!

If you need to compare data for all months with January, for example, we use an absolute cell reference with the desired value ($ sign).

How to make a percentage chart

The first option is to make a column in a table with data. Then use this data to build a chart. Select the cells with percentages and copy - press "Insert" - select the chart type - OK.

The second option is to set the format of the data signatures in the form of a fraction. In May - 22 work shifts. You need to calculate as a percentage: how much each worker worked. We draw up a table where the first column is the number of working days, the second is the number of days off.

Making a pie chart. Select the data in two columns - copy - "Insert" - chart - type - OK. Then we insert the data. We click on them with the right mouse button - "Format of data labels".

We select "Shares". On the "Number" tab - percentage format. It turns out like this:

We will stop there. And you can edit to your liking: change the color, the appearance of the diagram, make underlining, etc.

During calculations, sometimes it is required to add percentages to a specific number. For example, to find out the current profit indicators, which have increased by a certain percentage compared to the previous month, you need to add this percentage to the last month's profit. There are many other examples when you need to perform a similar action. Let's see how to add a percentage to a number in Microsoft Excel.

So, if you just need to find out what the number will be equal to, after adding a certain percentage to it, then you should drive the expression into any cell of the sheet, or into the formula bar, according to the following pattern: "= (number) + (number) * (percentage_value )% ".

Let's say we need to figure out what number we get when we add twenty percent to 140. We write the following formula in any cell, or in the formula bar: "= 140 + 140 * 20%".

Applying a Formula to Actions in a Table

Now, let's figure out how to add a certain percentage to the data that is already in the table.

First of all, we select the cell where the result will be displayed. We put the "=" sign in it. Next, we click on the cell containing the data to which the percentage should be added. We put the "+" sign. Again, click on the cell containing the number, put the "*" sign. Next, we type on the keyboard the value of the percentage by which the number should be increased. Do not forget to put the "%" sign after entering this value.

We click on the ENTER button on the keyboard, after which the result of the calculation will be shown.

If you want to extend this formula to all column values ​​in the table, then just stand on the lower right edge of the cell where the result is displayed. The cursor should turn into a cross. Click on the left mouse button, and with the button held down, drag the formula down to the very end of the table.

As you can see, the result of multiplying numbers by a certain percentage is displayed for other cells in the column.

We've found that adding a percentage to a number in Microsoft Excel is not that difficult. However, many users do not know how to do this and make mistakes. For example, the most common mistake is to write a formula according to the algorithm "= (number) + (percentage_value)%" instead of "= (number) + (number) * (percentage_value)%". This guide should help you avoid such mistakes.

If you are reading this material, then you probably already have some idea of ​​how Excel works. Accordingly, you know that this program is the best suited for carrying out various kinds of calculations. Thus, everything that you previously calculated in your head or on a calculator can be easily calculated in Excel, which, of course, significantly saves time for people of the respective professions.

In order not to beat around the bush for a long time, I'll tell you how to subtract percentages in excel. Again, for sure you are used to doing this in your head, however, like an elementary one, but why bother if you have an application in front of you that can do all this by itself? Let's take a closer look.

What is interest?

If you do not mind, I will make a short excursion to the school mathematics course, when you went through this topic and, perhaps, forgot a little. So, the percentage is actually some part of an integer. To calculate the percentage, you need to divide the desired figure by an integer value, and then multiply the result by 100.

Simple, right? Performing this operation in Excel is even easier, and now you will see for yourself.

How to subtract a percentage

Let's imagine that any percentage is some part of a unit. Well, for example, 15% can be altered as 0.15, 138% - as 1.38, etc., respectively, say, the formula "= 200 + 15%" is no different from the formula "= 200 + 0.15". This was a short introduction, now I propose to move on to the practical part, written in the form of examples based on the above 200 and 15%.

Each of these methods is good in its own way, and each will find its own user. Choose and you are the most convenient and understandable for yourself!