How to Calculate Gross Profit Margin in Excel (2 Examples)

One of the most useful key performance indicators (KPIs) in a business is the gross profit margin. By calculating the gross profit margin, we get the profit margin we are gaining on the cost of goods sold. Analysts can use this data to find trends in sales and direct costs, and help management make informed decisions. Although Excel does not provide a dedicated function to calculate this, the general formula is very simple. In this article, we will learn how to calculate gross profit margin in Excel.

We have information for a year to calculate the gross profit margin of. There are the revenues and COGS values for twelve months. We will use this information to demonstrate the methods in this tutorial.

➤ First, create two columns to calculate gross profit and gross profit margin.

➤ Calculate the gross profit using the following formula in D2. Then, use the autofill handle to autofill the table till D13:

➤ Now, calculate the gross profit margin in the E2 cell using the following formula and autofill:

➤ The values are shown in decimals. To make them show up as percentages, select E2:E13 cells, and select the percentage (%) icon from the Number group of the Home tab in the ribbon.

➤ You can also press  Ctrl  +  Shift  +  %  after selecting the cells to change the number format.

We already know that Excel does not provide a dedicated function for calculating gross profit margin. However, using VBA, we can create our own custom function to calculate the gross profit margin. Follow the steps below:

➤ Press  Alt  +  F11  to open the Microsoft Visual Basic for Applications.
➤ Go to Insert > Module to open the code editor window.

➤ Insert the following code in the code editor:

Function GrossProfitMargin(Revenue As Double, COGS As Double) As String
    Dim gpm As Double
    If Revenue = 0 Then
        GrossProfitMarginText = "0%"
        Exit Function
    End If
    gpm = (Revenue - COGS) / Revenue * 100
    GrossProfitMargin = Format(gpm, "0") & "%"
End Function

➤ Go back to the worksheet, and add a column for the gross profit margin.
➤ Add the following formula and autofill the table:

Frequently Asked Questions

What is a 30% margin on $100?

For the gross profit margin, if you get a 30% margin on $100, it means that in your $100 of revenue, your profit is $30, and the rest goes for direct costs. You earned 30 dollars from the 100-dollar transaction.

Is a 43% gross margin good?

Usually, a business has a lot of expenses other than the COGS. To cover those costs, a gross margin under 50% is not healthy. Therefore, a 43% gross margin is not good. However, if you are operating in a sole proprietorship, 43% can be enough.

What is the difference between a 30% margin and a 30% markup?

Let’s understand this with an example. If you bought a product for $100 and sold it for $130, it is a 30% markup. If you bought a product for $70 and sell it for $100, that is a 30% margin. Margin is calculated on the selling price, and markup is calculated on the cost.

Which one is better, markup or margin?

There is no better or worse option here; it depends on how the company sets its budget. If the company is going for a markup pricing, it will use markup. However, margin is usually used when setting the marketing budget and how much margin they can gain against the budget they set.

How to convert markup to margin?

Here is the Excel formula to convert markup to margin:
=A1/(1+A1)
Here, A1 is the markup percentage. For example, if A1 contains 25%, Excel will return 20%.

Wrapping Up

In this article, we have learned two methods for calculating gross profit margin in Excel. The Excel file with the formula and the VBA code is available to download for free. Comment down on what topic you want the next tutorial to be. Stay tuned for our next article.