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.

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.

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

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.

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.

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.

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.

• 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 + %’.

• Now, we can see the result in percentage.

• After that, drag the Fill Handle down.

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

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

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.

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

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

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

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.

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

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

Download Practice Workbook

You can download the workbook and practice with them.

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!

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