We will use the following dataset of a company containing the details of the selling prices and different costs of various products. We will calculate the Gross Profit Margin Percentage, Operating Profit Margin Percentage, and Net Profit Margin Percentage.

### Method 1 – Calculate the Gross Profit Margin Percentage

**Gross Profit Margin **is the difference between the *Selling Price *and the *Cost of Goods Sold* (Raw material, Labour Cost, etc), divided by the Selling Price.

** Steps**:

- Use the following formula in cell
**E5:**

`=(C5-D5)/C5`

- Press
**Enter**and drag down the**Fill****Handle**tool.

- You will get the
**Gross Profit Margin**for the products.

- Select the values of the
**Gross Profit Margin**column and choose the**Percent Style**option under the**Home**tab. You can also select it using the shortcut key**Ctrl + Shift + %**.

- Here’s the result.

### Method 1 – Calculate the Operating Profit Margin Percentage in Excel

The Operating Profit Margin percentage is the difference between the Selling Price and the sum of Cost of Goods SoldÂ and Operational Costs (Rent, Equipment, Inventory cost, Advertisement, etc.), divided by the Selling Price of the final products.

** Steps**:

- Use the following formula in cell
**F5**

`=(C5-D5-E5)/C5`

**C5 **is the *Selling Price*, **D5 **is the *Cost of Goods Sold *and **E5 **is the *Operational Cost*.

- Press
**Enter**and drag down the**Fill****Handle**tool.

- Apply the Percent style to the column.

- Here’s the result.

### Method 3 – Calculate the Net Profit Margin Percentage in Excel

The Net Profit Margin Percentage is the difference between the Selling Price and the sum of the Cost of Goods Sold, Operational Costs, Interest, and Tax, all divided by theÂ Selling Price.

** Steps**:

- Use the following formula in cell
**H5**

`=(C5-D5-E5-F5-G5)/C5`

**C5 **is the *Selling Price*, **D5 **is the *Cost of Goods Sold*, **E5 **is the *Operational Cost*, **F5 **is the *Interest *and **G5 **is the *Tax *of the product *Shirt1*.

- Press
**Enter**and drag down the**Fill****Handle**tool.

- Apply the Percent style to these values.

- You will get the
**Net Profit Margin Percentage**values for different products.

### Method 4 – Using the Table Option to Calculate the Margin Percentage

We have the Selling Price and the Cost of Goods in the sample dataset.

** Steps**:

- Go to the
**Insert**tab and select**Table**.

- The
**Create Table**dialog box will appear. - Select the range of the dataset.
- Check the
**My table has headers**option and click**OK**.

- You’ll get a table.

- Select the cell
**E5**and insert the following formula:

`=(C5-D5)/C5`

- When you select the cells
**C5**and**D5**, Excel will convert them automatically to the structured reference system and modify the formula as follows:

`=([@[Selling Price]]-[@[Cost of Goods]])/[@[Selling Price]]`

- Hit
**Enter**, and you will get the**Gross Profit Margin**values for all of the products automatically. - Apply the
**Percent Style**to these values.

- Here’s the result.

### Method 5 – Using VBA Code to Calculate the Margin Percentage

We’ll use the expanded dataset to calculate the margin percentage.

** Steps**:

- Go to the
**Developer**tab and select**Visual Basic**.

- The
**Visual Basic Editor**will open. - Go to
**Insert**and pick**Module**.

- A
**Module**will be created.

- Insert the following code:

```
Function margin(s As Double, c As Double, Optional o As Double, _
Optional i As Double, Optional t As Double) As Variant
margin = ((s - c - o - i - t) / s)
End Function
```

The code will create a custom function named **margin** that uses up to five parameters. We have declared **o**, **i**, and **t **as optional. Without them, you can calculate the **Gross Profit Margin. **By including **o,**Â you can calculate the **Operating Profit Margin**. Adding the additional parameters **i **and **t** will turn it into the **Net Profit Margin **formula.

- Go back to the sheet and insert the following formula in cell
**H5:**

`=margin(C5,D5,E5,F5,G5)`

**C5 **is the Selling Price, **D5 **is the Cost of Goods Sold, **E5 **is the Operational Cost, **F5 **is the Interest and **G5 **is the Tax of the product Shirt1.

- Press
**Enter**and drag down the**Fill****Handle**tool.

- Apply the Percent Style to the column.

- You will get the
**Net Profit Margin Percentage**values for different products.

## Practice Section

We have provided a** Practice** section like below in a sheet named **Practice**.

**Download the Practice Workbook**

**<< Go Back to Formula List | Learn Excel**