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.

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:

• 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 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 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:

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

• 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:

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