Excel Business Template for Profit and Loss

How-to basics · Updated November 22, 2026

The fastest way to get a profit and loss statement in Excel is to use the built-in Profit and Loss Statement template (File > New, search “profit and loss”), which comes pre-loaded with formulas for revenue, cost of goods sold, and expenses. If you need a custom version, you can build one in about 15 minutes using a simple layout with SUM formulas and a bottom-line net income calculation.

What a Profit and Loss Statement Actually Shows

A profit and loss (P&L) statement, also called an income statement, summarizes revenues, costs, and expenses over a specific period—monthly, quarterly, or annually. It answers one core question: did the business make money? The standard structure is:

  • Revenue – all income from sales or services
  • Cost of Goods Sold (COGS) – direct costs to produce what you sell
  • Gross Profit – Revenue minus COGS
  • Operating Expenses – rent, salaries, marketing, utilities, etc.
  • Operating Income – Gross Profit minus Operating Expenses
  • Other Income/Expenses – interest, taxes, one-off items
  • Net Income – the final bottom line

This structure is universal, whether you use a template or build your own. The template simply automates the math.

How to Use Excel’s Built-in P&L Template

Microsoft Excel for Microsoft 365 includes a free, professionally designed P&L template. Here’s how to access it:

  1. Open Excel and click File > New.
  2. In the search box, type “profit and loss” and press Enter.
  3. Choose the Profit and Loss Statement template (usually the first result).
  4. Click Create to download it.

The template has two main sections: a monthly breakdown (January through December) and a yearly total column. It includes rows for revenue, COGS, gross profit, operating expenses, and net income. The formulas are already in place—you only need to type your numbers in the input cells. For example, cell C5 might be “Sales Revenue,” and you type your January sales there. The template automatically calculates totals and percentages.

On a Mac, the process is identical: File > New, search, and Create. Excel for the web also has the template, but the search may show fewer results.

Building a Custom P&L from Scratch

If you prefer a simpler or more tailored layout, you can build your own. Here’s a minimal example:

  • Row 1: Title “Profit and Loss – January 2025”
  • Row 3: “Revenue”
  • Row 4: “Product Sales” – enter 5000
  • Row 5: “Service Income” – enter 2000
  • Row 6: “Total Revenue” – formula: =SUM(B4:B5) → result: 7000
  • Row 8: “Cost of Goods Sold” – enter 1500
  • Row 9: “Gross Profit” – formula: =B6-B8 → result: 5500
  • Row 11: “Operating Expenses”
  • Row 12: “Rent” – enter 1000
  • Row 13: “Salaries” – enter 2000
  • Row 14: “Marketing” – enter 500
  • Row 15: “Total Operating Expenses” – formula: =SUM(B12:B14) → result: 3500
  • Row 17: “Net Income” – formula: =B9-B15 → result: 2000

This layout is clean and easy to modify. To add a month, copy the column and adjust the formulas. For a yearly view, use a separate column for each month and a total column with =SUM(B4:M4) for each row.

Key Excel Formulas for P&L Templates

Most P&L templates rely on a few basic functions. The most common are:

  • =SUM(range) – adds up a range of cells, e.g., =SUM(B4:B10) for total expenses.
  • =B6-B8 – simple subtraction for gross profit or net income.
  • =B6/B3 – calculates a percentage, e.g., gross margin = gross profit / revenue.
  • =IF(B3=0,0,B6/B3) – avoids division by zero errors when revenue is zero.

For a more dynamic template, you can use SUMIF to total expenses by category. For example, if you have a list of expenses in column A and amounts in column B, =SUMIF(A:A,"Rent",B:B) would sum only the rent entries. This is useful if you track expenses in a separate sheet.

How to Choose the Right Template for Your Business

Not all P&L templates are the same. Consider your business type and reporting needs:

  • Service business – you may have no COGS, so a template that focuses on operating expenses is better.
  • Retail or manufacturing – you need a template with detailed COGS sections (materials, labor, overhead).
  • Freelancer or sole proprietor – a simple one-page template with a few revenue lines and expense categories is enough.
  • Multi-entity or investor reporting – you might need a template that separates operating and non-operating income.

Excel’s built-in template is a good starting point for most small businesses. If you need more customization, search for “small business profit and loss template” on reputable sites, but always check that the formulas are correct and that it uses standard accounting structure.

Common Mistakes and How to Avoid Them

Even with a template, mistakes happen. Here are the most frequent pitfalls:

  • Mixing cash and accrual accounting – decide whether you record income when received (cash) or when earned (accrual). Consistency is key.
  • Forgetting to update formulas – if you add a new expense row, make sure the SUM range includes it. Use Excel’s Format Painter or copy an existing row to keep formulas aligned.
  • Entering numbers as text – if a cell shows a green triangle, the number might be stored as text, breaking calculations. Select the cells and click the warning icon to convert to number.
  • Not separating one-time items – large, unusual expenses (like equipment purchase) should be listed separately under “Other Expenses” to avoid distorting operating income.

A quick check: after entering data, verify that the net income formula matches a manual calculation. Use Excel’s Trace Precedents (Formulas > Trace Precedents) to see which cells feed into a formula.

Frequently Asked Questions

Can I use a P&L template for tax filing?
No, a P&L is an internal management report, not a tax form. Tax authorities require specific forms (like Schedule C in the US). However, your P&L provides the numbers you need to fill those forms, so it’s a useful starting point.

How often should I update my profit and loss statement?
Most businesses update it monthly to track trends and catch problems early. Weekly updates are common for high-volume businesses, while quarterly is enough for some service firms. The key is consistency—update it on the same day each period.

What’s the difference between a P&L and a balance sheet?
A P&L shows performance over a period (income and expenses), while a balance sheet shows a snapshot of assets, liabilities, and equity at a single point in time. They work together: net income from the P&L flows into the balance sheet’s retained earnings.

Can I link my P&L template to my bank statements automatically?
Excel doesn’t natively connect to bank feeds, but you can use Power Query (Data > Get Data) to import CSV files from your bank. Some third-party add-ins offer direct connections. For most users, manual entry or CSV import is sufficient.

Is it safe to store financial data in Excel?
Excel files are not encrypted by default, so you should protect them with a password (File > Info > Protect Workbook > Encrypt with Password). Also, store backups in a secure cloud service. For sensitive data, consider using a dedicated accounting software with built-in security.

Alternatives to Excel Templates

If Excel feels limiting, consider these alternatives:

  • Google Sheets – free, cloud-based, and has similar templates. The main difference is that formulas use the same syntax, but some features like Power Query are absent.
  • QuickBooks or Xero – accounting software that automatically generates P&L statements from your transactions. They cost money but save time on data entry.
  • Wave – a free accounting app that includes P&L reporting. It’s a good middle ground for small businesses.

Excel remains the most flexible option for custom reporting, but for real-time accuracy and multi-user access, dedicated software is often better.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.