Gross Margin Percentage Formula in Excel: A Clear Guide

Formulas & functions · Updated November 18, 2026

To calculate gross margin percentage in Excel, subtract cost of goods sold (COGS) from revenue, then divide the result by revenue. If revenue is in B2 and COGS is in C2, enter =(B2-C2)/B2 in D2 and format D2 as a percentage.

What Gross Margin Percentage Measures

Gross margin percentage shows how much of each dollar of revenue remains after subtracting the direct costs of producing or purchasing the goods or services sold. It is a profitability ratio, not a dollar amount. For example, a 36% gross margin means that 36 cents of each revenue dollar remains after the costs included in COGS are deducted. That remainder helps cover operating expenses and, if enough remains, contribute to operating profit.

Gross margin depends on how a business defines and records COGS. For a product seller, COGS commonly includes the purchase or manufacturing cost of the items sold. Depending on the business and its accounting policy, direct labor, production overhead, freight, or other direct costs may also be included. Rent for an office, advertising, and general administrative costs are usually operating expenses rather than COGS. Use figures prepared on a consistent basis if you want a meaningful comparison.

Gross profit is the dollar amount left after COGS is subtracted from revenue; gross margin is that amount expressed as a share of revenue. Keeping the two terms distinct matters when reading reports: a business can earn more gross profit in dollars while having a lower gross margin percentage if its revenue grows faster than its gross profit.

The Formula and What Each Part Means

The underlying calculation is gross profit divided by revenue. Since gross profit is revenue minus COGS, the equivalent calculation is (revenue − COGS) ÷ revenue. Excel does not need a special gross-margin function: ordinary cell references and arithmetic operators are enough. Parentheses make the order clear, so Excel subtracts COGS from revenue before dividing by revenue.

With revenue in B2 and COGS in C2, enter =(B2-C2)/B2. The formula returns a decimal ratio: a result of 0.36 represents 36%. Apply percentage formatting to display it as 36%; do not multiply the formula by 100 if you plan to use Excel’s percentage format, or the displayed result will be 3,600%.

The formula assumes revenue is the denominator and that both inputs refer to the same product, period, currency, and accounting basis. If a report uses net sales after returns or discounts, use that net-sales figure consistently as revenue. A result is only as useful as the definitions and data behind it.

Set Up the Worksheet

A simple worksheet makes the calculation easy to inspect and copy down. In row 1, add headings such as Product, Revenue, COGS, and Gross Margin %. Put each product or reporting period on its own row. Enter revenue in column B and COGS in column C, then place the calculation in column D. Use clear labels and, where relevant, note whether amounts are monthly, annual, or per unit.

With the headings in row 1 and the first product’s revenue in B2 and COGS in C2, enter =(B2-C2)/B2 in D2. Select D2 and use the fill handle—the small square at the lower-right corner of the selected cell—to copy the formula down. Excel adjusts the relative references for each row, so the next row will refer to B3 and C3. Check a few copied formulas to make sure the references line up with the intended records.

In Excel for Microsoft 365 on Windows, the percentage display is available from Home > Number > Percent Style. The ribbon layout is broadly similar in Excel for Mac and Excel for the web, though details can vary with the interface. Formatting changes how a value appears; it does not change the calculation stored in the cell.

Worked Example: Calculate and Check the Result

Suppose a product earned $250 in revenue and had $160 in COGS. Gross profit is $90, because $250 minus $160 equals $90. Dividing $90 by $250 gives 0.36, or 36%. This arithmetic check is useful when confirming that the spreadsheet formula references the correct cells.

With revenue of 250 in B2 and COGS of 160 in C2, enter =(B2-C2)/B2 in D2. The result is 0.36 before formatting and 36% after percentage formatting. The result does not mean the business earned $36: it means gross profit was 36% of the $250 revenue.

For another example, imagine revenue of $800 and COGS of $500. Gross profit is $300, and $300 divided by $800 is 0.375, which displays as 37.5%. If the business instead records COGS of $900 against $800 of revenue, the formula returns −12.5%. A negative margin can indicate that the direct costs exceeded revenue for that row; it is not automatically an Excel error, though the source data and accounting treatment should be checked.

Format the Result Without Changing It

Percentage formatting is usually the clearest way to present margin. Select the result cells, then choose Home > Number > Percent Style. Excel displays the underlying decimal as a percentage. Use Increase Decimal or Decrease Decimal in the Number group if readers need more or fewer decimal places. For example, a stored value of 0.367 can display as 37% with no decimal places or 36.7% with one.

Rounding the display does not necessarily round the stored value used in later calculations. That is often desirable because calculations can retain precision while reports show tidy figures. If a report specifically requires rounded values to be used in subsequent calculations, that is a separate accounting or reporting decision; do not assume that changing the displayed decimal places has done it.

