How to add percentages to a number. How to add and subtract dates, days, weeks, months and years in Excel. Possible problem when adding interest
The main work in Excel is related to numbers and calculations. Often the user is faced with the task of adding a percentage to a number. Suppose you need to analyze sales growth by a certain percentage, and to do this you will need to add this same percentage to the original value. In this article you will learn how this operation is performed in Excel.
For example, you need to find out the value of a certain number with a percentage added to it. To perform this simple arithmetic operation, you will need to enter the formula below in an empty cell or in the formula bar.
The formula looks like this:
“=(digit) + (digit) * (percentage value) %“.
Let's look at a specific example. We need to add 10% of the same number to the number 250. To do this, write the following expression in the cell/formula bar: “ =250+250*10% “.
After that, click on “Enter” and get the finished result in the selected cell.
Previously, we already figured out how to do calculations manually. Now let's look at how to perform calculations with data already entered into the table.
Adding a percentage to an entire column
There are times when we have an even more complete table, in which, along with the original values in one column, there is also data with percentages in another, and the percentage values can be different from each other.
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, let's remember your school math lessons, where you studied 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:
- Multiplying by 0.5 is equal to dividing by 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. 2. Let's solve the inverse problem. It is known that there are 45 units of goods in the warehouse. It also states that only 20% have been sold. How many total units of the product were sold?
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:
- We are given the price of the product. It is necessary to add VAT percentage to it (VAT is 18%).
- 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%.
- Pull down to get all the 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 will not be what we need.
- But this formula will also work if we change the cell format from “percentage” to “numeric”.
- 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 “-”. The 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.
Afterwards, your diagram will appear in the table. If you are not satisfied with its location, then move it by pulling it outside the diagram. 
bar chart
For this we need data. For example, sales data. To create a histogram, we need to select all numerical values (except the total) and select the histogram in the “Insert” tab. To create a histogram, we need to select all numerical values (except the total) and select the histogram in the “Insert” tab. 
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.
When working in Excel, you often 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 a specific percentage value, the cell address is used, and after it the percentage.
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 many elements in your cells. big numbers, as well as a percent sign.
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 you correct yourself after this and enter the correct formula =A2+A2*18%, then you will get some incredibly large number.
In this lesson, you will see how to quickly calculate percentages using Excel, get acquainted with the basic formula for calculating percentages, and learn a few tricks that will make your work with percentages easier. For example, the formula for calculating percentage growth, calculating the percentage of the total amount and something else.
Knowing how to work with percentages can be useful in a variety of areas of life. This will help you estimate the amount of tips in a restaurant, calculate commissions, calculate the profitability of any enterprise and the degree of your personal interest in this enterprise. Tell me honestly, will you be happy if they give you a promotional code for a 25% discount to buy a new plasma? Sounds tempting, right?! Can you calculate how much you will actually have to pay?
In this tutorial, we'll show you several techniques that will help you easily calculate percentages using Excel, as well as introduce you to the basic formulas that are used to work with percentages. You'll learn some tricks and hone your skills by working out solutions to practical problems using percentages.
Basic knowledge about percentages
Term Percent(per cent) came from Latin (per centum) and was originally translated as OUT OF HUNDREDS. At school you learned that a percentage is some part of 100 shares of the whole. The percentage is calculated by dividing, where the numerator of the fraction is the desired part, and the denominator is the whole, and then the result is multiplied by 100.
The basic formula for calculating interest looks like this:
(Part/Whole)*100=Percentage
Example: You had 20 apples, 5 of which you gave to your friends. What percentage of your apples did you give away? Having made simple calculations, we get the answer:
(5/20)*100 = 25%
This is exactly how you were taught to calculate percentages in school, and you use this formula in Everyday life. Calculating percentages in Microsoft Excel is an even simpler task, since many mathematical operations are performed automatically.
Unfortunately no universal formula to calculate interest for all occasions. If you ask the question: what formula to use for calculating interest to get the desired result, then the most correct answer will be: it all depends on what result you want to get.
I want to show you some interesting formulas for working with data presented as percentages. This, for example, is the formula for calculating the percentage increase, the formula for calculating the percentage of the total amount, and some other formulas that are worth paying attention to.
Basic formula for calculating percentage in Excel
The basic formula for calculating percentage in Excel looks like this:
Part/Whole = Percentage
If you compare this formula from Excel with the usual formula for percentages from a math course, you will notice that it does not multiply by 100. When calculating a percentage in Excel, you do not need to multiply the result of division by 100, since Excel will do this automatically if for the cell given Percentage format.
Now let's see how calculating percentages in Excel can help in real work with data. Let's say that in column B you have recorded a certain number of ordered products (Ordered), and in column C you have entered data on the number of delivered products (Delivered). To calculate what share of orders have already been delivered, we do the following:
- Write down the formula =C2/B2 in cell D2 and copy it down as many lines as necessary using the autofill marker.
- Click command Percent Style(Percent Format) to display division results in percent format. It's on the tab Home(Home) in the command group Number(Number).
- If necessary, adjust the number of decimal places displayed to the right of the decimal point.
- Ready!
If you use any other formula to calculate percentages in Excel, the general sequence of steps will remain the same.
In our example, column D contains values that show, as a percentage, what proportion of total number orders are already delivered orders. All values are rounded to whole numbers.
Calculate percentage of total amount in Excel
In fact, the example given is special case calculating a percentage of the total amount. To better understand this topic, let's look at a few more problems. You'll see how you can quickly calculate a percentage of a total in Excel using different data sets as examples.
Example 1. The total amount is calculated at the bottom of the table in a specific cell
Very often at the end of a large data table there is a cell labeled Total in which the total is calculated. At the same time, we are faced with the task of calculating the share of each part relative to the total amount. In this case, the formula for calculating the percentage will look the same as in the previous example, with one difference - the reference to the cell in the denominator of the fraction will be absolute (with $ signs before the row name and column name).
For example, if you have some values written in column B, and their total is in cell B10, then the formula for calculating percentages will be as follows:
Clue: There are two ways to make the cell reference in the denominator absolute: either by entering a sign $ manually, or select the desired cell reference in the formula bar and press the key F4.
The figure below shows the result of calculating the percentage of the total amount. The Percent format with two decimal places is selected to display the data.
Example 2: Parts of the total amount are on multiple lines
Imagine a data table like the previous example, but here the product data is spread across multiple rows in the table. You need to calculate what portion of the total amount is made up of orders for a specific product.
In this case we use the function SUMIF(SUMIF). This function allows you to summarize only those values that meet a specific criterion, in our case a given product. We use the result obtained to calculate the percentage of the total amount.
SUMIF(range,criteria,sum_range)/total
=SUMMIF(range,criteria,sum_range)/total sum
In our example, column A contains the names of products (Product) - this is range. Column B contains quantity data (Ordered) - this is sum_range. In cell E1 we enter our criterion– the name of the product for which the percentage needs to be calculated. The total amount for all products is calculated in cell B10. The working formula will look like this:
SUMIF(A2:A9,E1,B2:B9)/$B$10
=SUMIF(A2:A9,E1,B2:B9)/$B$10
By the way, the name of the product can be entered directly into the formula:
SUMIF(A2:A9,"cherries",B2:B9)/$B$10
=SUMIF(A2:A9,"cherries";B2:B9)/$B$10
If you need to calculate how much of the total amount comes from several different products, you can add up the results for each of them and then divide by the total amount. For example, this is what the formula would look like if we wanted to calculate the result for cherries And apples:
=(SUMIF(A2:A9,"cherries",B2:B9)+SUMIF(A2:A9,"apples",B2:B9))/$B$10
=(SUMIF(A2:A9,"cherries";B2:B9)+SUMIF(A2:A9,"apples";B2:B9))/$B$10
How to Calculate Percentage Change in Excel
One of the most popular tasks that can be performed using Excel is calculating the percentage change in data.
Excel formula that calculates percentage change (increase/decrease)
(B-A)/A = Percentage change
When using this formula when working with real data, it is very important to correctly determine which value to put in place A, and which one - in place B.
Example: Yesterday you had 80 apples, and today you have 100 apples. This means that today you have 20 more apples than you had yesterday, that is, your result is an increase of 25%. If yesterday there were 100 apples, and today there are 80, then this is a decrease of 20%.
So, our formula in Excel will work as follows:
(New value - Old value) / Old value = Percentage change
Now let's see how this formula works in Excel in practice.
Example 1: Calculate percentage change between two columns
Let's assume that column B contains prices from last month (Last month), and column C contains prices that are current this month (This month). In column D, enter the following formula to calculate the change in price from last month to the current month as a percentage.
This formula calculates the percentage change (increase or decrease) in price this month (column C) compared to the previous month (column B).
After you write the formula in the first cell and copy it to all the necessary rows, by dragging the autofill marker, do not forget to set Percentage format for cells with a formula. As a result, you should get a table similar to the one shown in the figure below. In our example, positive data that shows an increase is displayed in standard black, and negative values (percentage decrease) are highlighted in red. For details on how to set up this formatting, read this article.
Example 2: Calculate percentage change between rows
In the case where your data is located in one column, which reflects information about sales for a week or month, the percentage change can be calculated using the following formula:
Here C2 is the first value and C3 is the next value.
Comment: Please note that with this arrangement of data in the table, you must skip the first row of data and write the formula from the second row. In our example, this will be cell D3.
After you write down the formula and copy it to all the necessary rows in your table, you should end up with something similar to this:
For example, this is what the formula would look like to calculate the percentage change for each month compared to the indicator January(January):
When you copy your formula from one cell to all the others, the absolute reference will remain the same, while the relative reference (C3) will change to C4, C5, C6 and so on.
Calculation of value and total amount based on a known percentage
As you can see, calculating percentages in Excel is easy! It is also easy to calculate the value and total amount using a known percentage.
Example 1: Calculating a value based on a known percentage and total amount
Let's say you buy a new computer for $950, but you need to add VAT of 11% to this price. Question - how much extra do you need to pay? In other words, 11% of the indicated cost is how much in foreign currency?
The following formula will help us:
Total * Percentage = Amount
Total * Percentage = Value
Let's pretend that total amount(Total) is written in cell A2, and Interest(Percent) – in cell B2. In this case, our formula will look quite simple =A2*B2 and will give results $104.50 :
Important to remember: When you manually enter a numeric value into a table cell followed by a % sign, Excel understands this as hundredths of the entered number. That is, if you enter 11% from the keyboard, then the cell will actually store the value 0.11 - this is the value Excel will use when performing calculations.
In other words, the formula =A2*11% is equivalent to the formula =A2*0.11. Those. in formulas you can use either decimal values or values with a percent sign - whichever is more convenient for you.
Example 2: Calculating the total amount using a known percentage and value
Let's say your friend offered to buy his old computer for $400 and said that it was 30% cheaper than its full price. Do you want to know how much this computer originally cost?
Since 30% is a reduction in price, the first thing we do is subtract this value from 100% to calculate what fraction of the original price you need to pay:
Now we need a formula that will calculate the initial price, that is, find the number 70% of which is equal to $400. The formula will look like this:
Amount/Percentage = Total
Value/Percentage = Total Amount
To solve our problem we will get the following form:
A2/B2 or =A2/0.7 or =A2/70%
How to increase/decrease a value by a percentage
With the onset of the holiday season, you notice certain changes in your usual weekly expenses. You may want to make some additional adjustments to the calculation of your spending limits.
To increase the value by a percentage, use this formula:
Value*(1+%)
For example, the formula =A1*(1+20%) takes the value contained in cell A1 and increases it by 20%.
To decrease the value by a percentage, use this formula:
Value*(1-%)
For example, the formula =A1*(1-20%) takes the value contained in cell A1 and decreases it by 20%.
In our example, if A2 is your current expenses, and B2 is the percentage by which you want to increase or decrease their value, then in cell C2 you need to write the following formula:
Increase by percentage: =A2*(1+B2)
Decrease by percentage: =A2*(1-B2)
How to increase/decrease all values in a column by a percentage
Let's assume that you have an entire column filled with data that needs to be increased or decreased by some percentage. In this case, you do not want to create another column with a formula and new data, but change the values in the same column.
We need only 5 steps to solve this problem:
In both formulas, we took 20% as an example, and you can use the percentage value that you need.
As a result, the values in column B will increase by 20%.
In this way, you can multiply, divide, add, or subtract a percentage from an entire column of data. Simply enter the desired percentage in the blank cell and follow the steps above.
These methods will help you in calculating percentages in Excel. And even if percentages have never been your favorite branch of mathematics, mastering these formulas and techniques will force Excel to do all the work for you.
That's all for today, thank you for your attention!
The Microsoft Office Excel spreadsheet editor is often needlessly underrated. Many people think 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 fully automatically. Yes, and you can master the Excel database in a couple of days. If you want to learn all the functionality of this utility, then visit the website https://tutorexcel.ru/. There you can find any answer to a question regarding Excel.Adding interest
Often, people need to add interest. To avoid doing this manually, just 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, enter our amount in cell A1, from which the percentage will be derived. 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). To do this, we 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: equal sign, source cell number (A1), asterisk and multiplier. It looks like this: =A1*1.25. Now all that remains is to confirm the result by pressing the Enter key. The program will give you the result in a matter of seconds.
But it is not always the case that you need to multiply by a fixed percentage. If it changes, then you will 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 obtained. In order to calculate the percentage, enter the following formula into 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 changing the percentage value, we do not change the formula. 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 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 due to the ease of creating tables. Most SEO specialists use it to group key queries for their semantic core.