Profit Margin Formula in Excel: Gross Margin, Markup and Pricing (Free Calculator)

The profit margin formula in Excel is =(Price-Cost)/Price, which gives gross profit as a share of the selling price. Markup uses the same profit but divides by cost: =(Price-Cost)/Cost. A product that sells for 24.00 and costs 11.50 has a 52.1% margin and a 108.7% markup.

The free Profit Margin & Markup Calculator (Excel) is below, with every formula in this guide already built in.

What the calculator does and who it is for

The workbook has two sheets: ‘How to use’ and ‘Margin Calculator’. You enter a product name, selling price, unit cost and units sold for up to 15 products. The sheet returns gross profit per unit, gross margin %, markup %, total revenue and total gross profit for each one.

It suits shop owners, freelancers who sell products or packages, and anyone who needs to check prices before a supplier conversation or a promotion. It also includes a price-for-target-margin tool and a margin vs markup reference table, so you can work forwards from a price or backwards from a margin you want.

If you only need the answer to how to calculate profit margin for one item, the two formulas above are enough. The template earns its place when you have several products and want to see which ones carry the business and which ones barely pay their way.

Margin vs markup in one minute

Margin divides profit by price. Markup divides profit by cost. Because the price is larger than the cost, margin is always the smaller number for the same product.

A 50% markup is only a 33.3% margin. Mixing the two up is the most common pricing mistake, and it quietly cuts your profit. If you want to know how to calculate profit margin correctly, always divide by the selling price.

The question of markup vs margin comes up in real conversations all the time. A supplier may talk about adding a percentage to cost, while your accountant or your own targets talk about the share of each sale you keep. Both are valid measures. They answer different questions, so you need to know which one a number refers to before you act on it.

Measure	Formula	Divides profit by
Gross margin %	=E8/B8	Selling price
Markup %	=E8/C8	Unit cost

Build the calculator step by step

You can follow these steps in a blank workbook, or open the download and read the formulas in place. Either way, the layout is the same: inputs on the left, calculated columns to the right, totals at the bottom.

Step 1: Set up the product table

On the ‘Margin Calculator’ sheet, put the headers in row 7. Use A for product, B for selling price, C for unit cost and D for units sold. Products go in rows 8 to 22, which gives you room for 15. Columns A to D are the only ones you type into.

Keeping inputs in four columns and everything else calculated means you never overwrite a formula by accident. If a number looks wrong, check the inputs first.

Step 2: Gross profit per unit

In E8 enter:

=IF(A8="","",B8-C8)

This subtracts unit cost from selling price. The IF wrapper keeps the cell blank when no product name is entered, so empty rows stay tidy. Fill down to E22.

Step 3: Gross margin formula in Excel

In F8 enter:

=IF(OR(A8="",B8=0),"",E8/B8)

This is the gross margin formula in Excel: gross profit divided by selling price. The check for B8=0 stops a divide-by-zero error if a price has not been entered yet. Format column F as a percentage and fill down to F22.

Step 4: Markup formula in Excel

In G8 enter:

=IF(OR(A8="",C8=0),"",E8/C8)

This is the markup formula: gross profit divided by unit cost. It guards against a zero cost in the same way as the margin formula. Format column G as a percentage too, then fill down to G22.

Place columns F and G side by side, as the template does. Reading the two percentages next to each other is the quickest way to build a feel for how they differ.

Step 5: Revenue and total gross profit

In H8 enter =B8*D8 for total revenue. In I8 enter =E8*D8 for total gross profit. Fill both down to row 22.

These two columns show which products matter most in money terms. A product with a high margin and tiny volume may add less gross profit than one with a modest margin and strong sales.

Step 6: Totals and overall margin

Row 23 holds the totals. Enter =SUM(H8:H22) in H23 and =SUM(I8:I22) in I23. Then in F23 enter:

=IF(H23=0,0,I23/H23)

This gives the overall margin across all products, weighted by what you actually sold. It is not an average of the individual margins, which would treat a slow seller and a best seller as equals.

Step 7: Add data bars

Select F8:F22 and apply data bars from the Conditional Formatting menu. The bars make it easy to spot your weakest and strongest margins at a glance, without reading every percentage.

Worked example with the sample figures

Row 8 is ‘Bag of coffee 1 kg’. It sells for 24.00 and costs 11.50. Here is how each column gets its result:

• Gross profit per unit (E8): 24.00 – 11.50 = 12.50
• Gross margin (F8): 12.50 / 24.00 = 52.1%
• Markup (G8): 12.50 / 11.50 = 108.7%

