How to Build a Profit & Loss Statement in Excel

This guide shows you how to build a profit and loss (P&L) statement in Excel from scratch, using a simple layout that tracks revenue, cost of goods sold, operating expenses, and net income. You’ll create a template with formulas that automatically total your income and expenses, so you can update it month after month without redoing the math. No advanced Excel skills are needed—just basic data entry and a few formulas.

What You’ll Need

• A computer with Microsoft Excel (2016 or later, or Microsoft 365) or a free alternative like Google Sheets (the steps are nearly identical).
• Your income and expense records for the period you want to report (e.g., sales receipts, bank statements, invoices).
• Basic familiarity with entering text and numbers into cells.

Step-by-Step

1. Open a new workbook and set up your columns. In row 1, create headers: A1 = “Item”, B1 = “Amount ($)”. In A2, type “Revenue” (this will be a section header). In A3, type “Sales”. In A4, type “Service Income”. In A5, type “Other Income”.
2. Enter your revenue numbers. Click B3 and type your total sales amount (e.g., 50000). B4 for service income, B5 for other income. Leave B2 blank for now—it will hold the total revenue formula.
3. Add a total revenue formula. Click B2 and enter =SUM(B3:B5). This adds all revenue items. Format the cell as currency: right-click, Format Cells, choose Currency.
4. Create the cost of goods sold (COGS) section. In A7, type “Cost of Goods Sold” (section header). In A8, type “Inventory Purchases”. In A9, type “Shipping & Freight”. In A10, type “Direct Labor”. Enter amounts in B8, B9, B10.
5. Add a COGS total formula. Click B7 and enter =SUM(B8:B10). Format as currency.
6. Calculate gross profit. In A12, type “Gross Profit”. In B12, enter =B2-B7. This subtracts COGS from revenue. Format as currency.
7. List operating expenses. In A14, type “Operating Expenses”. In A15, type “Rent”; A16 “Salaries”; A17 “Utilities”; A18 “Marketing”; A19 “Insurance”; A20 “Other Expenses”. Enter amounts in B15:B20.
8. Total operating expenses. Click B14 and enter =SUM(B15:B20). Format as currency.
9. Calculate operating income. In A22, type “Operating Income”. In B22, enter =B12-B14. This shows profit before taxes and interest.
10. Add other income and expenses (optional). In A24, type “Interest Income”; A25 “Interest Expense”. Enter amounts if applicable. In A26, type “Other Gains/Losses” and enter a positive or negative number.
11. Calculate net income before taxes. In A28, type “Net Income Before Taxes”. In B28, enter =B22+B24-B25+B26 (adjust signs based on your items). Format as currency.
12. Add income tax (optional). In A29, type “Income Tax”. In B29, enter the tax amount (you can compute it separately as a percentage). In A30, type “Net Income”. In B30, enter =B28-B29. This is your bottom line.
13. Format the sheet for readability. Bold all section headers (Revenue, COGS, etc.). Use a larger font for totals. Apply currency format to all amount cells. Add a top border above totals to visually separate them.
14. Save your template. Go to File > Save As, choose Excel Template (*.xltx) if you want to reuse it, or save as .xlsx for a single period. Name it something like “P&L Template.xlsx”.
15. Test your formulas. Change a number in B3 and verify that B2, B12, B22, and B30 update correctly. If they don’t, check your cell references.

Common Mistakes

• Mixing up revenue and expense signs. Revenue should be positive, expenses positive (since you subtract them). If you enter expenses as negative, your totals will be wrong.
• Using the wrong range in SUM. Double-check that your SUM formulas include exactly the cells you want. A stray blank cell can be fine, but missing a row will understate the total.
• Forgetting to include all expenses. It’s easy to miss small recurring costs like bank fees or software subscriptions. Review your bank statements to catch them.
• Not separating one-time items. If you put a large one-time sale or expense in a regular line, it distorts your ongoing profitability. Use an “Other” line for unusual items.
• Hard-coding totals. Never type a number in a total cell—always use a formula. Otherwise the total won’t update when you change inputs.

Tips

• Use Excel Tables. Select your data range (e.g., A1:B30) and press Ctrl+T to turn it into a table. This makes formulas auto-fill and keeps ranges dynamic.
• Add a month column. If you want to track multiple periods, put months in columns (Jan, Feb, etc.) and keep the same row structure. Then use SUMIF to total each month.
• Use named ranges. Select B2 and name it “TotalRevenue” in the Name Box. Then your formulas read =TotalRevenue - TotalCOGS, which is easier to audit.
• Add a comparison column. Include a “Budget” column next to “Actual” so you can see variances. Use a formula like =Actual - Budget to show the difference.
• Protect formulas. After building the template, select all formula cells, go to Format Cells > Protection, and check “Locked”. Then protect the sheet (Review > Protect Sheet) to prevent accidental overwrites.

Troubleshooting

• Formulas show #VALUE! or #NAME?. This usually means a cell reference is wrong or a function name is misspelled. Check for typos like SUMM instead of SUM. Also make sure you’re not adding text to a number.
• Totals don’t update when I change inputs. Excel should recalculate automatically. If not, press F9 to force a recalculation. Also check that the cell is not formatted as text (right-click > Format Cells > General).
• Currency format shows $0.00 but the number is hidden. The column might be too narrow. Widen the column by dragging the header edge, or select the cells and click “Increase Decimal” to see more digits.
• I accidentally deleted a formula. Use Ctrl+Z to undo. If you saved and closed, you can restore a previous version via File > Info > Version History (if using OneDrive or SharePoint).
• The sheet is a mess with too many rows. You can hide rows for sections you don’t use (right-click row number > Hide). Or create a clean version by copying only the rows you need to a new sheet.

Frequently Asked Questions

Can I use this template for a service business with no COGS?

Yes. Simply leave the COGS section blank (enter 0) or delete those rows. Your gross profit will equal total revenue, and you’ll go straight to operating expenses.

How do I add a new expense line?

Insert a row within your expense section (right-click row number > Insert). Then update the SUM formula in the total row to include the new cell. If you used an Excel Table, the formula updates automatically.

What’s the difference between operating income and net income?

Operating income is profit from core business operations (revenue minus COGS and operating expenses). Net income is the final profit after adding other income and subtracting taxes and interest.

Can I track multiple months in one sheet?

Yes. Put months across columns (e.g., B=Jan, C=Feb) and keep the same row structure. Use SUMIF to total each month, or simply sum each column.

How do I calculate income tax in the statement?

You can either enter the exact tax amount from your accountant, or use a formula like =B28*0.21 for a flat 21% corporate rate. For personal taxes, use your effective rate—check with a tax professional.

Is this template GAAP-compliant?

It follows the basic structure of an income statement, but for formal reporting you may need additional details like EBITDA, depreciation, and comprehensive income. This template is best for internal management and small business tracking.

Related

• Fix Microsoft Access Error 3011 on Windows 11
• Fix Function Keys Not Working on Windows 10
• Linux Filesystem ACLs: A Practical How-To