When you are a rising entrepreneur, there will be a few investors who believe in you. In the meantime, you need to do copious tasks to grow your business. One of the greatest important tasks is to calculate the profit loss. Some may use dedicated software to measure and analyze profit loss percentages. On the contrary, using additional software can be a surfeit of money for you. MS Excel is the software that can be used to measure the Profit and Loss percentage using a simple formula without demur in Excel.

Following the article, one can ferret out the calculation of the profit and loss percentage in Excel. We will expound on each and every method, even if the formulas are cryptic to you or you are little more than a dilettante.

## 📁 Download Excel File

To practice please download the Excel file from here:

## Learn to Calculate Profit and Loss Percentage with Formula in Excel with These 7 Examples

In this article, we will construe **7 handy methods** to measure the profit and loss percentage using the formula in Excel. Following that, we consider a dataset named **Weekly Sales Data of Amigo Super Store** consisting of **Seller**, **Timeline**, **Product**, **Cost** **Price**, and **Selling** **Price**. There are also **6 columns** along with **10 rows**. We will learn the simple calculation to measure Profit-Loss, the use of **IF**, **CONCAT** functions, and **VBA** **Macro**. So, Let’s get started.

**method**

### 1. Calculate Profit Margin Percentage from Selling and Cost Price in Excel

By calculating the **Net Income** in a cell and converting the value into percentages in another cell, one can easily measure the Profit-Loss statement. Please check out the following procedure.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, select the
**E6**blank cell. - Secondly, get the net value by subtracting
**C6**from the**D6**cell. Or, input the following formula in the**E6**cell.

**=D6-C6**

- Therefore, obtain the value of
**$700**in the**E6**cell.

- Now, pull down the
**Fill Handle**icon to fill the cells automatically.

- Now, obtain the values in all the
**E6:E10**cells.

- Again, by dividing the
**E6**by**C6**, we obtain the**percentage**on the**decimal**.

- To express in percentages,
**multiply**by**100**. Or, select the**%**option from the**Number**feature of the**Home**tab from the**Top Ribbon**.

- Finally, all the data in
**F6:F10**are expressed in percentages where**positive**remarks the**profit**and**negative**is the symbol of**Loss**.

**📕 Read More: ****Calculate Percentage in Excel Using Absolute Cell Reference**

**method**

### 2. Measure Gross Profit Margin Percentage Using Formula in Excel

By generating a simple formula and referring the cells accordingly, you can calculate Profit-Loss with numbers within cells. First, all you need to learn is the equation to measure profit margin percentage.

Please follow the essential steps below to measure profit margin.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, select cell
**E6**and insert the following formula.

**=(D6-C6)/C6**

- Then,
**auto-filling**the cells, achieve the results containing**positive**and**negative**values.

- To convert into percentages,
**multiply**by**100**. Or, select the**%**option from the**Number**feature of the**Home**tab from the**Top Ribbon**.

- In the end, all the data in
**E6:E10**are expressed in**percentages**where**positive**delineates the**profit**and**negative**is the symbol of**Loss**.

**📕 Read More: ****Calculate Percentage Change with Negative Numbers in Excel**

**method**

### 3. Use IF Formula for Profit and Loss Percentage in Excel

**IF** function generally allows the logical expression between the values and expectations. The use of the **IF** function can be a way to measure Profit and Loss from the cost and selling price in Excel. Please watch out for the following steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, select cell
**F6**to get the statement. - Then insert the following in the
**F6**cell containing the**IF**function.

**=IF(E6>0,”Profit”,”Loss”)**

**🔨 Formula Breakdown **

👉 General** Logical expression** of the formula, **IF(logic,”statement1″,”statement2″)**

👉 Firstly, the value of **E6** is measured and if it is **greater than 0** then it signals to write **Profit** in **F6**, Else it writes **Loss**.

**📕 Read More: ****3 Ways to Add Percentage to Price in Excel with Formula**

**method**

### 4. Apply a Combination of ABS and IF Functions to Calculate Profit Margin in Excel

The implication of a combination of **ABS** and **IF** functions can be a key to calculating profit margin along with a statement. The **ABS** function converts the **negative** values into **positive** ones. On the other hand, the **IF** function makes the statement based a **logic**. Please watch out for the following steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, select cell
**E6**to get the statement along with net income percentages. - Then, write the following formula in the
**E6**cell containing the**ABS**and**IF**functions.

**=ABS((D6-C6)/C6) & IF((D6-C6)/C6>0,”% Profit”,”% Loss”)**

**🔨 Formula Breakdown **

👉 General** Logical Expression** of the formula, **=ABS(formula)&IF(logic,”statement1″,”statement2″)**

👉 Firstly, the **ABS** function eliminates the **negative** values and converts them into **positive **ones.

👉 Secondly, **(D6-C6)/C6** calculation takes place, and if **(D6-C6)/C6 is greater than 0** then the **IF** function denotes to write down **% Profit**; Otherwise, it writes **% Loss** in cell **E6** along with the value obtained from the calculation of **(D6-C6)/C6**.

