How to Calculate Gross Profit Margin Percentage with Formula in Excel: 2 Methods

Method 1 – Excel Formula to Calculate Gross Profit Margin

STEPS:

• Select the cell where you want to get the gross profit margin. Select cell E5.
• Write down the formula there.

C5 is the selling price, and D5 is the cost of goods.

• Press Enter and we will get the Gross Profit Margin.

• Add a percentage (%), first choose the values in the Gross Profit Margin column range E5:E10, go to the Home tab from the ribbon and select the Percent option in the Number group.
• You can also use the keyboard shortcut key ‘Ctrl + Shift + %’.

• We can see the result in percentage.

• Drag the Fill Handle down.

• Get the Gross Profit Margin Percentage.

Method 2 – User-Defined Formula Using VBA Code to Find Gross Profit Margin

STEPS:

• Go to the Developer tab from the ribbon.
• Click on Visual Basic to open the Visual Basic Editor.
• Another way to open the Visual Basic Editor is simply to press Alt + F11.

• Right-click on the sheet, select View Code.

• Go to Insert and select Module from the drop-down menu.

• Copy and paste the VBA code below.

VBA Code:

Function GrossProfitMargin(s As Double, c As Double) As Variant
GrossProfitMargin = ((s - c) / s)
End Function

• Save the code by pressing Ctrl + S.

This will create a function named GrossProfit Margin, and here, s is selling price and c is the cost of goods.

• The worksheet and select the cell where we want the gross profit margin result to appear. So, we select cell E5.
• Type the formula we have just created.

• Press Enter.

C5 is the selling price and D5 is the cost of goods.

• Get the result in cell E5. The user-defined formula will show in the formula bar.

• Drag down the Fill Handle over the range of the Gross Profit Margin column.

• The result for Gross Profit Margin Percentage is in column E.

Download Practice Workbook

You can download the workbook and practice with them.

<< Go Back to Formula List | Learn Excel