To make a profit and loss (P&L) report in Microsoft Excel, list revenue and expenses for a defined period, total each, and subtract expenses from revenue. With revenue in B2 and expenses in B3, the basic result formula in B4 is =B2-B3; a positive result is profit, while a negative result is a loss.
What a profit and loss statement shows
A profit and loss statement, also called an income statement, summarizes a business’s income and expenses over a period such as a month, quarter, or year. It helps answer practical questions: How much did the business earn? Which costs were largest? Did it make a profit during the period? Excel can organize the underlying transactions and calculate totals, but the report is only as reliable as its source data and accounting choices.
A simple P&L might show sales, cost of goods sold, gross profit, operating expenses, and net profit. A service business may have little or no cost of goods sold, while a retailer may track inventory costs separately. The exact categories depend on the business. A spreadsheet can be useful for a small or straightforward operation, but it does not automatically determine the correct accounting treatment or replace accounting software and professional review.
Plan the workbook and reporting period
Keep transaction-level data separate from the summary report. On a sheet named Transactions, use one row per transaction and consistent columns such as Date, Type, Category, Description, and Amount. Enter the transaction date as a real Excel date, select a consistent type such as Revenue or Expense, and enter amounts as positive numbers. Using positive amounts for both types makes it easier to total them separately; the report’s profit calculation handles the subtraction.
For example, the transaction list might include a 12,000 sale and an 8,500 sale, plus expenses of 4,000 for materials, 2,200 for rent, and 1,500 for software. Those illustrative figures produce revenue of 20,500 and expenses of 7,700 for the period. The resulting profit is 12,800. These figures demonstrate spreadsheet arithmetic only; they are not financial, tax, or legal advice. Confirm real classifications and reporting requirements with a qualified professional.
- Choose the period before calculating totals, and label it clearly in the report.
- Use one spelling for each category, such as Rent, rather than mixing Rent, Office Rent, and rent.
- Keep source records and supporting descriptions so unusual entries can be traced later.
- Avoid putting subtotals or explanatory notes inside the transaction rows that formulas include.
Build the transaction table in Excel
In Excel for Microsoft 365 on Windows, enter the column headings and transactions, select the data, and choose Insert > Table. Confirm that the table has headers. A table expands when new rows are added and carries formatting and formulas into the additional rows, which helps keep an ongoing transaction list organized. Give the table a useful name from the Table Design tab, such as Transactions, if you plan to use structured references later.
For a straightforward formula-based summary, assume the Transactions sheet has dates in A2:A1000, transaction types in B2:B1000, categories in C2:C1000, and amounts in E2:E1000. On a P&L sheet, place the period start date in J1 and end date in K1. Put a category name such as Rent in G2, then use the corresponding formula in H2 to total that category’s expenses. The formula requires dates in the source column to be actual Excel dates and category names to match exactly.
With those source columns, the category in G2, and the reporting dates in J1 and K1, this formula in H2 totals expenses for that category and period: =SUMIFS(Transactions!$E$2:$E$1000,Transactions!$B$2:$B$1000,"Expense",Transactions!$C$2:$C$1000,G2,Transactions!$A$2:$A$1000,">="&$J$1,Transactions!$A$2:$A$1000,"<="&$K$1). Copy it down for the other expense categories. To total revenue by category, use the same layout and replace the type criterion with "Revenue", or create a separate revenue section. The example amounts above total 4,000, 2,200, and 1,500 in their respective expense categories.
Calculate revenue, expenses, and profit
A small report can have a revenue total, an expense total, and a net profit line. Suppose the P&L sheet has revenue category totals in H2:H5 and expense category totals in H8:H14. These are example cell ranges; adjust them to fit the categories in your own report. The total cells should sum only their respective sections, not the entire summary column, to avoid counting subtotals twice.
With revenue totals in H2:H5, enter this formula in H6 for total revenue: =SUM(H2:H5). For the illustrative transactions, where the two revenue categories contain 12,000 and 8,500, the result is 20,500. With expense totals in H8:H14, enter =SUM(H8:H14) in H15; if those rows contain 4,000, 2,200, and 1,500 and the remaining rows are blank or zero, the result is 7,700.
With total revenue in H6 and total expenses in H15, enter this in H17 for net profit or loss: =H6-H15. The illustrative result is 12,800. Label the line clearly and format the amounts as currency or accounting format using Home > Number. A negative result is not a formula error: it means recorded expenses exceeded recorded revenue for that report period.
Businesses that track product costs may want to show gross profit as well as net profit. Gross profit is revenue less cost of goods sold; operating profit and net profit involve additional categories and may be defined differently depending on reporting conventions. Do not treat a simple spreadsheet layout as a universal accounting template. Decide which subtotals are appropriate for the business and keep them distinct from the final result.
Use formulas to report by date and category
SUMIFS is useful when the transaction list is longer than a handful of rows because it adds amounts only when all specified conditions are met. In the example above, the conditions are transaction type, category, start date, and end date. To report one month, enter the first and last dates of that month in J1 and K1. Change those cells for another month rather than rewriting the formula. If your date column includes times as well as dates, an inclusive end-date test may miss transactions recorded after midnight on the last day; use a next-day exclusive boundary instead.
With the same source columns, category in G2, start date in J1, and end date in K1, this version includes any time on the end date by setting the upper limit to the following day: =SUMIFS(Transactions!$E$2:$E$1000,Transactions!$B$2:$B$1000,"Expense",Transactions!$C$2:$C$1000,G2,Transactions!$A$2:$A$1000,"<"&($K$1+1),Transactions!$A$2:$A$1000,">="&$J$1). Use either the date-only version or this version according to how dates are stored, not both at once.
You can also compare periods by placing monthly totals in separate columns or by creating a PivotTable. To make one, select the transaction table and choose Insert > PivotTable, then place Type or Category in Rows, Date in Columns or Filters, and Amount in Values. Check that the Values field summarizes by Sum rather than Count. PivotTables offer quick ways to rearrange and inspect data, but refresh them after adding transactions; select the PivotTable and choose PivotTable Analyze > Refresh.
Make the report readable and useful
A correct calculation is easier to interpret when the report’s structure is obvious. Put the business or report name at the top, show the start and end dates, separate revenue from expenses, and emphasize important subtotals without relying on color alone. Use a consistent number format, and decide whether to display cents based on the scale and purpose of the report. Freeze or repeat headings where helpful, and avoid decorative charts that obscure actual values.
- Use bold text or borders for total revenue, total expenses, and net profit or loss.
- Keep category labels specific enough to be useful, but avoid making the summary needlessly long.
- For a simple visual comparison, select period labels and profit values, then choose Insert > Insert Column or Bar Chart.
- Include a note about whether the report is cash-based or accrual-based if that distinction matters to its users.
One useful check is to compare the summary totals with an independent total of the transaction list for the same dates. Another is to inspect unusually large or negative amounts and confirm that they are intended. Consider saving the workbook with a clear period in its filename and retaining a separate copy of the underlying records. If a report is used for formal accounts, taxes, lending, or management decisions, have the relevant figures and methods reviewed by an appropriate professional.
Common Excel P&L problems
A total that seems too low or too high often reflects inconsistent data rather than a broken SUMIFS formula. A transaction may have a misspelled type or category, a date stored as text, or an amount stored as text. Excel may not treat text dates as dates in date criteria, and a category with an extra space may not match the category label in the summary. Inspect the source cells, standardize entries, and make sure the formula ranges cover all transaction rows.
Another frequent issue is counting a transaction twice or using the wrong sign convention. If expenses are entered as negative numbers but the report also subtracts total expenses, the result can be overstated because subtracting a negative adds it back. Choose one convention and apply it consistently. The example in this guide uses positive amounts for both revenue and expenses, then subtracts the expense total once.
For ongoing work, Excel’s Data > Data Validation command can create a dropdown list for fields such as Type or Category, reducing spelling variations. If the transaction list is an Excel table, formulas that use fixed ranges such as rows 2:1000 must still be expanded if the data grows beyond row 1000. Check that limit or use a table-based formula approach and verify its result against a known sample.
Frequently Asked Questions
Does Excel have a built-in profit and loss template?
Excel offers templates through its template gallery, and available choices can vary. You can also create a basic P&L from a transaction list and formulas as described here. Whichever route you use, review the categories, period, and calculation rules rather than assuming a template matches your business.
What is the basic Excel formula for profit?
Profit is revenue minus expenses. With revenue in B2 and expenses in B3, enter =B2-B3 in B4. If expenses exceed revenue, the result is negative and represents a loss under this simple calculation.
Can Excel calculate profit and loss by month?
Yes. Store transaction dates as Excel dates, set a month’s start and end dates in the report, and use SUMIFS with date criteria to total transactions within that period. You can repeat the summary for each month or use a PivotTable grouped or filtered by date.
Should expenses be entered as negative numbers?
Not necessarily. The example here enters expenses as positive amounts and subtracts their total from revenue. Negative expense entries can work in a different design, but mixing conventions or subtracting a negative total can reverse the intended calculation.
Is an Excel P&L the same as a balance sheet?
No. A P&L summarizes income and expenses over a period and shows the resulting profit or loss. A balance sheet reports assets, liabilities, and equity at a particular date. They answer different questions and should not be treated as interchangeable reports.
Related
- Excel Strikethrough Shortcut: How to Cross Out Text
- Keyboard Shortcut to Capitalize Text in Excel: What Works
- Excel Unmerge Shortcut: How to Unmerge Cells on Windows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete a Page Range in Word Without Losing TextTo delete a page range in Word, select the content from the beginning of the first page through…
- How to Add a Drop-Down List in Excel: Step-by-Step GuideTo add a drop-down list in Excel for Microsoft 365 on Windows, select the cell or cells, choose…
- How to Unhide Rows in Excel All at Once on WindowsTo unhide every hidden row on an Excel worksheet at once, click the Select All button at the…
- Proper Excel Formatting: Clear, Reliable Spreadsheet RulesProper Excel formatting makes a spreadsheet easier to scan, understand, update, and print. In Microsoft Excel for Microsoft…
- Excel Split Cell in Half Horizontally: Real OptionsNo, Excel cannot split one cell into two independent cells with a horizontal boundary; every cell belongs to…
- Excel Get Data From Table or Range: A Practical GuideTo get data from a worksheet table or cell range into Power Query in Excel for Microsoft 365…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.