**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-D5)/C5`

**C5 i**s 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.

`=GrossProfitMargin(C5,D5)`

- Press
**Enter**.

**C5 i**s 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**