We spend days cleaning Excel files before every report. What should we automate first?

The short answer

If you clean the same Excel files the same way every month, that clean-up is a repeated transformation you have not written down yet. A transformation is any repeatable change from raw data to tidy data: renaming columns, trimming stray spaces, correcting data types, removing duplicates, reshaping a table. When the change is the same every month, a person should not have to redo it by hand.

So automate the parts that are mechanical and identical each month. Fix the worst of the mess at its source, so it stops arriving at all. Keep human judgement and final presentation in Excel, where they belong. Do all three in that order.

Do not try to automate everything at once. Start with the one step you repeat most and trust least. That is where recording the clean-up once saves the most time and removes the most risk.

What this usually looks like

It usually runs like a monthly ritual. Someone exports three or four files: a sales extract, a ledger, a CRM report. Each file is slightly different from the one before. A column header has moved. A new column has appeared. Dates have arrived as text rather than real dates. Amounts carry a stray currency symbol.

The same fixes then get applied by hand. Delete the top two rows. Rename “Cust Name” to Customer. Split one column into two. Look up a product code and paste in its label. It takes the best part of a day, sometimes two. Nobody has written the steps down, so only one person can do them, and the report is late whenever that person is away.

Underneath this is schema variation. The schema is the shape of a dataset: which columns it has, what they are called, and what type each one holds. Schema variation is that shape changing from file to file. It is the single most common reason a monthly export needs cleaning by hand, and the reason naive automation breaks the first time a column moves.

Map the clean-up first

Before you automate anything, map what you already do. The goal is not a flowchart for its own sake. It is to separate the steps a machine should record from the steps a person must keep, and to find where the mess actually starts. You can do this in an afternoon with the same workbook you clean every month open in front of you.

When the map is done, one thing is usually clear. Most of the day is a small handful of mechanical steps, repeated across several files. Those steps are the first things to record, and the map tells you exactly which ones they are.

What the map tells you

Read the map in three passes, because each mark points somewhere different.

• The mechanical steps are your automation candidates. A step that is identical every month and needs no decision is a transformation you can record once and re-run for good.
• The judgement steps stay with a person. When you look at a figure and decide whether it is right, or choose which adjustment to apply, that is not clean-up. It is analysis, and it should stay visible rather than being buried in a script.
• The source notes point upstream. If several cleaning steps all trace back to one bad export, fixing that export removes all of them at once. That is the cheapest win on the whole map.

The order of value follows from this. Fixing at source removes work entirely. Recording a mechanical step removes the repetition. Keeping judgement in Excel protects the part that actually needs a human. Automate in that order and you never automate something you should have fixed or should have kept.

What is actually happening

Under the surface, a few plain ideas explain how a day of clean-up becomes a refresh you press once.

Power Query is the first tool most teams already own. It is a repeatable transformation recorded once and re-run on refresh. In Excel and in Power BI, you perform each clean-up step once in a visual editor. Power Query records the step as code in a language called M. Next month you point the same query at the new file and press refresh. Every step runs again, in the same order, in seconds. The manual afternoon becomes a button.

Moving logic to SQL or a model is the next step when one query is no longer enough. A model is a defined, reusable dataset that lives in a database or warehouse and is built with SQL. You move the clean-up there when more than one report needs the same cleaned data, or when the files are too large to handle comfortably in Excel. Then every report reads one governed copy of the truth, instead of each analyst cleaning a private copy in a slightly different way. This is the same discipline as automating the monthly report safely: move shared logic upstream, and leave the spreadsheet to do only what a spreadsheet should.

Fixing the problem at the source system is the cheapest clean-up of all, because it is the one you never have to do again. If dates arrive as text because an export option is wrong, change the export. If a product code has no readable label, add the label in the system that owns it. A fix at source removes the step from every downstream report at once, and it removes it permanently.

Two failure modes deserve naming, because they are where automation quietly goes wrong.

Schema variation breaks brittle automation. If your steps assume a column sits in a fixed position, a moved column silently shifts every value beneath it and the totals still look plausible. The guard is simple: match columns by name, not by position, and make the refresh fail loudly when an expected column is missing rather than carry on with the wrong data.

Manual overrides are the other trap. A manual override is a value you type over the data by hand: a hard-coded number, a cell you quietly “correct” because it looked wrong. It fixes today and hides tomorrow. The underlying fault returns next month, the override may now be wrong, and nothing warns you, because a typed value carries no rule and raises no alarm. Prefer a rule you can see over a value you typed.

