Yes. In Excel for Microsoft 365 on Windows, choose File > New and search for “profit and loss” or “income statement” to find a suitable workbook template. If the available choices do not fit, build a sheet with revenue, cost of goods sold, operating expenses, and formulas for gross and net profit.
Find a template in Excel
Excel’s template gallery can provide a ready-made starting point, but the available designs can vary by account, version, and catalog availability. After opening a candidate, inspect its labels and formulas rather than assuming it matches your business or reporting method. A template may include example figures, categories you do not use, or calculations that need adapting.
To start from the gallery:
- Open Excel and select File > New.
- Enter profit and loss, income statement, or a related phrase in the template search box.
- Preview a result and open it if its layout suits your needs.
- Replace sample data, confirm that totals and dates work as intended, then save a working copy under a new name.
If you cannot find an appropriate result, a blank workbook is enough: a profit and loss statement is a structured list of income and expenses, and its core calculations use ordinary Excel formulas. Excel for the web also offers templates, though the gallery and commands can look different from the Windows desktop app.
What a profit and loss statement shows
A profit and loss statement, often shortened to P&L, summarizes revenue and expenses over a period, such as a month, quarter, or financial year. Its purpose is to show how the business arrived at a profit or loss for that period. It is not the same as a balance sheet, which reports assets, liabilities, and equity at a particular date, or a cash flow statement, which tracks cash movements.
A simple P&L usually moves through several levels:
- Revenue: sales or service income before relevant deductions.
- Net sales: revenue less items such as returns or allowances, when applicable.
- Cost of goods sold (COGS): costs directly associated with goods or services sold, classified consistently with the business’s accounting records.
- Gross profit: net sales less COGS.
- Operating expenses: costs of running the business, such as rent, payroll, software, or marketing.
- Net profit or loss: the remaining result after other income, expenses, and any tax expense included in the statement.
A business that sells services may have a different direct-cost structure from a retailer. Avoid copying categories just because they appear in a downloaded template; use labels that match the way transactions are recorded and reviewed.
Set up a practical worksheet
For a monthly report, put account names in column A and the months in columns B through M, with an annual total in column N if useful. Put each line item on its own row and use the same row for that item in every month. A compact example layout is:
- Rows 2–3: Revenue and returns or allowances; row 4: net sales.
- Row 6: COGS; rows 7–8: gross profit and gross margin.
- Rows 11–15: individual operating expenses; row 16: total operating expenses.
- Row 18: operating profit; rows 20–22: other income, interest expense, and tax expense; row 23: net profit.
- Row 24: net profit margin.
Enter expenses as positive numbers in this example and subtract them in the calculations. Keep the underlying data consistent: use actual numeric values, enter dates as dates, and avoid typing currency symbols into cells. Apply a currency number format to the amount columns and percentage formatting to margin rows. This makes the figures easier to sort, total, and reuse.
Use formulas for totals and profit
The following worked example assumes January is in column B. It uses $12,000 revenue, $500 in returns, $4,200 COGS, operating expenses of $3,000, $900, $200, and $450, plus $50 other income, $100 interest expense, and $500 tax expense. Adjust row references if your layout differs.
- With revenue in B2 and returns or allowances in B3, enter
=B2-B3in B4. Net sales are $11,500. - With net sales in B4 and COGS in B6, enter
=B4-B6in B7. Gross profit is $7,300. - With gross profit in B7 and net sales in B4, enter
=IFERROR(B7/B4,0)in B8. Gross margin is about 63.48%; format B8 as a percentage. - With operating expenses in B11:B15, enter
=SUM(B11:B15)in B16. The example expenses total $4,550. - With gross profit in B7 and total operating expenses in B16, enter
=B7-B16in B18. Operating profit is $2,750. - With operating profit in B18, other income in B20, interest expense in B21, and tax expense in B22, enter
=B18+B20-B21-B22in B23. Net profit is $2,200. - With net profit in B23 and net sales in B4, enter
=IFERROR(B23/B4,0)in B24. Net margin is about 19.13%.
IFERROR returns zero if a margin calculation encounters an error, such as dividing by zero when there are no sales. That prevents an error message from appearing, but it does not make a zero-sales margin meaningful; consider adding a note or leaving that period blank if that better suits your reporting.
Make the template useful month to month
Once one month calculates correctly, copy its formulas across the other month columns. Relative references such as B2 usually adjust to C2 or D2 when copied horizontally. Check at least one later month to confirm that formulas point to the intended rows and columns. For an annual total, sum the monthly figures for each relevant line; do not simply add percentages to create an annual margin. Calculate an annual margin from annual profit divided by annual sales.
Separate actual results from forecasts if you use the workbook for planning. You might add a second sheet for budget assumptions, or add budget and variance columns beside actuals. Label estimates clearly and avoid overwriting original records. A small chart can help show revenue or profit trends, but retain the underlying statement because a chart alone hides category detail.
Choose the right template and reporting basis
Before settling on a template, consider who will use it and what decisions it needs to support. A sole proprietor tracking a few income and expense categories may need a concise monthly summary. A company comparing departments, products, or locations may need separate categories or a consistent dimension for each. Check whether the template accommodates your reporting period, currency, expense groups, and level of detail without making entry cumbersome.
Also establish whether your figures are prepared on a cash or accrual basis. Cash-basis reports generally record income and costs when cash is received or paid; accrual accounting generally recognizes them when earned or incurred under the applicable rules. Excel does not choose or validate the accounting basis for you. A spreadsheet is a useful organizer, but it is not a substitute for accounting software or a qualified accountant where formal reporting is required. Confirm the appropriate method for your circumstances with a qualified professional.
Common errors to check
A polished template can still produce misleading totals. Review the figures and formulas whenever you adapt a workbook, especially before sharing or relying on a report.
- Mixing periods: make sure every column covers the same dates and that transactions are not counted in two periods.
- Double-counting expenses: do not include a cost both in COGS and again in operating expenses.
- Changing signs midway: if expenses are entered as positive amounts, subtract them consistently; if using negative amounts, design the formulas accordingly.
- Overwriting formulas: distinguish input cells from calculation cells with formatting or labels, and protect formulas if appropriate.
- Using a percentage sum as a margin: calculate margin from total profit and total revenue, not by adding monthly margin percentages.
- Confusing profit with cash: a profitable period does not necessarily mean cash increased by the same amount, because timing and non-cash items can differ.
Use Excel’s formula bar to inspect a total that looks unusual, and compare totals with the source records. A check against accounting-system reports or bank and sales records can reveal missing entries, duplicated amounts, and incorrect classifications. Keep a clean original template and save each reporting period or working version clearly.
When Excel is enough—and alternatives
Excel works well for a simple, manually maintained P&L, a one-off analysis, or a small business that needs a flexible summary. It is easy to tailor categories and add calculations, but the workbook depends on accurate data entry and careful version control. It does not automatically establish that transactions are complete or correctly classified.
Accounting software may be a better source for ongoing business reporting when it can import or record transactions, reconcile accounts, and produce reports from the books. You can still export a report to Excel for analysis, while preserving the accounting system as the underlying record. A PDF or printed report is more appropriate when recipients need a fixed snapshot rather than a working spreadsheet.
Frequently Asked Questions
Does Excel have a free profit and loss template?
Excel’s File > New gallery may offer relevant templates, including income statement-style workbooks. Availability can vary. You can also make a basic P&L in a blank workbook using labeled rows and formulas.
What is the difference between a profit and loss statement and an income statement?
They are commonly used as names for the same type of report: one that summarizes income, expenses, and the resulting profit or loss over a period. The exact format and labels can vary by organization and reporting requirements.
Can I use a P&L template for a personal budget?
You can adapt its income and expense structure to summarize a household budget, but a personal budget is not the same as a business financial statement. Rename categories and choose a period that fits your needs.
How do I calculate profit margin in Excel?
Divide profit by revenue and format the result as a percentage. In the example above, B23 contains net profit and B4 contains net sales, so B24 uses =IFERROR(B23/B4,0); the result is about 19.13%.
Why does my template show a loss when the business made money?
Check that income and expense signs are consistent, the selected dates are correct, and revenue has not been left out or counted in another period. Inspect formulas for overwritten cells and check that expenses are not being added when they should be subtracted.
Related
- Master Excel Custom Number Formats: A Practical Guide
- Master List Excel Template: Create, Organize, and Use It
- Master Contact List Excel Template: Build and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMIFS Between Two Dates: Formula and ExamplesTo sum values in Excel between two dates, use SUMIFS with the date range as a criteria range…
- Excel Formula to Find Unique Names in a List With ExamplesIn Microsoft 365 or Excel 2021 and later, enter =UNIQUE(A2:A100) to return each distinct name from the list…
- Concatenate in Excel With Quotes: Formulas and ExamplesTo concatenate text in Excel with quotation marks, join the parts with the ampersand operator (&) and use…
- Excel Slicer Custom Sort: Control the Order of ItemsTo custom-sort an Excel slicer, create or use an Excel custom list for the item labels, then open…
- How to Show a Unique Count in Excel with a FormulaTo count distinct nonblank values in Excel for Microsoft 365, put your data in A2:A100 and enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0)…
- Outlook Folder Rules: Create Rules to Sort EmailCreate an Outlook rule by choosing a condition, such as a sender or subject phrase, and an action…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.