You finish the month, open a folder full of receipts, and realize you have no idea whether your business actually made money. So you spend a weekend copying numbers from bank statements into a spreadsheet, and the totals still don’t match what you expected.
Every hour spent retyping figures is an hour you’re not serving customers or chasing new work. One misplaced decimal or a forgotten expense can also throw off your tax estimates, and your CPA will notice.
There’s a better way. With a simple transaction log and the SUMIFS function, Excel can build your Profit & Loss (P&L) statement automatically. You enter each transaction once, and the report updates itself.
This tutorial takes about 30 minutes. You’ll need Excel 2016 or later (Microsoft 365 works too).
The Scenario & Dummy Data
Let’s say you own Lone Star Loaves, a small bakery in Austin, Texas. You sell bread and pastries at a farmers market and to a few local coffee shops. You want to know how much you earned in January 2026, after paying for ingredients, rent, and other costs.
Here’s a sample of the transactions you’ll enter. In your own file, you’d have many more rows.
| Date | Description | Category | Type | Amount |
|---|---|---|---|---|
| 01/03/2026 | Farmers market sales | Retail Sales | Income | $1,840.00 |
| 01/05/2026 | Flour and yeast (Restaurant Depot) | Ingredients | Expense | $412.50 |
| 01/08/2026 | Wholesale order, Cedar Street Coffee | Wholesale Sales | Income | $960.00 |
| 01/10/2026 | Butter and eggs | Ingredients | Expense | $268.75 |
| 01/12/2026 | Kitchen rental, shared commissary | Rent | Expense | $850.00 |
| 01/15/2026 | Farmers market sales | Retail Sales | Income | $2,105.00 |
| 01/18/2026 | Paper bags and boxes | Packaging | Expense | $134.20 |
| 01/20/2026 | Electric bill (Austin Energy) | Utilities | Expense | $187.40 |
| 01/24/2026 | Wholesale order, Cedar Street Coffee | Wholesale Sales | Income | $1,020.00 |
| 01/27/2026 | Facebook ad campaign | Marketing | Expense | $95.00 |
| 01/29/2026 | Farmers market sales | Retail Sales | Income | $1,915.00 |
| 01/31/2026 | Liability insurance | Insurance | Expense | $72.00 |
Step 1: Set Up Your Transaction Log
Everything in this tutorial depends on a clean log. Think of it as the single source of truth for your business.
- Open a new workbook and rename the first sheet Transactions. Right-click the tab at the bottom and select Rename.
- In row 1, type these headers in columns A through E:
Date,Description,Category,Type,Amount. - Enter the sample data from the table above, starting in row 2.
- Select column A, then on the Home tab, open the Number Format dropdown and choose Short Date. Format column E as Currency.
[INSERT SCREENSHOT: The Transactions sheet with headers in row 1 (Date, Description, Category, Type, Amount) and the first six sample rows entered, with column E displaying dollar signs]
Next, turn the range into an Excel Table. Click any cell in your data, press Ctrl + T, check My table has headers, and click OK. Then, on the Table Design tab, rename the table to Txns in the Table Name box.
Why use a Table?
A regular range stays the same size until you change it by hand. A Table grows automatically when you add a new row, and every formula that points to it picks up the new data.
That means you’ll never have to edit a formula in February just because you added more transactions.
[INSERT SCREENSHOT: The Table Design tab in the Excel ribbon with the Table Name box showing “Txns” and the data range highlighted in blue table formatting]
Step 2: Build the P&L Layout
Now create the report itself on a separate sheet. Click the + button next to your sheet tabs and rename the new sheet P&L.
Set up the layout like this:
| Cell | Entry |
|---|---|
| A1 | Lone Star Loaves: Profit & Loss |
| A2 | Month Starting: |
| B2 | 01/01/2026 |
| A4 | INCOME |
| A5 | Retail Sales |
| A6 | Wholesale Sales |
| A7 | Total Income |
| A9 | EXPENSES |
| A10 | Ingredients |
| A11 | Rent |
| A12 | Packaging |
| A13 | Utilities |
| A14 | Marketing |
| A15 | Insurance |
| A16 | Total Expenses |
| A18 | NET PROFIT |
Cell B2 is your control panel. Change that date to 02/01/2026 later, and the whole report switches to February.
Bold the section headings and totals by selecting them and pressing Ctrl + B. Format column B as Currency.
[INSERT SCREENSHOT: The P&L sheet with all labels in column A, the date 01/01/2026 in cell B2, and empty currency-formatted cells in column B]
Step 3: Pull Amounts with SUMIFS
This is the step that saves you the most time. Click cell B5 and enter this formula:
=SUMIFS(Txns[Amount], Txns[Category], $A5, Txns[Date], ">="&$B$2, Txns[Date], "<="&EOMONTH($B$2,0))
Press Enter. You should see $5,860.00 for Retail Sales (that’s $1,840 + $2,105 + $1,915).
Now copy the formula down. Click B5, drag the fill handle (the small square in the bottom-right corner of the cell) down to B6, then B10 through B15. Cells B6 and B10 through B15 will fill in with the matching category totals.
[INSERT SCREENSHOT: Cell B5 selected on the P&L sheet with the full SUMIFS formula visible in the formula bar and the result $5,860.00 displayed in the cell]
How the formula works
SUMIFS adds up numbers, but only the ones that meet every condition you give it. Read the formula above as a plain sentence:
“Add up the Amount column, but only where the Category matches the label in column A, the Date is on or after the start date in B2, and the Date is on or before the last day of that month.”
Here’s what each piece does:
Txns[Amount]is the column being added up.Txns[Category], $A5matches the category name in the label next to it. The$locks column A so it stays put when you copy the formula.">="&$B$2means “on or after the date in B2.” The&joins the symbol to the date so Excel reads them together.EOMONTH($B$2,0)returns the last day of the month for whatever date is in B2. The0means “this same month.”
The $B$2 is fully locked with dollar signs on both the column and the row. Every formula you copy will still point back to your one control cell.
Step 4: Add the Totals and Net Profit
Now for the totals. In cell B7, enter:
=SUM(B5:B6)
In cell B16, enter:
=SUM(B10:B15)
Finally, in cell B18, calculate your bottom line:
=B7-B16
Using the sample data, your results should look like this:
| Line Item | Amount |
|---|---|
| Total Income | $7,840.00 |
| Total Expenses | $1,219.85 |
| Net Profit | $6,620.15 |
Wait, does that look too good? Your total expenses only add up to $1,219.85 because the sample table is short. A real month would include payroll, equipment, taxes, and dozens of smaller costs.
To add a profit margin, type Profit Margin in A19, then enter this in B19:
=IF(B7=0,0,B18/B7)
Format B19 as Percentage on the Home tab.
[INSERT SCREENSHOT: The completed P&L sheet showing Total Income, Total Expenses, Net Profit, and Profit Margin with all dollar amounts filled in]
Why the IF wrapper?
If you open a new month with no sales entered yet, B18/B7 would divide by zero and show a #DIV/0! error. The IF function says, “If income is zero, show 0. Otherwise, do the math.”
Step 5: Make the Report Reusable Every Month
You don’t need to rebuild anything for February. Just keep adding transactions to the Transactions sheet, then go to the P&L sheet and change cell B2 to 02/01/2026.
Every number updates instantly. To keep a record of past months, right-click the P&L tab, choose Move or Copy, check Create a copy, and rename the copy to something like P&L Jan 2026. Then paste values only before you change the date on the original.
For a cleaner look, select your P&L cells and use Home > Cell Styles to add borders and shading. A simple double underline under Net Profit is a classic accounting touch.
Pro-Tip & Troubleshooting
Fix #1: Your totals are $0.00 but you know there’s data
This almost always means Excel is reading your dates as text. Click a date in the Transactions sheet. If it’s left-aligned or has a small green triangle in the corner, it’s stored as text.
To fix it, select the whole Date column, click Data > Text to Columns, then click Finish without changing any settings. Excel will convert the text into real dates.
[INSERT SCREENSHOT: The Text to Columns wizard open on the Data tab with the Date column selected, and a text-formatted date visible with a green triangle in the corner]
Fix #2: A category shows $0.00 when it shouldn’t
Check for typos and trailing spaces. “Ingredients ” (with a space at the end) won’t match “Ingredients.” To prevent this, add a dropdown list: select the Category column, go to Data > Data Validation, choose List under Allow, and type your category names separated by commas.
Pro-Tip: Catch missing categories
Add a check row at the bottom of your P&L. In A20, type Check, then in B20 enter:
=SUMIFS(Txns[Amount],Txns[Type],"Income",Txns[Date],">="&$B$2,Txns[Date],"<="&EOMONTH($B$2,0))-B7
If the result isn’t $0.00, one of your transactions has a category that isn’t on the report. This catches mistakes before your CPA does.
Conclusion
You now have a Profit & Loss statement that updates itself every time you log a transaction and change one date. No more weekends lost to retyping numbers, and far fewer chances for errors.
Start by entering last month’s real transactions into the Transactions sheet. Once you see your actual net profit appear on the report, you’ll have a clearer picture of where your money goes. From there, you can add more categories, track quarterly totals, or share the file with your bookkeeper.