How to Build a Profit and Loss Statement in Excel (Free Template)

How-to · Updated July 2026 · ~11 min read

Your accounting system prints a P&L, but the one you need has different groupings, last year beside this year, and a variance column your manager asked for. So you export a transaction list to Excel and start typing SUM ranges by hand — and three months later nobody can tell which cells are formulas and which are numbers somebody pasted over.

This guide gives you a working profit and loss format with live formulas in it, then explains the method underneath: a flat transaction table, a SUMIFS line per category, prior-period and variance columns, margins that recalculate. (From a trial balance instead? Trial balance to financial statements covers that.)

Get the template first

Download the HISAB profit and loss template for Excel — a plain .xlsx, no macros, no add-in required. Two sheets:

"P&L" holds the statement: Revenue (product sales, service income, other income), Cost of sales (materials, direct labour, subcontractors), a calculated Gross profit and gross margin %, eight operating-expense lines — salaries & wages, rent & utilities, marketing, software & subscriptions, professional fees, insurance, depreciation, other — then a calculated Net profit and net margin %. Four columns run across it (This period, Prior period, Variance, Var %), and every total and variance is a live formula, so you type only into the category rows.

"Transactions" is a four-column sheet — Date, Description, Category, Amount — for pasting a ledger export into, with a worked SUMIFS example.

Microsoft publishes free P&L spreadsheets at templates.office.com too. The rest of this article explains the formulas — sooner or later you'll need to add a category or fix a line that returns zero.

What a P&L is, and the order the lines go in

A profit and loss statement (income statement) reports revenue and expenses over a period; a balance sheet is a snapshot at one date.

The multi-step layout isn't a style choice. It follows the order set out in the SEC's Regulation S-X Rule 5-03: net sales and gross revenues; costs and expenses applicable to sales and revenues; other operating costs and expenses; selling, general and administrative expenses; non-operating income; interest expense; non-operating expenses; income before income tax; income tax expense; then net income or loss.

Software follows the same shape with plainer names. Zoho Books' Profit and Loss report has four sections — Operating Income, Cost of Goods Sold, Operating Expense, Non Operating Income/Expense — with the bottom line "Net Profit / Loss = Total Revenue - All expenses and losses".

The subtotals in between are accounting convention, not something a vendor defines: Gross Profit = Revenue − Cost of Goods Sold; Operating Income = Gross Profit − Operating Expenses; Net Income = Operating Income plus non-operating income, less non-operating expenses and income tax.

The template uses the simplified small-business version: revenue, cost of sales, gross profit, operating expenses, net profit. For interest, tax or discontinued operations, add blocks below operating expenses and extend the SUM ranges.

Decide your sign convention first

This ruins more Excel P&Ls than any formula error, and no function warns you. SUMIFS adds whatever is in the Amount column; it doesn't know that "Rent & utilities" is an expense and "Product sales" is revenue. Pick one rule and write it at the top of the Transactions sheet:

• Recommended: store every Amount as a positive number and let the P&L subtract, so gross profit is =B8-B14 where B14 is the cost-of-sales total.
• Or: store expenses as negative numbers and add everything together.

Mixing the two — some exports come out with credits negative, some don't — produces a net profit that is silently wrong. Normalise the signs first; cleaning messy data in Excel covers the culprits, including amounts stored as text and trailing minus signs.

Build it from a transaction list with SUMIFS

Step 1: lay out the Transactions sheet

Four columns, one header row, no blanks, no merged cells: Date in A, Description in B, Category in C, Amount in D. Select the data, press Ctrl+T to make it an Excel Table, and name it Ledger on the Table Design tab. Table references adjust as rows are added, so next month's paste doesn't leave formulas pointing at last month's range. Structured references take the form TableName[ColumnName], always in brackets.

Step 2: put the period dates in cells

On the P&L sheet reserve four cells: $B$1 period start, $C$1 period end, $B$2 prior period start, $C$2 prior period end. Changing the period is then typing two dates, not editing forty formulas.

Step 3: the category formula

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Read that order carefully — it's the most common mistake in this topic. Microsoft flags it explicitly: "the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF." SUMIF is SUMIF(range, criteria, [sum_range]).

With category labels down column A from A5, against the Ledger Table:

=SUMIFS(Ledger[Amount],Ledger[Category],$A5,Ledger[Date],">="&$B$1,Ledger[Date],"<="&$C$1)

