Automate Your Weekly Report in Excel (and When to Stop Fighting Excel Entirely)

The report that eats your Monday

Every trades office has one. The dispatcher’s weekly summary: jobs completed, callbacks, revenue by crew, hours logged against hours scheduled. The data lives in ServiceTitan or Jobber. The report lives in Excel. And every Monday somebody rebuilds it: export, paste, fix the columns, refresh the pivot, fix the pivot, email it out.

If that takes two hours a week, it’s 104 hours a year — two and a half working weeks of rebuilding the same file. I’ve spent 24 years in IT and I’ll tell you the same thing I tell every owner who calls me: there is a rung on the ladder for this, and it probably isn’t the rung you think. The honest ladder has four rungs. Most people start too high or stop too low.

Rung 1: Formulas that make the spreadsheet do the arithmetic

If the report is paste-and-calculate (export a CSV, paste it in, sum things up), the cheapest fix is making the workbook receive data instead of interpreting it. XLOOKUP or INDEX/MATCH to pull job details off a paste-in tab. SUMIFS instead of filtering by hand. A pivot table pointed at a named table that grows automatically instead of a fixed range that silently stops at row 500.

This rung costs a few evenings of learning or a couple hundred dollars of a consultant’s time, and it’s the right answer when the report is genuinely small: one export, one paste, one clean summary.

The ceiling is just as real: formulas can only see the data already inside the workbook. The export, the paste, the “format the columns” dance: none of that moves. You’ve made the arithmetic faster and left the ritual intact.

Rung 2: Power Query kills the paste step

Power Query is built into modern Excel and it’s the most underused thing in the building. Point it at the CSV you export every week and it remembers everything you do by hand: remove the junk rows, split the column, fix the dates, merge last week’s file. Next week you export, click Refresh, done.

For a dispatcher’s weekly report, this is often the sweet spot. The export takes ninety seconds because the field system writes it; Power Query does the other ninety minutes because it never forgets a step. Total cost: a few hours of setup, no software to buy.

The ceiling: it’s still one person, one machine, one click. It doesn’t run by itself, it doesn’t email anyone, and when the person who owns the query is on vacation, the report is on vacation too. And if the source export changes format (new column, renamed header), it breaks and waits for a human to notice.

Rung 3: A script takes the human out of the loop

When the report matters on a schedule, stop clicking Refresh and let code do the whole thing: pull the data from the system, build the workbook or the email, send it. VBA can do this inside Excel. A Python script outside it can do more, including talking to ServiceTitan’s or Jobber’s exports, QuickBooks, email, all of it.

This is the rung where the 5 AM version of your life starts. I built exactly this for a CFO who was coming in at 4 AM every Monday to assemble management reports by hand — six hours a week, 312 hours a year, done by one of the most expensive people in the company. That case study is here; the short version is the script does what she did, in the same order, minus the mouse. And now it just runs.

Cost at this rung: real, because somebody has to write and maintain code. But so is the alternative; price your own report ritual in the calculator and see what the manual version already costs you a year. The script usually wins that comparison with room to spare.

The ceiling: if the script’s only job is to keep feeding an Excel file that five people have five opinions about, you’ve automated the horse in front of the cart. Which brings us to the top rung.

Rung 4: A custom build, for when the report outgrew Excel

Some reports aren’t really reports anymore. They’re systems: multiple sources, per-person views, history you’re asked about months later. When your weekly summary needs input from the field system and QuickBooks, when each manager only wants their own numbers, when the file has tabs nobody remembers building, Excel is doing a database’s job under a spreadsheet’s name, and it does that job badly.

There’s a separate article about the five signs you’re using Excel as a database: version chaos, formula rot, the whole set. If two or more of those signs describe your weekly report, don’t buy a better saddle; change horses. A small dedicated build pulls the numbers on schedule, gives every manager their own view, and keeps the history queryable. Excel goes back to what it’s genuinely good at: somewhere to slice numbers when you feel like slicing.

Which rung?

Three questions decide it:

• Does the report depend on one paste? Rung 2, Power Query. Cheapest fix that removes most of the ritual.
• Does it need to exist on a schedule whether or not a human remembers? Rung 3 — a script that runs itself.
• Do multiple people need different numbers from the same sources, with history? Rung 4, and stop forcing Excel to be the system.

One caution from the other side of 24 years: don’t jump rungs for the technology’s sake. Power Query is boring and unglamorous and it’s the right answer a surprising amount of the time. The expensive mistake is rebuilding the same manual report every week because nobody ever climbed the ladder at all.

If you want a second opinion on which rung your Monday lives on, tell me what the report is: what goes in, what comes out, who reads it. I read these emails myself, and I’ll tell you straight, including when the answer costs you nothing.