Excel’s profit margin formula shows what share of revenue remains as profit, making it easier to compare products, periods, or businesses.
The basic calculation is simple, but choosing the right profit figure and handling edge cases can change what the result means.
What is the Excel profit margin formula?
Profit margin is profit divided by revenue.
To calculate it in Excel, subtract costs from revenue, then divide the result by revenue:
=(Revenue-Costs)/Revenue
For example, if revenue is $250 and costs are $175, the profit is $75.
The margin is $75 divided by $250, or 30%.
In a worksheet where revenue is in A2 and costs are in B2, enter this in C2:
=(A2-B2)/A2
Format C2 as a percentage.
The underlying result is 0.3, which Excel displays as 30% when percentage formatting is applied.
You can choose the number of decimal places in the Home tab’s Number group.
How do you calculate gross profit margin?
Gross profit margin measures the portion of sales left after subtracting the direct costs of producing or purchasing the goods or services sold.
It does not subtract operating expenses such as rent, marketing, or administrative salaries.
Suppose a table has sales in column B and cost of goods sold in column C.
In D2, enter:
=IFERROR((B2-C2)/B2,0)
Copy the formula down for other rows, then format column D as a percentage.
If row 2 contains $1,200 in sales and $720 in direct costs, the result is 40%.
IFERROR returns zero if the calculation produces an error, such as when sales are zero.
If a zero result could be mistaken for a real margin, you may prefer to display a blank:
=IFERROR((B2-C2)/B2,"")
Another option explicitly checks for zero sales:
=IF(B2=0,"",(B2-C2)/B2)
This version leaves the cell blank when sales equal zero but still shows other results, including negative margins.
In current versions of Excel and Google Sheets, IF and IFERROR are available.
These formulas use ordinary cell references and work in older Excel versions as well.
How do you calculate net profit margin?
Net profit margin uses net income, which accounts for expenses beyond direct production costs.
Depending on the figures in your source data, that can include operating expenses, interest, and taxes.
If net income is in E2 and revenue is in B2, calculate net profit margin with:
=IFERROR(E2/B2,"")
For example, $90 of net income on $1,200 of revenue gives a net margin of 7.5%.
The denominator should be revenue for the same business, period, and reporting basis as the net income figure.
If you have revenue and a complete total of all expenses rather than net income, you can calculate it directly:
=IFERROR((B2-F2)/B2,"")
Here, F2 should contain total expenses for the same period.
Avoid subtracting only direct costs when you intend to report net margin; that produces gross margin instead.
What is the difference between margin and markup?
Margin and markup both compare profit with another amount, but they use different denominators.
Margin divides profit by revenue.
Markup divides profit by cost.
If an item costs $60 and sells for $100, profit is $40.
The margin is 40 divided by 100, or 40%.
The markup is 40 divided by 60, or about 66.7%.
In Excel, if the selling price is in A2 and cost is in B2, use these formulas:
- Margin:
=IFERROR((A2-B2)/A2,"") - Markup:
=IFERROR((A2-B2)/B2,"")
Label the resulting column clearly.
Calling a markup percentage a margin can make pricing comparisons misleading.
How can you calculate profit from a target margin?
If you know the selling price and desired margin, you can work out the maximum cost that preserves that margin.
For price in A2 and target margin in B2, calculate allowable cost with:
=A2*(1-B2)
If the price is $100 and the target margin is 35%, the maximum cost is $65.
Enter the target as a percentage, such as 35%, or as a decimal, such as 0.35.
Typing 35 into a General-format cell means 35, not 35%, and will produce a different result.
To calculate the selling price needed for a target margin when cost is known, use:
=IFERROR(B2/(1-C2),"")
Here, B2 is cost and C2 is the target margin.
A $65 cost at a 35% target margin requires a selling price of $100.
A target margin of 100% makes the denominator zero, so this formula returns a blank through IFERROR.
How should you handle negative values and zero sales?
When costs exceed revenue, the formula returns a negative margin.
For example, revenue of $80 and costs of $100 produce a margin of -25%.
This indicates a loss relative to revenue; it is not a formula error.
Keep negative results visible if they are meaningful to your analysis.
When revenue is zero, margin is undefined because division by zero is not possible.
Decide whether your report should show a blank, a label, or an error indicator.
For a clear label, use:
=IF(B2=0,"No sales",(B2-C2)/B2)
This formula returns text for zero sales and a numeric margin otherwise.
Because the output mixes text and numbers, use a separate status column if you need to sort, average, or chart the margin values consistently.
Also check that revenue and costs are stored as numbers rather than text.
Imported data may contain currency symbols or spaces that prevent calculations.
Excel’s error values, such as #VALUE! or #DIV/0!, can help identify rows that need cleanup; wrapping every formula in IFERROR may hide issues that you should investigate.
How do you calculate an overall margin from multiple rows?
For an overall margin across several products or periods, divide total profit by total revenue.
Do not normally average the individual margin percentages, because that gives a small sale the same weight as a large one.
If revenues are in B2:B10 and costs are in C2:C10, calculate the combined margin with:
=IFERROR((SUM(B2:B10)-SUM(C2:C10))/SUM(B2:B10),"")
This calculates total profit first and then divides it by total revenue.
For example, a $100 sale at 50% margin and a $900 sale at 10% margin have an overall margin of 14%, not the simple average of 30%.
Use a consistent set of rows and matching periods in both ranges.
If a report excludes returns, discounts, or certain expenses, document that choice so readers can interpret the reported margin correctly.