Profit Margin and Markup Formulas in Excel
By ExcelReady TeamUpdated 5 min read
The short answer
Profit margin is profit divided by the selling price: =(C2-B2)/C2, where B2 is the cost and C2 is the price. Markup is profit divided by the cost: =(C2-B2)/B2. Format both cells as percentages. A product that costs 60 and sells for 80 has a 25% margin but a 33.3% markup, because the same profit is divided by different numbers.
Key takeaways
- Margin = (Price - Cost) / Price. Markup = (Price - Cost) / Cost.
- The same product always has a higher markup than margin: 60 cost and 80 price is 25% margin, 33.3% markup.
- To price for a target margin, divide: =Cost/(1-Margin). For a target markup, multiply: =Cost*(1+Markup).
- Margin can never reach 100%, but markup can go above 100%.
- Guard against blank prices with IF or IFERROR so the sheet doesn't fill with #DIV/0!.
On this page+
The profit margin formula in Excel is =(Price-Cost)/Price: the share of each sale you keep as profit. Markup is the close cousin, =(Price-Cost)/Cost: how much you added on top of the cost. They use the same profit but divide by different numbers, so they give different percentages. This guide shows both on one product list, how to price for a target margin, and how to avoid the usual errors.
Margin vs markup: what each one means#
Margin answers "what percent of the price is profit?". Markup answers "by what percent did I raise the cost?".
| Measure | Formula | Divides profit by | Cost 60, price 80 |
|---|---|---|---|
| Profit | =Price-Cost | nothing | 20 |
| Margin | =(Price-Cost)/Price | the price | 25% |
| Markup | =(Price-Cost)/Cost | the cost | 33.3% |
The cost is always smaller than the price, so markup is always the bigger number for the same product. Mixing them up is the most common pricing mistake. If your manager asks for a "30% margin" and you add 30% to the cost, you only get a 23.1% margin.
Here is the product list used in the examples below, in A1:C5:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Cost | Price |
| 2 | Mug | 4.00 | 10.00 |
| 3 | T-shirt | 9.00 | 25.00 |
| 4 | Notebook | 3.50 | 6.00 |
| 5 | Poster | 12.00 | 15.00 |
How to calculate profit margin in Excel#
Subtract the cost from the price, divide by the price, and format the result as a percentage.
- Click D2 and type
=(C2-B2)/C2. - Press Enter. The result is 0.6.
- Format the cell as a percentage: Home > %, or Ctrl+Shift+% on Windows and ⌃⇧% on Mac. It now shows 60%.
- Fill down to D5 (Windows: Ctrl+D; Mac: ⌘D).
The margins are 60%, 64%, 41.67% and 20%. The brackets are essential. Without them, =C2-B2/C2 divides first and returns 9.6, which is not a margin at all.
If you already have a Profit column in D, the margin is simply =D2/C2. This is the same "part divided by whole" idea as every percentage, which the lesson on percentages and % change practises.
How to calculate markup in Excel#
Use the same profit, but divide by the cost instead of the price.
=(C2-B2)/B2On the same list, the markups are 150%, 177.78%, 71.43% and 25%. Notice that the mug has a 60% margin but a 150% markup. Markup can go above 100%; margin can't, because profit can never be bigger than the whole price.
How to set a price for a target margin or markup#
To hit a target margin, divide the cost by one minus the margin. To hit a target markup, multiply the cost by one plus the markup.
| Goal | Formula | Cost 30 |
|---|---|---|
| 40% margin | =B2/(1-40%) | 50.00 |
| 40% markup | =B2*(1+40%) | 42.00 |
Check the first one: a price of 50 minus a cost of 30 is 20 profit, and 20 / 50 is 40%. The second price, 42, gives only a 28.6% margin.
Prices from a margin formula often come out with long decimals. Wrap them in ROUND, for example =ROUND(B2/(1-40%),2), so the stored price matches the price tag. The ROUND function guide and the article on rounding numbers with ROUND, ROUNDUP and ROUNDDOWN cover the options. Practise them in the rounding lesson.
How to convert between margin and markup#
You don't need the cost and price to convert. Use these two formulas, where the percentage sits in A2:
- Markup to margin:
=A2/(1+A2). A 50% markup is a 33.3% margin. - Margin to markup:
=A2/(1-A2). A 25% margin is a 33.3% markup.
These are handy when a supplier quotes markup and your report needs margin.
Gross margin vs net margin#
The formulas above give gross margin: price minus the direct cost of the product. Net margin divides net profit, after all expenses such as rent, wages and tax, by total revenue. The formula shape is the same:
=NetProfit/RevenueIf a shop has revenue of 50,000 and a net profit of 4,000 for the month, the net margin is =4000/50000, or 8%. Use gross margin to price products and net margin to judge the whole business.
To compare margins across many products, add the margin column, then sort or filter by it. A quick =AVERAGE(D2:D5) gives the simple average margin of the four products, 46.4%. That's not the same as the margin on total sales, =(SUM(C2:C5)-SUM(B2:B5))/SUM(C2:C5), which is 49.1% here, because the T-shirt, with the highest price and margin, carries more weight.
How to stop #DIV/0! and other margin errors#
Margin formulas break in a few predictable ways:
- #DIV/0! appears when the price is blank or zero. Use
=IF(C2=0,"",(C2-B2)/C2)to leave the cell empty until a price is typed. - Missing brackets make Excel divide before it subtracts. Always write
(C2-B2)in brackets. - Negative margins mean the price is below the cost. That's a real result, not an error, so don't hide it.
- Percent typed as a whole number. A target margin of 40 instead of 40% gives
=B2/(1-40), a negative price.
Margins interact with discounts too. A 20% discount on a product with a 25% margin wipes out almost all the profit. To model sale prices, see how to calculate discounts and sale prices, and for raising prices by a percentage, read how to increase a number by a percentage. To split a price into whole units and pence, MOD and INT do the job.
Practise it#
Margin and markup questions show up in finance and retail screening tests because they reveal whether you understand what you are dividing by. Practise percentages and % change in a live spreadsheet: you type the formula and it checks it instantly. Then practise rounding prices with ROUND. For a timed check, the finance Excel test puts these formulas to work.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What is the profit margin on the Notebook? Click D4 and divide the profit (price C4 minus cost B4) by the price, rounded to 4 decimals with ROUND.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Margin | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 |
Tip: Put the subtraction in brackets so it happens first: (C4-B4)/C4, then wrap it in ROUND(...,4).
Practise this in a lesson
Frequently asked questions
What is the formula for profit margin in Excel?
Use =(Price-Cost)/Price, for example =(C2-B2)/C2, then format the cell as a percentage. If the profit is already in its own column, divide it by the price: =D2/C2.
What is the difference between margin and markup?
Both use the same profit, but margin divides it by the selling price and markup divides it by the cost. A product that costs 60 and sells for 80 has a 25% margin and a 33.3% markup.
How do I calculate a selling price from a margin in Excel?
Divide the cost by one minus the margin: =Cost/(1-Margin). A cost of 30 with a 40% target margin gives =30/(1-40%), which is 50.
How do I convert markup to margin?
Use =Markup/(1+Markup). To go the other way, use =Margin/(1-Margin). A 25% margin equals a 33.3% markup.
Why does my margin formula show #DIV/0!?
The price cell is empty or zero, and Excel cannot divide by zero. Wrap the formula: =IF(C2=0,"",(C2-B2)/C2) leaves the cell blank until a price is entered.