Gross margin in Excel

Metrics and finance1min read

"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:

CellEntryMeaning or result
B2100000Revenue
B380000Cost of goods sold
B4=B2-B3$20,000 gross profit
B5=B4/B220% 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.

Related terms