In 2013, three economists at the University of Massachusetts Amherst tried to reproduce a widely cited finding about public debt and economic growth. Working from the original authors’ own spreadsheet, Thomas Herndon, Michael Ash and Robert Pollin found an average that was meant to cover rows 30 to 49 but stopped at row 44, which left five countries out of the calculation. It was one of three problems they reported in a result that had already been cited in debates over government budgets.¹

The same risk runs through all spreadsheet automation. Formulas calculate, macros replay steps you recorded, and scripts run small programs on a schedule or when something happens; each repeats your logic faithfully, mistakes included, so build in this order:

1. Put the data in a table first
2. Replace retyped numbers with formulas
3. Record a macro for the clicks you repeat
4. Move to a script when the job needs a clock
5. Test with answers you already know

The three levels of spreadsheet automation, from formula to script

Spreadsheet automation works at three levels, and each hands more of the work to the computer. A formula calculates inside the sheet. A macro replays a series of steps you recorded once. A script is a program, usually a recording you have edited or code written on purpose, that can run on its own at a set time or when something happens.

The difference is what each one remembers. A formula remembers a calculation, so when an input changes, the answer changes with it. A macro remembers actions: when you record one in Excel, the Macro Recorder writes almost every move you make as code in Visual Basic for Applications (VBA), including clicks you did not mean to make.² Google Sheets does the same in its own language, saving each recorded macro as an Apps Script function.³ A script is that kind of code put to work beyond a recording: Apps Script, for example, can run a function at set times or intervals, or when someone submits a form.⁴

Take a weekly sales report. A formula column works out each rep’s commission. A macro takes the raw export every Friday and deletes the blank rows, sorts by region and applies the house formatting at one keystroke. A script could do all of that at 7 on Friday morning, before anyone arrives, and email the summary to the team.

1. Formulas: calculate inside the sheet and update when their inputs change
2. Macros: replay a series of steps you recorded once, on demand
3. Scripts: programs that can run on a schedule or when something happens, and can reach other files and apps

Each step up buys reach and costs upkeep. A formula sits in plain view in its cell; a macro’s code sits out of sight in an editor; a script may run while nobody is watching. This guide stays with the sheet itself; choosing which recurring task to automate at all, and keeping any automation watched, is covered in workflow automation for beginners.

Automation repeats whatever the sheet gets wrong

Errors are ordinary in working spreadsheets, and automation copies them as faithfully as it copies everything else. Close audits of working spreadsheets, collected by the spreadsheet-error researcher Raymond Panko in a 2015 review, found errors in 94 percent of the 85 sheets examined; most of the audits were done by commercial auditing firms and reported between 1995 and 2001.⁵ Old and mostly commercial as those audits are, the working assumption they support is plain: a sheet nobody has checked probably holds a mistake somewhere.

Myth

A spreadsheet that has been in use for years must be right by now.

Fact

Close audits of 85 working spreadsheets, reported from 1995 to 2001, found errors in nearly all of them, and studies consistently find spreadsheet builders overconfident about their sheets.

The reason is arithmetic more than carelessness. Human-error research finds that people slip at a steady rate of a few in every hundred simple actions, such as writing a formula, and Panko argues that a sheet with long chains of formulas gives each slip many chances to reach the bottom line. He also reports that spreadsheet builders are consistently overconfident about their own sheets.⁵

The study

Limited evidence

Enron's email archive, scanned formula by formula (2015)

Felienne Hermans of Delft University of Technology and Emerson Murphy-Hill of North Carolina State University scanned the spreadsheets attached to emails made public during the investigation of the energy company Enron. About a quarter of the spreadsheets that contained formulas showed at least one Excel error value, such as a broken reference or a division by zero. Fewer than 1 in 10 of the files held a test formula that checks a result, and long chains of formulas feeding one another were more common than in a widely studied set of spreadsheets gathered from the web.⁶

An error value is the loud kind of mistake, the kind you can at least see; the authors note that some may simply reflect blank inputs. The quiet kind, like an average that stops five rows short, shows no warning at all, so a scan like this cannot count it. The files are also more than 20 years old and come from one company, so read the quarter as a snapshot of one workplace’s visible errors, not a rate for yours.

For your own sheet, the lesson is to repair first and automate second. Say someone once typed a figure over one rep’s commission formula to settle a dispute. The sheet still looks normal, but a macro that copies that row down every Friday now carries the frozen number into each new row, faster than any person could. Before you record anything, clear the error values and look for numbers typed over a formula, which spreadsheet audit studies generally count as an error in their own right.⁵

Automate from the bottom up, in five steps

Automate a spreadsheet from the bottom up: tidy the data, let formulas do the arithmetic, record a macro for repeated clicks, and write a script only when a job needs a clock or another file. The steps below follow Microsoft’s and Google’s documentation and the error research above; no study we found compares the levels head to head.

1. Put the data in a table first

