How to Calculate Gross Margin by Product in Google Sheets

Revenue, cost, gross profit and margin percent for every product, sorted by the profit each one actually brings in.

The short answer: QUERY the revenue and cost per product, subtract, and divide: margin is =(Revenue - Cost) / Revenue. In the sample data, overall 2026 gross margin is 43.6%; Earl Grey Loose Leaf 1 lb has the best margin (60.7%) but House Blend 5 lb makes the most gross profit ($165,956).

The sample data

Sample data: a tab named Orders with 8,120 order lines from January 2025 to October 2026, one row per order line. Revenue is Qty × Unit price; Cost is the line's cost of goods (Qty × unit cost). Download orders.csv and import it (File → Import) to follow along. The business is fictional.

The classic way: QUERY revenue and cost, then a margin column

Column F is Product, J is Revenue and K is Cost (the line's cost of goods). In A1 of a new tab:

=QUERY(Orders!A:K, "select F, sum(J), sum(K), sum(J)-sum(K) where year(A) = 2026 group by F order by sum(J)-sum(K) desc label sum(J) 'Revenue', sum(K) 'Cost', sum(J)-sum(K) 'Gross profit'", 1)

QUERY can do arithmetic on its own totals, so sum(J)-sum(K) is gross profit, and ordering by it puts the biggest earners first. Type Margin in E1 and, in E2 copied down:

=IFERROR(D2/B2, "")

Format E as a percent. For the Total row, sum B, C and D, and divide the totals for the margin (=D17/B17).

Computed from the sample CSV above. Cells formatted as currency and percent.

Don't average the margins. On the sample data, =AVERAGE(E2:E16) says 45.1%. The real margin is 43.6%: total gross profit over total revenue. Small, high-margin products like the Milk Pitcher Set count as much as House Blend in a plain average.

Gotchas

• Cost per line, not per unit. The Cost column has to be quantity × unit cost. If your sheet only has unit cost, add a column =H2*UnitCost first, or sum(K) adds up unit prices.
• Missing costs. A product with no cost shows a 100% margin. Check for blanks in the cost column before trusting the top of the list.
• Gross margin is before overheads. Shipping, payment fees and discounts are usually not in cost of goods. Operating expenses sit on top.

The one-cell way: =QUIRIZ

With the Quiriz add-on for Google Sheets, you type the question instead of building the formula. =QUIRIZ does not read the cells around it: it answers from the datasets in your Quiriz account. For this example, upload orders.csv as a dataset, or connect your Shopify store. You can also add a Google Sheet itself as a dataset that refreshes on a schedule. Then, in any empty cell:

=QUIRIZ("gross profit and gross margin by product in 2026, sorted by gross profit, with a total row", "table")

The "table" mode spills the answer down and to the right from that cell, header row first:

Same figures as the formula result above, from the same sample data. The spilled cells are plain numbers, so you can format them, total them or chart them; here they are formatted as currency and percent. Column headings are written with each answer and may be worded differently.

The other modes: "verbose" (the default) answers in a sentence, "bullets" writes two to four points one per cell, and "notes" adds how the figure was calculated. Install Quiriz for Google Sheets and try the question on your own data.

Related questions

• Best-selling products
• Sales by product category
• Budget vs actual
• Top customers by revenue
• Gross margin by SKU from a Shopify export
• Contribution margin by product category
• How to use the QUERY function in Google Sheets
• All Google Sheets questions

Frequently asked questions