Cellwise Start free

Excel guide · 6 min read

How to make a profit and loss statement in Excel

Quick answerExport your ledger lines (date, account, debit, credit), give each account a type (Revenue, COGS or Expense) with a lookup to your chart of accounts, and put the first day of each month across the top of a P&L grid. Each cell is a SUMIFS on the type with dates from the header date to EOMONTH(header,0)+1: credits minus debits for revenue, debits minus credits for costs. Gross profit is revenue - COGS, net profit is gross profit - expenses, and =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.

RowABCDEFGHIJKL
1DateAccountDebitCreditTypeAccountTypeLineAug 1, 2026Sep 1, 2026
2Aug 1, 2026Rent1100ExpenseSalesRevenueRevenue42004920
3Aug 3, 2026Sales4200RevenueShipping incomeRevenueCOGS13501860
4Aug 5, 2026Materials1350COGSMaterialsCOGSGross profit28503060
5Aug 14, 2026Advertising260ExpensePackagingCOGSOperating expenses32492989
6Aug 20, 2026Equipment2400AssetRentExpenseNet profit-39971
7Aug 29, 2026Wages1800ExpenseWagesExpenseNet margin-0.0950.014430894309
8Aug 31, 2026Software89ExpenseAdvertisingExpenseYTD net profit-399-328
9Sep 1, 2026Rent1100ExpenseSoftwareExpense
10Sep 2, 2026Sales5100RevenueEquipmentAsset
11Sep 8, 2026Materials1620COGSLoanLiability
12Sep 15, 2026Sales180Revenue
13Sep 18, 2026Packaging240COGS
14Sep 26, 2026Wages1800Expense
15Sep 30, 2026Software89Expense
16Sep 30, 2026Loan500Liability
17Oct 1, 2026Rent1100Expense
18Oct 4, 2026Sales6350Revenue
19Oct 9, 2026Materials1980COGS
20Oct 12, 2026Packaging310COGS

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.

=XLOOKUP(B6,G2:G11,H2:H11,"Missing")

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.

=SUMIFS(D2:D23,E2:E23,"Revenue",A2:A23,">="&L1,A2:A23,"<"&EOMONTH(L1,0)+1)-SUMIFS(C2:C23,E2:E23,"Revenue",A2:A23,">="&L1,A2:A23,"<"&EOMONTH(L1,0)+1)

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.

=SUMIFS(C2:C23,E2:E23,"Expense",A2:A23,">="&M1,A2:A23,"<"&EOMONTH(M1,0)+1)-SUMIFS(D2:D23,E2:E23,"Expense",A2:A23,">="&M1,A2:A23,"<"&EOMONTH(M1,0)+1)

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!.

=IF(M2=0,0,M6/M2)

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.

=SUM($K6:M6)

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.

  1. To show losses in red: select K2:M8, then Home > Number Format > More Number Formats > Custom.
  2. Enter #,##0;[Red]-#,##0 and click OK. Negative months now stand out. For the margin row, use 0.0%;[Red]-0.0%.
=SUM(K6:M6)-SUMPRODUCT(ISNUMBER(MATCH(E2:E23,{"Revenue","COGS","Expense"},0))*(D2:D23-C2:C23))

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.

=SUMIFS(C2:C23,E2:E23,"Asset")+SUMIFS(C2:C23,E2:E23,"Liability")

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

Related guides

Updated