How to Calculate Markup in Excel

Markup Percent Calculations
Image Credit: Image courtesy of Microsoft

To use Excel 2013 to compute markup as a percentage of wholesale cost, create a formula that divides the markup amount by the cost.

Computing Markup Percent

Step 1

Enter Cost and Price
Image Credit: Microsoft

Enter the wholesale cost and retail price for each item into a worksheet.

Step 2

Markup Percent Formula
Image Credit: Microsoft

Compute the markup percent by writing a formula that divides the difference between price and cost by the cost. This can be visualized as (Price-Cost)/Cost. For example, if cost is $10 and price is $12, then the markup amount is $2 ($12-$10) and the markup percent is 20 percent ($2/$10).

In Excel, assuming the cost for the first item is stored in C7, the price is stored in D7, and the markup percent needs to be computed in cell E7, enter =(D7-C7)/C7 into cell E7.

Press the Enter key on your keyboard and confirm the formula returns the expected markup percent.

Step 3

Format Cells
Image Credit: Microsoft

If the markup percent cell is not formatted as a percentage, the displayed value may be 0.2 instead of the desired 20 percent. If this is the case, change the cell format by selecting the formula cell and then the Home > Format > Format Cells icon to open the Format Cells dialog box. Change the formatting to Percentage and specify the number of decimals to display.

Step 4

Markup Percent
Image Credit: Microsoft

Fill the markup percent formula down through the range by copying the formula cell and pasting it into the remaining cells in the range. Excel updates the markup percentage when the cost or price values change.

Computing Retail Sales Price

Step 1

Cost and Markup Percent
Image Credit: Microsoft

If you know the cost and markup percentage, you can write an Excel formula that computes the retail sales price. Enter the cost and markup percent for each item into a worksheet.

Step 2

Price Formula
Image Credit: Image courtesy of Microsoft

The price is equal to the cost plus the markup amount. The markup amount is computed by applying the markup percent to the cost. For example, if the cost is $10 and the markup is 20 percent, the sales price of $12 is determined by adding the cost of $10 to the markup amount of $2 ($1020%). Assuming the cost is stored in cell C7, the markup percent is stored in D7 and the price will be computed in E7, enter **=C7+(C7D7)** into E7.

Step 3

Retail Prices
Image Credit: Image courtesy of Microsoft

Fill the formula down and confirm that Excel has computed the expected sales prices.

Tip

In addition to filling the formula down by copying and pasting, you can also use several alternatives:

  • Select the formula cell and then double-click the fill handle -- the little square in the lower right corner of the cell.
  • Select the formula cell and then click and drag the fill handle.
  • Select the formula cell and the remaining cells and then use the Home > Fill > Down command.
  • Select the formula cell and the remaining cells and press Ctrl-D.

In addition to computing price with the Cost+(Cost*Markup) formula, you can use the equivalent Cost*(1+Markup) formula.

If you have a retail sales spreadsheet, you can include the cost data and use the markup formula to determine the markup percent.

resources