A formula or macro needs to know where the data ends, and a plain range of cells does not tell it. In an Excel table, a formula typed once in a column fills the whole column automatically, and formulas can refer to a column by its name instead of to fixed cell addresses.⁷ Type or paste a new row just below the table and the table expands to take it in.⁸ That is the fix for the opening’s mistake: a range that ends at a fixed row misses whatever is added below it, while a table grows with the data.

While you are tidying, set any column that holds codes, IDs or product numbers to plain text before you paste into it. In a 2021 test by Mark Ziemann’s group at Deakin University, Excel and Google Sheets turned gene names such as MARCH3 into dates when the names were opened, pasted or typed, and formatting the cells as plain text first prevented it; the same team found such errors in about 3 in 10 of the genetics papers it screened that shared Excel gene lists.⁹

2. Replace retyped numbers with formulas

Every number you copy from a calculator or another file is a chance to slip, and it goes stale the moment its source changes. A formula works from the source values and updates with them. In the sales report, the commission total becomes a sum of the table’s commission column rather than a figure typed in each Friday.

A formula is still code, though, and code can be wrong while looking right, as the debt spreadsheet showed.¹ Keep each assumption, such as a commission rate, in one labelled cell and point every formula at it. Hermans and Murphy-Hill counted numbers typed inside formulas among the warning signs, or “smells”, they scanned for.⁶ A rate buried in dozens of formulas has to be found and changed in every one of them. A quick test before step 3: if changing one assumption means editing more than one cell, the sheet is not ready for a macro.

3. Record a macro for the clicks you repeat

A macro earns its place when you make the same series of clicks every week: the Friday clean-up of a raw export is the classic case. Microsoft’s guidance carries three warnings. The recorder keeps your mistakes, so practise the steps before recording and keep each macro short. A macro recorded on a range runs only on that range, so a row added later is skipped. And a macro cannot be undone, so save the workbook or work on a copy before running it the first time.²

In Google Sheets, a recorded macro can be tied to a keyboard shortcut and run again, typically in a different place or on different data.³ Recording into a table rather than a fixed block of cells helps with the skipped-row problem.

Run only macros you wrote or received from someone you trust. Because VBA macros are a common way for attackers to spread malware and ransomware, Microsoft changed Office on Windows to block macros by default in files from the internet, such as email attachments.¹⁰ If a file you did not expect asks you to enable macros, the safe answer is no.

4. Move to a script when the job needs a clock

A macro waits for you to press a key; a script can start itself. Google’s Apps Script can run a function at set times or intervals, or when a form is submitted, and because nobody may be at the screen when a triggered script fails, Apps Script sends an email summarizing the failure instead.⁴ In Excel for Microsoft 365, where your plan includes them, Office Scripts let you record actions without code, edit them in TypeScript and connect them to Microsoft’s Power Automate to run as part of a larger flow.¹¹

Scripts run inside limits. As of September 2026, Google caps a single Apps Script run at 6 minutes and says its limits can change without notice, so a script that works on a small test file can fail on a large one.¹² In the sales example, a script that builds and emails the Friday summary is worth writing only once the macro version has run cleanly for a few weeks.

5. Test with answers you already know

People are poor at finding spreadsheet errors by looking. In nine inspection experiments with more than 1,000 participants in total, people found about 60 percent of the errors on average, Panko’s 2015 review reports, and in his own earlier experiment teams of three caught more than people working alone.⁵ Put the other way, roughly 4 in 10 errors went unfound in those experiments despite a deliberate search, so reading your own formulas again is not enough.

So test the way the result will be used. Feed a new formula, macro or script a few cases whose answers you worked out by hand, including the edge cases: a rep with no sales, a sale exactly on a commission threshold, an empty row. Then add a check cell, such as one that shows “OK” only when the detail rows add up to the report total, the kind of test formula that was rare in the Enron files.⁶ Ask a colleague to look before the sheet feeds anything that matters.

The same routine, as a list to keep beside the file.

WiserHours

Eight checks for an automated sheet

• Convert the data range to a table
• Set code and ID columns to plain text before pasting
• Clear error values and numbers typed over formulas
• Put each assumption in one labelled cell
• Record macros on a copy, one short task at a time
• Enable macros only in files you trust
• Use a script only for jobs that need a clock or another file
• Test with hand-worked answers and add a check cell

Find this checklist online:
https://wiserhours.com/automation/spreadsheet-automation/#checklist-spreadsheet-automation-first-build

Solid on errors, silent on time saved

Research on spreadsheet errors agrees that errors are common in working sheets and hard to spot by inspection; how much time formulas, macros or scripts save, and which level prevents the most errors, has not been measured in any comparison we found. The steps above rest on product documentation and on that error research, not on trials of automation itself. Treat them as sensible defaults, and let the hand-worked test cases from step 5 be the evidence that counts for your own sheet.

The bottom line

The safest spreadsheet automation is the lowest level that does the job: a tidy table, then formulas, then a short macro, and a script only when a job needs a clock or another file. Every level repeats your logic without judgment, so before you let it run on its own, test it against answers you already know.