# Gross Margin Formula: How to Calculate Gross Margin in Excel

- URL: https://betteranalyst.com/metrics/gross-margin
- Published: 2026-10-28
- Updated: 2026-10-28
- Author: David Bressler, Founder (https://betteranalyst.com/authors/david-bressler)

> **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.

Category: [Finance](https://betteranalyst.com/metrics#finance)

Gross margin formula with a worked example by product line, the Excel formula, and the mistakes that make margins look better than they are.

## 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.

*Q3 revenue and COGS by product line (table starts in cell A1)*

| Product line | Revenue | COGS |
| --- | --- | --- |
| Whole-bean coffee | $182,400 | $101,300 |
| Brewing equipment | $64,900 | $47,800 |
| Subscriptions | $96,300 | $41,200 |

1. Total revenue = 182,400 + 64,900 + 96,300 = $343,600.
2. Total COGS = 101,300 + 47,800 + 41,200 = $190,300.
3. Gross profit = 343,600 − 190,300 = $153,300.
4. Gross margin = 153,300 ÷ 343,600 = 0.446, or 44.6%.
5. 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](https://betteranalyst.com/data-connectors/google-sheets), [Google BigQuery](https://betteranalyst.com/data-connectors/bigquery), [Snowflake](https://betteranalyst.com/data-connectors/snowflake), [PostgreSQL](https://betteranalyst.com/data-connectors/postgresql), [MySQL](https://betteranalyst.com/data-connectors/mysql), [Microsoft SQL Server](https://betteranalyst.com/data-connectors/mssql).

[Try Better Analyst free](https://betteranalyst.com/signup)

## Further reading

- [Markup calculator](https://betteranalyst.com/markup-calculator)
- [Profit and loss template](https://betteranalyst.com/excel-templates/profit-and-loss-template)
- [SUMIF function for margin by category](https://betteranalyst.com/excel/sumif-function)
- [Chart revenue and margin together](https://betteranalyst.com/blog/how-to-compare-revenue-and-margin-on-one-chart)
- [Better Analyst for finance teams](https://betteranalyst.com/solutions/finance)

## Frequently asked questions

### What is the difference between gross margin and gross profit?

Gross profit is a currency amount (revenue minus COGS); gross margin is that amount divided by revenue, expressed as a percentage. $153,300 of gross profit on $343,600 of revenue is a 44.6% gross margin.

### Is gross margin the same as gross profit margin?

Yes. Gross margin, gross profit margin and gross margin ratio all mean (revenue − COGS) ÷ revenue. Net profit margin is different: it subtracts every expense, including overhead, interest and tax.

### How do I calculate gross margin in Excel for each row?

With revenue in B and COGS in C, enter =(B2-C2)/B2 in D2, format D as a percentage and fill down. Wrap it as =IFERROR((B2-C2)/B2,"") to hide errors on rows with no revenue.

### Can gross margin be negative?

Yes, when COGS exceeds revenue, for example clearance sales below cost or a product whose input costs jumped faster than prices. A negative gross margin means every extra sale loses money before overhead.

## Related metrics

- [Inventory Turnover Ratio: Formula, Example and Excel Steps](https://betteranalyst.com/metrics/inventory-turnover)