Work order tracking spreadsheet: columns, formulas, routine

A work order tracking spreadsheet needs one row per open job and eleven columns: work order number, property, issue, vendor, vendor email, date sent, days open, last vendor status, status date, next follow-up and state. Add a days-open formula and sort by it every Monday, and the oldest jobs with no vendor answer rise to the top.

This guide shows the columns, the formulas and a weekly routine, and then where a spreadsheet stops being enough.

What columns should a work order tracking spreadsheet have?

Use these, in this order, with a header row you freeze.

A twelfth column for "state" (waiting, scheduled, stalled, done) is useful once you have more than twenty rows.

How do I calculate days open in a spreadsheet?

Subtract the date sent from today. In Google Sheets or Excel, with the date sent in column G and the days open in column H, row 2:

=IF(G2="","",TODAY()-G2)

Fill it down. Format column H as a number, not a date, or you will see a date in 1900. To flag jobs with no vendor answer in seven days, add a conditional format on column H with this custom formula, and colour it red:

=AND($H2>7,$I2="")

Pick your own threshold. Seven days is a common starting point for routine work; emergencies should never reach it.

What is a good weekly routine?

Once a week, on the same day, do four things:

1. Update. Paste the latest open work orders from your property software, or add new rows by hand. Remove or mark done anything closed.
2. Sort. Sort by days open, largest first.
3. Ask. For every row past its next follow-up date with no vendor answer, send the vendor a message. If a vendor has several, put them all in one email.
4. Escalate. Anything that has had four asks over about three weeks and no answer goes on a short call list.

For the message, see the follow-up template in our work order template guide.

Where does a spreadsheet break down?

Three places, and they arrive in this order.

The asking. The sheet tells you who is overdue; it does not ask anyone. The follow-up emails are written and sent by you, row by row, and that is the part that eats the hours. Property managers advertise for assistants whose duties include tracking work orders and getting vendor ETAs, which tells you where the time goes.

The answers. Vendors reply by email, text, phone and voicemail. Someone has to copy each answer back into columns I and J. If that is skipped for a week, the sheet is wrong and you stop trusting it.

The refresh. If your property software holds the real list, the sheet is a copy. Copies drift. Re-importing an export each week and matching by work order number, without duplicating rows, is simple in principle and easy to get wrong in a formula.

Can I get the same list without maintaining the sheet?

Yes, from an export. If your property software or spreadsheet can produce a CSV or Excel file with the open orders and vendor emails, you can make a stuck list from your own export and see every order grouped by vendor with days open and the first email each vendor would get. It stores nothing and emails no vendor.

Open Work-Order Chase goes further: it imports that export, emails each vendor for status at day 3, 7, 14 and 21 through a no-login update page, and sends a one-page stuck list every Monday. See the work order tracking software guide for how to compare options.

Frequently asked questions

Is Excel or Google Sheets better for tracking work orders? Either works. Google Sheets is easier to share with a maintenance coordinator and has the same formulas; Excel is fine if you already live in it.

How many work orders can a spreadsheet handle? The sheet can hold thousands of rows; the limit is how many you can check by hand each week. A few dozen open orders is comfortable, a few hundred is not.

What should I do with closed work orders? Move them to a second tab named for the year instead of deleting them. The work order log guide covers what to keep.