By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated November 8, 2026
To run a gross margin analysis in Excel, put revenue in one column and cost of goods sold (COGS) in the next. Then enter =(B2-C2)/B2 beside them and format the cell as a percentage. That gives you gross margin for one row. Add a total row, a product or category column, and a month-by-month view, and the same sheet shows which products carry your profit and which ones quietly drain it.
This guide walks through the formulas, a layout you can reuse every month, the price math for hitting a target margin, and the mistakes that produce wrong numbers. It also covers Google Sheets.
TL;DR
- Gross margin % = (net sales − COGS) ÷ net sales. In Excel:
=(B2-C2)/B2, formatted as a percentage. - Gross profit is a dollar amount. Gross margin is a percentage. Markup is a third thing, because it divides by cost.
- Analyze by product, category and month, and calculate the blended margin from summed totals, not by averaging percentages.
- To hit a target margin, price = cost ÷ (1 − target margin).
- Add input cells for assumptions and a variance column, and the sheet becomes a small profitability model.
Table of Contents
- Gross profit, gross margin and markup: get the terms straight
- How to calculate gross margin in Excel, step by step
- Build a reusable margin analysis template
- Work out the price you need for a target margin
- Turn the analysis into a profitability model
- Contribution margin: a close relative worth tracking
- Gross margin analysis in Google Sheets
- Perspective
- Get a margin model built for your business
- Quick checklist before you trust the numbers
- FAQ
- Sources
Gross profit, gross margin and markup: get the terms straight
Most wrong margin reports start with mixed-up vocabulary, so settle the terms first.
- Gross profit is net sales minus COGS, expressed in money. Business Victoria defines gross profit and gross margin this way: gross margin is gross profit divided by net sales.
- Gross margin is that same profit as a percentage of sales. The Business Development Bank of Canada notes that gross margin and gross profit margin mean the same thing.
- Net sales is gross sales less returns, allowances and discounts, according to Shopify’s gross margin guide.
- Markup divides the same profit by cost instead of by sales.
The difference is easy to see with numbers. Take a $40 price and a $16 cost. Margin is (40 − 16) ÷ 40 = 60%. Markup is (40 − 16) ÷ 16 = 150%. PodVector’s margin guide uses this exact example. Same product, same profit, two very different percentages.
The dollar figure and the percentage also answer different questions. Gross profit is what is left to pay rent, salaries and everything else. Gross margin lets you compare months, products and industry peers. If you want to go one step further and see which customers or channels eat into that profit after delivery costs, a cost to serve analysis is the natural next layer.
What counts as COGS
COGS covers the direct costs of what you sell: wholesale costs, raw materials, direct production labor, inbound freight and import duties, and the packaging needed to get a product ready for sale. Shopify’s guide lists marketing, administrative salaries, office rent, utilities, interest and income taxes as outside COGS. Keep them out of the gross margin calculation. They belong lower on your income statement, where operating margin and net margin live. Our profit and loss statement in Excel guide shows where each line sits.

