Before You Automate a Spreadsheet, Read It

Picture a workbook called MonthEnd_FINAL_v7.xlsx. Three tabs are pasted in fresh each month: a bank export, a sales report out of Tally, a payroll sheet someone emails across. Ten more do arithmetic nobody has checked in years. The last tab, Final, goes to the owner on the third working day. It takes two days to run, and everyone agrees it should be automated.

That workbook is made up, but you have probably met its cousin. A spreadsheet that has run a process for years is not just a calculation. It is a record of every decision anyone made about that process, most of them undocumented. Automate it without reading it and you get the same mistakes, faster, with nobody watching.

Small VBA tools were part of my own software business from its early years, and I spent years in audit and accounting systems. Both taught me that a workbook knows more than its owner thinks, and less than its owner hopes. Here is how to read one.

Find the inputs

Every future failure enters from outside, and there are always more inputs than the pasted tabs.

• Pasted exports. Note the system, the report, the filters and who runs it. Then check what the export does in an odd month.
• Typed numbers. An exchange rate or a manual adjustment typed in each month is an input, wherever it sits.
• Links to other files. Edit Links, on the Data tab, lists every workbook this one reaches into, each of which someone can rename.
• Constants inside formulas. =B14*1.18 hides a tax rate that will change one day, copied down four hundred rows.

Find the hidden rules

In the formulas

Press Ctrl+` to show formulas, and look for anything that differs from its neighbours. One typed number in a column of formulas is a rule: somebody overrode the calculation once and it stuck. Go To Special, then Constants, finds them all. Watch for a VLOOKUP without FALSE at the end (an approximate match that returns the wrong row when the list is unsorted), and for IFERROR turning useful errors into zeros.

In people’s heads

The rest is not in the file. Sit with the person who runs it, once, start to finish, and write down every step that is not a formula: deleting the last row of the bank export because it is a total, skipping a branch that closed, waiting for one particular email. Those steps stopped being decisions years ago, which is why nobody mentions them, and why an automation gets them wrong first. It is why I map a process as it really runs, not as the manual says, before I automate any of it.

Find what the output is really used for

Follow Final out of the door. Who opens it, which numbers do they read, and does anyone re-type them into SAP, Tally or a bank portal? Often half a workbook answers a question nobody asks any more, while the one figure the owner cares about is calculated in the least reliable place. Automating it faithfully preserves both problems. The output also sets the tolerance: a number that must agree with the bank to the rupee needs different care from an estimate for a planning meeting.

Decide: tidy it, automate it, or replace it

Tidy it when the work is mostly judgement. One input tab per source, constants on a labelled assumptions tab, and a checks tab that compares totals. Cheapest, and sometimes enough.

Automate it with VBA when the record lives in Excel and so do the people. The macro travels inside the file and needs nothing installed, but it is hard to version and only runs when someone presses the button.

Automate it with a script, usually Python, when inputs come from several systems or it should run unattended. The output can still be an Excel file, while the work moves somewhere it can be logged and raise an alert when it fails.

Replace it when the spreadsheet has become a database: several people editing at once, history that matters, permissions. Then it needs a small application with a real database, and the workbook becomes one of its exports.

Whatever you choose, guard the inputs first

Many spreadsheet mistakes are correct formulas run on incomplete inputs. So the first automation worth writing is a guard, not the report:

Option Explicit

' Stops, and changes nothing, if an input sheet is missing or empty.
Public Sub RunMonthEnd()
    Dim sheetName As Variant, ws As Worksheet, problems As String

    For Each sheetName In Array("Bank", "Sales", "Payroll")
        Set ws = Nothing
        On Error Resume Next
        Set ws = ThisWorkbook.Worksheets(CStr(sheetName))
        On Error GoTo 0

        If ws Is Nothing Then
            problems = problems & sheetName & ": sheet is missing" & vbCrLf
        ElseIf Application.WorksheetFunction.CountA(ws.Range("A2:A5000")) = 0 Then
            problems = problems & sheetName & ": no data below the header" & vbCrLf
        End If
    Next sheetName

    If Len(problems) > 0 Then
        MsgBox "Month-end not run." & vbCrLf & problems, vbExclamation
        Exit Sub
    End If

    BuildMonthEndReport   ' the existing routine
End Sub

A wrong number in the owner’s inbox becomes a message saying exactly what is missing. Every automation should fail like that: early, loudly, and before it has changed anything.

Before you start

1. List every input, who produces it, and when it arrives.
2. Show formulas; note every typed value and buried constant.
3. Watch the process once and write down each step outside the file.
4. Find out who reads the output and where its numbers go next.
5. Archive the tabs nobody uses.
6. Choose tidy, VBA, script or replace, and write down why.
7. Build the guard first.
8. Run old and new side by side for a month, and switch only when they agree.