- Finally, by auto-filling the range
**E6:E10**, we achieve the results of Profit Loss based on gross Net income.

**📕 Read More: ****5 Easy Methods to Add 10 Percent to a Number in Excel**

**method**

### 5. Implement CONCAT Function

As we calculated net income and statement based on the Cost and Selling Price earlier. Using the **CONCAT** function, we can combine the two cells in a cell and get a better overview. Please follow the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Primarily, select cell
**G6**to get the full statement including percentages. - Secondly, type the following formula in the
**G6**cell containing the**CONCAT**function.

**=CONCAT(E6,”% “,F6)**

**🔨 Formula Breakdown**

👉 **Logical Expression** of **CONCAT** function, **=CONCAT(statement1, statement,…..)**.

👉 **CONCAT** function generally connects the cells to one another. Collecting the value from **E6** and **F6**, the function gathers data in the **F6** cell.

- Therefore, by pressing
**Enter**on cell**G6**and**auto-filling**till cell**G10**, we obtain the results of profit Loss including the**percentages**.

**📕 Read More: ****3 Quick Ways to Add 20 Percent to a Price in Excel**

**method**

### 6. Use VBA Macro to Create Profit Calculator Template in Excel

Generating a **VBA code**, one can calculate automatically just by putting values in the Excel cells. By getting the **Developer** option in the **Top Ribbon**, you can generate a program on your own. Please follow the necessary steps below.

**⬇️⬇️ STEPS ⬇️⬇️**

- Check first if your
**Developer**option is available or not. - Secondly, to enable the
**Developer**option,**Right-Click**on the mouse at the**Top Ribbon**and select**Customize the Ribbon**.

- Thirdly, click on
**Developer**and click on the**OK**button.

- Now
**Developer**mode is on. - Then, click on the
**Visual Basic**option**.** - Next, click on
**Module**from the**Insert**menu to insert the**VBA**code.

- Now, write the following code in the
**module**and hit on the**Run**icon to get the margin and statement.

__Code__

```
Sub ProfitLoss()
Dim a As Integer
Dim m As Integer
Dim n As Integer
Dim net As Single
For a = 6 To 10
m = Cells(a, 3).Value
n = Cells(a, 4).Value
net = (n - m) / m
Cells(a, 5).Value = net
If Cells(a, 5).Value > 0 Then
Cells(a, 6).Value = "Profit"
Else: Cells(a, 6).Value = "Loss"
End If
Next
End Sub
```

- Therefore, we obtain the
**Net**income and statement based on**Profit**/**Loss**for each seller automatically.

**method**

### 7. Utilize of Conditional Formatting Feature to Measure Profit and Loss Percentage in Excel

Suppose your boss asks to highlight the loss measured from the selling price and cost price. How can you do that? Well, Selecting cells one by one is not sufficient for bulk data. Yes,** Conditional Formatting** can be a lifesaver in this case. Please follow the necessary steps below.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select the range
**E6:E10**where net percentages are calculated. - Then select
**New Rule**from the**Conditional Formatting**feature of the**Home**tab.

- Next, a box named
**New Formatting Rule**appears. - After that, select
**Format only****Cells****that Contain**from the**Select New Rule Type**section. - Further select
**Cell value**and**Less than**from the**Format only Cells with**option. - Also, Insert
**0**in the cell and click on the**Format**button.

- Thus, another cell named
**Format Cells**appears. - From the
**Fill**menu, select the**color**and hit the**OK**button.

- Therefore, all the cells get colored that showed Loss percentage in the
**E7**,**E8**, and**E10**cells.

**📕 Read More: ****Calculate Percentage in Excel Based on Conditional Formatting**

## 📄 Important Notes

🖊️ Enable the** Developer** menu first to execute** VBA** code.

🖊️ Watch your **cell reference**, **logic**, and **formula** carefully during the use of **ABS** and **IF** functions combination.

🖊️ **Conditional Format** feature to color the cells once under a single criterion at an operation.

## 📝 Takeaways from This Article

📌 General formula to calculate profit margin in a cell from cost and selling price.

📌 Use of **Conditional Format** feature to color the cells under criteria.

📌 **IF **function to measure Profit or Loss based on cell values.

📌 **VBA macro** to use as a calculator in case of measuring profit margin.

📌 **CONCAT ** function to write in a cell from two or multiple.

📌 **ABS **function converts the negative value into a positive.

## Conclusion

In this article, we construe various formulas to calculate profit and loss percentages in Excel. I hope you enjoyed your learning and will be able to differentiate profit margin from cost price. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit **www.ExcelDen.com**.

## Related Articles

**3 Easy Ways to Calculate Accuracy Percentage in Excel****4 Easy Ways to Calculate Percentage in Excel Based on Cell Color****6 Ways to Forecast Growth Rate in Excel****4 Simple Ways to Calculate Forecast Accuracy Percentage in Excel**