The same 12.50 of profit produces two very different percentages. Neither is wrong. The 52.1% says that a little over half of each sale is left after the product cost. The 108.7% says that you charge a bit more than double what the item cost you.

Across all 15 sample products, total revenue in H23 is 30,280 and total gross profit in I23 is 16,139. The overall margin in F23 is 16,139 / 30,280 = 53.3%.

Once the table is full, sort your eyes down column F. Products with the shortest data bars are the ones to look at first. You might raise the price, negotiate the unit cost, or decide the product is there to bring customers in and accept the lower margin on purpose.

Price for a target margin

The block at K5:L9 works backward from the margin you want. Enter a unit cost in L6 (the sample uses 40) and a target margin in L7 (the sample uses 35%). Then L8 holds:

=IF(L7>=1,"Margin must be below 100%",L6/(1-L7))

With the sample inputs, the price is 61.54. The IF check returns a message instead of a nonsense number if you type a margin of 100% or more, since no price can leave you more than the whole sale.

Cell L9 shows the equivalent markup with =L7/(1-L7), which is 53.8% here. In other words, a 35% margin on a cost of 40 needs a markup of 53.8%, not 35%.

The common error is to add the target margin percentage to cost as if it were a markup. That gives a price that falls short of your target margin. Dividing cost by 1-margin avoids this, and the tool does it for you.

Cell	What it holds	Sample value
L6	Unit cost	40
L7	Target margin	35%
L8	Price needed	61.54
L9	Equivalent markup	53.8%

Margin to markup reference table

Cells K13:L19 convert common margins to the markup needed, using =K13/(1-K13). Keep it handy when a supplier quotes in markup and your targets are in margin, or the other way round.

Margin	Markup needed
10%	11.1%
20%	25%
25%	33.3%
30%	42.9%
40%	66.7%
50%	100%
60%	150%

Notice how the gap widens as margins rise. At 10% the two measures are close, at 50% the markup is double the margin, and at 60% the markup is 150%. This is why high-margin businesses can sound as if they are charging enormous markups even when the margin looks ordinary.

How to adapt it

• Replace the sample products in A8:D22 with your own. Clear the old values rather than deleting rows, so the formulas stay intact.
• Need more than 15 products? Insert rows inside the table, above row 22, copy the formulas down, and check that the sums in row 23 still cover every row.
• Include packaging, card fees or delivery in unit cost if you want a margin that reflects what each sale really leaves you with. Decide the rule once and apply it to every product, otherwise the comparison is not fair.
• Use one currency throughout. The template does not convert anything, so prices and costs should be in the same unit.
• For services, treat the direct cost of delivering one job or package as the unit cost, and the number of jobs as units sold.
• Use the target price block each time you quote a new item. Type in the cost, try a few target margins in L7, and see what price each one needs.
• Once you know your product margins, carry them into a 12-month profit and loss template to see what is left after overheads.
• Check that your prices also leave enough cash. A cash flow forecast template shows timing, which margin alone does not.

Common mistakes

• Treating markup as margin. A 50% markup is a 33.3% margin, not 50%.
• Dividing by cost when you mean margin. Margin always uses the selling price as the base.
• Averaging the margin column to get an overall margin. Use total gross profit divided by total revenue, as in F23.
• Leaving a price or cost at zero. The formulas return blanks for these rows, so a missing result usually means a missing input.
• Typing percentages as whole numbers. In L7, enter 35% or 0.35, not 35.
• Forgetting that gross margin ignores overheads such as rent and wages. A healthy gross margin does not mean the business as a whole is profitable.
• Leaving the product name blank. Because the formulas check column A, a row with a price and cost but no name will show nothing in columns E to G.
• Formatting the result cells as numbers instead of percentages, which makes 0.521 look like it is missing a step.

Excel vs Google Sheets

All the formulas work in both. IF, OR and SUM behave the same way, and you can upload the file to Google Drive and open it in Sheets.

Data bars are the one difference. Excel has them under Conditional Formatting, while Google Sheets has no direct equivalent, so you would use a color scale on column F instead. Check that percentage formats carried over after you upload.

If you build the sheet from scratch rather than using the download, the steps are the same in both programs. Type the formula in the first row, then drag the fill handle down. In Sheets, you can also double-click the handle to fill to the end of the neighboring data.

Next step

Download the file, replace the sample products with your five best sellers, and compare column F against column G. Then try a target margin in L7 for your next quote. If you are new to the site, the start here guide shows where to go next, and the free templates page lists the rest. If you want a fuller reporting layout, the Small Business Profit and Loss Template in Excel is a paid option.

Education only. Not financial, tax or investment advice.