How to Create a Profit and Loss Statement in Excel

A Profit and Loss (P&L) statement is an essential financial document that helps businesses track revenue, expenses, and overall profitability. While some companies have complex P&L statements, the fundamental structure remains the same. In this guide, we’ll walk you through creating a simple yet effective P&L statement in Excel from scratch. We’ll also explore how to use Excel’s built-in templates for an even easier setup.

Note that this originally appeared on our YouTube channel with a video walkthrough. If you prefer to work with free spreadsheet software, we’ve got you covered. There’s also a free Google Sheets P&L Statement spreadsheet template.

Step 1: Set Up Your Spreadsheet

Begin by opening a new Excel worksheet. The P&L statement will typically have months across the top (columns) and different financial categories along the left side (rows).

1. Label the first row with the months of the year. Enter “January” in the first column and use Excel’s autofill feature by dragging the corner of the cell to fill in the remaining months.
2. Adjust the column width for better readability.

Note that there are other, similar sheets you can use to track specific parts of your business. Income statements can give you a deeper view into specific revenue sources. And Excel inventory templates can track what’s in stock.

Step 2: Add Income Categories

Your P&L statement should start with the income section, which lists all sources of revenue.

1. In column A, enter categories such as “Income” and list specific revenue sources below, like “Product 1” and “Product 2.”
2. Enter sample values under each month. For example, if Product 1 generates $500 in January, enter “500” in the corresponding cell.
3. Sum up the total revenue by using the formula:
   

   =SUM(B2:B4)(Adjust the cell range based on your actual data.)

4. Format the numbers as currency by selecting the cells and clicking on the currency format in the toolbar.

Categories should be made especially for your business. Depending on your industry, there may be standard methods for measuring income. If you’re not sure where to start, look for publically-traded companies with P&L statements you can review as a guide.

Step 3: Calculate the Cost of Goods Sold (COGS)

To determine gross profit, subtract the cost of goods sold (COGS) from the revenue.

1. Label a new section as “Cost of Goods Sold.”
2. Add specific costs like “Packaging” and “Shipping.”
3. Enter estimated costs for each month.
4. Use the sum formula to total these expenses:
   

   =SUM(B6:B7)

Step 4: Determine Gross Profit

Gross profit is calculated by subtracting COGS from total revenue.

1. In a new row labeled “Gross Profit,” enter the formula:
   

   =B5-B8

2. Drag the formula across all months to calculate gross profit for the entire year.

Step 5: List Operating Expenses

Operating expenses include costs such as employee salaries, office equipment, and rent.

1. Create a section labeled “Operating Expenses.”
2. Add specific expense categories such as “Employee Salaries” and “Office Equipment.”
3. Input sample values under each month.
4. Use the sum formula to get total expenses:
   

   =SUM(B10:B12)

If you’re working on an existing template and have already made a bunch of changes yourself, you can also quickly highlight duplicates to help you spot errors.

Step 6: Calculate Net Profit

Net profit (or loss) is calculated by subtracting total expenses from gross profit.

1. In a row labeled “Net Profit,” enter:
   

   =B9-B13

2. Drag this formula across all months.
3. Apply conditional formatting to highlight negative values in red. Go to Home > Conditional Formatting > Highlight Cell Rules > Less Than and set the format for negative numbers.

We have a robust guide on conditional formatting in Google Sheets. It covers much more advanced techniques than we have here.

Step 7: Formatting for Readability

To enhance clarity and presentation:

• Bold key totals like “Gross Revenue,” “Total Expenses,” and “Net Profit.”
• Apply cell borders to separate sections.
• Remove gridlines by going to View > Show > Uncheck Gridlines.
• Use different colors to distinguish income, expenses, and totals.

The industry standard in formatting is still to bold the totals for groups. So if you’re including individual income lines for separate products, those would be unbolded.

Then the sum of those, the total income from products, would be bolded.

Using A Profit and Loss Excel Template (Alternative Approach)

If you want to use our free profit and loss spreadsheet, we’ve got two free versions to choose from. You can get them here. Just remember to make your own copy.

• Here’s the version for Google Sheets.
• Here’s the version for Excel.

If you prefer a ready-made format, Excel offers built-in P&L templates:

1. Go to File > New and search for “Profit and Loss.”
2. Choose a template that fits your needs and click Create.
3. Input your data into the pre-formatted sections.

There are so many existing templates that track profit and loss. If you want to use one that’s already built, you can access it directly from Excel.

Conclusion

Creating a P&L statement in Excel is straightforward and provides valuable insights into your financial health. You can find some spreadsheet templates already inside Excel, but you don’t need to use one of the pre-built statements. It’s easy to build your own.

I hope you enjoyed our guide on simple P&L statements in Excel. They really only take a few minutes to make, and doing so will give you a bespoke spreadsheet made especially for your business.

Whether you build it from scratch or use a template, Excel’s features make tracking revenue, expenses, and profitability easier.