How to Calculate Gross Profit Margin Percentage with Formula in Excel

If you need to calculate gross profit margin percentage, you can easily do it in excel. Here, we can use a very easy and simple formula to compute the gross profit margin percentage. In this article, we will learn how to calculate the gross profit margin percentage with a formula in Microsoft Excel.


Download Practice Workbook

You can download the workbook and practice with them.


What is Gross Profit Margin Percentage?

Gross profit margin is used to measure a company’s financial condition by estimating the amount of money left over after subtracting the cost of goods from the selling price. We can get the gross profit margin by dividing the selling price from (selling price – cost of goods). It is mainly expressed as a percentage of the below formula shown in the picture.


2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

To calculate gross profit margin percentage with a formula in Excel, we are going to use the following dataset of ABC Company. The dataset contains some products in column B, selling prices in column C, cost of goods in column D to calculate the gross profit margin in column E. So, let’s get into the methods of calculating the gross profit margin in excel.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel


1. Excel Formula to Calculate Gross Profit Margin

The distinction between the Selling Price and the Cost of Goods in relation to the Selling Price is known as the Gross Profit Margin. We can calculate the gross profit margin by using a simple formula. Let’s go through the steps below to calculate the gross profit margin.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

STEPS:

  • Firstly, select the cell where you want to get the gross profit margin. So, we select cell E5.
  • Secondly, write down the formula there.
=(C5-D5)/C5

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

Here, C5 is the selling price and D5 is the cost of goods.

  • Thirdly, press Enter and we will get the Gross Profit Margin as a result.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

  • Now, to add a percentage (%), first choose the values in the Gross Profit Margin column range E5:E10, then go to the Home tab from the ribbon and select the Percent option in the Number group.
  • Or, alternatively, we can also use the keyboard shortcut key ‘Ctrl + Shift + %’.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

  • Now, we can see the result in percentage.

  • After that, drag the Fill Handle down.

  • And, finally, we get the Gross Profit Margin Percentage.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

Related Content: How to Calculate Net Profit Margin Percentage in Excel


Similar Readings:


2. User-Defined Formula Using VBA Code to Find Gross Profit Margin

We are now creating a user-defined function using the Excel VBA to calculate the gross profit margin percentage. Let’s demonstrate the steps below about how we can create the function and use the function in our spreadsheet.

STEPS:

  • In the beginning, go to the Developer tab from the ribbon.
  • After that, click on Visual Basic to open the Visual Basic Editor.
  • Another way to open the Visual Basic Editor is simply to press Alt + F11.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

  • Or, right-click on the sheet, then select View Code.

  • Next, go to Insert and select Module from the drop-down menu.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

  • After that, copy and paste the VBA code below.

VBA Code:

Function GrossProfitMargin(s As Double, c As Double) As Variant
GrossProfitMargin = ((s - c) / s)
End Function
  • Then, save the code by pressing Ctrl + S.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

This will create a function named GrossProfit Margin, and here, s is selling price and c is the cost of goods.

  • Then back to the worksheet and select the cell where we want the gross profit margin result to appear. So, we select cell E5.
  • After that, type the formula we have just created.
=GrossProfitMargin(C5,D5)
  • Press Enter.

Here, C5 is the selling price and D5 is the cost of goods.

  • And, we will get the result in cell E5. The user-defined formula will show in the formula bar.

  • Drag down the Fill Handle over the range of the Gross Profit Margin column.

2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel

  • And, there you go! The result for Gross Profit Margin Percentage is in column E.

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


Conclusion

The has been a guide to the gross profit margin percentage formula in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo