Profit and Loss in Excel — Build a P&L Statement with Live Margins (2026)

A profit and loss statement tells the most important story in business: did you make money, and where did it go? In 40 minutes you’ll build a clean, formula-driven P&L in Excel — from revenue down to net profit — with gross, operating, and net margins, period-over-period variance, and red-flagged losses. Every subtotal is a formula, so the whole statement recalculates the moment you change a single figure.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is a Profit and Loss Statement?

A profit and loss statement (also called an income statement or P&L) summarises a business’s revenues and expenses over a period — a month, quarter, or year — to show whether it made a profit or a loss. It is one of the three core financial statements, and it is the one owners and managers read most often because it answers the everyday question: are we making money?

The P&L works as a cascade. It starts with revenue at the top (the “top line”), subtracts the direct cost of goods sold to reveal gross profit, then subtracts operating expenses to reveal operating profit, and finally subtracts interest and tax to reach net profit (the “bottom line”). Each level strips away a different category of cost, so you can see exactly where profit is created and where it leaks away.

That layered structure is what makes the P&L so diagnostic. A strong gross profit but a weak net profit points to bloated overheads. A shrinking gross margin points to rising input costs or discounting. Because each line connects to the next by a simple formula, Excel is the natural home for a P&L: change one assumption and watch the consequence ripple all the way down to net profit instantly.

The discipline that separates a useful P&L from a fragile one is simple: never type a subtotal. Every total, every profit line, and every margin should be a formula referencing the cells above it. Do that and your statement is auditable, error-resistant, and reusable every period. This guide builds exactly that, then adds margins, period comparison, and formatting that makes the numbers legible at a glance.

Before You Start

Gather and categorise your figures first — the structure depends on getting the categories right.

Separate direct costs from overheads

Cost of goods sold (COGS) is the direct cost of producing what you sold — materials, manufacturing labour, payment processing, the wholesale cost of resold goods. Operating expenses are the overheads of running the business regardless of sales volume — rent, salaries, software, marketing. Getting this split right is what makes gross profit meaningful; lump them together and you lose the most useful line on the statement.

Pick your period and be consistent

Decide whether the statement covers a month, quarter, or year, and make sure every figure belongs to that same period. Mixing a year’s revenue with a month’s rent produces nonsense. If you want a monthly P&L, every input should be the monthly figure.

Cash vs accrual

Decide whether you are recording money when it moves (cash basis) or when it is earned/incurred (accrual basis). For a simple owner-managed P&L, cash basis is fine; for anything reported to lenders or investors, accrual is the standard. Either way, be consistent across periods so comparisons are valid.

1.Step 1 — Lay Out the P&L Structure

In column A, type the statement skeleton top to bottom, leaving the value columns (B onward) empty for now:

Revenue
  (revenue lines...)
Total Revenue
Cost of Goods Sold
  (COGS lines...)
Total COGS
Gross Profit
Operating Expenses
  (expense lines...)
Total Operating Expenses
Operating Profit
Interest
Tax
Net Profit

This ordering is the universal P&L convention, and it matters: each subtotal sits directly below the lines it sums, and each profit line sits directly below the two numbers it subtracts. That physical layout is what lets you write clean, copyable formulas. Put your period label (e.g. “Jan 2026”) in B1.

2.Step 2 — Enter Revenue

List each revenue stream on its own row — product sales, service income, subscriptions — rather than a single lump. Then total them:

Keeping streams separate costs nothing and pays off immediately: you can see which line drives the top line, spot a stream that is shrinking, and later chart the revenue mix. Put this total in your “Total Revenue” row — it is the denominator for every margin you will calculate later, so it earns its own clearly labelled cell.

3.Step 3 — Calculate Gross Profit

List and total your COGS lines the same way with SUM. Then gross profit is one subtraction — total revenue minus total COGS:

(Here B5 is Total Revenue and B9 is Total COGS — adjust to your row numbers.) Gross profit is the money left after the direct cost of what you sold, before any overheads. It answers the question: is the core product actually profitable to make and sell? If gross profit is thin or negative, no amount of cost-cutting elsewhere will save the business — the unit economics are broken.

4.Step 4 — Subtract Operating Expenses

List overheads — salaries, rent, marketing, software, utilities — each on its own row, and total them with SUM. Operating profit (also called EBIT, earnings before interest and tax) is gross profit minus total operating expenses:

(B10 = Gross Profit, B16 = Total Operating Expenses.) Operating profit is the cleanest measure of how well the core business runs, because it ignores how the company is financed (interest) and the tax regime it sits in. It is the line investors scrutinise most when comparing companies.

5.Step 5 — Interest, Tax, and Net Profit

Subtract interest on any borrowing and your estimated tax to reach the bottom line:

(B17 = Operating Profit, B18 = Interest, B19 = Tax.) Net profit is the true bottom line — what the business actually kept. A company can post a healthy operating profit and still end up with a small or negative net profit if it carries heavy debt or sits in a high-tax bracket, which is exactly why the P&L separates these layers rather than jumping straight from revenue to net.

