Profit & margins
How to calculate profit margin in Excel
To calculate profit margin in Excel, subtract cost from price and divide by price: with the price in cell B2 and the cost in C2, the formula is =(B2-C2)/B2. Format the cell as a percentage to see the margin, such as 60%. The same formula works in Google Sheets.
The profit margin formula in Excel
Set up one row per product, with the price in column B and the cost in column C.
Gross profit: =B2-C2 Gross margin: =(B2-C2)/B2
- B2
- selling price
- C2
- cost of the product (COGS per unit)
This is gross margin: the share of the price left after the cost of the product itself. It's the version most people mean by "product margin". The guide to gross profit margin covers what belongs in the cost.
Worked example: four products in a sheetExample numbers
| A: Product | B: Price | C: Cost | D: =B-C | E: =(B-C)/B | |
|---|---|---|---|---|---|
| 2 | Organic cotton tee | $30.00 | $12.00 | $18.00 | 60.0% |
| 3 | Fleece hoodie | $60.00 | $27.00 | $33.00 | 55.0% |
| 4 | Ceramic mug | $18.00 | $7.20 | $10.80 | 60.0% |
| 5 | Canvas cap | $25.00 | $9.00 | $16.00 | 64.0% |
Type the formula once in row 2, then drag the fill handle down to copy it to the other rows. Excel updates the row numbers automatically.
Formatting the result as a percentage
The formula returns a decimal: 0.6 for 60%. To show it as a percentage:
- Excel: select the column, then Home → Number → % (or press Ctrl+Shift+% on Windows).
- Google Sheets: select the column, then Format → Number → Percent.
Use the increase or decrease decimal buttons to show 60.0% instead of 60%.
Margin vs markup in Excel
Margin divides profit by price. Markup divides profit by cost. The same product has a very different number for each.
Margin: =(B2-C2)/B2 Markup: =(B2-C2)/C2
For the tee above, margin is ($30 − $12) ÷ $30 = 60%, while markup is ($30 − $12) ÷ $12 = 150%.
To set a price from a target margin, divide cost by (1 − margin):
=C2/(1-G2)
- G2
- target margin as a percentage, e.g. 60%
A $27 hoodie cost at a 60% target margin needs a price of $27 ÷ (1 − 0.6) = $67.50. The guide to selling price formulas goes deeper on pricing from margin.
Handling divide-by-zero with IFERROR
If a price or cost cell is blank or zero, the formula shows #DIV/0!. Wrap it in IFERROR to show something cleaner:
=IFERROR((B2-C2)/B2, 0) =IFERROR((B2-C2)/C2, "n/a")
The second form is useful for markup on products with no unit cost, such as digital downloads, where markup isn't meaningful.
Calculating margin for your whole store
Averaging the margin column gives the wrong answer for your store, because each product sells different amounts. Weight it by revenue instead: total gross profit ÷ total revenue.
Worked example: why a simple average misleadsExample numbers
| Product | Revenue | COGS | Margin |
|---|---|---|---|
| Product A | $10,000 | $4,000 | 60% |
| Product B | $2,000 | $1,400 | 30% |
| Store | $12,000 | $5,400 | 55% |
The simple average of 60% and 30% is 45%. The true store margin is ($12,000 − $5,400) ÷ $12,000 = 55%, because most revenue comes from the higher-margin product.
If your sheet has price, cost and units sold per product (say in columns B, C and H), you can get the store margin in one formula:
=(SUMPRODUCT(B2:B5,H2:H5)-SUMPRODUCT(C2:C5,H2:H5))/SUMPRODUCT(B2:B5,H2:H5)
SUMPRODUCT multiplies each price (or cost) by its units sold and adds the results.
Common Excel margin mistakes
- Dividing by cost when you mean margin. =(B2-C2)/C2 is markup, not margin, and gives a much bigger number.
- Averaging percentages. Use totals, as above, for anything bigger than a single product.
- Typing 60 instead of 60%. In a target margin cell, 60 means 6,000%. Format the cell as a percentage first.
- Using supplier price instead of landed cost. Freight, duties and packaging belong in the cost column.
- Typing numbers into formulas. =(30-12)/30 works once; =(B2-C2)/B2 works for every row and updates when prices change.
Download the free profit margin spreadsheet
The sample sheet has the examples above already set up: gross profit, gross margin, markup, and price for a target margin, all as live formulas. Replace the example products and numbers with your own.
Profit margin calculator spreadsheet
Free template with example numbers. Replace them with your own.
Going beyond product margin
Product-level gross margin is the starting point, not the whole story. Shipping, payment fees, discounts and ad spend all come out before you see profit. To see what's left after those, try the Shopify profit margin calculator, or the contribution margin calculator for a per-product view.
If you're wondering what margin to aim for, see what a good profit margin is for an online store.
FAQ
What is the formula for profit margin in Excel?
With the price in B2 and the cost in C2, use =(B2-C2)/B2 and format the cell as a percentage. That gives gross margin: the share of the price left after the cost of the product.
How do I calculate a 30% margin in Excel?
To find the price that gives a 30% margin on a cost in C2, use =C2/(1-0.3). A $14 cost needs a $20 price for a 30% margin. Multiplying cost by 1.3 gives a 30% markup instead, which is only about a 23% margin.
Why does my margin formula show #DIV/0!?
The cell you're dividing by is empty or zero, usually a blank price or cost. Wrap the formula in IFERROR, for example =IFERROR((B2-C2)/B2,0), to show 0 or a blank instead of the error.
Does this work in Google Sheets?
Yes. Every formula in this guide works the same way in Google Sheets, including IFERROR and percent formatting.
How do I calculate net profit margin in Excel?
Use the same pattern with net profit instead of gross profit: subtract every cost, including operating expenses, interest and taxes, from revenue, then divide by revenue. For example =(B2-SUM(C2:F2))/B2 if the costs are in C2 to F2.