The safeguard against both is a data-quality test, also called an assertion. An assertion is a rule that must be true, checked automatically on every refresh: an amount is never negative, every row has a customer, the total matches the ledger. If the rule fails, the refresh stops or raises an alert. An assertion turns a silent bad file into a visible, named failure that someone can act on. Assertions are the heart of testing that the data is reliable, and they are what separates automation you can trust from automation that fails quietly.

Here is one recorded transformation in Power Query (M) that replaces a morning of manual steps, with an assertion built in.

// Power Query (M): the monthly clean-up, recorded once and re-run on refresh
let
    // read the sheet from this month's workbook
    Source   = Excel.Workbook(File.Contents(SourcePath)){[Item="Sales", Kind="Sheet"]}[Data],
    Headers  = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // match columns by NAME, so a moved column does not shift the data
    Renamed  = Table.RenameColumns(Headers, {
        {"Cust Name", "customer_name"},
        {"Amt (GBP)", "amount_gbp"}
    }),
    Trimmed  = Table.TransformColumns(Renamed, {{"customer_name", Text.Trim, type text}}),
    Typed    = Table.TransformColumnTypes(Trimmed, {
        {"amount_gbp",   Currency.Type},
        {"invoice_date", type date}
    }),

    // assertion: a missing or negative amount must NOT pass silently
    BadRows  = Table.SelectRows(Typed, each [amount_gbp] = null or [amount_gbp] < 0),
    Checked  = if Table.RowCount(BadRows) = 0
               then Typed
               else error "amount_gbp: " & Text.From(Table.RowCount(BadRows)) & " invalid rows"
in
    Checked

Read it top to bottom. The first steps do what you used to do by hand: read the sheet, promote the header row, rename the inconsistent headers to stable names, trim stray spaces, and set proper types. Because the rename step matches on the old name, a column that moves position is still picked up correctly. The last two steps are the assertion. They collect any row where the amount is missing or negative, and if any exist the query raises an error instead of returning data. A malformed file now stops at the door with a clear message, rather than flowing into the board pack unnoticed.

What good looks like

Good is not a spreadsheet with no manual work in it. It is a clear line between the mechanical work a machine does and the judgement a person keeps. In practice it looks like this.

• The clean-up is recorded once as a transformation, in Power Query or in SQL, not repeated by hand each month.
• Columns are matched by name, and the refresh fails loudly when a file does not match the shape it expects.
• The worst of the mess is fixed at source, so fewer cleaning steps are needed downstream at all.
• Assertions run on every refresh and stop a bad file before it reaches the report.
• A named person still reviews the final numbers, approves the commentary, and signs off before anything goes out.
• Anyone competent on the team can run the refresh, so the report is not hostage to one person and one laptop.

This last point is worth holding onto. Excel keeps a real job even when the clean-up is automated. It is where the last mile happens: the board-pack layout, the choice of which chart tells the story, the ordering and formatting, the human sense-check. That is presentation and judgement, and it is exactly what a spreadsheet is good at.

Common ways this goes wrong

• Automating everything at once, including the judgement steps, so the output is a black box that nobody quite trusts.
• Matching columns by position instead of name, so one moved column silently shifts every value and the totals still look believable.
• Burying hard-coded overrides inside the transformation, which hides a recurring error rather than fixing it.
• Recording no assertions, so a malformed file flows straight through to the board pack with nothing to catch it.
• Saving the whole thing as one private macro on one laptop, which simply moves the single point of failure rather than removing it.
• Cleaning in the spreadsheet when the fault is really in the export, so the same fix is rebuilt from scratch every month.

Source problem or spreadsheet problem

Recording the clean-up in Power Query is often the right stopping point, and for a single monthly report it may be all you ever need. It stops being enough when the same fault keeps arriving at source, when several reports need the same cleaned data, or when no spreadsheet fix can stop the problem recurring. At that point you are no longer cleaning a file. You are running a small data pipeline, and it needs to be owned and built like one.

A decision guide

Once the map is done, most steps sort themselves into one of five homes. Use this as a quick lookup for where each kind of clean-up belongs.

The pattern behind the table is simple. Push each fix as far upstream as it will sensibly go. Fix it at source if you can, record it once if you cannot, and keep only judgement and presentation in the spreadsheet. Automate in that order and the monthly clean-up stops being a day you dread and becomes a refresh you trust.