6.Step 6 — Add Margin Percentages

Absolute pounds tell you the size of profit; margins tell you the quality. In a column beside each profit line, divide it by total revenue and format as a percentage:

Do the same for operating margin (=B17/B5) and net margin (=B20/B5). Margins are powerful because they normalise for size: a business that grows revenue 50% but holds a 22% net margin is scaling efficiently; one whose margin drops to 12% as it grows is buying revenue at the expense of profitability. Track these three margins over time and you have an early-warning system for the health of the business.

7.Step 7 — Compare Periods and Add Variance

A single period is a snapshot; the value is in the trend. Put each month or year in its own column (B, C, D…) and copy your formulas across — because the references are relative, each column recalculates against its own inputs. Then add two variance columns comparing the latest period to the prior one:

Wrap the percentage in IFERROR(...,"") to avoid a #DIV/0! when the prior period was zero. A year-over-year P&L with variance columns is the single most useful management report a small business can keep — it shows not just where you are, but the direction and speed of travel.

8.Step 8 — Format and Visualise

Formatting turns a grid of numbers into a readable statement. Apply the Accounting number format (Ctrl+1) so currency symbols and decimals align in neat columns. Bold the subtotal rows (Total Revenue, Gross Profit, Operating Profit, Net Profit) and give them a light fill so the eye lands on them. Indent the detail lines beneath each heading.

Flag losses automatically: select the value cells, Home → Conditional Formatting → Highlight Cells Rules → Less Than → 0, with a red font. Now any negative line — an operating loss, an unfavourable variance — stands out the instant someone opens the file.

Finally, a chart. A waterfall chart is purpose-built for a P&L: it shows revenue as the starting bar and each cost as a step down, ending at net profit, making the cascade visual. Insert → Waterfall, or see our waterfall chart tutorial for the full build.

Try It: Live P&L Calculator

Type your own figures and watch the statement compute every subtotal and margin instantly — exactly as the linked Excel formulas do. Notice how gross, operating, and net margins each respond differently as you change revenue, direct costs, and overheads: the gap between them is the diagnostic story of the business.

Advanced P&L Tips

Build it from transactions with SUMIFS

Instead of typing category totals, pull them from a transaction sheet: =SUMIFS(Txns[Amount],Txns[Category],"Marketing",Txns[Month],B$1). Each P&L line becomes self-updating, and a new column per month gives you a rolling twelve-month statement with zero retyping.

Common-size the statement

Express every line as a percentage of revenue, not just the profit lines. A “common-size” P&L makes it obvious when, say, marketing creeps from 8% to 14% of revenue — a shift that is invisible in raw pounds as the business grows.

Add a 12-month rolling total

A rolling 12-month net profit (=SUM(last 12 monthly cells)) smooths out seasonality and is often a truer signal of the trajectory than any single month or the calendar-year-to-date figure.

Automate it end to end

When monthly P&L prep becomes a chore, DataHub Pro builds the statement and margin trends straight from your uploaded transactions or accounting export — live, shareable, and refreshed automatically.

A Worked Example

Walk a simple month through the cascade. Revenue is £100,000. Cost of goods sold — the direct cost of delivering what you sold — is £40,000, so gross profit is £60,000 and the gross margin is 60% (60,000 / 100,000). That 60% is the headline efficiency of the product itself: six out of every ten pounds of sales survive the direct cost of production.

Operating expenses — salaries, rent, marketing, software — total £32,000. Subtract them and operating profit is £28,000, a 28% operating margin. This is the cleanest read on the core business: after running costs but before financing and tax, the company keeps 28p per pound of sales. Then interest of £2,000 and tax at 19% of the £26,000 pre-tax profit (about £4,940) come off, leaving a net profit of roughly £21,060 — a 21% net margin.

The three margins — 60%, 28%, 21% — tell the whole story in one glance. A wide gap between gross and operating margin (60% to 28%) says overheads consume a large slice of gross profit; if that gap widened next month, you would investigate which overhead grew. The small step from operating to net margin (28% to 21%) shows financing and tax are modest. Track these three numbers every month and you have a continuous read on the health of the business that no single profit figure can give. The interactive calculator above is loaded with this exact scenario — change any input and watch all three margins move.

Common Errors & Fixes

Subtotals don’t match the lines above

Your SUM range is too short or too long — it is missing a row or accidentally including a subtotal (double-counting). Click the total cell and check the highlighted range covers exactly the detail lines, nothing more.

Margins show as huge numbers, not percentages

The cell is formatted as a number. Select it and apply Percentage format (Ctrl+Shift+%), or the 0.22 will display as 0 or 22000% depending on format.

#DIV/0! in a margin or variance

Revenue or the prior period is zero. Wrap the formula in IFERROR(...,"") or IF(B5=0,"",...) so blank periods display cleanly.

Net profit looks wrong after adding a row

Inserting a row inside a SUM range usually extends it automatically, but inserting at the very edge may not. After structural edits, click each subtotal and confirm its range still covers the right cells.