# The One Google Sheet That Runs a Small Business > How to structure one spreadsheet so it runs a small service business: the tabs, the columns, the rules, and a Today tab that answers three questions. - Author: Tai Pham Phu - Published: 14 Oct 2026 - Tags: Spreadsheets, Small Business, Business, Automation - URL: https://phamphutai.com/2026/10/14/the-one-google-sheet-that-runs-a-small-business/ Most small businesses already run on a spreadsheet. The problem is that it's usually five spreadsheets. There's the one with customer phone numbers. The one with this month's bookings. A copy of last month's bookings, renamed "FINAL v2". A tab somewhere with who has paid. And a notebook, because the spreadsheet got too confusing to trust. Every morning the owner opens all of them, scrolls, squints, and tries to answer three simple questions. Who are we serving today? Who owes us money? What have we forgotten? This post is about replacing that pile with **one file that answers those questions in ten seconds.** It's not a formula tutorial. You only need five formulas, and I'll name them in plain words. Most of the value comes from structure: what goes in which tab, and a few rules you never break. --- ## The example we'll use To keep this concrete, I'll use one example the whole way through. **It's made up.** Mai runs a small home-cleaning company in Đà Nẵng. She has four cleaners, around sixty regular customers, and does five to ten jobs a day. Some customers pay on the spot, some pay by bank transfer at the end of the month, and a few always "forget." If you run a salon, a tutoring center, a repair shop or a few rental apartments, swap the words. The shape is the same. Customers book something, someone does the work, you send a bill, money comes in (or doesn't), and you spend money to keep going. --- ## The tabs One file. Seven tabs at most. Each tab holds **one type of thing**. 1. **Customers**: one row per customer. 2. **Jobs** (or Bookings): one row per job. 3. **Invoices**: one row per bill you send. 4. **Payments**: one row per payment that actually arrives. 5. **Expenses**: one row per thing you spend money on. 6. **Staff**: one row per person, only if you have a team. 7. **Today**: no typing here at all. It only shows answers. The trick that holds it together is the **ID**. Every customer gets a short code like `C014`. Every job gets `J0231`. Every invoice gets `INV-0102`. When a job belongs to a customer, you don't type the customer's name into the Jobs tab. You type `C014`, and the sheet looks up the rest.

Each tab holds one kind of thing. Short IDs connect them, and the Today tab only reads.

