Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
To calculate profit margin in Excel, divide profit by revenue. If revenue is in B2 and cost is in C2, enter =(B2-C2)/B2, then format the result as a percentage. With $250 in revenue and $150 in cost, the profit is $100 and the margin is 40%.
The formula is simple; the important choice is what counts as “profit” and “revenue.” Those definitions determine whether you are calculating gross, operating, or net margin.
Profit margin formula
Profit margin expresses profit as a share of revenue:
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Profit = Revenue − CostProfit margin = Profit ÷ Revenue
For example, $250 in revenue less $150 in cost leaves $100 profit. Divide $100 by $250 to get 0.4, or 40%. In Excel, Percentage formatting displays the decimal as a percentage; it does not change the underlying calculation. See Microsoft’s guide to calculating percentages and formatting percentages.
Margin is not markup. Margin divides profit by revenue; markup divides profit by cost. In this example, the margin is 40%, while markup is $100 ÷ $150, or 66.67%. If you divide profit by cost when you mean margin, you will get the wrong metric.
Set up the worksheet
Use one row per product, transaction, or reporting period. A basic layout is:
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →| Column | Heading | What it contains |
|---|---|---|
| A | Product or period | Identifies the item or time period |
| B | Revenue | Sales revenue or selling price |
| C | Cost | The costs included in this margin calculation |
| D | Profit | Revenue less the selected costs |
| E | Profit Margin | Profit divided by revenue |
Enter the headings in row 1. For the example, put 250 in B2 and 150 in C2. Enter the formulas below:
D2: =B2-C2
E2: =D2/B2You can also calculate margin directly in E2:
=(B2-C2)/B2Parentheses make the intended order clear: subtract cost from revenue first, then divide by revenue. Excel uses standard operator precedence, so parentheses are useful when a formula combines operations; see Microsoft’s explanation of formula calculation order.
Rank #2
- Used Book in Good Condition
Format the result as a percentage
- Select the margin cell, such as
E2. - Choose Home → Number → Percent Style, or use Ctrl+Shift+% on Windows.
- Adjust decimal places with Increase Decimal or Decrease Decimal.
A result of 0.4 will display as 40% (or, with more precision, 40.00%). A whole-number value of 40 formatted as a percentage displays as 4000%. Make sure the formula returns a decimal ratio before applying the format.
Use currency or accounting formatting for revenue, cost, and profit, and percentage formatting for margin. Keep the formula result numeric rather than appending a percent sign as text, so Excel can use it in further calculations.
Copy the formula down for more rows
After entering the formulas in D2 and E2, select those cells and drag the fill handle down, or copy and paste them into later rows. Relative references adjust automatically: the next row will use =B3-C3 and =D3/B3.
You can also select the range and press Ctrl+T to turn it into an Excel Table, which helps when adding rows and filtering products. In a table whose columns are named Revenue and Cost, the margin formula can use structured references:
=IFERROR(([@Revenue]-[@Cost]) / [@Revenue],"")Start with ordinary cell references if you are learning the calculation; table formulas are optional and their interface details can vary across Excel editions.
Choose the right margin: gross, operating, or net
The arithmetic stays similar, but “profit” changes with the costs you include. The denominator should normally be the revenue generated by the same activity as the profit in the numerator.
Recommended Free Tools
- Gross profit margin uses revenue less cost of goods sold (COGS), such as direct product or manufacturing costs:
=(B2-C2)/B2. Define direct costs consistently; do not quietly mix in rent or other overhead if you intend to report gross margin. - Operating margin subtracts COGS and operating expenses from revenue. If
B2is revenue,C2is COGS, andD2is operating expenses, use=(B2-C2-D2)/B2. - Net profit margin uses net income after the expenses included in your chosen accounting view, often including interest and taxes:
=(Revenue-COGS-OperatingExpenses-Interest-Taxes)/Revenue, or simply net income divided by revenue if net income is already calculated.
If you allocate fixed overhead, advertising, or other shared expenses to individual products, label the resulting measure accordingly; it is not the same as a product’s direct-cost gross margin. Accounting classifications vary with purpose and business practice, so use consistent definitions and reconcile the spreadsheet to your accounting records.
Prevent division-by-zero and missing-data problems
If revenue is zero or blank, dividing by it can produce #DIV/0!. A zero-revenue row does not have a meaningful ordinary margin, so do not automatically treat it as a genuine 0% result. Choose a visible fallback that suits your report:
=IF(B2=0,"N/A",(B2-C2)/B2)Or leave the result blank when an error occurs:
=IFERROR((B2-C2)/B2,"")Use =IFERROR((B2-C2)/B2,0) only if displaying 0% is appropriate for your reporting purpose. Microsoft documents IFERROR as returning a chosen fallback when its expression produces an error. It can also conceal mistakes, so first check that the plain formula works and that the inputs are correct. For example, a blank cost may be treated like zero in arithmetic and create an unrealistically high margin. To flag missing revenue or cost explicitly, use:
=IF(OR(B2="",C2=""),"Missing data",IFERROR((B2-C2)/B2,""))If Excel rejects the commas separating function arguments, your regional settings may require semicolons instead.
Calculate an overall margin for several products
For the combined business margin, divide total profit by total revenue. If profit is in D2:D100 and revenue in B2:B100, use:
=IFERROR(SUM(D2:D100)/SUM(B2:B100),"")If you do not have a profit column, calculate it from total revenue and cost:
=IFERROR((SUM(B2:B100)-SUM(C2:C100))/SUM(B2:B100),"")This is a weighted overall margin: products with more revenue contribute more to the result. Do not generally use =AVERAGE(E2:E100) for a combined business margin, because it gives a low-revenue product the same weight as a high-revenue one.
For instance, a product with $10 revenue and $9 profit has a 90% margin. Another with $1,000 revenue and $100 profit has a 10% margin. Their simple average is 50%, but their combined margin is ($9 + $100) ÷ ($10 + $1,000), or about 10.79%.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteIf your report is filtered and the overall calculation should include only visible rows, use SUBTOTAL in place of SUM:
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
=IFERROR((SUBTOTAL(9,B2:B100)-SUBTOTAL(9,C2:C100))/SUBTOTAL(9,B2:B100),"")Define revenue and costs before interpreting the answer
A formula cannot decide what belongs in the calculation. State whether revenue means list price, gross sales, net sales after discounts and returns, or another figure from your records. For many sales analyses, net sales are calculated as gross sales minus discounts, returns, and allowances. Use the same basis from one product or period to another.
For example, a sales worksheet might calculate net revenue and contribution margin this way:
| Item | Amount or formula |
|---|---|
| Gross sales | $250 |
| Discounts | $20 |
| Returns | $10 |
| Net revenue | =250-20-10 ($220) |
| Product cost | $120 |
| Payment fee | $7 |
| Shipping | $8 |
| Contribution profit | =220-120-7-8 ($85) |
| Contribution margin | =85/220 (38.64%) |
Payment-processing fees, marketplace commissions, shipping, fulfillment, and overhead may be classified differently depending on the purpose of the report and your accounting practices. Sales tax treatment also depends on jurisdiction and accounting context; follow your accounting system’s definition of revenue rather than assuming every amount collected from a customer is revenue. Keep the spreadsheet definitions consistent with the records you are comparing.
Free tools Windows power users keep installed
One-click scans. No signup required.
If a discount is stored as a percentage, multiply the price by the discount rate to find the discount amount. For example, with price in B2 and discount rate in C2, =B2*C2 returns the discount amount; subtract it from the price to get revenue after discount. Microsoft’s percentage examples cover this distinction between calculating a percentage amount and calculating a ratio.
Negative margins and unusual revenue rows
If costs exceed revenue, the formula correctly returns a negative margin. For example, $100 revenue less $125 cost is a $25 loss, and $25 ÷ $100 is -25%. Do not replace a negative result with zero unless your reporting rules explicitly call for it. To display negative percentages in red, Excel supports custom number formats; for example, 0.00%;[Red]-0.00%. See Microsoft’s guidance on percentage formats.
A negative revenue figure may represent a return, credit, or adjustment rather than an ordinary sale. Consider combining it with the related sale to calculate net revenue, or clearly label it as an adjustment instead of interpreting its row-level margin as a normal product margin.
Common mistakes to check
- Dividing by cost: Profit divided by cost calculates markup, not margin.
- Formatting a whole number: A value of 40 formatted as a percentage becomes 4000%; the ratio should be 0.4 for 40%.
- Leaving cost blank: A missing input can behave like zero and inflate the reported margin.
- Averaging product percentages: Use total profit divided by total revenue for an overall weighted margin.
- Mixing margin types: State whether you included only direct costs, operating expenses, or expenses through net income.
- Counting inconsistent sales amounts: Decide how discounts, returns, fees, shipping, and taxes are handled before comparing periods.
- Hiding errors too soon: Validate the basic formula and data before wrapping it in
IFERROR.
The basic subtraction, division, percentage formatting, and IFERROR approach is supported across current Excel desktop and web editions covered by Microsoft’s help pages. The same arithmetic can be used in other spreadsheet apps, but menus, table features, and some formula details may differ.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
