How to Calculate Profit Margin in Excel, and Why Markup Is Not Margin
The margin and markup formulas in Excel, the total margin of a product range, the price for a target margin, and margin by product from an order export.
- Excel
- Formulas
- Reporting Workflows

On this page
- What is the difference between margin and markup?
- How do you calculate profit margin in Excel?
- How do you calculate markup in Excel?
- What is the total margin of several products?
- How do you set a price from a target margin?
- How do you calculate margin by product from an order export?
- Where does Glow Reports fit?
- Common questions
You sell a product for 100 that costs you 60. Is your margin 40% or 67%? Both numbers come up, and they answer two different questions. Margin divides the profit by the selling price. Markup divides the same profit by the cost. In Excel, the margin formula is , with the price in C2 and the cost in D2, formatted as a percentage. Below: margin and markup side by side, the formulas, the total margin of a product range, the price that hits a target margin, and the version for an order export.
What is the difference between margin and markup?
Margin is profit as a share of the selling price. Markup is profit as a share of the cost. A product bought at 60 and sold at 100 earns 40 of profit, which gives a 40% margin and a 67% markup.
Xero's guide uses the same example: a product that costs 60 and sells for 100 has a gross margin of 40% and a markup of about 67% Xero. Neither figure is wrong. The trouble starts when a price list is built with one and a report reads it as the other.
| Figure | Formula | Product sold at 100, bought at 60 |
|---|---|---|
| Gross profit | price - cost | 40 |
| Margin | (price - cost) / price | 40% |
| Markup | (price - cost) / cost | 67% |
Margin can never reach 100%, because the profit can't exceed the price. Markup has no ceiling. A product bought at 10 and sold at 40 carries a 300% markup and a 75% margin.
The examples below use three products, with the name in column A, units sold in B, the unit price in C and the unit cost in D.
| Product | Units | Unit price | Unit cost |
|---|---|---|---|
| Basic | 900 | 4.50 | 2.70 |
| Plus | 200 | 12.00 | 6.00 |
| Pro | 20 | 30.00 | 12.00 |
How do you calculate profit margin in Excel?
Subtract the cost from the price and divide by the price. With the price in C2 and the cost in D2, type and apply the Percentage format. Basic returns 40%, Plus 50% and Pro 60%.
=(C2-D2)/C2The brackets matter. Without them, divides the cost by the price first and subtracts the result from the price, which gives 3.90 for Basic, a number with no meaning.
Apply the format with HomeNumberPercent Style. Microsoft warns about one trap: if a cell already holds 10 and you apply the format, "Excel multiplies that number by 100, which means that you will see 1000.00%" Microsoft Support. Keep the formula as a plain division and let the format do the multiplying. Multiplying by 100 inside the formula and then formatting as a percentage shows 4000%.
When the export gives revenue and cost of goods sold rather than unit prices, the formula keeps the same shape: . Xero writes it as "Gross Profit Margin = (Revenue - COGS) / Revenue x 100", where COGS covers the direct cost of what you sold Xero. Leave rent, salaries and software out of it. Those belong to the operating margin, a different figure.
How do you calculate markup in Excel?
Divide the same profit by the cost. With the price in C2 and the cost in D2, type and apply the Percentage format. Basic returns 66.67%, Plus 100% and Pro 150%.
=(C2-D2)/D2Each figure converts into the other, so you never need to go back to the prices:
- Markup from margin: . A 25% margin is a 33.33% markup.
- Margin from markup: . A 50% markup is a 33.33% margin.
The conversion is where most pricing mistakes come from. A supplier who adds 50% to the cost has not earned a 50% margin. They earned a third of the selling price.
What is the total margin of several products?
Divide total profit by total revenue. An average of the margin rates gives the wrong answer as soon as the products sell in different amounts. In the example, the three rates average 50%, while the range earns a 45.11% margin.
=(SUMPRODUCT(B2:B4,C2:C4)-SUMPRODUCT(B2:B4,D2:D4))/SUMPRODUCT(B2:B4,C2:C4)SUMPRODUCT multiplies units by price on each row and adds the results, 7,050 of revenue. The second SUMPRODUCT does the same with the cost, 3,870. The profit is 3,180, and 3,180 divided by 7,050 is 45.11%. SUMPRODUCT "returns the sum of the products of corresponding ranges or arrays" Microsoft Support.
45.11% is the margin of the whole range. The plain average of the three margins says 50%.
The gap comes from Basic. It brings 4,050 of the 7,050 of revenue at the lowest rate, 40%, and pulls the total down. Pro has the best rate but sold 20 units. The same rule applies to any average of rates, as shown in how to calculate a weighted average in Excel: a total margin is a margin weighted by revenue.
The same trap catches markups. Their plain average is 105.56%, while total profit over total cost gives 82.17%.
How do you set a price from a target margin?
Divide the cost by one minus the target margin. A product that costs 6.00 needs a price of 10.00 to earn a 40% margin, with . Adding 40% to the cost gives 8.40, which is a 40% markup and a 28.57% margin.
=D3/(1-F1)Put the target margin in a cell, here F1, so one formula serves every row. For a target markup, the formula is .
Check the result with the margin formula from the first section. 10.00 minus 6.00 is 4.00, and 4.00 divided by 10.00 is 40%. If the check does not return the target, the price was built on the cost when it should have been built on the price.
How do you calculate margin by product from an order export?
Sum the revenue and the cost of each product with SUMIFS, then divide. An order export holds one row per order line, so the margin of a product is its total profit over its total revenue, never the average of the margins on each line.
With the product in column A, the revenue of the line in E and its cost in F, type the product name in H2 and use this formula:
=(SUMIFS(E:E,A:A,H2)-SUMIFS(F:F,A:A,H2))/SUMIFS(E:E,A:A,H2)SUMIFS takes the range to add first, then each criteria range followed by its criterion Microsoft Support. Add a month column and a second pair of criteria to get the margin of a product for one month. When SUMIFS returns 0 although the rows are there, the dates or numbers are often stored as text, a case covered in SUMIFS with multiple criteria.
A PivotTable gives the same result for every product at once. Put the product in Rows, the sum of revenue and the sum of cost in Values, then add a calculated field . Calculated fields work on the sums, so the result is a true total margin. To track the margin from one month to the next, see how to calculate percentage change in Excel.
The same formulas work in Google Sheets. Apply the format with FormatNumberPercent Google Docs Editors Help.
Where does Glow Reports fit?
The formulas above are enough for one price list. The work grows when margin by product, by month and by channel goes into a monthly report built from a fresh export each time, and someone has to check that every range still covers every row.
Glow Reports starts from that export. You upload the CSV or Excel file and ask, in plain language, for the gross margin by product and by month. The AI writes the SQL, which divides the sum of profit by the sum of revenue for each group, builds the charts and drafts the text. The SQL sits under each figure, so you can confirm that the margin is a ratio of sums and edit it if the wrong cost column was used. Reports export to PDF. The free plan gives 300 credits once, and a report costs 100 credits. For the layout of the rest of the report, see how to make a monthly sales report in Excel.
Common questions
Is a 50% markup the same as a 50% margin?
No. A 50% markup on a cost of 100 gives a price of 150 and a profit of 50, which is a 33.33% margin. A 50% margin on the same cost needs a price of 200.
What is the formula for gross profit margin in Excel?
, formatted as a percentage. With revenue in B2 and the cost of goods sold in C2, type .
Why is my margin over 100%?
Margin can't exceed 100%, so the formula divides by the cost when it should divide by the price. That figure is a markup. Check the cell after the slash.
How do I calculate the average margin of my products?
Divide total profit by total revenue. A plain AVERAGE of the margin column gives each product the same weight, whatever it sold, and overstates the margin when the high-margin products sell little.
I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.
More from El Mehdi BenaneUpload your order export to Glow Reports and get margin by product and month, with the SQL behind each figure.


