Build a Profit and Loss Statement in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
To build a profit and loss statement in Excel, keep every transaction in one list with a Category column. On a separate sheet, SUMIF totals each category, such as =SUMIF(A:A,"Sales",C:C). Revenue minus cost of goods gives gross profit; minus operating expenses gives operating profit. Divide by revenue for margins and compare with last month.
Key takeaways
- Record transactions once, with a category, and let SUMIF build every line of the statement.
- Gross profit = revenue − cost of goods sold; operating profit = gross profit − operating expenses.
- Margin = profit ÷ revenue, formatted as a percentage.
- % change vs last month = (this month − last month) ÷ last month.
On this page+
A profit and loss statement in Excel shows how much a business earned in a period, what it spent and what was left as profit. It's also called a P&L or an income statement. The best way to build one is to keep a single list of transactions and let SUMIF total each line. This guide builds a one-month P&L for a small shop, with margins and a comparison with last month.
How a profit and loss statement is structured#
A P&L runs from revenue at the top to profit at the bottom. Each step subtracts one kind of cost:
| Line | What it means |
|---|---|
| Revenue | Money earned from sales |
| Cost of goods sold (COGS) | The direct cost of what you sold, such as stock |
| Gross profit | Revenue − COGS |
| Operating expenses | Running costs: rent, wages, marketing, utilities |
| Operating profit | Gross profit − operating expenses |
A full statement goes on to subtract interest and tax to reach net profit. For a small business or a job test, operating profit is usually enough.
Step 1: Keep a transaction list#
Record each transaction once, on its own sheet called Transactions. Every row needs a category that matches a line on the P&L.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Description | Amount |
| 2 | Sales | Store orders | 8,400 |
| 3 | Cost of goods | Stock purchase | 4,000 |
| 4 | Sales | Wholesale order | 6,250 |
| 5 | Rent | Shop rent | 1,800 |
| 6 | Cost of goods | Stock purchase | 2,720 |
| 7 | Sales | Online orders | 2,150 |
| 8 | Wages | Staff pay | 4,200 |
| 9 | Marketing | Social ads | 900 |
| 10 | Utilities | Power and internet | 380 |
Enter all amounts as positive numbers; the P&L formulas decide what to add and what to subtract. Use a drop-down list (Data > Data Validation > List) for the Category column, so "Sales" is never typed as "sale" or "Sales " with a space. If you already track spending, the expense tracker in Excel uses the same kind of list.
Step 2: Total each line with SUMIF#
SUMIF adds the numbers in one range where another range matches a condition. Its order is: range to check, what to match, range to add.
On a sheet called P&L, put the line names in column A and SUMIF in column B. For revenue in B4:
=SUMIF(Transactions!$A$2:$A$10,A4,Transactions!$C$2:$C$10)With "Sales" in A4, the result is 16,800 (8,400 + 6,250 + 2,150). Pointing at A4 instead of typing "Sales" means you can fill the same formula down for every category. The $ signs keep the ranges fixed as you fill. The SUMIF function guide covers every argument, and you can practise SUMIF in a live sheet.
Step 3: Build the statement#
Lay out the P&L with SUMIF for each category and simple subtraction for the profit lines:
| A | B | |
|---|---|---|
| 4 | Sales | 16,800 |
| 5 | Cost of goods | 6,720 |
| 6 | Gross profit | =B4-B5 → 10,080 |
| 7 | Rent | 1,800 |
| 8 | Wages | 4,200 |
| 9 | Marketing | 900 |
| 10 | Utilities | 380 |
| 11 | Total operating expenses | =SUM(B7:B10) → 7,280 |
| 12 | Operating profit | =B6-B11 → 2,800 |
Rows 4, 5 and 7 to 10 all use the same SUMIF, filled down. Bold the profit lines and add a top border so they stand out.
A useful check: every transaction should land on exactly one line. Compare =SUM(Transactions!C2:C10) with =B4+B5+SUM(B7:B10). Both give 30,800. If the transaction total is bigger, a row has a category the P&L doesn't list, often a typo like "Utilites".
Step 4: Add margins#
A margin shows profit as a share of revenue, so you can compare months or businesses of different sizes. In C6:
=B6/$B$4Gross margin is 0.6, or 60% once formatted as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%). Fill the formula to C12 for operating margin: 2,800 ÷ 16,800 = 16.7%. The $ keeps every row dividing by revenue in B4. The guide to the profit margin formula in Excel explains margin versus markup, which people often mix up.
Step 5: Compare with last month#
A P&L means more next to the previous period. Put last month's figures in column D and the percent change in column E. For revenue, if last month was 15,000, in E4:
=(B4-D4)/D4The result is 12%: (16,800 − 15,000) ÷ 15,000. If last month's operating profit was 3,200, operating profit fell 12.5% this month, even though revenue rose. The P&L shows why: costs grew faster than sales.
Two cautions with percent change:
- If last month is 0, the formula shows #DIV/0!. Wrap it in IFERROR:
=IFERROR((B4-D4)/D4,""). - For costs, an increase is bad news. Some reports color cost increases red and revenue increases green to make that clear.
The percentages lesson practises percent change and percent of total step by step.
Step 6: Show several months side by side#
To show January, February and March in columns, add a Date column to the transactions and switch to SUMIFS, which takes several conditions. With dates in column D of the transactions sheet and the month's first day in row 3 of the P&L:
=SUMIFS(Transactions!$C:$C,Transactions!$A:$A,$A4,Transactions!$D:$D,">="&B$3,Transactions!$D:$D,"<="&EOMONTH(B$3,0))This adds amounts for the category in A4 with dates from the first to the last day of the month in B3. The mixed references, $A4 and B$3, let one formula fill across months and down categories.
Where P&L skills come up at work#
Reading and building a P&L is common in finance, retail and sales roles. The articles on Excel skills for retail jobs and Excel skills for sales operations show where SUMIF summaries and margins appear in day-to-day work. For a stock-based business, link cost of goods to an inventory tracker.
Practise it#
Do the exercise on this page: total the month's revenue with SUMIF. Then practise SUMIF in a live spreadsheet to build every line of the statement, and practise percentages and percent change for margins and month-on-month comparisons.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Total the revenue for the month. In F2, use SUMIF to add the Amount (C2:C8) for rows where the Category (A2:A8) is Sales.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Amount | ||||||
| 2 | Revenue | ||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 | |||||||
| 11 |
Tip: SUMIF goes range to check, what to match, range to add: the Category column, "Sales", then the Amount column.
Practise this in a lesson
Frequently asked questions
How do I make a profit and loss statement in Excel?
List your transactions with a category for each, then use SUMIF to total each category on a summary sheet. Subtract cost of goods sold from revenue for gross profit, then subtract operating expenses for operating profit.
What is the difference between gross profit and net profit?
Gross profit is revenue minus the direct cost of what you sold. Net profit goes further and also subtracts operating expenses, interest and tax. A simple statement often stops at operating profit.
How do I calculate profit margin in Excel?
Divide the profit by revenue and format the result as a percentage: =B10/B4. A gross profit of 10,080 on revenue of 16,800 is a 60% gross margin.
How do I show a P&L by month in Excel?
Add a Date column to your transactions and use SUMIFS with a category and a date range, for example dates from the first to the last day of the month. Put one month in each column.