Gross Margin Excel Calculation: Formula and Examples

Formulas & functions · Updated November 18, 2026

With revenue in B2 and cost of goods sold (COGS) in C2, calculate gross margin in Excel with =(B2-C2)/B2, then format the result as a percentage. For example, $125,000 of revenue and $75,000 of COGS produce a gross margin of 40%.

What gross margin measures

Gross margin is the share of revenue left after subtracting the direct costs of the goods or services sold. It is usually expressed as a percentage. The dollar amount left over is gross profit: revenue minus COGS. Gross margin helps compare profitability across products, time periods, or businesses of different sizes because it relates gross profit to revenue rather than showing dollars alone.

Gross margin is not the same as operating margin or net profit margin. It does not normally subtract expenses such as general administration, marketing, interest, or income taxes. Those expenses are considered later in the income statement. The exact presentation can vary by business and accounting policy, so use figures prepared on a consistent basis when making comparisons.

Set up the worksheet

Use clear labels and keep the revenue and cost figures for the same product and period on the same row. A basic layout could be:

  • A1: Product or period; B1: Revenue; C1: COGS; D1: Gross profit; E1: Gross margin.
  • Enter the revenue amount in B2 and the related COGS amount in C2.
  • Use the same currency and reporting period for both amounts. For example, do not compare monthly revenue with annual COGS.

Revenue should reflect the sales measure you intend to analyze. Many businesses use net sales, which account for items such as returns, allowances, or discounts, rather than the original list price of all orders. COGS should correspond to those sales, not simply to all cash spent or all inventory purchased during the period. Matching revenue and its related cost is essential to a meaningful result.

Calculate gross profit and margin

With revenue in B2 and COGS in C2, calculate gross profit in D2 with =B2-C2. If those cells contain $125,000 and $75,000, respectively, the result is $50,000. Gross margin divides that profit by revenue. With the same cell layout, enter =(B2-C2)/B2 in E2. The result is 0.4, which is 40% when displayed as a percentage.

To format the result in Excel for Microsoft 365 on Windows, select E2 and choose Home > Number > Percent Style. Adjust the decimal places with the increase or decrease decimal controls in the Number group if you want a more detailed display. The percentage format changes how Excel displays the value; it does not change the underlying calculation. A displayed 40% is stored as 0.4, which is useful for further calculations.

For a larger worksheet, copy the formulas down the rows. If the first product is in row 2, select the formula cell and drag its fill handle down, or double-click the fill handle when adjacent rows contain data. Excel updates relative references, so the next row uses B3 and C3. The same formulas and ribbon formatting are available in Excel for Mac, although ribbon placement and keyboard shortcuts can differ slightly. In Excel for the web, the calculation works as well; its ribbon layout may be more compact.

Calculate margin for multiple products or periods

Suppose the worksheet has product revenue in B2:B3 and corresponding COGS in C2:C3. Product A brings in $100 and has $60 of COGS, while Product B brings in $80 and has $52 of COGS. Their individual margins are 40% and 35%. To calculate the combined margin for those rows, use =(SUM(B2:B3)-SUM(C2:C3))/SUM(B2:B3). Total revenue is $180, total COGS is $112, and total gross profit is $68, so the combined margin is about 37.78%.

You can also calculate a margin for each month, store, or sales channel by giving each one a row and applying the same formula to its revenue and COGS. A comparison is most useful when the periods use the same number of days or otherwise have a comparable scope, and when the accounting treatment has not changed. If costs are entered as negative numbers rather than positive expenses, the standard subtraction formula will produce the wrong result; choose a consistent sign convention and adapt the calculation accordingly.

Why the weighted margin differs from an average

A frequent spreadsheet mistake is to average product margin percentages to get the overall business margin. An ordinary average gives every product equal weight, even if one product generated far more revenue. In the example above, the average of 40% and 35% is 37.5%, while the combined margin is about 37.78%. The combined calculation is the appropriate overall margin because it divides total gross profit by total revenue.