How to calculate gross margin in Excel, step by step
Here is the simplest working setup.
- In A1:D1, type the headers Product, Revenue, COGS, Gross margin %.
- Enter revenue in column B and COGS in column C, one row per product.
- In D2, type
=(B2-C2)/B2. - Select D2 and press Ctrl+Shift+% (or use Home > Number > Percentage).
- Drag the fill handle down to copy the formula to every row.
This is the per-row formula described in Better Analyst’s gross margin guide. The same guide suggests wrapping it as =IFERROR((B2-C2)/B2,""), which keeps rows with no revenue from showing a #DIV/0! error.
If you prefer to see the dollars first, add a gross profit column with =B2-C2, then divide it by revenue in the next column. The result is identical. The two-column version is easier to audit, and it gives you the dollar figure you need anyway.
A worked example
| Product | Revenue | COGS | Gross profit | Gross margin |
|---|---|---|---|---|
| Product A | $50,000 | $30,000 | $20,000 | 40.0% |
| Product B | $30,000 | $12,000 | $18,000 | 60.0% |
| Product C | $20,000 | $15,000 | $5,000 | 25.0% |
| Total | $100,000 | $57,000 | $43,000 | 43.0% |
The figures are illustrative. Notice the total margin: 43.0%, not the 41.7% you would get by averaging 40, 60 and 25. Averaging percentages ignores how much revenue each product brings in.
Calculate the blended margin the right way
For the total row, sum revenue and COGS first, then divide. With data in rows 2 to 4:
=(SUM(B2:B4)-SUM(C2:C4))/SUM(B2:B4)
The same pattern is shown in 365 Dropship’s margin formula guide. Use a wider range such as B2:B200 so new rows are included automatically. If your data lives in an Excel Table, structured references do this for you.
Three mistakes that break the result
PodVector names three causes behind nearly every wrong margin in a spreadsheet:
- Dividing by cost instead of price, which gives markup, not margin.
- Forgetting parentheses, so Excel does not subtract before it divides.
- Skipping percentage formatting, so 0.6 looks like an error instead of 60%.
Add a fourth of our own: mixing gross and net revenue. If some rows are before discounts and others after, your margins will not be comparable.
Build a reusable margin analysis template
A one-off calculation answers one question. A template answers the same question every month. Here is a layout that scales from a handful of products to a few hundred.
Tab 1: Data
One row per sale line or per product per month. Suggested columns:
- Date or month
- SKU or product
- Category
- Units sold
- Net revenue
- COGS
- Gross profit (
=F2-G2, adjusting to your columns) - Gross margin % (
=IFERROR(H2/F2,""))
The ElyxAI profit margin walkthrough takes a similar approach: product, revenue, COGS and margin columns, plus operating expenses and net profit if you want to go beyond gross margin. It also recommends conditional formatting and PivotTables to summarize by product, region or period.
Tab 2: Category map
Individual SKUs are noisy. Coefficient’s price-volume-mix guide recommends a SKU Map tab that groups SKUs into product categories, so you can read the drivers of a product group rather than isolated codes. Build a two-column table (SKU, Category) and pull the category into your data tab with XLOOKUP or VLOOKUP.
Tab 3: Summary
Insert a PivotTable from the data tab. Put category in rows, month in columns, and sum revenue and COGS in values. Then compute margin next to the pivot with a normal formula, because a pivot’s own “average” of a margin column would repeat the averaging error above.
Add conditional formatting to the margin column: red below your floor, green above your target. Our variance analysis in Excel guide shows how to extend this into month-over-month and budget-versus-actual views.
Tab 4: Dashboard
A gross margin dashboard does not need to be elaborate. Four things cover most owners’ needs:
- Blended gross margin for the current month, next to the prior month
- A line chart of gross margin % by month
- A bar chart of margin by category, sorted high to low
- A short list of the five lowest-margin products by revenue
That last list is where the money is. A product with a low margin and high revenue is a pricing or sourcing problem. A product with a high margin and low revenue may deserve more promotion.
Work out the price you need for a target margin
“How do I add a 40% profit margin?” is one of the most common questions on this topic, and it is where markup confusion hurts most. Adding 40% to cost gives you a 28.6% margin, not 40%.
To get a margin of 40%, rearrange the margin formula:
Selling price = cost ÷ (1 − target margin)
With a cost of $60 and a 40% target, the price is 60 ÷ 0.60 = $100. Check it: (100 − 60) ÷ 100 = 40%.
In Excel, with cost in B2 and the target margin in a labeled input cell F1 (entered as 40%):
=B2/(1-$F$1)
If you prefer Excel’s Goal Seek, JAD Apps’ pricing guide shows the same case, COGS of 60 and a 40% target giving a price of 100. It warns that the margin formula and the target must use matching units, such as 0.40 versus 40. The direct formula is simpler for a whole price list because it updates instantly. Goal Seek is better when price feeds into a longer chain of calculations. For more on both approaches, see our Excel pricing models guide.

