Excel formula to subtract percentage from amount. We calculate the percentage of a number in Excel. How to calculate percentage difference in Excel
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 securities, and discounts. All these calculations are made as a percentage of the amount.
Click on the button below to download an 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.
In the process of solving various kinds of problems, both educational and practical, users often turn to Excel.
Spreadsheets allow you to analyze data, create charts and graphs, and perform a variety of calculations. One of the common operations is calculating interest. The ability to correctly make the necessary calculations is a useful skill that can be successfully used in almost all areas of life. What techniques can help you calculate percentages using Excel tables?
How to calculate percentages in Excel - basic calculation formula
Before you begin calculating percentages, you need to define the terminology. The term "percentage" means the number of shares out of all 100 parts of a whole. The mathematical definition of percentage is a fraction, the numerator of which determines the required number of parts, and the denominator determines the total. The result is multiplied by 100 (since an integer is 100%). Working with a spreadsheet, the formula for determining percentage is as follows:
Part/whole = Percentage
The only difference from the usual mathematical interpretation is the absence of further multiplication by 100. The properties of the table fields will help you obtain the required value format - just activate the Percentage cell format.
Example 1
Here is a series of data entered, for example, in column D (D2, D3, D4, D5, ...). It is necessary to calculate 5% of each value.
- Activate the cell adjacent to the first value (or any other) - the result of the calculations will be located in it.
- In cell E2 write the expression “=D2/100*5” or “=D2*5%”.
- Press Enter.
- “Extend” cell E2 to the required number of rows. Thanks to the autofill marker, the above formula will also calculate the remaining values.
Example 2
In front of you are 2 columns of values - for example, sold cakes (D2, D3, D4, D5, ...) and the total number of baked goods (E2, E3, E4, E5, ...) of each type. It is necessary to determine what part of the product has been sold.
- In the cell where the result will be calculated (for example, F) write the expression “=D2/E2”.
- Press Enter and “stretch” the cell by the required number of lines. Using the autofill marker will allow you to apply this formula to all subsequent cells and make the correct calculations.
- To convert the result to percent format, select the required cells and use the Percent Style command. To activate the latter, you can right-click and select “Cell Format” – “Percentage” from the list that appears. In this case, you specify the desired number of decimal places. Or go to the “Home” – “Number” section and select the “Percentage” view.
How to calculate percentages in Excel - percentage of the amount
To calculate the share of each part relative to the total amount, use the expression “=A2/$A$10”, where A2 is the value of interest, the total amount is indicated in cell A10. What if the position you are interested in appears in the table several times? In this case, use the SUMIF function with the parameters:
SUMIF(range,criteria,sum_range)/total
SUMIF(range,criteria,sum_range)/total sum
- Move to the cell where the result will be obtained.
- Write down the expression “=SUMIF(C2:C10;F1;D2:D10)/$D$14” (or =SUMIF (C2:C10;F1;D2:D10)/$D$14), where
C2:C10, D2:D10 – ranges of values within which calculations occur,
F1 – cell in which the characteristic under study is indicated,
D14 is the cell in which the amount is calculated. 
How to calculate percentages in Excel - percentage change
The need for such calculations often arises in the course of assessing growth or loss based on operating results. So, sales volumes by product category for 2015. entered in column D, similar data for 2016. - in column E. It is necessary to determine by what percentage the sales volume increased or decreased.
- In cell F2, enter the formula “=(E2-D2)/D2”.
- Convert cell data to Percentage format.
- To calculate the gain or loss for the remaining categories (cells), drag F2 for the required number of rows.
- Evaluate the result. If the value is positive, you have an increase, if negative, you have a decrease.
Interest rates are everywhere in the modern world. 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, write to us. We will try to help you.
Hi all! Do you know how to calculate percentages in Excel? In fact, percentages accompany us very often in life. After today's lesson, you will be able to calculate the profitability of an idea that suddenly arises, find out how much you actually get by participating in store promotions. Master some points and be on top of your game.
I'll show you how to use the basic calculation formula, calculate percentage growth, and other tricks.
This definition as a percentage is familiar to everyone from school. 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 gave to your friends. Determine in percentage what part did you give? Thanks to simple calculations we get the result: 
This is how percentages are calculated both at school and in everyday 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 also depend on the desired result.
How to calculate percentages in Excel: basic calculation formula
There is a basic formula that looks like: 
Unlike school calculations, this formula does not need to multiply by 100. Excel takes care of this step, provided that the cells are assigned a certain percentage format.
Let's look at a specific example of the situation. There are products that are being ordered, and there are products that have been delivered. Order in column B, named Ordered. Delivered products are located in column C named Delivered. It is necessary to determine the percentage of fruit delivered:
- In cell D2, write the formula =C2/B2. Decide how many rows you need and, using autocomplete, copy it.
- In the “Home” tab, find the “Number” command, select “Percent Style”.
- Look at the decimal places. Adjust their quantity if necessary.
- All! Let's look at the result.
The last column D now contains values that show the percentage of orders delivered. 
How to calculate percentages of the total amount in Excel
Now we will look at some more examples of how how to calculate percentages in excel from 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 amount is located. We have to make the calculation of each part towards the total value. The formula will look like the previously discussed example, but the denominator of the fraction will contain an absolute reference. We will have the $ sign 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 reference in cell B2 will allow it to be copied and pasted into cells in the Ordered column. 
2.Parts of the amount are located on different lines
Suppose we need to collect data that is located in different rows and find out what part is taken by orders for a certain product. Adding specific values is possible using the SUMIF function. The result we get will be necessary for us to calculate the percentage of the amount.
Column A is our range, and the summing range is in column B. We enter the product name in cell E1. This is our main criterion. Cell B10 contains the total for products. The formula takes on the following form: 
In the formula itself you can place the name of the product: 
If you need to calculate, for example, how much cherries and apples occupy as a percentage, then the amount for each fruit will be divided by the total. The formula looks like this:
Calculation of percentage changes
Calculating data that changes can be expressed as a percentage and is the most common task in Excel. The formula to calculate the percentage change is as follows: 
In the process of work, you need to accurately determine which of the meanings occupies which letter. If you have more of any product today, it will be an increase, and if less, then it will be a decrease. The following scheme works:
Now we need to figure out how we can apply it in real calculations.
1.Count changes in two columns
Let's say we have 2 columns B and C. In the first we display the prices of the month of the past, and in the second - of this month. To calculate the resulting changes, we enter a formula in column D. 
The calculation results using this formula will show us whether there is an increase or decrease in price. Fill in all the lines you need with the formula using autocomplete. For formula cells, be sure to activate the percentage format. If you did everything correctly, you get a table like this, where the increase is highlighted in black and the decrease in red. 
If you are interested in changes over a certain period and the data is in one column, then we use this formula: 
We write down the formula, fill in all the lines that we need with it and get the following table: 
If you want to count changes for individual cells, and compare them all with one, use the already familiar absolute reference, using the $ sign. We take January as the main month and calculate the changes for all months as a percentage: 
Copying a formula across other cells will not modify it, but a relative link will change the numbering.
Calculation of value and total amount by known percentage
I have clearly demonstrated to you that there is nothing complicated in calculating percentages through Excel, as well as in calculating the amount and values when the percentage is already known.
1.Calculate the value using a known percentage
For example, you buy a new phone that costs $950. You are aware of the VAT surcharge of 11%. It is necessary to determine the additional payment 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 either decimal values or using percentages.
2.Calculation of the total amount
Consider the following example, where the known original amount is $400, and the seller tells you that the price is now 30% less than last year. How to find out the original cost?
The price decreased by 30%, which means we need to subtract this figure from 100% to determine the required share: 
The formula that will determine the initial cost: 
Given our problem, we get: 
Converting a value to a percentage
This method of calculation is useful for those who especially carefully monitor 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 a 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 expenses in one direction or another. Cell C2 should be filled with the formula: 
Increase by a percentage the values in a column
If you want to make changes to an entire data column without creating new columns and using an existing one, you need to do 5 steps:
Now we see values that have increased by 20%. 
Using this method, you can perform various operations on a certain percentage, entering it into a free cell.
Today was an extensive lesson. I hope you've made it clear how to calculate percentages in Excel. And, despite the fact that such calculations are not very popular for many, you will do them with ease.
Subtracting percentages from a number during mathematical calculations is not that uncommon. For example, in trade establishments a percentage of VAT is deducted from the total amount in order to set the price of the product without VAT. This is also done by various regulatory bodies. Let's find out how to subtract a percentage from a number in Microsoft Excel.
First of all, let's understand how interest is subtracted from the total number. To subtract a percentage from a number, you need to immediately determine how much a certain percentage of a given number will be in quantitative terms. To do this, multiply the original number by the percentage. Then, the resulting result is subtracted from the original number.
In the form of a formula in Excel, it will look like this: “=(number)-(number)*(percentage_value)%”.
Let's demonstrate subtracting a percentage using a specific example. Let's say we need to subtract 12% from the number 48. Click on any cell of the sheet, or make an entry in the formula bar: “=48-48*12%”.
To perform the calculation and see the result, click on the ENTER button on the keyboard.
Subtracting percentages from a table
Now let's figure out how to subtract a percentage from the data that is already entered into the table.
If we want to subtract a certain percentage from all cells of a particular column, then, first of all, we stand on the topmost empty cell of the table. We put a “=” sign in it. Next, click on the cell from which you want to subtract the percentage. After this, we put the “-” sign and again click on the same cell that we clicked on before. We put the “*” sign and from the keyboard we enter the percentage value that should be subtracted. At the end we put a “%” sign.
We click on the ENTER button, after which calculations are performed, and the result is displayed in the cell in which we wrote the formula.
In order for the formula to be copied to the remaining cells of this column, and, accordingly, the percentage to be subtracted from other rows, we stand in the lower right corner of the cell in which there is an already calculated formula. Press the left button on the mouse and drag it down to the end of the table. Thus, we will see in each cell numbers that represent the original amount minus the set percentage.
So, we looked at two main cases of subtracting percentages from a number in Microsoft Excel: as a simple calculation, and as an operation in a table. As you can see, the procedure for subtracting interest is not too complicated, and using it in tables helps to significantly simplify the work in them.