Two things to notice. $A5 is absolute-column, relative-row, so the formula fills down and each row picks up its own label. And the date bounds use operator concatenation — ">="&$B$1, not ">=$B$1": quoting the whole thing makes it literal text that never matches (Microsoft's own example: =SUMIF(A2:A5,">" & C2,B2:B5)).

On a fixed range like $D$2:$D$1000 the shape is identical, just longer — and it won't grow with your data.

Fill that down every category row. Section headings and subtotals get SUM, not SUMIFS.

Step 4: subtotals, gross profit, net profit

With revenue categories in rows 5 to 7, cost of sales in 11 to 13 and operating expenses in 19 to 26 — all amounts stored positive:

B8    Total revenue             =SUM(B5:B7)
B14   Total cost of sales       =SUM(B11:B13)
B15   Gross profit              =B8-B14
B27   Total operating expenses  =SUM(B19:B26)
B28   Net profit                =B15-B27

Rows 9-10 and 16-18 are blank rows and section headings. Never type a total: a subtotal hardcoded as 142500 stops reconciling the moment a transaction is added.

Step 5: prior period and variance columns

The prior-period column is the identical formula pointed at the other pair of dates:

=SUMIFS(Ledger[Amount],Ledger[Category],$A5,Ledger[Date],">="&$B$2,Ledger[Date],"<="&$C$2)

Then, with This period in column B and Prior period in column C:

D5   Variance  =B5-C5
E5   Var %     =IFERROR(D5/ABS(C5),"")

ABS keeps the percentage meaningful when the prior figure is negative; IFERROR stops a wall of #DIV/0! on categories with no activity last period. For driver-level explanations, see variance analysis.

Step 6: margin percentages

B16   Gross margin %  =IFERROR(B15/B8,"")
B29   Net margin %    =IFERROR(B28/B8,"")

Format both as percentages. IFERROR earns its place on a sheet with no revenue yet, which would otherwise show #DIV/0!.

Step 7 (optional): month-by-month columns

For twelve monthly columns instead of two comparatives, put each month's first day in row 3 (C3, D3, E3 …) and let EOMONTH close the period:

=SUMIFS(Ledger[Amount],Ledger[Category],$A5,Ledger[Date],">="&C$3,Ledger[Date],"<="&EOMONTH(C$3,0))

EOMONTH(start_date, months) returns the last day of the month N months from the start date, so EOMONTH(C$3,0) is C3's month-end. The mixed reference C$3 locks the row as you fill right.

SUMIFS rules that will bite you

• criteria_range must contain the same number of rows and columns as sum_range, or you get #VALUE!. (Only plain SUMIF tolerates a differently-sized sum range.)
• Up to 127 range/criteria pairs. Criteria over 255 characters return incorrect results.
• Criteria can be a number, a comparison string like ">32", a cell reference, or quoted text; text and logical symbols must sit inside double quotes.
• Pointing SUMIFS at a closed workbook returns #VALUE! — open the source and press F9.
• Wildcards: ? matches one character, any sequence, a leading ~ escapes a literal ? or .
• Availability: Microsoft 365, Excel 2024, 2021, 2019, 2016 and the Excel Web App — treat it as Excel 2016 or later.

Make categories impossible to mistype

SUMIFS matches category text exactly. "Software & subscriptions" and "Software and subscriptions" are two different categories, and the one that isn't in the ledger returns 0 — no error, no warning, and a statement that still foots. Fix it structurally: select the Category column, go to Data > Data Validation, and on the Settings tab set Allow to List, sourced from the labels down column A of the P&L.

The PivotTable route (no formulas at all)

To group revenue and expenses by category, a PivotTable gets there in a minute. Source data must be in columns not rows, with a single header row of unique non-blank labels and no blank rows, blank columns or merged cells.

1. Select a cell inside the transaction data, then Insert > PivotTable (in newer builds you may see a From Table/Range option under it).
2. The Create PivotTable dialog appears showing the range or table name. Choose New Worksheet or Existing Worksheet and select OK. Excel opens the PivotTable Fields pane, with the areas Filters, Columns, Rows, Values.
3. Drag Category to Rows and Amount to Values. Defaults get you close: non-numeric fields go to Rows, date/time fields to Columns, numeric fields to Values.
4. To change the aggregation, right-click the field in Values, choose Value Field Settings, then Summarize Values By (default SUM).
5. To restrict the Pivot to a period, filter the source by date first, or add a month column to the ledger and put that field in Filters.

PivotTables do not refresh themselves. After adding transactions, select the PivotTable and use the PivotTable Analyze tab > Refresh in the Data group, or press Alt+F5; the Refresh dropdown offers Refresh All. Two opt-in settings live in PivotTable Options > Data: Refresh data when opening the file and Automatically refresh when the data source changes.

A Pivot is faster to build, but it controls the layout, gross and net profit need cells outside it, and a reviewer can't see which rows fed a figure. Use a Pivot to explore the export, SUMIFS for the statement you issue.

Three accounting mistakes no formula will catch

1. Mixing cash and accrual. A P&L covers revenue earned and expenses incurred in the period, not just cash that moved. A bank export is a cash view — unpaid invoices and accruals are missing. Decide the basis and label the statement.
2. Putting balance-sheet items in the P&L. Loan principal repayments, owner drawings, asset purchases, VAT/GST collected for the tax authority and customer deposits are neither revenue nor expenses; they belong on the balance sheet. Depreciation brings an asset purchase into the P&L over time — the payment never appears.
3. Stale ranges. $D$2:$D$1000 silently ignores row 1001. Use a Table, and keep =SUM(Ledger[Amount]) on the sheet to compare against the sum of all P&L lines — if they don't tie, a transaction fell outside your categories or dates.

Where HISAB 360 fits

Everything above works with nothing but Excel, and the template is free to keep. What stays annoying is the input: every month you re-run the export, re-check the signs, re-paste, re-check the categories, re-tie the totals.

HISAB 360 is a paid Excel add-in that removes the paste step. It docks an AI chat panel inside Excel and connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage with full read and write access. Instead of exporting a CSV you ask in plain English for the general ledger or P&L detail for a period and it lands in the sheet as a structured range — then you ask for the SUMIFS lines and it applies them to the live workbook instead of printing formula text to copy. Double-clicking a total drills into the rows behind it. See also the QuickBooks general ledger in Excel.

Honest limits: Windows desktop Excel only (365 or 2019+) — no Mac, no Excel for the web. It is paid after a 15-day free trial covering every feature plus 50 HISAB AI credits, no credit card, once per machine; there is no permanently free tier. You authorise each accounting system yourself via OAuth, and since the add-in can write to a ledger as well as read from it, review anything it posts. It will not email you a scheduled monthly P&L — you open Excel and ask. Pricing: $7/month or $60/year if you bring your own AI key, $10/month for HISAB AI Basic, $20/month for Plus, $199 perpetual (pricing). The month-end close checklist shows where the P&L sits in the close.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.