Xl add interest. How to calculate percentages in Excel
A simple but very common task that can occur in daily work. How to add percentages in Excel using formula?
Let's look at a simple example again and take a closer look at this task. Let's say we have a table with the price of a product to which we need to add 18% VAT.
If you write a formula in cell "C2"
B2 + 18%, then this 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 \u003d 57.6 or B2 * 0.18
So, the final formula will look like this
\u003d B2 + B2 * 0.18 or \u003d 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 the correct formula, you can get the following result
To display the correct result, you need to change the format of the cells from percent to numeric. Select all cells with the price with VAT, right-click and select "Format cells", in the window that opens, select "Numeric" and click on "OK"
A simple task, but many people ask similar requests, so we decided to cover it in a separate article and describe it so that it is clear why this is so.
I hope this article helped you in solving your problem. We would be grateful if you click on the social buttons +1 and "Like" below this article.
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.
Computing actions in a cell
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: “\u003d (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: "\u003d 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, select the cell where the result will be displayed. We put the "\u003d" 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 percentage value 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 \u200b\u200bin 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 writing a formula according to the algorithm "\u003d (number) + (percentage_value)%" instead of "\u003d (number) + (number) * (percentage_value)%". This guide should help you avoid such mistakes.
We are glad that we were able to help you solve the problem.
Ask your question in the comments, detailing the essence of the problem. Our specialists will try to answer as quickly as possible.
Did this article help you?
In various activities, the ability to count percentages is required. Understand how they "turn out". Trade margins, VAT, discounts, profitability of deposits, securities and even tips - all of 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 draw up 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 \u003d (25 * 5) / 100 \u003d 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 didn't multiply 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: \u003d (25 * 5) / 100. Result:
Or: \u003d (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, rate is known. Let's find the cost of the product.
Calculation formula: \u003d (B1 * 100) / 18. Result:
We know the amount of goods sold, individually 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 the column name: $ In $ 7.
How to add a percentage to a number
The task is solved in two steps:
- Find the percentage of the number. Here we figured out what would be 5% of 25.
- Let's add the result to the number. Example for reference: 25 + 5%.
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 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 \u003d (new data - old data) / old data * 100%.
In our example, the unit purchase cost increased by 50%.
Let's calculate the difference in percentage between the data in two columns:
Do not forget to set the "Percentage" cell format.
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 reference to a cell 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 cells with percentages and copy - press "Insert" - select the chart type - OK.
The second option is to set the format of data signatures as 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 - "Data Label Format".
We choose "Shares". On the "Number" tab - percentage format. It turns out like this:
Download all examples with percentages in Excel
We will stop there. And you can edit to your liking: change the color, the type of the diagram, make underlining, etc.
Percentage concept
Percentage is one hundredth of the whole. Imagine that each number with which you operate, "cut" into one hundred equal parts. Based on this assumption, further operations become simple and understandable.
How to add percentages to a number
Let us need to solve the problem.
The product cost 1600 rubles. How much did the product cost after a 5% price increase?
Stage one... We find how many rubles are one percent. That is, we divide the price by 100 parts. We get the "size" of one part.
1600/100 \u003d 16 rubles in one percent
Stage two... We find how many rubles in five percent.
16 * 5 \u003d 80 rubles
Stage three... We find the price that turned out after the rise in price.
1600 + 80 \u003d 1680 rubles
The 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 (to 100% of which the original price is, we add a 5% increase) and in the denominator - 100%, because the number with which we operate is always 100%.
NUMBER * (100% + ADDITION%) / 100% \u003d Add Percentage to Number
Based on the above, you can also find out how to subtract the required percentage from the number. You just need to put a minus sign in the numerator.
2080.1947
Interest in the modern world is spinning everywhere. Not a day goes by without using them. Buying products - we pay VAT. Having taken 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% \u003d 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:
- Multiplying by 0.5 is equal to dividing by 2.
- Any percentage is expressed as a fraction (25% \u003d 1/4; 50% \u003d 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 items were sold in a day. How much was sold as a percentage?
9 is part, 45 is 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 a percentage, using 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:
- We are given the price of the item. It is necessary to add to it the VAT percentage (VAT is 18%).
- 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%.
- Pull down to get all answers at once.
- In case you use the formula B1 + 18 (without the% sign), then the answers will be obtained with "%" signs, and the results are not what we need.
- But this formula will also work if we change the cell format from "percentage" to "number".
- The number of decimal places can be removed (0) or set 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, add 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 numeric values \u200b\u200b(except for the total) and select the histogram in the "Insert" tab. To create a histogram, we need to select all numeric values \u200b\u200b(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,. We will try to help you.
Microsoft Office Excel spreadsheet editor is often overlooked in vain. It seems to many that it is difficult to understand, so they use a calculator and other improvised means 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 fully automatically. And you can master the Excel database 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 your Excel question.Add interest
Often times, people need to add interest. To avoid doing this manually, just use Excel. And we will tell you how.Let's say that to a certain number, you need to add some kind of fixed percentage. To do this, in cell A1 we enter our amount, from which the percentage will be derived. It will appear in cell A2. But first, we do the following. As we said above, the percentage in this example is fixed. First, we determine the magnitude of the multiplier. You can't just enter 25% (our example). To do this, use the formula 1+ (25/100) \u003d 1.25. The resulting value is our multiplier, which must be written into cell A2. To do this, click on it and enter the following: an equal sign, a 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 you have to use three cells.
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, the formula does not change. She 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 straightforward. MS Excel is a multifunctional editor that is quite easy to learn, but nevertheless has the best basis for working with graphs, tables and formulas.
Excel is used very often because of the simplicity of building tables. Most SEO specialists use it to group key queries for their semantic core.
Calculating and manipulating percentages is best done in Excel in Microsoft Office because all you need to do is enter the values \u200b\u200band the desired formula.
Percentage is one hundredth of a whole number, which on paper is indicated by a% sign or decimal fractions (70% \u003d 0.70). The standard expression for calculating percentages is Integer / Part * 100, but thanks to Excel, you don't have to manually calculate anything.
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 \u200b\u200bof the whole and its part (or select from the previously entered data), and indicate the principle of calculation, and Excel will make the calculations independently. In Excel, the percentage is calculated as follows - Part / Whole \u003d Percentageand multiplication by 100 happens automatically when the user selects a 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 the plan for each product.
Percentage of the number
In Excel, you can calculate a figure knowing only its fraction: % * Part \u003d Whole... Let's say you need to calculate, which is 7% of 70. To do this:
If the calculation is carried out when working with a table, then instead of entering numbers, you must provide links to the necessary cells. You should be careful, when calculating the format should be Common.
Percentage of the amount
If the data is scattered throughout the table, then you must use the formula SUMIF - it adds up the values \u200b\u200bthat correspond to the given parameters, in the example - the given products. The formula for the example will look like this - "\u003d SUMIF (range criterion; range of addition) / total sum":
Thus, each parameter is counted, i.e. product.
Calculating percent change
Comparing two lobes is also possible with Excel. To do this, you can simply find the values \u200b\u200band subtract them (from the larger to the smaller), or you can use the increment / decrement formula. If you need to compare the numbers A and B, then the formula looks like “ (B-A) / A \u003d difference". Let's consider an example of calculation in Excel:
- Stretch formula the entire column using the autocomplete 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 \u200b\u200bindicate an increase, while negative values \u200b\u200bindicate a decrease.
Calculating the value and total
Often, it is necessary to determine the total amount knowing only the share. There are two ways to do this in Excel. Consider buying a laptop assuming it costs $ 950. The seller says that this is the price excluding VAT, which is 11%. You can find out the final markup by making calculations in Excel:
Let us consider the second calculation method using another example. Let's say that when buying a laptop for $ 400, the seller says that the price is calculated with a 30% discount. You can find out the starting price like this:
The initial cost will be $ 571.43.
How to change a value by a percentage
It is often necessary to increase or decrease the total number by some fraction of it, for example, it is necessary to increase monthly costs by 20%, 30% and 35%:
The program will calculate the total on its own along the entire column, if you stretch the expression using the fill marker. The expression for decreasing the amount is the same, only with a minus sign - " \u003d Value * (1-%)».
Interest transactions
With fractions, you can perform the same operations as with ordinary numbers: addition, subtraction, multiplication. For example, in Excel, you can calculate the difference in indicators between the company's sales using the ABS command, which returns the absolute value of a number:
The difference between the indicator will be 23%.
You can also add (or subtract) a percentage to the number - consider the action using the example of planning a vacation:
The program will independently make 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 specify the required value and percentage, and the program will calculate everything itself:
All amounts can be quickly recalculated by stretching the formula to the entire column F. 
To subtract a share, you must enter a number in cell A1, and a percentage in cell A2. Make calculations in cell B1 by entering the formula “ \u003d A1- (A1 * A2)». 