--- ## What goes in each tab Here are the columns I'd start with for Mai. Fewer is better. You can always add a column later. Removing one that people got used to is much harder. ### Customers | Column | Example | |---|---| | CustomerID | C014 | | Name | Ms. Lan | | Phone | 0905 xxx xxx | | Address | 12 Bạch Đằng, Hải Châu | | Area | Hải Châu | | Payment habit | Monthly transfer | | Notes | Has a cat. Key with the guard. | | Since | 2026-03-04 | The **Notes** column is where the gold lives. "Has a cat" is the kind of thing that, if a new cleaner doesn't know it, turns into a lost customer. ### Jobs | Column | Example | |---|---| | JobID | J0231 | | Date | 2026-10-25 | | Start time | 08:30 | | CustomerID | C014 | | Customer name | *(formula, looked up)* | | Service | Deep clean | | StaffID | S02 | | Price | 450,000 | | Status | Booked / Done / Cancelled | Notice that **Customer name is a formula**, not something you type. You type `C014` once and the name appears. More on that below. ### Invoices | Column | Example | |---|---| | InvoiceID | INV-0102 | | CustomerID | C014 | | JobID | J0231, or blank for a monthly bill | | Issue date | 2026-10-01 | | Due date | 2026-10-08 | | Amount | 1,800,000 | | Paid so far | *(formula, from Payments)* | | Balance | *(formula: Amount minus Paid)* | Some businesses invoice every job, so the invoice carries a JobID. Mai bills her monthly-transfer customers once a month for all their jobs, so those invoices only need the CustomerID. Either works, as long as each customer is billed one way. ### Payments | Column | Example | |---|---| | PaymentID | P0457 | | Date | 2026-10-06 | | InvoiceID | INV-0102 | | Amount | 1,000,000 | | Method | Transfer / Cash | Why keep Payments separate from Invoices? Because real life is messy. Ms. Lan pays 1,000,000 now and 800,000 next week. If "paid" were a tick box on the invoice, you'd lose that. With a Payments tab, every bit of money that arrives gets its own row, and the invoice simply adds them up. ### Expenses Date, Category (from a dropdown: Supplies, Fuel, Wages, Rent, Other), Amount, Paid to, Note, and a link to a photo of the receipt. That's it. This tab doesn't need IDs, because nothing else points at it. ### Staff (only if you have a team) StaffID, Name, Phone, Pay type (per job or per day), Rate, Start date. When it's time to pay people, a single formula can count each person's finished jobs for the month. You don't need a separate payroll system for four cleaners. ### Today The front page. We'll build it at the end. --- ## The rules that keep a sheet healthy A spreadsheet doesn't usually die from one big mistake. It dies from a hundred small shortcuts. These rules prevent most of them. ### 1. One row = one thing One row is one customer, one job, one payment. Never two jobs in one row "because it was the same customer." Never a row that says "Lan + her sister." The moment a row means two things, every total underneath it starts lying. ### 2. One tab = one type of thing Don't make a new tab for every month. "October Jobs", "November Jobs" feels tidy, but now every report has to look in twelve places. Keep **all jobs in one Jobs tab** with a Date column, and let formulas pick the month for you. ### 3. Use IDs, never retype names If you type "Ms. Lan" in Jobs and "Lan" in Invoices and "Chị Lan" in Payments, the sheet thinks those are three people. An ID like `C014` is boring, short and impossible to misspell in a way that matters. Type the ID. Look up the name. ### 4. Use dropdowns for anything with a fixed list Status, Service, Payment method, Expense category, StaffID. In Google Sheets, select the column and choose **Insert > Dropdown**, or **Data > Data validation** to add a rule. You can make the dropdown read from a range, so the StaffID dropdown in Jobs always matches the IDs in your Staff tab. Dropdowns stop "Done", "done", "DONE " and "finished" from becoming four different statuses. ### 5. Never type a number that can be calculated If a number can be worked out from other cells, it must be a formula. The balance on an invoice. The total owed by a customer. The number of jobs this month. The moment you type one of these by hand, it's correct for exactly one day. ### 6. Keep raw data apart from summaries The five data tabs are where you **type**. The Today tab (and any monthly summary) is where you **read**. Never type into a summary, and never put a total in the middle of a data tab. Totals in the middle of data get sorted, filtered and copied into places they shouldn't be. ### 7. Freeze the header row **View > Freeze > 1 row** on every data tab. When you scroll down to row 400, you still see which column is which. Small thing, huge difference. ### 8. Protect the formula columns Someone will eventually type over a formula. It might be you, at 11 p.m. In Google Sheets, go to **Data > Protect sheets and ranges** and protect the formula columns. If you're the only editor, choose **"Show a warning when editing this range"**. It doesn't block you, it just asks "are you sure?" first, which is usually enough. Google's own help page says protection isn't a security feature, so treat it as a seatbelt, not a lock. ### 9. Know where your backups are Google Sheets keeps a version history: **File > Version history > See version history**. If something breaks, you can go back to how it looked yesterday. You can also name a version ("End of September, checked") so it's easy to find. Once a month, I'd also download a copy (**File > Download**) and keep it somewhere else. It takes ten seconds. --- ## Five formulas worth knowing You don't need fifty formulas. You need these five. All the examples below are written for Google Sheets, using Mai's tabs. In each one, a range like `Customers!A:A` means "column A of the Customers tab." ### Look something up: XLOOKUP **What it does:** finds a value in one column and returns the matching value from another. This is how the customer's name appears in Jobs when you only typed the ID. In Jobs, column E (Customer name), with the CustomerID in column D: ``` =XLOOKUP(D2, Customers!A:A, Customers!B:B, "Unknown ID") ``` In words: look for D2 in the Customers ID column, and give me the name from the same row. If the ID doesn't exist, say "Unknown ID" instead of showing an error. That last part is useful. A typo in an ID shows up straight away. If you or your team use an older version of Excel that doesn't have XLOOKUP, **VLOOKUP** does the same job for this case: ``` =VLOOKUP(D2, Customers!A:B, 2, FALSE) ``` In words: find D2 in the first column of A to B, return the 2nd column, and only accept an exact match (`FALSE`). Always use `FALSE` here. Without it, VLOOKUP may return a "close enough" customer, which is the worst kind of wrong. ### Add up with conditions: SUMIFS **What it does:** adds up a column, but only for rows that match your conditions. This is how an invoice knows how much has been paid. In Invoices, "Paid so far", with the InvoiceID in column A. In Payments, InvoiceID is in column C and Amount is in column D: ``` =SUMIFS(Payments!D:D, Payments!C:C, A2) ``` In words: add up the payment amounts, but only where the payment's InvoiceID matches this invoice. Ms. Lan pays in two parts, and this adds both. The order matters: in SUMIFS, the column you're adding comes **first**, then pairs of "where to look" and "what to look for." ### Count with conditions: COUNTIFS **What it does:** counts rows that match all your conditions. This is the core of a simple payroll. How many jobs did cleaner S02 finish in October? In Jobs, StaffID is column G, Status is column I, Date is column B: ``` =COUNTIFS(Jobs!G:G, "S02", Jobs!I:I, "Done", Jobs!B:B, ">="&DATE(2026,10,1), Jobs!B:B, "<"&DATE(2026,11,1)) ``` In words: count rows where the cleaner is S02, the job is done, and the date is from 1 October up to (but not including) 1 November. Multiply by the per-job rate in the Staff tab and you have the pay. ### Show a list that matches: FILTER **What it does:** shows every row that meets your conditions, and updates itself when the data changes. This is what powers the Today tab. ``` =FILTER(Jobs!A2:I, Jobs!B2:B = TODAY()) ``` In words: show me every job whose date is today. When you add a job for today in the Jobs tab, it appears here on its own. One thing to know: if nothing matches, FILTER shows an error (`#N/A`). On a quiet day that looks alarming. Wrap it in IFERROR to show a friendly message instead: ``` =IFERROR(FILTER(Jobs!A2:I, Jobs!B2:B = TODAY()), "No jobs today") ``` ### Colour what needs attention: conditional formatting **What it does:** changes a cell's colour automatically when a rule is true. It isn't a formula you type in a cell. It's a rule you add with **Format > Conditional formatting**, then choose **"Custom formula is"**. To turn an overdue invoice row red, select the Invoices data (say A2:G) and use: ``` =AND($G2 > 0, $D2 < TODAY()) ``` In words: if the balance (G) is more than zero and the due date (D) has passed, colour this row. The `$` in front of the letter keeps the column fixed, and leaving the row number without a `$` lets the rule move down row by row. Red should mean "do something." If half your sheet is red, it stops meaning anything. --- ## The Today tab: three questions every morning This is the whole point of the file. Mai shouldn't need to scroll through five tabs at 7 a.m. She opens one tab, and it answers three questions. ### 1. Who are we serving today? A FILTER of today's jobs, sorted by time: ``` =IFERROR(SORT(FILTER(Jobs!B2:I, Jobs!B2:B = TODAY(), Jobs!I2:I <> "Cancelled"), 2, TRUE), "No jobs today") ``` In words: today's jobs, not cancelled, sorted by the 2nd column of the result (the start time), earliest first. With the customer name already looked up in the Jobs tab, each line reads like "08:30, Ms. Lan, Deep clean, S02." If you add a column to Customers called Notes, you can look it up in Jobs too. Then "Has a cat" shows up right next to the job, where the cleaner needs it. ### 2. Who owes us money? Every invoice with a balance above zero: ``` =IFERROR(FILTER(Invoices!A2:G, Invoices!G2:G > 0), "Nobody owes anything") ``` And one number on top, the total owed: ``` =SUMIFS(Invoices!G:G, Invoices!G:G, ">0") ``` ### 3. What's overdue? The same list, but only where the due date has passed: ``` =IFERROR(FILTER(Invoices!A2:G, Invoices!G2:G > 0, Invoices!D2:D < TODAY()), "Nothing overdue") ``` This is the list Mai actually acts on. Not "everyone who owes something," which includes people who were billed yesterday, but "everyone who is late." That's usually a short list, and a short list gets done. You can add more to the Today tab later: jobs tomorrow, money in this month, expenses this month. But start with these three. If the Today tab grows to forty lines, it becomes another thing to scroll, and you're back where you started. --- ## How to move in without losing a week You don't need to rebuild everything on a Sunday. Here's a calmer path: 1. **Make the new file with empty tabs and headers.** Freeze the header rows. 2. **Fill Customers first.** Give every customer an ID. This is the slowest step, and the most valuable. 3. **Start Jobs from today forward.** Don't copy two years of history. If you need old data, keep the old file as read-only. 4. **Add Invoices and Payments for anything still unpaid.** Paid invoices from last year can stay in the old file. 5. **Build the Today tab last.** Then use it every morning for two weeks before changing anything. After two weeks you'll know which columns you never use (delete them) and which ones you keep wishing you had (add them). --- ## When one sheet stops being enough A well-built sheet goes a long way. But it has limits. It doesn't send reminders by itself. It gets slow and fragile when several people edit it at once all day. And it can't read your booking platform or your WhatsApp for you. I wrote a whole post on where that line is: [Your Spreadsheet Keeps Beating Every App You Try. Here's When to Switch.](https://phamphutai.com/2026/10/14/your-spreadsheet-keeps-beating-every-app/) The short version: if the sheet is answering your questions and nobody's fighting over it, keep it. Switching tools has a cost, and as I argued in [Your Real Competitor Is a Spreadsheet](https://phamphutai.com/2026/10/06/your-real-competitor-is-a-spreadsheet/), a good sheet is a hard thing to beat. --- ## If you'd rather not build it yourself Full disclosure: I co-founded [HiTaVa](https://phamphutai.com/projects/hitava/), which sells ready-made Google Sheets and Excel templates for small businesses, with the formulas already written. There are 11 templates (154 sheets in total) covering things like invoicing, payroll, rentals and lead tracking. It's a one-time payment from 49,000 VND, with a 7-day money-back guarantee. But honestly, you don't need it to follow this guide. Everything above can be built by hand in an afternoon or two. A template saves you the setup time and the formula debugging. That's worth paying for to some people and not to others, and both are fine choices. --- ## The takeaway The sheet that runs a business isn't clever. It's **tidy**. - One tab per type of thing. One row per thing. - IDs instead of names. Dropdowns instead of free typing. - Never type a number a formula can work out. - Data tabs for typing, one Today tab for reading. - Protect the formulas, freeze the headers, know where your backups are. Get the structure right and the formulas are easy. Get it wrong and no formula will save you. And every morning, one tab, three questions: **who's due, who owes, what's late.** If your sheet can answer those in ten seconds, it's doing its job, and so are you. --- *Sources: Google Docs Editors Help pages for [XLOOKUP](https://support.google.com/docs/answer/12405947), [SUMIFS](https://support.google.com/docs/answer/3238496), [COUNTIFS](https://support.google.com/docs/answer/3256550), [FILTER](https://support.google.com/docs/answer/3093197), [dropdowns and data validation](https://support.google.com/docs/answer/186103), [conditional formatting](https://support.google.com/docs/answer/78413) and [protecting ranges](https://support.google.com/docs/answer/1218656), checked October 2026.* *Related: [People Don't Pay for Features. They Pay to Do Less.](https://phamphutai.com/2026/10/06/people-pay-to-do-less/), and [Boring Businesses Make the Best Businesses](https://phamphutai.com/2026/10/07/boring-businesses-make-the-best-businesses/).*