Profit and Loss Statement in Excel: Build It Right

Formulas & functions · Updated November 20, 2026

To make a profit and loss statement in Excel, organize income and expense transactions in a dated ledger, list matching categories on a report sheet, and use SUMIFS to total each category for the chosen month. Subtract costs and expenses from revenue to calculate gross profit, operating income, and net profit.

What a profit and loss statement shows

A profit and loss statement (also called an income statement) summarizes revenue and expenses over a period, such as a month, quarter, or financial year. It answers a basic question: did the business earn more than it spent during that period? It reports performance over time, unlike a balance sheet, which shows assets, liabilities, and equity at a point in time.

A practical small-business statement often follows this sequence:

  • Revenue: income earned from sales or services.
  • Cost of goods sold (COGS): direct costs associated with goods sold or services delivered.
  • Gross profit: revenue less COGS.
  • Operating expenses: costs such as wages, rent, software, and marketing.
  • Operating income: gross profit less operating expenses.
  • Other expenses and taxes: items shown below operating income when relevant.
  • Net profit: the amount remaining after the expenses included in the statement.

Businesses use different account names and levels of detail. The useful principle is consistency: classify similar transactions the same way each period, and make clear which period and accounting basis the report covers.

Set up a clean transaction ledger

Start with a worksheet named Transactions. Use one row per transaction and put a single field in each column. For example, set up columns A through D as Date, Description, Category, and Amount. Keep the date in column A, the category in column C, and the amount in column D if you plan to use the formula below. Enter dates as real Excel dates, not text that merely looks like a date.

For a simple P&L, record income as positive amounts and expenses as positive amounts too; the report will subtract expense totals in its profit calculations. Give each transaction one category, such as Sales, Service revenue, Cost of goods sold, Wages, Rent, Marketing, Utilities, or Interest expense. Do not mix signs by entering expenses as negatives if your report formulas assume positive expenses.

On Windows, select the ledger range and use Insert > Table to make it easier to filter and extend. Confirm that the table has headers. Keep the report on a separate worksheet, and avoid blank rows within the transaction data. A separate ledger makes it possible to correct or audit an entry without replacing totals in the report.

Build the monthly report layout

Create a worksheet called P&L. Put the first day of the reporting month in B2 as a genuine date—for example, April 1, 2026—and format it as a month and year if you prefer. Use column A for the labels and column B for April’s figures. A straightforward layout is:

  • Row 6 Sales; row 7 Service revenue; row 8 Total revenue.
  • Row 11 Cost of goods sold; row 12 Gross profit.
  • Rows 15–18 Wages, Rent, Marketing, and Utilities; row 19 Total operating expenses.
  • Row 21 Operating income; row 24 Interest expense; row 25 Profit before tax.
  • Row 26 Income tax expense; row 27 Net profit.

Put section labels such as Revenue and Operating expenses on their own rows if helpful, but do not include those headings in a sum. The category text in column A must match the ledger’s Category values exactly; extra spaces or different wording can prevent a category total from matching.

Use formulas to total categories and calculate profit

With transaction dates in Transactions!A2:A1000, categories in Transactions!C2:C1000, amounts in Transactions!D2:D1000, the report category in A6, and the month’s first date in B2, enter this in P&L!B6. It totals the category for dates from the start of the month up to, but not including, the start of the next month. If April’s Sales transactions total $12,500, the result is $12,500.

=SUMIFS(Transactions!$D$2:$D$1000,Transactions!$C$2:$C$1000,$A6,Transactions!$A$2:$A$1000,">="&B$2,Transactions!$A$2:$A$1000,"<"&EDATE(B$2,1))

Copy the formula down to other category rows and across to later months. When copying down, make sure each row label corresponds to a category in the ledger. When copying across, put the first day of each reporting month in the next column’s row 2. The end-exclusive date condition also includes transactions with times on the last calendar day, which can be missed by a criterion that stops at midnight at the start of that day.

With category amounts in B6:B7, enter this in B8 to add sales and service revenue. If those amounts are $12,500 and $3,000, the result is $15,500.

=SUM(B6:B7)

With total revenue in B8 and COGS in B11, enter this in B12. If revenue is $15,500 and COGS is $5,200, gross profit is $10,300.

=B8-B11

With operating expense amounts in B15:B18, enter this in B19. For illustrative amounts of $4,000 in wages, $1,200 in rent, $600 in marketing, and $250 in utilities, the total is $6,050.

=SUM(B15:B18)

With gross profit in B12 and total operating expenses in B19, enter this in B21. Using the example totals, operating income is $4,250.

=B12-B19

With operating income in B21 and interest expense in B24, enter this in B25. If interest expense is $100, profit before tax is $4,150.

=B21-B24

With profit before tax in B25 and income tax expense in B26, enter this in B27. If the illustrative tax expense is $800, net profit is $3,350.