A common mistake is to enter a formula that already multiplies the result by 100 and then apply percentage formatting. For a 36% margin, the correct unformatted result is 0.36. Multiplying by 100 produces 36, which percentage formatting displays as 3,600%. Use either a decimal ratio with percentage formatting or a number multiplied by 100 displayed as a plain number, not both approaches at once.

Calculate Margin Across Products or Periods

When a table contains several products or months, calculate each row’s margin if you want to compare individual results. But do not usually take a simple average of those percentages to get the overall business margin. A small sale and a large sale should not automatically have equal influence on the total. The overall margin is based on total gross profit divided by total revenue, which weights the result according to revenue.

With revenues in B2:B4 and COGS in C2:C4, enter =(SUM(B2:B4)-SUM(C2:C4))/SUM(B2:B4) in D5 to calculate the combined margin. For example, if the rows contain revenues of $100, $200, and $300, and COGS of $60, $150, and $210, total revenue is $600 and total COGS is $420. The formula returns 30%, since the $180 total gross profit is 30% of $600.

In that example, the individual margins are 40%, 25%, and 30%. Their simple average is about 31.7%, not the correct combined margin of 30%. The difference occurs because the rows have different revenue amounts. A simple average can answer a different question—such as the average of the listed row percentages—but it does not represent the total gross margin for all sales.

Common Errors and How to Handle Them

  • Revenue is zero or blank: Dividing by zero returns a division error, and a blank revenue cell may be treated like zero. Decide whether a blank result or a visible message is more useful for your sheet.
  • Revenue and COGS are reversed: Check the column headings and the signs of the values. Revenue should be the denominator, and COGS should be subtracted from revenue.
  • Percentage is 100 times too large: Remove an unnecessary multiplication by 100 if the cell is percentage-formatted.
  • Totals do not match the source report: Confirm whether the revenue figure is gross sales or net sales and whether the COGS figure includes the same categories and period as the source report.
  • Unexpected negative values: Check for credits, returns, unusual costs, and data-entry mistakes. A negative result may be mathematically valid even when it warrants investigation.

To avoid a division-by-zero error while leaving the result cell blank when revenue is zero, use this formula. With revenue in B2 and COGS in C2, enter =IF(B2=0,"",(B2-C2)/B2) in D2. It returns a blank when B2 is zero and otherwise calculates the margin. A blank can be convenient in a report, but it may also conceal missing or incomplete input, so label the worksheet or validate the source data when that distinction matters.

Excel may also treat numbers stored as text differently from numeric values. If a formula gives an unexpected result or a cell shows a warning indicator, check whether the source values are truly numbers and whether imported data includes currency symbols or spaces as text. Correcting the input data is generally better than building a more complicated margin formula around inconsistent entries.

Gross Margin Versus Markup

Gross margin and markup are related but use different denominators. Gross margin divides gross profit by revenue; markup divides gross profit by cost. Because cost is usually lower than the sale price when there is a profit, markup percentage is typically higher than gross margin percentage for the same transaction.

For a sale of $250 with COGS of $160, gross profit is $90. Gross margin is $90 divided by $250, or 36%. Markup is $90 divided by $160, or 56.25%. Confusing the two can lead to misleading pricing comparisons. If a request specifically asks for gross margin, use revenue as the denominator rather than cost.

Frequently Asked Questions

What is the Excel formula for gross margin percentage?
With revenue in B2 and COGS in C2, use =(B2-C2)/B2. Format the result cell as a percentage to display the margin in the familiar form.

Do I multiply gross margin by 100 in Excel?
Not if you format the result as a percentage. The formula returns a decimal such as 0.36, and percentage formatting displays it as 36%. Multiplying by 100 as well would make the displayed percentage 100 times too large.

Why is my gross margin percentage negative?
A negative result means the COGS entered for that row exceeds its revenue. Check the figures, units, period, and accounting definitions. If the inputs are correct, the negative percentage is the mathematical result of a loss on those direct costs.

Should I average the gross margin percentages for multiple products?
Not to report the combined margin. Calculate total gross profit divided by total revenue instead, because that accounts for the different revenue amounts. A simple average is appropriate only when you specifically want each listed percentage to count equally.

How do I show two decimal places in Excel?
Select the margin cells, apply Percent Style from Home > Number, and use Increase Decimal or Decrease Decimal to choose the display precision. The displayed rounding does not by itself change the underlying values.

Can gross margin percentage be more than 100%?
Under the usual definition, with positive revenue and nonnegative COGS, gross profit cannot exceed revenue, so the margin cannot exceed 100%. A result above 100% often signals negative COGS, a reversed or incorrect input, or a different definition; investigate the underlying data before interpreting it.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.