How to add percentage to a number. All methods for calculating percentage in Microsoft Excel - formulas and examples of use
IN various types activity requires the ability to calculate percentages. Understand how they are “produced.” Trading premiums, VAT, discounts, returns on deposits, securities and even tips - all this is calculated as some part of the whole.
Let's figure out how to work with percentages in Excel. A program that performs calculations automatically and allows variations of the same formula.
Working with percentages in Excel
Calculating the percentage of a number, adding and subtracting percentages on a modern calculator is not difficult. The main condition is that there must be a corresponding icon (%) on the keyboard. And then it’s 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 symbols on the calculator. The result is 26.25. You don't need a lot of intelligence with this technique.
To create formulas in Excel, let’s remember the school basics:
A percentage is a hundredth part of a whole.
To find a percentage of a whole number, you need to divide the desired fraction by the whole number and multiply the total by 100.
Example. 30 units of goods were delivered. On the first day we sold 5 units. What percent of the goods were sold?
5 is part. 30 is a whole. 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 they made up the proportion:
X = (25 * 5) /100 = 1.25
After this you can perform addition.
Once basic computational skills are restored, formulas will be easy to understand.
How to calculate percentage of a number in Excel
There are several ways.
Let's adapt the mathematical formula to the program: (part / whole) * 100.
Look closely at the formula bar and the result. The result turned out to be correct. But we not multiplied by 100. Why?
IN Excel program The cell format changes. For C1 we assigned the “Percentage” format. It involves multiplying the value by 100 and displaying it on the screen with a % sign. If necessary, you can set a certain number of decimal places.
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 acquired knowledge 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 by the entire column. To do this, click on the lower right corner of the cell with the mouse and drag it down.
The VAT amount and rate are known. Let's find the cost of the goods.
Calculation formula: =(B1*100)/18. Result:
The quantity of goods sold, individually and in total, is known. It is necessary to find the share of sales for each unit relative to the total quantity.
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 before the row name and column name: $B$7.
How to add a percentage to a number
The problem is solved in two steps:
And here we have done the actual addition. Let's omit the intermediate action. Initial data:
VAT rate is 18%. We need to find the VAT amount 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, you should follow the same procedure. Only instead of addition we perform subtraction.
How to calculate percentage difference in Excel?
The percentage change between the two values.
First, let's abstract from Excel. 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 purchase price per unit of goods increased by 50%.
Let's calculate the difference in percentage between the data in two columns:
Don’t forget to set the “Percentage” cell format.
Let's calculate the percentage change between the lines:
The formula is: (next value – previous value) / previous value.
With this arrangement of data, we skip the first line!
If you need to compare data for all months with January, for example, use an absolute reference to the cell with the desired value($ sign).
How to make a chart with percentages
The first option: make a column in the table with data. Then use this data to build a chart. Select the cells with percentages and copy them - click “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 weekends.
Let's make a pie chart. Select the data in two columns - copy - "Paste" - chart - type - OK. Then we insert the data. Right-click on them - “Data Signature Format”.
Select "Shares". On the “Number” tab - percentage format. It turns out like this:
We'll leave it at that. And you can edit to your taste: change the color, type of diagram, make underlines, etc.
A percentage is one hundredth of a number taken as a whole. Percentages are used to indicate the relationship of a part to the whole, as well as to compare quantities.
1% = 1 100 = 0,01
The interest calculator allows you to perform the following operations:
Find the percentage of a number
To find the percentage p from a number, you need to multiply this number by a fraction p 100
Let's find 12% of the number 300:
300 12
100
= 300 · 0.12 = 36
12% of 300 is 36.
For example, a product costs 500 rubles and there is a 7% discount on it. Let's find the absolute value of the discount:
500 7
100
= 500 · 0.07 = 35
Thus, the discount is 35 rubles.
What percentage is one number of another?
To calculate the percentage of numbers, you need to divide one number by another and multiply by 100%.
Let's calculate what percentage the number 12 is from the number 30:
12
30
· 100 = 0.4 · 100 = 40%
The number 12 is 40% of the number 30.
For example, a book contains 340 pages. Vasya read 200 pages. Let's calculate what percentage of the entire book Vasya read.
200
340
· 100% = 0.59 · 100 = 59%
Thus, Vasya read 59% of the entire book.
Add percentage to number
To add to a number p percent, you need to multiply this number by (1 + p 100)
Add 30% to the number 200:
200 (1 + 30
100
) = 200 1.3 = 260
200 + 30% equals 260.
For example, a swimming pool subscription costs 1000 rubles. Starting next month they promised to raise the price by 20%. Let's calculate how much a subscription will cost.
1000 (1 + 20
100
) = 1000 1.2 = 1200
Thus, the subscription will cost 1200 rubles.
Subtract the percentage from the number
To subtract from a number p percent, you need to multiply this number by (1 - p 100)
Subtract 30% from the number 200:
200 · (1 - 30
100
) = 200 · 0.7 = 140
200 - 30% equals 140.
For example, a bicycle costs 30,000 rubles. The store gave it a 5% discount. Let's calculate how much the bike will cost taking into account the discount.
30000 · (1 - 5
100
) = 30000 0.95 = 28500
Thus, the bike will cost 28,500 rubles.
What percentage is one number greater than another?
To calculate how many percent one number is greater than another, you need to divide the first number by the second, multiply the result by 100 and subtract 100.
Let's calculate what percentage is the number 20 more number 5:
20
5
· 100 - 100 = 4 · 100 - 100 = 400 - 100 = 300%
The number 20 is 300% greater than the number 5.
For example, the boss’s salary is 50,000 rubles, and the employee’s salary is 30,000 rubles. Let's find out how many percent the boss's salary is greater:
50000
35000
· 100 - 100 = 1.43 * 100 - 100 = 143 - 100 = 43%
Thus, the boss's salary is 43% higher than the employee's salary.
What percentage is one number less than another?
To calculate how many percent one number is less than another, you need to subtract from 100 the ratio of the first number to the second, multiplied by 100.
Let's calculate what percentage is the number 5 less number 20:
100 - 5
20
· 100 = 100 - 0.25 · 100 = 100 - 25 = 75%
The number 5 is 75% less than the number 20.
For example, freelancer Oleg completed orders worth 40,000 rubles in January, and 30,000 rubles in February. Let's find how many percent less Oleg earned in February than in January:
100 - 30000
40000
· 100 = 100 - 0.75 * 100 = 100 - 75 = 25%
Thus, in February Oleg earned 25% less than in January.
Find 100 percent
If the number x This p percent, then you can find 100 percent by multiplying the number x on 100p
Let's find 100% if 25% is 7:
7 · 100
25
= 7 4 = 28
If 25% equals 7, then 100% equals 28.
For example, Katya copies photos from her camera to her computer. In 5 minutes, 20% of the photos were copied. Let's find how long the copying process takes:
5 · 100
20
= 5 5 = 25
We find that the process of copying all photos takes 30 minutes.
It is most convenient to calculate percentages and perform actions with them in Excel in the Microsoft Office package, since all that is required is to specify 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 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 the necessary 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:
- 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 absolute value numbers:
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 specify 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)». 
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 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 relative to yesterday has changed by 25% in big side. 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.
Interest concept
A percentage is one hundredth of the whole. Imagine that each number you operate with was “cut” into one hundred equal parts. Based on this assumption, further operations become simple and clear.
How to add percentages to a number
Suppose we need to solve a problem.
The product cost 1600 rubles. How much did the product cost after the price increased by 5%?
Stage one. Let's find out how many rubles there are for one percent. That is, we divide the price into 100 parts. We get the “size” of one part.
1600 / 100 = 16 rubles in one percent
Stage two. Find out how many rubles are in five percent.
16 * 5 = 80 rubles
Stage three. We find the price that turned out after the price increase.
1600 + 80 = 1680 rubles
Formula for adding percentage 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 is 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, the numerator of which is the percentage change that we need to get (we add a 5% increase to the 100% that is the original price) and the denominator is 100%, because the number with which we operate is always 100%.
NUMBER * (100% + INCREASE%) / 100% = AddPercentageToNumber
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