Profit margin formula in Excel
Profit margin is profit divided by the selling price, so with the cost in column A and the price in column B, the formula in C2 is =(B2-A2)/B2.[6] Excel works out a percentage as a part divided by a whole and returns a decimal, 0.4 for 40%, which the Percent Style button then shows as 40%.[1][2]
Fill the formula down the column and every row gets its own margin. Because the formula only uses cells in its own row, the references change with each row and no dollar signs are needed.
Every margin and markup formula in one sheet
Put each value in its own column once and every other number becomes a one-line formula. With the cost in A2, the price in B2 and a target in C2:
| What you want | Excel / Google Sheets formula | Example result |
|---|---|---|
| Profit | =B2-A2 | $40.00 on a $60 cost and $100 price |
| Margin % | =(B2-A2)/B2 | 40% |
| Markup % | =(B2-A2)/A2 | 66.7% |
| Price for a target margin in C2 | =A2/(1-C2) | $100.00 for 40% on a $60 cost |
| Price for a target markup in C2 | =A2*(1+C2) | $90.00 for 50% on a $60 cost |
| Highest cost for a margin at price B2 | =B2*(1-C2) | $70.00 for 30% at $100 |
| Markup from a margin in C2 | =C2/(1-C2) | 66.7% from 40% |
| Margin from a markup in C2 | =C2/(1+C2) | 33.3% from 50% |
The price formulas are the ones people most often get wrong. Adding a margin to the cost (=A2*(1+C2) with a margin in C2) gives a markup, not a margin: a 40% "margin" added to $60 is $84, which is only a 28.6% margin. The margin vs markup guide explains why the two percentages differ, and the margin calculator solves the same cases without a spreadsheet.
How to calculate margin in Excel for a product list
For a list of products, keep one row per item and let each formula refer to its own row. A small example:
| Row | A: Item | B: Cost | C: Price | D: Profit formula | Profit | E: Margin | F: Markup |
|---|---|---|---|---|---|---|---|
| 2 | Mug | $6.00 | $10.00 | =C2-B2 | $4.00 | 40.0% | 66.7% |
| 3 | Lamp | $40.00 | $50.00 | =C3-B3 | $10.00 | 20.0% | 25.0% |
| 4 | Chair | $150.00 | $200.00 | =C4-B4 | $50.00 | 25.0% | 33.3% |
In this layout the margin formula in E2 is =D2/C2 and the markup in F2 is =D2/B2, both formatted as percentages.
Gross margin formula in Excel for a whole list
The margin of a whole list is total profit divided by total revenue, =(SUM(C2:C4)-SUM(B2:B4))/SUM(C2:C4), not the average of the margin column. That is how gross margin is defined on an income statement: gross profit divided by net sales.[7]
In the example, =AVERAGE(E2:E4) returns 28.3%, but the list earns $64 of profit on $260 of sales, a blended margin of 24.6%. The simple average gives the $10 mug the same weight as the $200 chair; the blended formula weights each item by its revenue. If each row also has a quantity in another column, use SUMPRODUCT to multiply price and cost by quantity before dividing. The gross margin calculator does the same blend for up to three product lines.
Formatting margins as percentages
Enter target margins as 40% or 0.4, never as 40. Excel stores a percentage as a decimal, and applying the Percentage format to a cell that already holds 40 multiplies it by 100 and shows 4000%.[2] A target typed as 40 also breaks the price formula: =A2/(1-C2) with 40 in C2 gives a negative price.
- Format result cells with Home › Number › Percent Style before or after entering the formula; the stored value stays a decimal.
- In Google Sheets, use Format › Number › Percent.
- To show losses in red, a custom number format such as
0.0%;[Red]-0.0%works in Excel.[2]
Blank rows and #DIV/0! errors
A row with no price yet divides by zero and shows #DIV/0!. Wrap the formula in IFERROR to show a blank instead: =IFERROR((B2-A2)/B2,"").[3] Google Sheets has the same IFERROR function with the same arguments.[5]
Do not hide real errorsIFERROR also hides typing mistakes, such as text in a price cell. Use it on rows you expect to be empty, and check totals against a few rows worked by hand.
Rounding prices to cents
A price formula like =A2/(1-C2) returns as many decimals as the division produces: a $7 cost at a 35% margin gives $10.769230… Wrap it in ROUND to get a price you can charge: =ROUND(A2/(1-C2),2) returns $10.77.[4] To round up so the margin never falls below the target, use =ROUNDUP(A2/(1-C2),2); to end every price in .99, =CEILING(A2/(1-C2),1)-0.01 rounds up to the next whole dollar and takes off a cent.
Price formula with marketplace fees
If a platform takes a percentage of the price, the fee comes out of the price just like the margin does. With the cost in A2, the target margin in C2, the fee percentage in D2 and any fixed fee per order in E2, the price is =(A2+E2)/(1-C2-D2). A $20 cost, a 30% margin and a 13% fee plus $0.40 gives $35.79. The selling price calculator uses this formula, and the platform calculators such as the eBay fee calculator handle fee tiers and caps that a single percentage cannot.
Check a formula by hand
Before trusting a sheet, work one row on paper: price minus cost, divided by price. If the result is above 100%, the formula divides by the cost (a markup). If it is negative, the cost is above the price or the cells are swapped. The profit margin calculator gives margin and markup for any price and cost to compare against.
Frequently asked questions
What is the formula for margin in Excel?
=(B2-A2)/B2, with the cost in A2 and the selling price in B2, formatted as a percentage. A $60 cost and $100 price return 0.4, shown as 40%.
How do I calculate a selling price from a margin in Excel?
Divide the cost by one minus the margin: =A2/(1-C2), with the margin in C2 entered as 40% or 0.4. Wrap it in ROUND(…,2) to get cents.
What is the markup formula in Excel?
=(B2-A2)/A2, profit divided by cost. To add a markup to a cost, use =A2*(1+C2).
How do I calculate the average profit margin of several products?
Divide total profit by total revenue: =(SUM(price range)-SUM(cost range))/SUM(price range). Averaging the margin column weights every product equally and gives the wrong answer (28.3% instead of 24.6% in the example).
Why does Excel show 4000% instead of 40%?
The cell holds 40, not 0.4. The Percentage format multiplies the stored value by 100. Enter 40% or 0.4 instead.
Sources
- Calculate percentages
- Format numbers as percentages in Excel
- IFERROR function
- ROUND function
- IFERROR function
- Principles of Marketing, 18.3 Retailing Strategy Decisions (markup and gross margin)
- Principles of Accounting, Volume 1: Financial Accounting, Chapter 6 key terms (gross margin, gross profit margin ratio)