Turn the analysis into a profitability model
Gross margin tells you what is left after direct costs. A profitability model carries the story further down the page and forward in time.
Extend down the income statement
DataHub Pro’s P&L guide lays out monthly columns for revenue, COGS, operating expenses, interest and tax, then calculates gross, operating and net profit by subtraction. If you want a structure for repeating the same monthly layout, this profit and loss walkthrough shows how a 12 to 24 month view can be organized. Xero’s guide gives the other two margins: operating margin subtracts operating expenses too, and net margin subtracts all expenses, including interest and taxes.
Add visible assumptions
Put every assumption in a labeled input cell: price change, volume growth, supplier cost increase, freight. Then every formula points at those cells instead of hardcoded numbers. The LibreTexts course on What-If Analysis and business forecasting explains how Goal Seek, Scenario Manager and Data Tables let you test conservative, expected and optimistic cases while keeping the same formulas and worksheet structure.
A practical scenario set for margin:
- Conservative: supplier costs up 5%, prices flat.
- Expected: this year’s trend continues.
- Optimistic: prices up 3%, costs flat.
Add variance columns
DataHub Pro also recommends variance columns for absolute and percentage change, with IFERROR to avoid divide-by-zero errors when the comparison period is zero. For margin, show the change in percentage points, not percent of percent. A drop from 43% to 40% is three points.
Once your margin by product is solid, two natural next steps are break-even analysis, which uses your margin to find the sales level that covers fixed costs, and client profitability analysis for service businesses. A broader view of how these pieces connect is in our financial modeling in Excel guide.
Contribution margin: a close relative worth tracking
Contribution margin subtracts only variable costs from revenue. COGS often includes some fixed elements, such as salaried production staff, so the two measures can differ. Contribution margin is the more useful figure when you ask what one more unit or one more order adds to profit. The Excel setup is the same: =(B2-variable_cost)/B2, in a column beside gross margin. Keep the two columns separate and labeled so nobody confuses them.
Gross margin analysis in Google Sheets
Everything above works in Google Sheets with the same formulas. Bricks’ Google Sheets guide follows the same logic: gross profit as revenue minus COGS in one column, then margin in the next. It also uses conditional formatting under Format > Conditional formatting to color-code high and low margins.
Two habits help in Sheets:
- Format the margin column as a percentage (Format > Number > Percent) before you share the file.
- Keep inputs on their own tab and protect that range, so teammates editing the sheet do not overwrite your assumptions.
Sheets suits teams who need several people updating the same file. Excel suits heavier PivotTable and scenario work. Either way, the model is the same.
Perspective
We see the same pattern with small businesses that run on spreadsheets. The owner knows revenue and knows the bank balance, but cannot say which product, channel or client makes the money. Usually the data is already there. It is just not arranged to answer that question.
Our view is that you do not need new software to fix this. A well-built spreadsheet, with clear inputs, margin by product, and a dashboard that updates when you paste in the month’s numbers, gives you control and understanding of your own data with tools you already use. That is what we build.
The most common gap we address is the one this article covers: gross margin calculated on totals only, with no view by product, and no link between margin and pricing decisions. Fixing that tends to change what an owner decides about price, mix and spend.
frac
Get a margin model built for your business
If you would rather not build this from scratch, we do it for you in the tools you already use. Our Profitability and Pricing Analysis product is designed to show margin by product and support pricing decisions. If you want a custom build, such as a KPI dashboard or a profitability review, see our one-time finance projects. For ongoing support, our fractional CFO plans start with STARTER at $600 per month (details).
You can also browse our Excel templates to start from a ready-made layout.
Quick checklist before you trust the numbers
- Revenue is net of returns and discounts in every row.
- COGS contains only direct costs, with marketing and admin salaries left out.
- The total margin is calculated from summed revenue and COGS.
- Every margin cell is formatted as a percentage.
- Target-price formulas divide by (1 − margin), not multiply by (1 + margin).
- Assumptions live in labeled input cells, not inside formulas.
- Each month’s data is pasted in the same layout, so formulas copy cleanly.
FAQ
What is the formula for gross margin in Excel?
Use =(Revenue-COGS)/Revenue, for example =(B2-C2)/B2, and format the result as a percentage. This is gross profit divided by net sales. Wrap it in IFERROR if some rows may have zero revenue.
What is the difference between gross margin and markup?
Both use the same profit, but margin divides by sales and markup divides by cost. On a $40 price and $16 cost, margin is 60% and markup is 150%. Pricing from markup when you mean margin is a common source of underpricing.
How do I calculate gross margin in Google Sheets?
The formula is identical: enter =(B2-C2)/B2 in the first row, set the format to percent, and fill down. You can add conditional formatting to flag products below your target margin.
How do I find the price that gives a 40% margin?
Divide your cost by 0.60. A $60 cost gives a $100 price, and (100 − 60) ÷ 100 equals 40%. In Excel, keep the target margin in an input cell and use =B2/(1-$F$1).
Should I average the margins of my products?
No. Sum total revenue and total COGS, then divide. Averaging percentages gives every product equal weight regardless of how much it sells, which misstates your real margin.
About the author
Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.
Sources
- Calculate your breakeven point, margin and markup (Business Victoria)
- What is gross margin? (Business Development Bank of Canada)
- What Is Gross Margin? (Shopify)
- The Excel Formula for Margin, Explained (PodVector AI)
- Gross Margin Formula (Better Analyst)
- Margin Formula Excel (365 Dropship)
- How to Create Profit Margin Analysis Tool in Excel (ElyxAI)
- Price-Volume-Mix Analysis in Excel (Coefficient)
- Price Optimization in Excel with Goal Seek (JAD Apps)
- Profit and Loss Statement in Excel (DataHub Pro)
- Profit margin formula (Xero)
- What-If Analysis and Business Forecasting (LibreTexts)
- How to Calculate Margin in Google Sheets (Bricks)
- Cost to Serve Analysis
- Profit and loss for a 12 to 24 month view

