Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

If you are searching for ways to calculate margin percentage in Excel, then you are in the right place. Margin is basically the difference between the selling price and costs for the production of this product and it is calculated as a percentage of the selling price. To know more details let’s start our main article.

Download Workbook


5 Ways to Calculate Margin Percentage in Excel

We will use the following dataset of a company containing the details of the selling prices and different costs of various products to calculate different types of margin percentages in Excel. The main 3 types of margin percentages such as the Gross Profit Margin Percentage, Operating Profit Margin Percentage, Net Profit Margin Percentage will be discussed in this article.

how to calculate margin percentage in Excel

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Calculate Margin Percentage in Excel for Gross Profit Margin

Gross Profit Margin is the difference between the Selling Price and the Cost of Goods Sold (Raw material, Labour Cost, etc) with respect to the Selling Price. By using a simple formula we will calculate it in this section.

how to calculate margin percentage in Excel

Steps:
➤ Type the following formula in cell E5

=(C5-D5)/C5

Here, C5 is the Selling Price, D5 is the Cost of Goods Sold.

gross profit margin

➤ Press ENTER and drag down the Fill Handle tool.

gross profit margin

Then, you will get the Gross Profit Margin for the products.

gross profit margin

Now, to add percentage select the values of the Gross Profit Margin column and then select the Percent Style option under the Home tab.
You can also select it using the shortcut key CTRL+SHIFT+%.

gross profit margin

Finally, we will have the Gross Profit Margin Percentages for the products.

how to calculate margin percentage in Excel

Read More: How to Calculate Gross Profit Margin Percentage with Formula in Excel


Method-2: Calculate Margin Percentage in Excel for Operating Profit Margin

Operating Profit Margin is the difference between the Selling Price and the Cost of Goods Sold, Operational Cost (Rent, Equipment, Inventory cost, Advertisement, etc.) with respect to the Selling Price of the final products. To calculate this margin percentage follow this method.

how to calculate margin percentage in Excel

Steps:
➤ Type the following formula in cell F5

=(C5-D5-E5)/C5

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

operating profit margin

➤ Press ENTER and drag down the Fill Handle tool.

operating profit margin

Now, we will get the fraction values of the Operating Profit Margin and add the Percent style to these values.

operating profit margin

After adding the Percent style we are getting the Operating Profit Margin Percentage for the products.

how to calculate margin percentage in Excel

Read More: How to Calculate Net Profit Margin Percentage in Excel


Method-3: Calculate Margin Percentage in Excel for Net Profit Margin

The Net Profit Margin is the difference between the Selling Price and the summation of the Cost of Goods Sold, Operational Cost, Interest, Tax with respect to the Selling Price. In this section, we will try to calculate the Net Profit Margin Percentage.

how to calculate margin percentage in Excel

Steps:
➤ Type the following formula in cell H5

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

Here, 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.

net profit margin

➤ Press ENTER and drag down the Fill Handle tool.

net profit margin

Afterward, we will get the fraction values of the Net Profit Margin and now add the Percent style to these values.

net profit margin

Finally, you will get the Net Profit Margin Percentage values for different products.

how to calculate margin percentage in Excel

Read More: Percentage Formula in Excel (6 Examples)


Similar Readings


Method-4: Using Table Option to Calculate Margin Percentage

Here, we will use the Table option to calculate the Gross Profit Margin of the products in a faster way with the Structured Reference system.

how to calculate margin percentage in Excel

Steps:
➤ Go to Insert Tab >> Table Option.

Table option

Afterward, the Create Table dialog box will appear.
➤ Select the range of your dataset.
➤ Check the My table has headers option and click OK.

how to calculate margin percentage in Excel

Then, we will have the following table.

Table option

➤ Select the cell E5 and start typing the formula

=(C5-D5)/C5

Here, C5 is the Selling Price, D5 is the Cost of Goods Sold.
But, when start to 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]]

Table option

When you will hit ENTER, you will get the Gross Profit Margin values for all of the products automatically and finally add the Percent Style to these values.

Table option

Eventually, we will get the Gross Profit Margin Percentages for the products.

how to calculate margin percentage in Excel

Related Content: Calculate Percentage of a Number in Excel (5 Easy Ways)


Method-5: Using a VBA Code to Calculate Margin Percentage

Here, we will create a function with the help of a simple VBA code, by which you can calculate the Gross Profit Margin Percentage, Operating Profit Margin Percentage, Net Profit Margin Percentage whatever you want.

how to calculate margin percentage in Excel

Steps:
➤ Go to Developer Tab >> Visual Basic Option.

VBA code

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

how to calculate margin percentage in Excel

After that, a Module will be created.

VBA code

➤ Write 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

It will create a function named margin and here s is for Selling Price, c is for Cost of Goods Sold, o is for Operational Cost, i is for Interest and t is for Tax.

We have declared o, i, and t as Optional because without them also you can calculate the Gross Profit Margin and by including o with it you can calculate the Operating Profit Margin and for adding the additional parameters i and t with it, it will turn into the Net Profit Margin.

VBA code

Now, go back to the sheet and write the following formula in cell H5

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

Here, 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.
margin will calculate the Net Profit Margin for this product.

VBA code

➤ Press ENTER and drag down the Fill Handle tool.

VBA code

After that, we will get the fraction values of the Net Profit Margin and now add the Percent style to these values.

VBA code

Finally, you will get the Net Profit Margin Percentage values for different products.

how to calculate margin percentage in Excel

Read More: Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover the ways to calculate margin percentage in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo