How to Track Monthly Revenue in Google Sheets (Free Layout and Formulas)

Updated 2026-10-31 · AI Agent Starter Kit

Most small business owners know roughly how the year is going, and very few could say what last March brought in, or which service grew. Your accounting tool has the numbers, but it's built for taxes, not for quick answers like "is this month better than last year?"

A small Google Sheet answers that in seconds. It takes about 30 minutes to set up and 10 minutes a month to keep up. Below is the layout, the exact formulas, how to see growth and where the money comes from, and a short monthly routine.

This is a management sheet, not bookkeeping. Your accountant and your accounting software stay the official record.

Why a separate revenue sheet

Step 1: the Sales tab (one row per job or invoice)

Create a tab named Sales with these columns:

Rules that keep it clean:

• One row per invoice or job. Not per payment, unless you only care about cash.
• Pick one meaning for Date and stick to it: the invoice date (revenue billed) or the payment date (cash received). Most small businesses start with invoice date.
• Use a dropdown for Service, Status and Lead source (Data, then Data validation) so "Deep clean" and "deep cleaning" don't count as two services.
• Amount is a plain number, without tax if you collect sales tax.

Status values: Invoiced, Paid, Refunded. If you track leads already, use the same source names as your lead source tracking sheet.

Step 2: the Monthly tab

Create a tab named Monthly. In A1 type Month, in B1 Revenue, in C1 Jobs, in D1 Average job, in E1 vs last month, in F1 vs last year.

In A2, put the first day of your first month, for example 2026-01-01, and format the column as MMM yyyy. In A3:

=EDATE(A2,1)

Fill it down for 24 rows (two years).

Revenue for the month (B2), counting everything except refunds:

=SUMIFS(Sales!D:D, Sales!A:A, ">="&A2, Sales!A:A, "<"&EDATE(A2,1), Sales!E:E, "<>Refunded")

Jobs for the month (C2):

=COUNTIFS(Sales!A:A, ">="&A2, Sales!A:A, "<"&EDATE(A2,1), Sales!E:E, "<>Refunded")

Average job (D2):

=IF(C2=0, "", B2/C2)

Change from last month (E3, starting on the second row of data):

=IF(B2=0, "", B3/B2-1)

Change from the same month last year (F14, starting 12 rows down):

=IF(B2=0, "", B14/B2-1)

Format E and F as percent. Fill everything down. Future months show 0 until sales arrive.

Year-over-year is the number to watch. Most small businesses are seasonal, so December against November tells you little, and December against last December tells you a lot.

Step 3: where the money comes from

On the right side of the Monthly tab, add two small tables.

Revenue by service this year. List your services in H2 down, then in I2:

=SUMIFS(Sales!D:D, Sales!C:C, H2, Sales!A:A, ">="&DATE(YEAR(TODAY()),1,1), Sales!E:E, "<>Refunded")

Revenue by lead source this year. List sources in K2 down, then in L2:

=SUMIFS(Sales!D:D, Sales!F:F, K2, Sales!A:A, ">="&DATE(YEAR(TODAY()),1,1), Sales!E:E, "<>Refunded")

These two tables usually change decisions. A service that's 40% of the work and 15% of the revenue, or a lead source that brings lots of inquiries and little money, shows up right away.

Step 4: unpaid and a chart

Unpaid right now (one cell, anywhere):

=SUMIFS(Sales!D:D, Sales!E:E, "Invoiced")

If that number keeps growing, see payment reminder email templates.

Chart: select columns A and B of the Monthly tab, then Insert, then Chart, and pick a column chart. Two years side by side make seasonality obvious.

The 10-minute monthly check

On the first business day of each month:

1. Catch up the Sales tab (5 minutes). Compare against your invoices or accounting tool so nothing's missing.
2. Read three numbers: last month's revenue, change from last year, unpaid total.
3. Look at the two source tables. Anything surprising?
4. Write one sentence in a Notes column next to the month: what happened and what you'll do. "Slow because of two cancellations; follow up with the waitlist sooner."

This fits inside a month-end checklist or a monthly business review. If you plan cash, feed the monthly totals into a cash flow forecast, and compare them with your budget and sales goals.

Example: a two-person cleaning company (illustrative)

These numbers are made up to show how the sheet reads.

Jobs are flat, revenue is up. The by-service table shows why: deep cleans grew and one-time standard cleans shrank. The by-source table shows referrals brought in more revenue than paid ads with half the inquiries. The owner moves part of the ad budget into a referral ask.

Common mistakes

• Mixing billed and paid dates. Pick one and say which in the header.
• Free-typing service and source names. Use dropdowns or the totals split.
• Comparing only to last month. Use the same month last year for seasonal businesses.
• Updating it "when there's time." Put the monthly check on the calendar.
• Treating it as your tax record. It's for decisions. Your books are the record.

Using AI here (read-only)

AI can help you read the sheet, not keep it:

• Ask for observations, not numbers. Paste the Monthly tab and ask what changed. Then check every figure it mentions against the sheet; models sometimes invent or misread numbers.
• Formulas do the math. Totals should come from SUMIFS, not from a chatbot.
• No bank logins or payment data in a chatbot. Paste totals, not account details.

Get the weekly side counted for you

Revenue tells you what happened. Leads and replies tell you what's coming. The Weekly CEO Report Kit is an n8n workflow that runs every Monday at 7 AM. It reads your lead sheet and Gmail (and, if you connect it, Google Calendar), counts new leads, first replies, won and lost leads, stuck leads and overdue emails with code rather than AI, has AI write a short plain-English summary, strips out any number the AI made up, saves a one-page report to your Gmail Drafts and logs a row to a Weekly tab. To be clear about what it doesn't do: it doesn't read invoices, payments, your bank or accounting tool, it doesn't track revenue, and it sends nothing to anyone but you. Your revenue sheet stays your own. Get the kit

Want the whole weekly pipeline in one place? The Solo Ops Command Center bundle includes the Weekly CEO Report Kit plus Speed-to-Lead, which logs every new inquiry to a Google Sheet and drafts first replies and follow-ups, and Inbox Chief of Staff, which sorts your Gmail and drafts replies, plus a dashboard sheet with Leads and Weekly tabs and an 8-week trend chart. It tracks leads and weekly activity, not revenue, and every reply waits in Gmail Drafts. Get the bundle

Related: contact form auto-reply email templates, so the leads that become revenue get a fast first answer.

More guides

• How to Set Sales Goals for a Small Business (Worksheet + Examples)
• Black Friday Email for a Service Business: 7 Templates That Don't Cheapen Your Work
• Small Business Budget Template for Next Year (Simple Google Sheets Version)
• n8n Cloud vs Self-Hosted: Which Should a Small Business Choose?
• Lead Source Tracking for Small Businesses: How to Know Where Your Customers Come From
• How to Write a Cancellation Policy for a Small Business (Template + Examples)
• How to Set Weekly Goals as a Small Business Owner (With a Simple Template)
• How to Respond to an Introduction Email: 7 Templates for Small Business Owners
• How to Fire a Client Politely (Email Templates for Ending a Client Relationship)
• How to Calculate Automation ROI for a Small Business (Simple Formula and Worksheet)