Excel guide · 6 min read
How to make a profit and loss statement in Excel
=SUM($K6:K6) filled right gives year to date.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Account | Debit | Credit | Type | Account | Type | Line | Aug 1, 2026 | Sep 1, 2026 | ||
| 2 | Aug 1, 2026 | Rent | 1100 | Expense | Sales | Revenue | Revenue | 4200 | 4920 | |||
| 3 | Aug 3, 2026 | Sales | 4200 | Revenue | Shipping income | Revenue | COGS | 1350 | 1860 | |||
| 4 | Aug 5, 2026 | Materials | 1350 | COGS | Materials | COGS | Gross profit | 2850 | 3060 | |||
| 5 | Aug 14, 2026 | Advertising | 260 | Expense | Packaging | COGS | Operating expenses | 3249 | 2989 | |||
| 6 | Aug 20, 2026 | Equipment | 2400 | Asset | Rent | Expense | Net profit | -399 | 71 | |||
| 7 | Aug 29, 2026 | Wages | 1800 | Expense | Wages | Expense | Net margin | -0.095 | 0.014430894309 | |||
| 8 | Aug 31, 2026 | Software | 89 | Expense | Advertising | Expense | YTD net profit | -399 | -328 | |||
| 9 | Sep 1, 2026 | Rent | 1100 | Expense | Software | Expense | ||||||
| 10 | Sep 2, 2026 | Sales | 5100 | Revenue | Equipment | Asset | ||||||
| 11 | Sep 8, 2026 | Materials | 1620 | COGS | Loan | Liability | ||||||
| 12 | Sep 15, 2026 | Sales | 180 | Revenue | ||||||||
| 13 | Sep 18, 2026 | Packaging | 240 | COGS | ||||||||
| 14 | Sep 26, 2026 | Wages | 1800 | Expense | ||||||||
| 15 | Sep 30, 2026 | Software | 89 | Expense | ||||||||
| 16 | Sep 30, 2026 | Loan | 500 | Liability | ||||||||
| 17 | Oct 1, 2026 | Rent | 1100 | Expense | ||||||||
| 18 | Oct 4, 2026 | Sales | 6350 | Revenue | ||||||||
| 19 | Oct 9, 2026 | Materials | 1980 | COGS | ||||||||
| 20 | Oct 12, 2026 | Packaging | 310 | COGS |
Download this sheet as an Excel practice file (.xlsx)
Start from ledger lines and a chart of accounts
A profit and loss statement (P&L, or income statement) is a summary of the ledger: everything earned in a period minus everything it cost. So don't type the totals. Export the ledger detail from your bookkeeping software, or use the journal you keep in Excel, and let formulas add it up. The sample in A:D is three months of a small print studio: date, account, debit and credit.
The ledger doesn't say which lines belong on a P&L, so add a small chart of accounts (G:H) that gives each account a type. Revenue, COGS (the direct cost of what you sold) and Expense go on the P&L. Asset and Liability accounts don't: they belong on the balance sheet. Which accounts count as COGS and which as expenses is a classification choice for you or your accountant; the formulas work the same either way.
Column E looks each line's account up in the chart. The fourth argument of XLOOKUP returns "Missing" for an account that isn't in the chart, so a new account added in your software can't silently drop out of the P&L.
Result: Asset
Asset: the 2,400 printer bought on 20 Aug is equipment, not a cost of the month, so it stays off the P&L. In Excel 2019 or earlier, use =IFERROR(VLOOKUP(B6,$G$2:$H$11,2,FALSE),"Missing").
One column per month, driven by a header date
Type the first day of each month in the header row (1 Aug, 1 Sep, 1 Oct in K1:M1) and format them as mmm yyyy so they read as months. Every number in the grid is then a SUMIFS between that date and the end of the month. EOMONTH(K$1,0) is the last day of the month; adding 1 and using "<" catches the whole last day even when the export has times in its dates.
Revenue sits on the credit side, so a revenue cell is credits minus debits. That subtraction matters: the 180 refund on 15 Sep was posted as a debit to Sales, and a formula that only added credits would overstate September by 180.
Result: 4920
4,920 for September: 5,100 of sales less the 180 refund. In the grid (L2) the ranges are locked with $ and the header row with K$1, so one formula fills across every month.
Why not one SUMIFS per account?
You can, and it's useful for a detailed P&L with a row per account (put the account name in column J and use it as the criteria instead of the type). Summing by type keeps the statement short and means a new account only needs a row in the chart of accounts, not a new row in the P&L.
COGS, gross profit, expenses and net profit
Costs sit on the debit side, so COGS and operating expenses are debits minus credits: the same formula with the two SUMIFS swapped and "COGS" or "Expense" as the type. A supplier credit note posted as a credit then reduces the cost, as it should.
The profit rows are plain subtraction: gross profit = revenue - COGS, net profit = gross profit - operating expenses. Keep those as formulas on the rows above, never typed numbers, so a late ledger entry flows all the way down.
Result: 3289
3,289 of operating expenses in October: rent 1,100, wages 2,100 and software 89. October's revenue is 6,625 and COGS 2,290, so gross profit is 4,335 and net profit 1,046.
What's the net margin?
Net profit divided by revenue. Wrap it in IF so a month with no revenue shows 0 instead of #DIV/0!.
Result: 0.15788679245 shows as 15.8% with Percent format
0.1579: about 15.8% of October's revenue was left as profit. August, with a net loss of 399, shows a negative margin.
Year to date with a growing range
A year-to-date row adds every month from the first column to the current one. Lock the start of the range and leave the end relative: =SUM($K6:K6) in the first month becomes =SUM($K6:L6) one column to the right and =SUM($K6:M6) in October.
Here the studio lost 399 in August, made 71 in September and 1,046 in October. The YTD row shows the climb out: -399, -328, then 718.
Result: 718
718 of net profit for August to October. The same pattern works on any row: =SUM($K2:M2) is year-to-date revenue.
My financial year doesn't start in January
Start the header row at the first month of your financial year. The growing range starts at the first column, so YTD always means "since the start of this sheet", whichever month that is.
Tie the P&L back to the ledger
Two check cells make the statement trustworthy. First, the net profit across all months should equal every P&L-type line in the ledger, credits minus debits. If someone types over a cell in the grid or a date falls outside the header months, this stops being 0.
Second, count the lines whose type came back "Missing": =COUNTIF(E2:E23,"Missing") should be 0. Anything else is an account to add to the chart of accounts.
- To show losses in red: select K2:M8, then Home > Number Format > More Number Formats > Custom.
- Enter #,##0;[Red]-#,##0 and click OK. Negative months now stand out. For the margin row, use 0.0%;[Red]-0.0%.
Result: 0
0: the three months of net profit match the ledger exactly.
Why aren't the printer and the loan repayment on the P&L?
Buying equipment and repaying a loan's principal move money between balance sheet accounts; they aren't costs of the month. Here they total 2,900. Put them on the P&L and August's loss of 399 would look like 2,799. How equipment is expensed over time is something to agree with your accountant.
Result: 2900
2,900: the 2,400 printer and the 500 loan repayment.
Common mistakes
Adding only credits for revenue
Refunds and corrections are posted as debits to income accounts. Revenue is credits minus debits; costs are debits minus credits.
Putting balance sheet accounts on the P&L
Equipment purchases, loan repayments and owner drawings aren't expenses of the month. Give every account a type and sum only Revenue, COGS and Expense.
Ending the month with "<="&EOMONTH(...)
Exported dates with times fail that test on the last day. Use "<"&EOMONTH(header,0)+1.
Typing totals into the grid
A typed number never updates when a late entry arrives. Keep every cell a formula and use the tie-back check to catch overtyped cells.
FAQ
What is the formula for a profit and loss statement?
Revenue - cost of goods sold = gross profit, and gross profit - operating expenses = net profit. In Excel each line is a SUMIFS on the ledger for that type of account and month.
Does Excel have a profit and loss template?
Excel's File > New gallery has some, and Cellwise has a free small business profit and loss template where you type each line. Building it from the ledger, as above, is better once you have more than a handful of transactions.
How do I make a P&L by month in Excel?
Put the first day of each month in the header row and use SUMIFS with the date between the header date and EOMONTH(header,0)+1. Fill the formula right and down.
How is a P&L different from a cash flow statement?
A P&L counts income and costs in the period they belong to; cash flow counts when money actually moves. An invoice raised in October is October revenue even if it's paid in December.
Functions used
SUMIFSAdds with several conditionsEOMONTHLast day of a monthXLOOKUPModern lookup in any directionVLOOKUPVertical lookupIFERRORFallback when a formula errorsSUMAdds numbersSUMPRODUCTMultiply then addISNUMBERIs it a number?MATCHPosition of a valueCOUNTIFCounts cells that matchIFPicks a result from a test
Related guides
- How to calculate EBITDA in Excel
- How to make a cash flow statement in Excel
- How to make a prepaid expense amortization schedule in Excel
- How to make a balance sheet in Excel
- How to calculate budget vs actual variance in Excel
- Excel test for accounting roles: what it covers and how to pass (or set) one
- How to calculate quota attainment in Excel
Updated