How to Calculate Gross Margin by Product in Excel

Calculate gross margin % for a product, total gross profit across a range, and the blended margin for the whole business.

You will learn to

• Calculate gross margin % from price and unit cost
• Total gross profit with SUMPRODUCT
• Calculate a revenue-weighted blended margin

How it works

Gross margin is the share of each sale you keep after paying for the product itself: (price − unit cost) ÷ price. A candle that sells for $24 and costs $7.80 to make has a 67.5% gross margin. Note the difference from markup, which divides by cost instead of price.

To see the whole business, total gross profit with =SUMPRODUCT(B2:B7,C2:C7-D2:D7) and divide by total revenue, =SUMPRODUCT(B2:B7,C2:C7). That blended margin is weighted by how much of each product you actually sell — a simple average of the product margins would not be.

=(C2-D2)/C2