A profit and loss statement tells you one thing every business owner needs to know: did you actually make money? If you run a small business or freelance, you do not need expensive accounting software to answer that question. Learning how to create a simple profit and loss statement in Excel gives you a clear picture of your income and spending in under an hour, and the spreadsheet you build today can serve you for years.
This guide walks you through the whole process. You will set up a clean layout, enter your revenue and expenses, add the formulas that do the math, and finish with a worked example you can copy. By the end, you will have a reusable template and the confidence to keep your books in order.
What a Profit and Loss Statement Actually Shows
A profit and loss statement, also called an income statement or P and L, summarizes how much money came in and how much went out over a specific period. The basic logic is simple. Start with revenue, subtract the direct costs of what you sold, subtract your operating expenses, and you arrive at net income. If the number is positive, you made a profit. If it is negative, you took a loss.
Lenders, investors, and tax preparers all read this statement first when they want to understand a business. For your own use, it is even more valuable. It shows which months were strong, where your spending is climbing, and whether that new product line or service is pulling its weight. A single glance at a well built P and L can tell you more than a folder full of receipts.
How to Create a Simple Profit and Loss Statement in Excel Step by Step
Before you type a single number, decide on the layout. Excel supports two common approaches, and the right one depends on how complex your finances are.
Choose single step or multi step
The single step method lists all revenue, then lists all expenses, then subtracts to reach net income. It is fast, easy to read, and ideal for freelancers, sole proprietors, and small service businesses with straightforward finances.
The multi step method breaks things into stages. It calculates gross profit first, then operating income, then net income. This version suits businesses that sell products or carry inventory, because it shows the margin at each stage. If you run a shop or an online store, the multi step layout is worth the extra rows.
For this guide, we will build the multi step version because it teaches you every part of the statement. If you are a freelancer with no cost of goods sold, you can simply skip the COGS section and your statement becomes a single step one automatically.
Set Up Your Workbook
Open a new Excel workbook and rename the first sheet to P and L Statement. A clean header block makes the report look professional and keeps you oriented when you print or share it.
In cell A1, type your business name. In cell A2, type Profit and Loss Statement. In cell A3, type the period it covers, for example For the Month Ending October 31, 2026. Clear headers matter more than fancy formatting, so keep this part simple.
Now set up your columns. Column A holds your labels. Column B holds the amounts for the current period. If you want to compare months later, add one column per month across the top and a final column for the year to date total. Most small businesses start with a single month and expand from there.
Enter Your Revenue
Start in column A below your header. Type Revenue as a section label, then list each income stream on its own row. A freelance designer might list Design Services and Template Sales. A bakery might list Bread Sales, Cake Orders, and Catering.
Enter the amounts for the period in column B next to each line. Keep the labels specific. Vague lines like Other Income become a junk drawer where numbers hide. If more than ten percent of your revenue lands in a miscellaneous line, break it into real categories so you can see what is actually driving your business.
List Your Cost of Goods Sold
Cost of goods sold, or COGS, covers the direct costs of producing what you sold. For a product business, that means raw materials, packaging, and direct labor. For a bakery, flour, sugar, and the baker hours tied to production belong here. Shipping costs for sold goods count too.
Service businesses handle this section differently. If you are a freelancer, your COGS might be small or zero. Subcontractor fees you paid to deliver client work, stock photos licensed for a project, or materials used on a job all qualify. Software subscriptions you use to deliver the work can sit here as well. When you are tracking your business expenses, separating these direct costs from general overhead is the single habit that makes your P and L useful instead of decorative.
If nothing in your business fits this category, leave the section out. There is no rule that forces you to include rows with zeros in them.
Calculate Gross Profit
Gross profit is revenue minus cost of goods sold. It tells you how efficiently you produce what you sell before overhead enters the picture. A healthy gross margin is the foundation of a healthy business.
Add a row labeled Gross Profit below your COGS lines. This is the first place Excel starts doing the work for you, and you will see the exact formula in the formulas section below. For now, know that this one number summarizes the earning power of your core operation.
List Your Operating Expenses
Operating expenses are the costs of keeping the business running that are not tied to a specific sale. Rent, utilities, insurance, marketing, website hosting, office supplies, professional fees, and salaries for non production staff all belong here.
Group them into categories that match how you think about your business. Common buckets include Rent and Utilities, Marketing and Advertising, Software and Subscriptions, Professional Services, Travel and Meals, and Office Supplies. Six to ten categories is the sweet spot. Too few and you cannot spot problems. Too many and data entry becomes a chore you will abandon by February.
Enter each expense as a positive number in column B. Excel will subtract them, so you do not need to type minus signs in your data. Consistency is what matters: categorize the same expense the same way every month, or your month to month comparisons will mislead you.
Calculate Net Income
Net income is the bottom line. Take gross profit, subtract total operating expenses, and the result is what the business actually earned. Some statements also include sections for other income, interest expense, and taxes, but a simple statement for a small business can stop here and still be perfectly useful.
This number is the one that matters at tax time, the one that decides whether you can afford to hire, and the one that tells you honestly whether the business is working. If net income is negative for one month, that is information. If it is negative for six months, that is a decision waiting to be made.
Add the Formulas That Do the Math
With your labels and numbers in place, add four formulas and Excel will maintain the statement for you from now on. Assume your total revenue sits in cell B10, total COGS in B14, gross profit in B16, total operating expenses in B22, and net income in B24. Adjust the cell references to match your own layout.
First, total your revenue. In the Total Revenue cell, type =SUM(B2:B9) using the range that covers your revenue lines. Second, total your COGS the same way with =SUM(B11:B13). Third, calculate gross profit with a subtraction formula: type the equals sign, click the total revenue cell, type a minus sign, then click the total COGS cell, giving you =B10-B14.
Fourth, total operating expenses with =SUM(B17:B21). Fifth, calculate net income with =B16-B22. From this point on, you only ever type raw numbers into the revenue and expense rows. Every total updates itself the moment you press Enter. That is the whole magic of building the statement in a spreadsheet instead of on paper.
A Worked Example With Real Numbers
Theory is fine, but a concrete example makes the structure click. Meet a fictional freelance web designer in Austin, Texas, building her October P and L.
Her revenue lines read: Client Projects 6,200 dollars, Website Maintenance Plans 900 dollars, Template Sales 350 dollars. Her total revenue formula returns 7,450 dollars. She paid a subcontractor 800 dollars to help with a large project, so her COGS is 800 dollars and her gross profit is 6,650 dollars.
Her operating expenses for the month: Home Office Rent Portion 450 dollars, Software and Subscriptions 120 dollars, Marketing 200 dollars, Internet and Phone 95 dollars, Professional Development 60 dollars, Office Supplies 35 dollars. The total comes to 960 dollars. Her net income for October is 5,690 dollars.
Notice what the example reveals. Revenue alone looked great at 7,450 dollars, but the P and L shows the real story: nearly 1,800 dollars went out before she paid herself. That gap between revenue and net income is exactly why the statement exists. It also shows her software and marketing costs are modest, so if she wants to grow, those are the levers with the most room.
Track Monthly Columns and a Year to Date Total
A single month is a snapshot. Twelve months side by side is a story. Once your first month is built, copy the label column into column C for the next month, column D for the one after, and so on. Your formulas copy across automatically because Excel adjusts the cell references.
Add a final column at the right labeled Year to Date. In each row of that column, sum across the monthly columns with a formula like =SUM(B2:M2) for twelve months. This column becomes your running scoreboard. It also feeds directly into tax preparation, since your annual totals are already calculated when April arrives.
Comparing months is where the statement earns its keep. Flag any expense category that jumps more than twenty percent month to month and ask why. Celebrate revenue growth, but watch the net income line most closely. Revenue can grow while profit shrinks, and only the full statement will show you that happening.
Common Mistakes That Make Your P and L Wrong
The statement is only as honest as the data behind it. Watch for these traps.
Mixing personal and business spending is the most common one. A business dinner is a business expense. Groceries are not, even if you bought them on the same card. A separate business bank account or card solves most of this before it starts.
Recording income when you invoice instead of when you are paid, or vice versa, creates confusion. Pick one method and stay with it. Cash basis, recording money when it moves, is simplest for most small businesses. Accrual basis matches income to the work period and suits larger operations.
Forgetting irregular costs is another classic. Annual software renewals, quarterly tax estimates, and yearly insurance premiums can ambush a monthly statement. Divide annual costs by twelve and enter the monthly share, or at least note them so the spike months do not surprise you.
Finally, never skip the review step. Each month, scan every line and ask whether each number looks right. Catching a duplicated entry or a misplaced decimal takes two minutes. Finding it six months later during tax prep takes an afternoon. Good record keeping habits, including keeping your business receipts organized, turn the monthly review from a chore into a quick sanity check.
How to Use Your Finished Statement at Tax Time
In the United States, sole proprietors and single member LLCs report business profit on Schedule C of the personal tax return. Your P and L maps almost directly onto that form. Total revenue becomes gross receipts. COGS becomes cost of goods sold. Your expense categories line up with the numbered lines for advertising, office expense, supplies, utilities, and other costs.
Handing your tax preparer a clean twelve month P and L saves you money twice: once in preparation fees and once in deductions you might otherwise miss. Many small business owners discover deductible expenses they had forgotten simply by reading their own statement line by line. If you do your own taxes, the IRS instructions for Schedule C walk through each line, and your spreadsheet already holds every number you need.
One caution: your P and L is a management tool, not tax advice. Unusual situations like home office deductions, vehicle use, and depreciation have specific rules. When the numbers get complicated, a short session with a tax professional pays for itself.
Formatting Tips That Keep It Readable
A statement you cannot read is a statement you will not use. Bold your section labels and totals so the structure is visible at a glance. Use borders under each section total to separate the blocks visually. Format all amount cells as currency with two decimal places so the columns align.
Add a light fill color to your calculated rows, the totals and the profit lines, to distinguish them from cells where you type data. Consider locking or clearly marking those formula cells so a stray keystroke does not overwrite them. A simple trick is to shade every input cell pale yellow and leave formula cells white, then add a legend at the top: yellow means type here.
Keep a second sheet in the same workbook named Transactions. Log every expense there with date, description, category, and amount as it happens. Your monthly P and L then becomes a summary of that log rather than a reconstruction from memory. Future you, the one doing taxes in April, will be grateful.
Conclusion
Learning how to create a simple profit and loss statement in Excel is one of the highest value skills a small business owner can pick up. The whole build takes less than an hour: set up the workbook, enter revenue, list cost of goods sold, total your operating expenses, and let four formulas calculate everything else.
The real payoff comes month after month. A living P and L shows you which income streams matter, which expenses are creeping up, and whether the business is genuinely profitable. It makes tax season dramatically easier, helps you manage your cash flow through slow periods, and gives you the numbers you need to make confident decisions. Open Excel, build your first month today, and make the monthly review a habit your business will thank you for.
Frequently Asked Questions
What is a profit and loss statement in simple terms?
It is a financial summary that shows your total income minus your total expenses over a set period. The result is your net profit or net loss, which tells you whether the business made money.
Do I need accounting software instead of Excel?
Not when you are starting out. Excel handles a simple P and L perfectly for freelancers and small businesses. Consider dedicated software only when your transaction volume grows or you need features like automatic bank feeds and payroll.
How often should I update my profit and loss statement?
Monthly is the sweet spot for most small businesses. It is frequent enough to catch problems early and light enough to maintain. Log transactions weekly so the monthly update takes minutes.
What is the difference between gross profit and net income?
Gross profit is revenue minus the direct cost of goods sold. Net income goes further and subtracts all operating expenses too, so it reflects what the business truly earned.
Can a profit and loss statement show a loss?
Yes, and that is useful information. A negative net income means expenses exceeded revenue for the period. Occasional losses are normal for new or seasonal businesses, but persistent losses signal that costs or pricing need attention.
