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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Calculate Margin Percentage in Excel: 5 Ways

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


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


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


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


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


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


Download Workbook


Conclusion

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo