A profit and loss report tells you whether your business made or lost money in a given period. In Google Sheets, you can build one that's clean, accurate, and easy to update every month — without paying for accounting software to generate it for you.
This guide walks through building a P&L from scratch, including the structure, the formulas, and how to set it up so monthly updates take minutes instead of hours.
The Structure of a P&L
A P&L has three sections: Revenue, Cost of Goods Sold (COGS), and Operating Expenses. The math flows like this:
- Revenue minus COGS equals Gross Profit
- Gross Profit minus Operating Expenses equals Net Income
Set up your Google Sheet with these sections as row groups. Column A holds the line item labels. Columns B onward hold monthly values — one column per month.
A clean layout looks like this:
| Jan | Feb | Mar
REVENUE | | |
Product Sales | 42,000 | 38,500 | 51,000
Service Revenue | 12,000 | 14,000 | 13,500
Total Revenue | 54,000 | 52,500 | 64,500
Keep line items granular in the source data, then sum them into category totals with SUM formulas.
Setting Up the Formulas
Total Revenue for January:
=SUM(B3:B4)
Gross Profit:
=B5-B8
Where B5 is Total Revenue and B8 is Total COGS.
Net Income:
=B9-B15
Where B9 is Gross Profit and B15 is Total Operating Expenses.
Use SUM for the expense subcategories (salaries, rent, software, marketing, etc.) and reference those totals in the Net Income formula.
Adding Variance vs. Budget
If you have a budget, add two columns next to each month: Budget and Variance. Variance is actual minus budget:
=B5-C5
Apply conditional formatting to variance cells: green if positive (over budget on revenue, under on expenses), red if negative. This makes it immediately obvious where you're off plan.
Month-over-Month Change
Add a % Change column between months:
=IF(B5=0, 0, (C5-B5)/B5)
Format as percentage. This gives you a quick read on whether revenue and expenses are trending in the right direction month to month.
Making It Easy to Update
The part most people get wrong is making the monthly update painful. Fix this by keeping your raw transaction data on a separate sheet — one row per transaction with columns for date, category, amount, and type (revenue or expense). Then use SUMIFS in your P&L to pull totals by category and month:
=SUMIFS(RawData!C:C, RawData!B:B, "Product Sales", RawData!D:D, "Revenue", RawData!A:A, ">="&DATE(2026,1,1), RawData!A:A, "<="&DATE(2026,1,31))
Now you just paste new transactions into the raw data sheet each month and the P&L updates automatically.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have transaction data already in the sheet.
"I have monthly transaction data on Sheet 1 with columns for date, category, type, and amount. Build a P&L report showing revenue, COGS, gross profit, operating expenses, and net income by month, with a variance column comparing to budget."
SheetXAI reads your data, structures the P&L layout, writes the SUMIFS formulas, and adds the variance column.
Example 2: Your data is in your accounting software.
"Pull this year's transactions from QuickBooks and build a monthly P&L report with revenue, COGS, and operating expenses broken down by category."
SheetXAI connects to QuickBooks, pulls the data, and builds the full P&L automatically.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Budget vs. Actuals Report in Excel, How to Create a Cash Flow Forecast in Google Sheets, and Google Sheets AI Guide.
