Calculate Profit and Loss Percentage with Formula in Excel

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.

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.

• 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

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.

• 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

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.

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

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.

• 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

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.

• 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

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.

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