In general, calculate an overall margin from totals: add the relevant revenue, add the matching COGS, subtract costs from revenue, and divide by total revenue. This is equivalent to weighting each product’s margin by its share of revenue. A plain average can still answer a different question—such as the average margin of a typical product when every product is deliberately given equal importance—but it should not be presented as the margin on all sales.

Choose a consistent definition of COGS

COGS generally represents the direct costs assigned to the goods or services sold. For a retailer, that often includes the cost of inventory sold. A manufacturer may include direct materials, direct labor, and allocated production overhead under its accounting method. A service business may track direct labor or other delivery costs. Which costs belong in COGS depends on the business and the applicable accounting approach; Excel cannot decide that classification for you.

Do not automatically treat every expense as COGS. Office rent, sales commissions, advertising, and shipping to customers may be classified outside COGS depending on the organization’s policy and reporting framework. Costs of bringing inventory into the business may be treated differently from delivery costs after a sale. When comparing internal reports or published financial statements, verify that both use comparable definitions. If a cost classification changes, note it because a margin shift may reflect the change in reporting rather than a change in underlying economics.

Common errors and how to avoid them

  • Dividing by cost instead of revenue: (Revenue minus COGS) divided by revenue is margin. Dividing gross profit by COGS calculates markup, a different measure.
  • Using gross profit as the percentage: Revenue less COGS is a dollar amount. Divide that amount by revenue to get the margin rate.
  • Mixing periods or units: Pair a month’s revenue with that month’s related costs, and use matching currencies and units.
  • Including unsold inventory costs in the wrong period: Purchases are not necessarily equal to COGS for the period. Use the cost assigned to goods actually sold under the business’s inventory accounting.
  • Ignoring returns and discounts: If the analysis is meant to use net sales, ensure those adjustments are reflected in revenue consistently.
  • Dividing by zero: If revenue is zero, gross margin is undefined because the formula divides by zero. Investigate the missing or zero revenue rather than interpreting an error as a margin result.
  • Misreading a percentage display: A value of 0.4 formatted as a percentage appears as 40%. Do not multiply by 100 in the formula and then apply percentage formatting, or the display will be 4,000%.

How to interpret the result

A 40% gross margin means that, for each $1 of revenue, about 40 cents remains after the COGS included in the calculation, before other expenses. It does not mean that the business keeps 40 cents as final profit. Operating costs and other items still have to be considered.

There is no single “good” gross margin that applies to every organization. Retail, software, manufacturing, and professional services have different cost structures, and product mix can change an overall result. For useful context, compare a business with its own prior periods and with relevant competitors using consistent definitions. Investigate changes alongside sales volume, pricing, discounts, product mix, supplier costs, and inventory adjustments rather than assuming the percentage alone explains the cause.

Frequently Asked Questions

What is the Excel formula for gross margin?
Subtract COGS from revenue, then divide by revenue. If revenue is in B2 and COGS is in C2, use =(B2-C2)/B2 and format the result as a percentage.

How do I calculate gross profit in Excel?
With revenue in B2 and COGS in C2, enter =B2-C2. Gross profit is a currency amount; gross margin is that amount divided by revenue.

Is gross margin the same as markup?
No. Margin divides gross profit by revenue, while markup divides gross profit by cost. For example, $50 profit on $100 revenue is a 50% margin; on $50 cost it is a 100% markup.

Should I average the margins of several products?
Not to find the total margin on all sales. Divide combined gross profit by combined revenue. A simple average answers an equal-weighted product question, not the overall sales-weighted result.

Can gross margin be negative or above 100%?
A negative margin can occur when COGS exceeds revenue. With positive revenue and nonnegative COGS, margin ordinarily cannot exceed 100%; unusual values may indicate sign, data, or classification problems. Check the underlying figures before drawing conclusions.

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.