How to automate Excel, and when a spreadsheet should become a system

Plenty of UK businesses run on spreadsheets: the price list, the job tracker, the monthly report that takes a morning to rebuild. Excel automation can take much of the repetitive work out of them, and Microsoft offers several tools for it. Past a certain point, though, the right move is to stop automating the spreadsheet and move the work somewhere better.

This guide works up a ladder: what Excel automates by itself, connecting it to other systems, the signs a spreadsheet has become a system, and what to move it to. We build business automation and integrations, and we would rather you climbed only as far as you need to.

The ladder at a glance

What Excel can automate by itself

Power Query: the same import, cleaned the same way

If someone downloads a file every week, deletes the same columns and fixes the same dates, that is a job for Power Query, which Excel calls Get & Transform. Microsoft describes four stages: connect to a source (a file, a database or the web), transform the data, combine it with other sources, and load the result into a worksheet or the Data Model. The editor records each change as a step, and every step runs again when you refresh.

Power Query is the safest rung, because the source itself is never changed. Its limit is direction: it brings data into a workbook, but doesn't send anything on or react when something happens elsewhere.

Macros and VBA: powerful, and treated as a security risk

The macro recorder captures what you do as VBA code you can replay and edit. Microsoft's comparison of Office Scripts and VBA says VBA macros "have the same security clearance as Excel", which gives them full access to the desktop. They run in Excel for Windows and Mac, but not in Excel on the web.

Because "VBA macros are a common way for malicious actors to gain access to deploy malware and ransomware", Microsoft changed Office on Windows to block macros in files from the internet by default. For Excel, the change started rolling out to the Current Channel on 27 July 2022. Open a macro workbook that arrived as an email attachment or download, and a Security Risk banner replaces the old option to enable content. To run macros you trust, you unblock the file, save it to a Trusted Location, or sign the macros and trust the publisher.

So a macro workbook sent by email may stop working for whoever receives it.

Office Scripts and Power Automate: automation without opening the file

Office Scripts are the newer option. An action recorder turns your steps into a script, a code editor adds conditions and loops, and a script can reach only the workbook, not the computer. The bigger difference is that Power Automate can run scripts on a schedule or when an email arrives, so the workbook updates without anyone opening Excel.

The catches are in Microsoft's platform limits. Outside a preview for personal plans, Office Scripts need a business or education Microsoft 365 licence and OneDrive for Business, and running them from Power Automate needs a business one. Each user can make 1,600 calls a day to the Run script action, a 120-second timeout means long scripts must be optimised or split, and a script run from Power Automate can't call external services.

Connecting Excel to other systems

The next rung moves data between Excel and other apps. Zapier's own guide to automating Microsoft Excel covers jobs like logging form submissions, sending notifications and syncing new leads. Inside Microsoft 365, Microsoft's own route is Power Automate's Excel Online (Business) connector, a standard connector that reads and writes rows in tables in workbooks kept in OneDrive for Business or SharePoint.

Its documented limits say a lot about spreadsheets as a home for live data:

• Listing the rows in a table returns at most 256 by default unless you turn on pagination, so a flow tested on a small sheet can miss rows once the table grows.
• Files can be at most 25 MB, and a file may stay locked for up to six minutes after the connector last used it.
• Microsoft says users "should avoid writing data to a single Excel file from multiple clients concurrently" (desktop Excel, Excel on the web or flows), as it can cause merge conflicts and inconsistent data.
• If complicated formulas or too many rows make the connector time out, its retry policy can insert the same data more than once.

None of this makes connectors a bad idea: a form that drops rows into a sheet one person reads is a good, cheap automation. The trouble starts when the workbook is both the database behind a flow and a document people edit by hand.

Signs a spreadsheet has become a system

Spreadsheet errors are not a sign of careless staff. Raymond Panko's 2000 paper to the European Spreadsheet Risks Interest Group, Spreadsheet Errors: What We Know. What We Think We Can Do, cites research tracing human error to the limits of human thinking, not sloppiness. Of the field audits of real organisations' spreadsheets it brings together, those from 1997 onwards found errors in 91% of the 54 examined. The only technique then shown to work was cell-by-cell inspection, and in experiments people checking alone caught only about half the errors. Every extra person, copy and link adds risk, so watch for these signs:

• Several people edit it at once. Co-authoring helps, but Microsoft's co-authoring guide says that when two people change the same thing, the last change saved generally "wins", and suggests giving each person their own area or sheet.
• Data is retyped between files. Orders copied from the sales sheet to dispatch, totals into the accounts. Every retype is a chance to mistype.
• Copies multiply. "Prices FINAL v3" on one laptop, a different version in someone's sent items, and nobody sure which is current.
• One person understands it. The macros and the column that must never be sorted live in one head. When that person is on holiday, the process waits.
• It holds data that matters. Customer details, prices, money owed. A workbook is shared as a whole file, so it is hard to let someone see some rows and not others.
• Other things depend on it. A flow reads it and a report pulls from it, so a renamed column breaks something nobody can see.

One or two of these is normal. Three or more, and you are running a system without the safeguards a system would have.

Where to move the work

Before moving anything, map how the work really runs today (our process mapping guide shows how). The map shows which parts of the spreadsheet are records, which are calculations and which are workarounds.

Buy a product, if one fits

Many business spreadsheets are a product nobody has bought yet: a customer list with a "last contacted" column is a CRM, an invoice log is an accounting package. If your process is common, a product built for it usually beats automating a workbook.

Once the customer list lives in a CRM, the copying into it can go too. We built and run Add to CRM, a Chrome extension that adds a person from LinkedIn, Gmail or Outlook to any of 29 CRMs in one click, with a verified email, instead of someone copying a name, role and company by hand. It also makes a check copy and paste never does: when a profile and a CRM record share only a name, it shows a possible match and writes nothing to that record until someone on the team approves.

A database with a simple app in front of it

When the process is your own but not complicated, the next step is a database-backed tool: records in a proper database, a form for each kind of change, access by role and a log of who did what. Excel becomes where you analyse the data, not where it lives, with Power Query pulling from the database for reports.

Bespoke software, when the spreadsheet is how you compete

Some spreadsheets hold the way a business does something no product does, such as a pricing model or a way of scheduling jobs. Those are candidates for bespoke software, built around your rules, with the spreadsheet's logic written down, tested and understood by more than one person. Our bespoke software examples show what that looks like, and when a product is the better buy.

If you want a hand

Power Query and a few well-kept scripts go a long way. If your spreadsheet shows several of the signs above, or you want automation that someone monitors and fixes when it breaks, start a project and tell us which spreadsheet you have in mind.