BizCellsHow to make a profit and loss statement in Google Sheets
A profit and loss statement (P&L) answers one question: did the business make money in a given period? You can build a clean, automatic one in Google Sheets in under an hour with two tabs and a handful of SUMIFS formulas. This guide walks you through it step by step.
What a profit and loss statement shows
A P&L lists everything you earned and everything you spent over a period, usually a month, a quarter or a year. The bottom line is net profit. For a small business it normally has five blocks:
| Line | What goes in it | Example |
|---|---|---|
| Revenue | Sales of products or services | Client projects, retainers, product sales |
| Cost of goods sold | Direct costs of what you sold | Materials, subcontractors, payment fees per sale |
| Gross profit | Revenue minus cost of goods sold | |
| Operating expenses | Costs of running the business | Rent, software, insurance, marketing |
| Net profit | Gross profit minus operating expenses |
Two ratios are worth adding: gross margin (gross profit ÷ revenue) and net margin (net profit ÷ revenue). They tell you more than the totals when you compare months.
Step 1: Set up two tabs
Keep the raw data and the report apart. Typing straight into the P&L is the most common reason homemade spreadsheets break.
Tab 1: Transactions
One row per income or expense, with these columns:
| A: Date | B: Type | C: Category | D: Description | E: Amount |
|---|---|---|---|---|
| Oct 3, 2026 | Income | Projects | Website for a bakery | 4,100 |
| Oct 5, 2026 | Expense | Rent | Studio rent | 1,800 |
| Oct 20, 2026 | Expense | Software | Design tools | 236 |
Enter every amount as a positive number and let the Type column decide whether it adds or subtracts. Use Data > Data validation to turn Type and Category into dropdowns, so “Software” and “software ” never end up as two categories.
Tab 2: Categories
A short list with each category and the P&L block it belongs to: Revenue, Cost of goods sold or Operating expense. Your dropdowns and your P&L both read from this list.
Step 2: The formulas
Everything in the P&L comes from one function: SUMIFS. It adds up the amounts that match a category and fall inside a date range.
Put the start date of the period in B1 of the P&L tab and the end date in B2. Then, for a category written in column A, the total for the period is:
=SUMIFS(Transactions!$E:$E,
Transactions!$C:$C, $A5,
Transactions!$A:$A, ">="&$B$1,
Transactions!$A:$A, "<="&$B$2)Copy it down for every category. Then the totals:
- Total revenue:
=SUM(B5:B7)over your revenue rows - Gross profit:
=Total revenue − Total cost of goods sold - Net profit:
=Gross profit − Total operating expenses - Net margin:
=IFERROR(Net profit / Total revenue, 0), formatted as a percentage
To pick a month instead of typing two dates, put the month's first day in B1 and use =EOMONTH(B1, 0) in B2.
Step 3: Lay out the P&L
A layout that reads well on one screen:
- Period selector at the top (start and end date).
- Revenue categories, then Total revenue in bold.
- Cost of goods sold categories, then Gross profit and gross margin.
- Operating expense categories, then Total operating expenses.
- Net profit and net margin, with a light background so it stands out.
For a monthly view, put the twelve months across columns C to N and change the date conditions to each column's month. A 13th column with =SUM(C5:N5) gives you the year.
A useful extra for freelancers and sole traders is a tax reserve line: net profit multiplied by your estimated tax rate. It shows how much of this month's profit is not really yours to spend.
Common mistakes to avoid
- Mixing personal and business spending. Only business transactions go in the sheet; use a separate bank account if you can.
- Recording invoices as income before they're paid without saying so. Choose cash basis (when money moves) or accrual basis (when you invoice) and stick to it.
- Free-typed categories. One typo and the SUMIFS misses the row. Dropdowns fix this.
- Treating loan repayments or owner withdrawals as expenses. They change your cash, not your profit.
- Hard-coded numbers in the report. Every figure in the P&L should be a formula pointing at Transactions.
Skip the build
Business Manager is a ready-made Google Sheets template with the P&L already built, plus a dashboard, invoices, 13-week cash flow, budget and tax reserve. Type your transactions and every tab updates by itself.
See the template · $19 one-timeFAQ
Is a profit and loss statement the same as an income statement?
Yes. Profit and loss statement, P&L and income statement are three names for the same report.
What is the difference between a P&L and a cash flow statement?
The P&L shows whether you made a profit. Cash flow shows whether you have money in the bank and when it comes in or goes out. A business can be profitable and still run short of cash if clients pay late.
How often should a small business do a P&L?
Monthly. It takes a few minutes once your transactions are up to date, and it lets you spot a bad month before it becomes a bad quarter.
Can I use this for my taxes?
A P&L in Google Sheets is a management tool. It helps you and your accountant, but it doesn't replace the official forms or advice for your country.