We calculate percentages in everyday life for all sorts of things, from how much we save on a sale to how much of our income goes to utility bills. Most of the time, we can sort things without a dedicated program, such as Excel.
Excel is an indispensable tool in a business environment, where calculations are often more complex than everyday scenarios. From determining a salary raise to tax deductions, budgeting, pricing, and VAT, small businesses heavily rely on Excel’s formulas for percentage calculations. This guide provides a comprehensive overview of working with percentages in Excel, complete with instructive examples and formulas.
Calculating percentages with dedicated software such as Excel not only makes your task easier but also empowers you to handle complex calculations with ease. Excel's formulas speed up the calculation process and help you avoid mistakes. You can reuse these formulas for various tasks, such as calculating the VAT for your invoices or a 10% salary raise for all your employees, instilling a sense of capability and efficiency.
Excel’s visual aids, such as charts and graphs, play a crucial role in data analysis. They clearly represent percentage changes, making you feel guided and supported in your data analysis. These tools help you visualize an increase or decrease in percentage, the percentage of different categories, and more. Once you input the data and apply the percentage formulas, you can easily create statistics and data analysis to help with time management, project management, budgeting, and so on.
In the following lines, we provide the most common ways of calculating percentages in Excel: percentage change, percentage increase, percentage decrease, percentage of total, plus several other common cases. Let's start with the beginning.
The formula to calculate percentage in Excel is
Let's say you want to calculate the percentage of items delivered from your order for office furniture.
In an Excel sheet, we have made four columns: product, ordered, delivered, and percentage.
In the first row, we've added Chairs, 20 Ordered and 15 Shipped.
To calculate the number of shipped products from the total ordered, we use the 15/20 formula, or =C2/B2 in Excel, where C2 is the Shipped and B2 is the Ordered.
By default, the result will be 0,75, yet one click on the % button or Percent Style button from the Home tab / Number group will change the resulting decimal fractions to percentages.
Therefore, the calculation will result in 75 percent.
What you would normally do to calculate percentages is to implement the following formula in Excel:
Let’s say you have a list of business expenses and want to figure out what percentage of the total business expenses budget was spent by each employee. After you input the total budget and create the list of employees and their corresponding expenses in your Excel file, you calculate the percentages using the formula:
In this example, both factors in the percentage formula are given in a particular cell. However, you can use other formulas instead of given values. For example, the total budget for business expenses may be the sum of how much each employee has spent. Or how much an employee has spent may be a sum of amounts spent for different expense categories (e.g., travel, office supplies, lunch, etc.). Excel allows you to use the percentage formula with other formulas as factors.
Tip: If you want to fix a cell’s address when copying formulas between cells (to make the denominator an absolute reference), use the $ symbol. For example, Excel will change the column and/or the row if it finds C2 in a formula but will not change either column or row for $C$1.
In this example, we want to figure out how much is $100 expenses of employee 1 (cell B4), out of the $1000 total expenses budget (cell C1).
Within Excel, we will use this formula = (B4/C1)*100 to get the percentage amount.
Therefore, the result will be 10%.
Excel provides a more straightforward way to implement the percentage formula, which is more efficient when analyzing data. The simplicity of using the percentage cell format in Excel makes you feel at ease and confident in your data analysis.
If you modify the format of a cell from number to percentage, Excel automatically adds the *100 part to your formulas. In addition, it adds the % symbol inside each cell, making the data more visual and accessible to follow. To change the format of the cell, select the cell, right-click on it, and choose Format Cells from the drop-down menu. Then, select Percentage from the list of cell formats available, insert the number of decimal places you need, and hit OK.
Now, all you have to do to calculate what percentage of the total business expenses budget was spent by each employee is to divide the expense each employee did by the total.
While you could use the complete formula, Excel’s percentage cell format offers a more intuitive approach. This format simplifies the process, making it easier to calculate what percentage of the total business expenses budget each employee spent. Therefore, we will use it throughout all the following examples and formulas.
Tip: To easily format a value as percent, within Excel, on the Home tab, select % Percent style button.
By now, you’ve learned how to calculate what percentage of a total a part represents and compute differences in percentages. It’s time to use Excel formulas to calculate a percentage of a number.
For example,
A supplier tells you they intend to increase their prices by 15%. What does 15% of the total price mean?
By reversing the formula for percentages:
We get:
Thus, you can calculate how much 15% means by multiplying old prices by 15. Remember, Excel will divide the result by 100.
In the image, the original price for product 1 was $1000 (cell B2), yet it will increase by 15% (cell C2).
To get the answer of how much 15% from $1000 is, we use the formula =B2*C2 in cell D2, and we get $150.
Now, to get the new price, we can use the simple method of calculating by adding the original number (A2) and the increase in price (D2): cell E2 = A2 + D2, and we get $1150.
An alternative way to arrive at the same result is to use the price increase formula: Original price * (1 + percentage increase). In this way, the price becomes =B2*(1+C2).
Related: Excel formulas.
Another common use of this formula is calculating VAT.
Suppose you sell a product and must invoice the client with the price plus VAT. The client must be informed about the tax, and the VAT amount must appear separately on the invoice. In Excel, list the items you sell in one column and their prices in another column. Use the formula above to calculate 19% of the price in the third column. Then, add another column for the total price and the VAT to the initial value.
For this example, we want to calculate the VAT amount in cell C4.
We know that the VAT value is 19%, as mentioned in the first row of the table, Cell B1, and the price is $123, as mentioned in Cell B4.
We get the result by multiplying the price by the VAT value, divided by 100 by Excel.
In this sense, we use the formula =B4 * B1 to get the result that is $23.27. If you need to use a different VAT percentage, change the value in B1.
To find total by amount and percentage, we use the following formula:
Let's say the cost of timber for construction is $15,000, which is 25% off the original price. What is the original price?
Since 25% is the discount, you deduct it from 100% first to know what percentage you actually have to pay. This means original price = Price paid/(1-0,25) in Excel.
If 100% is the full price, the discounted price is 100-25 = 75 percent of the original price. Or we use the decimal value as in the formula 1 - 0.25 = 0.75
Let's convert the percent to decimal 75/100 = 0.75.
Original price = $15,000/0.75 = $20,000.
In this example, the cost is in cell B2, and the discount is in C2. To calculate the discount price, we have used the price paid/(1-percentage decrease) or = B2/(1-0.25) in cell D2 in Excel.
The spreadsheet software will show us the result, like magic $20,000, which is the original price.
Percentages are often used to represent how numbers change over time. Especially when you aggregate small and large values in the same table, it’s more convenient to describe the change in percentages (and more accessible to visualize in graphs). This is why the percentage change formula is the following:
Let’s say you want to calculate the percentage of change between two numbers. In this case, your business costs from one month to another.
The costs associated with employees’ salaries were $20,000 in the first month and have increased by $5,000 in the next month due to hiring a new employee.
The costs associated with production were $500,000 in the first month and have increased by an equal amount, $5,000.
Both cost categories have increased by the same amount. However, the percentage change underlines that salary costs have increased by 25%, while production costs have increased only by 1%.
To compute the percentage change, format the result cell as a percentage cell and use the formula:
In this example, we have the Salaries in Month 1 - $20,000 in cell B2 and Month 2- $25,000 in cell C2.
We've used the formula =(C2-B2)/B2 to calculate the percentage change in Excel.
In this way, we get the answer in cell E2 as 25%.
You consider a percentual change when you think about increasing or decreasing a business-related value (e.g., prices, salaries, budgets, taxes, etc.). For example, VAT represents 19% of a product’s price. You raise employees’ wages by 5% each year's end. Or you can lower the budget for office supplies by 20% to reduce costs.
To increase a number by a percentage in Excel (also called percentage rise), use the following formula, where PERCENTAGE is the value of a cell formatted as a percentage:
For example, let’s say you want to give your employees a Christmas bonus equal to 10% of their current salary. Their salary in December will then increase by 10%.
For example, let’s say you want to give your employees a Christmas bonus equal to 10% of their current salary. Their salary in December will then increase by 10%.
As you can see in the image, the Regular salary is $1200 in cell B4, and the Christmas bonus is 10% in B1.
In Cell C4, we add the formula = B4+(B4*C1) and get the 10% raised salary as $1.320.
Tip: We got the same answer using the alternative formula = (1+B1)*B4.
If you want to give Christmas bonuses differentiated, you can add a column for the Christmas bonus percentage and use it as a reference to compute the salary for December.
To decrease a number by a percentage in Excel, use the following formula, where PERCENTAGE is the value of a cell formatted as a percentage:
For example, let’s say you want to reduce all prices by 30% as an autumn sale. You have the price list in Excel. Enter the sale discount in a cell and use the formula above to calculate prices reduced by 30%.
This image shows several examples of prices that will go on sale. The jacket cost was initially $120 (cell B4), and the sale is 30% (Cell B1).
To get the new 30% discounted price, we use this formula in Cell C4 = B4-B4*B1.
In this way, we get the reduced price of the Jacket within cell C4 at $84.
Tip: If you want to fix a cell’s address when copying formulas between cells (to make the denominator an absolute reference), use the $ symbol. For example, Excel will change the column and/or the row if it finds F1 in a formula but will not change either column or row for $F$1.
To calculate the profit margin as a percentage with a formula, subtract the cost from the revenue and divide the result by the revenue.
This one is simple. We have a Revenue of $50 in cell B6 and a Price of $40 in cell C6.
In cell D6, we get the net profit percentage by using the formula = (B6-C6)/B6, and we get the result of 20%.
Download our Excel percentage worksheet, where you can review each of the examples mentioned above.
FAQ
What is a percentage?
A percentage (%) is a fraction of 100, the 1/100th part of it. It represents the relationship (ratio) between numbers or between parts and their total. The term comes from the Latin per centum, meaning "by the hundred".
What is the percentage formula?
The percentage formula is PERCENTAGE (%)= PART/TOTAL*100
However, when using Excel’s dedicated percentage cells, you don’t have to include the multiplication by 100 in the formula as Excel does it by default. Therefore, the percentage formula for percentage cells is PERCENTAGE (%)= PART/TOTAL
How do you calculate a percentage?
You can calculate a percentage using the Rule of Three. If the total represents 100 parts (percentages), then one part (percentage) equals the total divided by 100.
For example, if 1000g of apples represents 100%, then 10g represents 1% of the apple quantity. If you have 400g of apples and want to know how many percentages they represent, you divide 400 by 1000 and multiply the result by 100. 400g of apples represent 40% of the apple quality.
We expand on this topic with 10 most common ways to calculate percentages.
How to calculate percent of a number?
To calculate a particular percent of a numerical value, we use this formula P% * X = Y.
For example 20% of 50, is 20/100 * 50 = 0.2 * 50 = 10.