How to Automate Excel

Every company keeps one workbook it cannot replace

Microsoft Excel is not really a spreadsheet program in most companies. It is the one file the department is run from, and it has a name like Revenue_Model_2026_v4_FINAL.xlsx.

That file is a file, and everything follows from it. It lives on a network drive or in SharePoint. It gets emailed as an attachment to five people, three of whom send it back changed. It was built by someone who left years ago and has fourteen tabs, four of which anybody still uses, plus one called Raw data that must not be touched.

What sits in those tabs is the serious end of a business. The joint interest billing for a set of wells. The feed conversion by house and flock. The unit scorecard a hospital reviews every month. The 1099 workings for a farm. Nobody rebuilt those in a browser tab, because the logic took years to get right and nobody is volunteering to prove a new one matches.

The model waits for somebody to paste into it

The formulas are finished. What the workbook does not have is this month's numbers.

So somebody signs into the system that holds them, runs a report, downloads a CSV, opens it, checks the columns did not shift, and pastes it into Raw data. Somebody types this week's price off a supplier's screen because there is no download button next to it. Somebody opens a statement PDF and keys in nine figures. Somebody saves the workbook, emails it round, and hopes the version that comes back is the one they sent.

It happens on the same days every month, it takes a full morning, and the morning is always the one before the deadline. The errors it produces are quiet ones: a column pasted one row down, a month included twice, a rate that moved in March and got updated in June.

Excel

Inputs waiting before the refresh Each statement and export downloaded from the system that holds it and dropped into the folder your query already reads, under the name it expects.

A pack that assembles itself The workbook opened, refreshed and turned into this month's report, with the figures that moved called out at the top of it.

Exceptions that reach a person The rows the model flags turned into a short worklist in the place your team already works, instead of a tab nobody opens.

Power Query refreshes from anything that will hand data over

Excel's own automation is stronger than its reputation, and plenty of people paying for something else already own it.

Power Query, in the Get and Transform Data group of the Data tab, connects to a source, cleans it, and remembers every step, so next month is a refresh rather than a rebuild. PivotTables restate the same data as many ways as a meeting demands. Office Scripts record what you do in a workbook and hand it to Power Automate, which will run it on a timer or when an email arrives. Macros written in VBA still carry an enormous amount of finance work.

The ceiling is the connector list. Power Query will talk to a file, a folder, a database, a SharePoint site or a web page whose data is already a table. All of those hand data over when asked.

The systems your figures live in do not. They ask who you are, put the answer behind a date filter, and draw it as a chart. A query cannot sign in, choose last month and click Export, so the automation starts one step too late. It begins when a file is already on a disk, and getting it there is the part that costs the morning.

A workbook whose inputs turn up before it opens

Close that one step and the model you already trust starts running itself.

Each purchaser statement downloaded from its own site on the day it is published, saved into the folder the query points at, under the name the query expects. The daily figure from a dashboard that only ever draws a chart, appended to the raw tab as a dated row so a year of it becomes a trend nobody had before. The rate that changes quarterly, checked on the site that publishes it and written into the input cell with the date it was read.

Then the other direction, because a workbook is rarely the end of the story. The file opened and refreshed, the pack produced, the figures that moved most called out in the covering note. The exception rows the model already calculates, the cost overruns and the failed addresses, lifted out into a worklist that reaches whoever must act on them.

There is a line worth holding. Collecting inputs and refreshing a model can run overnight. Sending the pack to a lender, a regulator or a board should wait for somebody who has read it, because a wrong number in a finished document is hard to take back.

The download is the last manual step

Everything after the file lands is already automatable, and plenty of teams have done that work. Everything before it is a person with a password.

WebRun is an AI agent that works a real Chrome browser, signed in as you. It opens each portal, sets the date range, downloads the export, names it your way and puts it where the workbook expects it. It can open the workbook in Excel for the web afterwards, refresh it, and send the result on.

It runs on your schedule in your own private environment, and you can watch a run and stop it. Nothing that leaves the building goes without a person saying so.

The workflows below are already built, and each one names the systems it collects from.