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.

Dataset named Weekly Sales Data of Amigo Super Store to measure profit and loss percentage formula in excel.

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.

Calculate the net amount from the selling price and cost price.

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

Auto-filling the net amount in excel.

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

Net amount for each seller.

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

Calculate the net profit and loss without percentage using the formula in excel.

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

Calculating the net profit and loss and converting them into percentages using the formula in excel.

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

profit and loss published in percentage using a formula in excel.

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

Profit margin formula to calculate profit loss.

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.

Measure profit loss without percentages in Excel by applying a formula.

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

Conversion of profit loss into percentages without formula in Excel.

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

Profit Loss percentages for each seller in Excel using formulas.

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

IF function to measure Profit or Loss based on formulas and percentages in Excel.

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

Combination of ABS and IF functions to write Profit Loss statements including percentages derived from the formula in Excel.

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

Profit Loss statement including percentages derived from the formula using CONCAT function in Excel.

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

Enabling VBA Macro by Right-Clicking of the Mouse at the Top Ribbon.

  • Thirdly, click on Developer and click on the OK button.

Getting the Developer menu in the Top Ribbon.

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

VBA Module to insert VBA code in Excel.

  • 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

VBA code to measure profit loss in percentages.

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

Profit Loss statement based on percentages derived from the formula in Excel.

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.

Use of Conditional Formatting to color loss in the dataset.

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

New Formatting Rule box to measure profit loss visually.

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

Formatting cells with colors.

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

Profit and loss percentage formula in excel.

📕 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

(Visited 32 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo