Gross margin in Excel
"Excel gross margin" is an informal search phrase for calculating gross margin in Microsoft Excel. Gross margin is the percentage of revenue left after cost of goods sold (COGS): `(Revenue - COGS) / Revenue`.
How to calculate it
Put revenue in one cell and COGS in another. Subtract COGS from revenue, then divide the result by revenue.
Format the result as Percentage using Excel's Home tab. Do not also multiply the formula by 100: Excel's percentage format handles the display conversion.
Use revenue and COGS from the same period. If revenue is zero, gross margin is undefined, not 0%.
$100,000 in revenue produces a 20% gross margin
A business records $100,000 in revenue and $80,000 in COGS for one month. Enter these values and formulas:
| Cell | Entry | Meaning or result |
|---|---|---|
| B2 | 100000 | Revenue |
| B3 | 80000 | Cost of goods sold |
| B4 | =B2-B3 | $20,000 gross profit |
| B5 | =B4/B2 | 20% gross margin after percentage formatting |
The arithmetic is $20,000 / $100,000 = 0.20 = 20%.
You can also calculate gross margin directly with =(B2-B3)/B2.
For a startup, that $20,000 remains to cover operating expenses. It is not net profit: if operating expenses exceed $20,000, the business still loses money.
Questions people ask
Is margin the same as markup?
No. Margin divides gross profit by revenue; markup divides gross profit by cost. In the example, margin is $20,000 / $100,000 = 20%, while markup is $20,000 / $80,000 = 25%.
How do you calculate a 20% profit margin?
For a 20% gross margin, divide unit COGS by 1 - 20%. With $80 in unit COGS, the price is $80 / 0.80 = $100. A 20% markup would produce a $96 price, not a 20% margin. Net profit margin also accounts for operating expenses and other costs.
