Gross profit is revenue minus cost: =B1-B2. Gross profit margin is that profit as a share of revenue: =(B1-B2)/B1, or =B3/B1 if the profit is already in B3.
Format the result cell as a percentage with Ctrl+Shift+%, or you’ll see 0.4 instead of 40%. The distinction worth getting right is margin versus markup — they use the same profit figure but a different denominator. Margin divides by the selling price (=(Price-Cost)/Price), markup divides by the cost (=(Price-Cost)/Cost).
An item bought at 60 and sold at 100 has a 40% margin but a 67% markup, so quoting one when you mean the other misprices the product. To work backwards from a target margin to a price, use =Cost/(1-Margin) — a 40% margin on a cost of 60 needs a price of 100. And guard against a zero revenue with =IFERROR((B1-B2)/B1,0), since dividing by zero returns #DIV/0!.
In Excel, if you want to calculate the gross margin (ratio of the profit margin), you need a formula. And in this tutorial, we will learn to write it.
Write the Formula to Get the Gross Profit (Margin and Ratio)
Below are the steps to write this formula:
- First, enter the equal to (=) operator in a cell and type a starting parentheses.
- After that, refer to cell B1 where you have the Revenue.
- Next, enter the minus operator, and refer to the Cost and Expenses (B2).
- Ultimately, enter the divide operator and refer to the revenue cell again. And hit enter to get the result.
The moment you hit enter, it returns the gross profit percentage in the result.
=(B1-B2)/B1
=(Revenue – Cost) / RevenueYou can also calculate the gross profit in one cell and the margin in another cell.
In the below example, we have separated profit and profit margin percentages in two different cells.
Gross Profit
=Revenue – Cost
=B1-B2Gross Profit Margin
= Gross Profit / Revenue
=B3/B1
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.