How to Calculate Margin Percentage in Excel (5 Easy Ways)

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.

how to calculate margin percentage in Excel

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.

how to calculate margin percentage in Excel

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.