Lorixen.
EN

A Google Sheets Job Tracker With Dropdowns, Status Colours and Filters

Short answer: A solid Google Sheets job tracker needs one row per job, a fixed set of columns, a status column built as a dropdown with coloured chips, conditional formatting that flags overdue work, and filter views so each person can see their own jobs without changing what everyone else sees. Protect the header row and any formula columns so nobody overwrites them by accident. Set it up once in about an hour, and it will carry a small trade or service team until the signs described at the end tell you an app would serve you better.

The scenario

Picture an electrical contractor in Brisbane with four electricians, an office manager and a steady flow of jobs: switchboard upgrades, fault calls, new builds. Jobs currently live in a whiteboard photo, text messages and the manager's head. The goal is one shared sheet that everyone can open on a phone, that shows what is booked, what is waiting on parts and what is overdue.

Step 1: Design the columns first

Decide columns before you type a single job. Changing structure later is where shared sheets fall apart.

Column Example Notes
Job ID J-0142 Unique, never reused
Date received 03/11/2026 Date format, not text
Customer Harris Bakery Name only, details live elsewhere
Suburb Paddington Helps plan runs
Job type Fault call Dropdown
Assigned to Mia Dropdown of staff names
Status Booked Dropdown with colours
Due date 07/11/2026 Date
Quote value 850 Number, no dollar text
Notes Needs ladder access Free text
Days open formula Calculated

Freeze the header row (View, Freeze, 1 row) so it stays visible when scrolling. Keep one tab for jobs and a second tab called Lists for dropdown values.

Step 2: Dropdowns with coloured chips

Google Sheets dropdowns now display as chips, and you can colour each option.

  1. Select the Status column below the header.
  2. Open Data, then Data validation, and add a rule.
  3. Choose Dropdown as the criteria.
  4. Enter options such as: New, Quoted, Booked, Waiting on parts, In progress, Done, Invoiced.
  5. Pick a colour for each: for example grey for New, blue for Booked, amber for Waiting on parts, green for Done.
  6. Under advanced options, choose to reject invalid input so nobody types "bookd".

Repeat for Job type and Assigned to. For staff names, use "Dropdown (from a range)" and point it at the Lists tab, so adding a new electrician means typing one name in one place. Google's help page on data validation covers the options.

Step 3: Conditional formatting rules

Chips colour the status cell; conditional formatting can colour the whole row or other cells based on logic.

Open Format, then Conditional formatting, select the data range (for example A2:K500) and add these rules using "Custom formula is":

  • Overdue: =AND($H2<TODAY(), $G2<>"Done", $G2<>"Invoiced"), light red fill.
  • Due this week: =AND($H2>=TODAY(), $H2<=TODAY()+7, $G2<>"Done"), light yellow fill.
  • Finished: =OR($G2="Done", $G2="Invoiced"), grey text.

The dollar signs lock the column while letting the row change, which is what makes the rule apply row by row. Order matters: Sheets applies the first rule that matches, so put Overdue at the top.

For the Days open column, use =IF(B2="", "", TODAY()-B2) and drag it down, or wrap it in an ARRAYFORMULA in the first row so new rows calculate automatically.

Step 4: Filter views so staff do not disturb each other

A normal filter changes the view for everyone currently in the sheet. If Mia filters to her jobs, the office manager suddenly sees only Mia's jobs too. Filter views fix this: each one is saved, named and private to whoever is using it at that moment.

  1. Open Data, then Filter views, then Create new filter view.
  2. Name it, for example "Mia: open jobs".
  3. Filter Assigned to = Mia and Status not equal to Done or Invoiced.
  4. Sort by Due date ascending.
  5. Save. Repeat for each person and for views like "Waiting on parts" or "Ready to invoice".

Each filter view has its own link. Send Mia the link to her view and bookmark it on her phone's home screen. On mobile, filter views can be selected in the Sheets app too, though the desktop screen is easier to set up.

Step 5: Protect what should not change

Open Data, then Protect sheets and ranges.

  • Protect the header row so columns are not renamed.
  • Protect the Days open formula column.
  • Protect the Lists tab so only the manager can edit staff names and job types.

You can set protection to show a warning rather than block editing, which suits small teams: it stops accidents without locking anyone out. Google's guide to protecting ranges explains both modes.

Step 6: Small habits that keep it clean

  • New jobs always go on the next empty row, never inserted in the middle.
  • Never delete a job; set it to a cancelled status instead so the history stays.
  • Once a month, move invoiced jobs older than 90 days to an Archive tab to keep the main list quick.
  • Use Version history (File, Version history) if someone breaks something; you can restore the sheet to an earlier point.

Signs it is time for an app

A sheet is a great first system. Watch for these signals:

  1. Staff need to attach photos, signatures or forms to jobs on site.
  2. Customers need booking confirmations or reminders sent automatically.
  3. You are copying job details into a quoting or invoicing tool by hand every day.
  4. Several people edit the same rows at the same moment and overwrite each other.
  5. You need different permissions, such as electricians who see only their own jobs and no prices.
  6. The sheet is past a few thousand rows and feels slow on phones.

When three or more apply, a job management tool or a small custom app will probably save more time than it costs.

FAQ

Can each electrician see only their own jobs?

Filter views help them focus, but anyone with access can still open the whole sheet. True per-person restrictions need separate sheets fed by formulas or a proper app.

Will dropdown colours show on phones?

Yes, chips and their colours display in the Google Sheets mobile app. Editing data validation rules is easier on desktop.

How do we stop people typing over formulas?

Protect the formula column with a warning or restriction, and consider putting the formula in the header row with ARRAYFORMULA so there is nothing in the data cells to overwrite.

Is there a row limit?

Google Sheets has a cell limit per spreadsheet that a small job tracker will not reach for years. Speed on phones usually becomes the issue first.

When your team has outgrown the spreadsheet and needs a job app with photos, scheduling and invoicing links, talk to us about it and we can map out the next step.

Sources

WhatsAppBook a call