=B25-B26

These figures show the arithmetic, not a recommended tax calculation or accounting treatment. If the business has other income or expenses, add clearly labeled lines in the appropriate place and adjust the subtotals so the statement includes them rather than silently omitting them.

Choose the reporting period and level of detail

A monthly report is useful for spotting changes and checking recent activity; quarterly and annual views help summarize longer periods. Use the same category structure from month to month so comparisons mean something. For a year-to-date statement, decide whether the report should show each month separately or one combined total, then use a layout that makes that choice explicit.

Consider whether the statement is cash-basis or accrual-basis. Under a cash basis, income and expenses are generally recorded when cash is received or paid; under an accrual basis, they are generally recorded when earned or incurred. The applicable rules depend on the business and its circumstances. Excel will total the dates and categories you give it, but it cannot decide when a transaction should be recognized. If the accounting basis or treatment is uncertain, check with a qualified accounting professional.

Format the workbook and check its totals

Format the report amounts as currency or accounting numbers, use bold type for key subtotals, and make the reporting month visible. On Windows, currency formatting is available from Home > Number; the Mac ribbon has the corresponding number-format controls. Formatting changes how a value appears, not the underlying amount. Avoid typing currency symbols into the number itself, since text values can interfere with calculations.

Before relying on a result, perform a few checks:

  • Filter the ledger to the reporting month and compare a category total with the report.
  • Check that every transaction has a date, category, and amount, and that dates are stored as dates.
  • Look for categories spelled differently from the report labels, including trailing spaces.
  • Confirm that expenses are entered as positive values if the report subtracts them.
  • Check that formula ranges include new rows. A fixed range ending at row 1000 will not include a transaction entered below row 1000.
  • Reconcile the ledger to source records, such as invoices, receipts, or bank records, as appropriate.

A profit figure is only as reliable as the entries and classification behind it. Preserve the original records and consider keeping a dated copy of each reporting period so later edits do not obscure what was reported.

Common errors and how to avoid them

Mixing revenue and cash received: a customer payment may settle an invoice recorded in an earlier period. A cash total and an accrual-basis revenue total are not necessarily the same. Choose the intended basis and avoid treating a bank deposit as new revenue without checking what it represents.

Double-counting transactions: importing a bank line and then entering the same transaction manually can inflate income or expenses. Use a reference, date, or review process to identify duplicates. Misclassifying costs: putting a direct production cost among operating expenses changes gross profit, even if the final arithmetic still works. Category choices affect interpretation, not just presentation.

Leaving out refunds or adjustments: returns, discounts, and corrections can change reported revenue or expenses. Record them consistently and explain unusual items when needed. Using incomplete ranges: if entries are added below the formula’s last row, they will not be counted. Extend the ranges or use a carefully structured Excel Table-based approach. After changing formulas, inspect one category against the underlying rows rather than assuming the totals are correct.

When Excel is enough—and when to use another tool

Excel is a flexible option for a simple statement, a one-off analysis, or a business whose transaction volume and reporting needs are manageable. It is easy to customize categories, add comparisons, and create charts. The trade-off is that users must maintain the data, formulas, and review controls themselves. A spreadsheet does not automatically establish that entries are complete, properly classified, or compliant with a reporting requirement.

Accounting software may be a better fit when several people enter transactions, invoicing and bank reconciliation need to connect, or reports must be produced repeatedly with an audit trail. You can still export data to Excel for analysis. If you use a downloaded template, inspect its formulas, date assumptions, and category definitions before entering sensitive or final figures. Excel for the web supports common spreadsheet work, but ribbon placement and some features can differ from Microsoft 365 for Windows; the formulas above use standard functions available in current Excel versions.

Frequently Asked Questions

Does Excel have a built-in profit and loss statement?
Excel may offer financial templates through its template gallery, but a blank workbook does not automatically know your transactions or accounting categories. A custom report or reviewed template still needs accurate source data and formulas.

What is the difference between a P&L and a balance sheet?
A P&L summarizes income and expenses over a period and shows profit or loss. A balance sheet summarizes assets, liabilities, and equity at a particular date. They answer different questions and should not be treated as interchangeable.

Can I make a profit and loss statement for each month?
Yes. Put the first date of each month in a separate report column and copy the category formula across. Keep categories consistent, and make sure each date header is an actual date so the monthly criteria work.

Why does my SUMIFS formula return zero?
Check that the category label matches exactly, the dates are genuine Excel dates, and the amount cells contain numbers. Also confirm that the formula range covers the relevant transactions and that the month header is the first day of the intended month.

Should expenses be negative or positive in Excel?
Either convention can work if every formula follows it, but the example here records expenses as positive amounts and subtracts them in the profit calculations. Do not mix conventions, or totals can be misleading.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.