Gross Margin Formula: How to Calculate Gross Margin in Excel
Summary
Gross margin = (Revenue − Cost of goods sold) ÷ Revenue. It tells you what share of each sales dollar is left after the direct cost of making or buying what you sold. A business with $343,600 of revenue and $190,300 of COGS has a 44.6% gross margin.
On this page
Gross margin formula
Gross margin % = (Revenue − Cost of goods sold) ÷ Revenue × 100- Revenue: Net sales for the period: gross sales minus returns, refunds, and discounts.
- Cost of goods sold (COGS): Direct costs of the units sold in the period: materials or wholesale cost, inbound freight, direct labor and packaging. Excludes rent, marketing and admin salaries.
- Gross profit: Revenue minus COGS, in currency. Gross margin is gross profit expressed as a share of revenue.
How to calculate gross margin in Excel and Google Sheets
Excel, using the cells in the worked example below:
=(SUM(B2:B4)-SUM(C2:C4))/SUM(B2:B4)Worked example
A specialty coffee roaster sells three product lines and wants its Q3 gross margin, overall and by line, before deciding whether to keep discounting brewing equipment.
| Product line | Revenue | COGS |
|---|---|---|
| Whole-bean coffee | $182,400 | $101,300 |
| Brewing equipment | $64,900 | $47,800 |
| Subscriptions | $96,300 | $41,200 |
- Total revenue = 182,400 + 64,900 + 96,300 = $343,600.
- Total COGS = 101,300 + 47,800 + 41,200 = $190,300.
- Gross profit = 343,600 − 190,300 = $153,300.
- Gross margin = 153,300 ÷ 343,600 = 0.446, or 44.6%.
- By line, put =(B2-C2)/B2 in D2 and fill down: whole bean 44.5%, equipment 26.3%, subscriptions 57.2%. Equipment drags the blend down even though it is 19% of sales.
Result: 44.6% gross margin ($153,300 gross profit on $343,600 revenue)
How to interpret gross margin
Gross margin is the ceiling on everything else: rent, salaries, marketing and profit all have to come out of it. Typical levels differ enormously by business model. Software and digital products often run well above 70%, while grocery, distribution and other high-volume resellers often sit in the 20–35% range. Compare a line's margin with its own history and with direct competitors' filings rather than a cross-industry average, and watch the mix: total margin can fall even when every line holds steady, simply because the low-margin line grew faster.
Common mistakes
- Confusing margin with markup. A product bought for $60 and sold for $100 has a 40% margin but a 66.7% markup; quoting one as the other misprices everything.
- Leaving discounts and returns in revenue. Use net sales, or a promotion that lifts gross sales can quietly lower real margin.
- Putting overhead into COGS (or leaving freight-in out). Be consistent month to month, otherwise trend lines reflect bookkeeping changes rather than economics.
- Averaging line-level percentages. The blended margin is total gross profit ÷ total revenue, not the mean of 44.5%, 26.3% and 57.2% (which would be 42.7%).
Calculate gross margin with Better Analyst
Connect your data or upload a spreadsheet, then ask in plain English. Better Analyst writes and runs the calculation and shows the steps, so you can check the number.
- “What was our gross margin by product line each month this year, and which line moved the blend most?”
- “Show gross margin before and after discounts for the Q3 promotion.”
- “Flag any SKU whose gross margin fell more than 5 points versus last quarter.”
Usually calculated from: Google Sheets, Google BigQuery, Snowflake, PostgreSQL, MySQL, 
Microsoft SQL Server.