Cleaning tax data with an AI copilot, part 1: three Excel tricks for a VAT-ready CSV

Nobody tells you that most of tax work isn't tax. It's data entry's grumpy cousin — taking a messy system export and wrestling it into a shape you can actually file. The thinking is the easy part. The hour before the thinking is where the day goes.

The good news: that hour is exactly the part an AI assistant is now good at. Not the judgment — the keystrokes. Below is how I split the two, and then three exact moves I use to turn a raw invoice dump into a VAT-ready CSV.

What actually eats the time

Pull any VAT report out of an ERP or a portal and the same three messes show up, every time:

• Invoice numbers full of junk — stray spaces, slashes, dashes, a rogue apostrophe Excel added itself. They won't match anything downstream.
• Dates in five different shapes — 2026-06-15, 6/15/2026, 15 Jun 2026, half of them stored as text. The tax portal wants one format and will reject the rest.
• Signs that don't match reality — credit notes sitting there as positive numbers, quietly inflating your output tax until someone notices.

None of this needs a brain. All of it needs doing, correctly, before any number you report means anything.

Where AI assistants fit, and where they don't

You have more help here than you think. Microsoft Copilot lives inside Excel and can transform a column on command. ChatGPT and Claude are excellent at writing the exact formula or Power Query step when you describe the rule. Power Query itself turns a one-off cleanup into a pipeline you can re-run next month with one click.

Here's the line I draw: let the assistant write the transformation, never trust it to silently decide. Ask it for a formula or a query step you can read and re-apply — not a magic "just clean this for me" pass you can't reproduce or check. A formula is auditable. A vibe is not.

The failure mode is real: point an assistant at a messy file and say "fix it," and it will confidently change things you didn't ask it to — coerce a text ID into a number, drop a leading zero, reformat a date into American order. AI doesn't fix a messy dataset. It scales whatever you point it at, faster. So you stay the one who decides; it does the typing.

How to prompt an assistant for data cleaning

Three rules make the difference between a clean result and a confident mess:

1. Name the column and the rule precisely. "Clean column A" is a coin flip. "In column A, remove every character that isn't a letter or a number" is a spec.
2. Ask for a formula or a Power Query step, not a one-time edit. You want something you can paste, inspect, and run again on next month's file.
3. Verify a sample by eye. Check five rows, including an edge case (a credit note, an empty cell). If those five are right, trust the column.

Now the part you came for.

Preparing a VAT-return CSV: three tricks

Say you've exported sales invoices and you need a clean CSV: tidy invoice numbers, one date format, and credit notes carrying the right sign. Here are the exact moves — the formula, the prompt, and the thing that bites you.

1. Strip every space and special character from the invoice number

If you're on Excel 365, one function does the whole job — it keeps letters and digits and deletes everything else (spaces, dashes, slashes, dots, apostrophes):

=REGEXREPLACE(A2, "[^A-Za-z0-9]", "")

On older Excel without REGEXREPLACE, kill the spaces with SUBSTITUTE and handle the rest in Power Query:

=SUBSTITUTE(A2, " ", "")

// Power Query — keep only letters and digits
Text.Select([InvoiceNo], {"0".."9", "A".."Z", "a".."z"})

Copilot prompt: "In a new column, take the invoice number in column A and remove every space and special character, keeping only letters and numbers."

What bites you: invoice numbers are text, not quantities. Keep the column formatted as Text before you start, or Excel will eat leading zeros and turn 00123 into 123.

2. Force the date column to DD.MM.YYYY

Reach for a helper column with TEXT, not the cell format. A custom format only changes what you see; when you save to CSV, Excel can still write the date in your system's locale order. TEXT bakes the literal string into the cell, so the CSV gets exactly what you want:

=TEXT(X2, "DD.MM.YYYY")

If column X is stored as text in some other order, convert it first, then format:

=TEXT(DATEVALUE(X2), "DD.MM.YYYY")

Copilot prompt: "Add a column that shows the date in column X as text in the format DD.MM.YYYY."

What bites you: DATEVALUE reads dates using your machine's regional settings, so an ambiguous 03/04/2026 can flip between March and April. Confirm a known date resolves correctly before you trust the column, then paste the helper column back as values.

3. Flip the sign for credit notes (invoice type 3)

This is the one people miss. When the invoice type means "credit note" — say type 3 in column G — the amount, tax, and total should be negative. One formula per money column does it. ABS forces the magnitude first, so it's correct whether the source was already signed or not:

=IF($G2=3, -ABS(C2), ABS(C2))

Repeat for each money column (point C2 at amount, then tax, then total). Or, if you prefer a multiplier:

=C2 * IF($G2=3, -1, 1)

Copilot prompt: "For every row where Invoice Type in column G equals 3, make Amount, Tax, and Total negative. Leave every other row positive."

What bites you: run this once, into helper columns, then paste values. Re-applying a sign flip on already-flipped numbers turns your credit notes back into positives — the exact bug you were fixing.

The rule that keeps you out of trouble

Every one of these is the assistant writing the transformation and you keeping the judgment. It doesn't know that type 3 is a credit note, that this client's invoice IDs carry a meaningful prefix, or that last month's file had a column in a different place. You do.

So the workflow that holds up: describe the rule precisely, get a formula or query you can read, apply it to the column, check five rows, paste values, move on. The assistant buys back the hour. What you do with the hour — deciding whether the numbers are actually right — is still the job.

FAQ

Which assistant should I use? Copilot in Excel for changes in place; ChatGPT or Claude to write a formula or Power Query step from a plain-English rule; Power Query when you'll repeat the cleanup every period. They overlap — the skill is describing the rule clearly, not picking the tool.

Will an AI change my numbers without me noticing? Only if you let it work without a formula you can inspect. Ask for the transformation, apply it yourself, and verify a sample. Never accept a silent "I cleaned it."

Is it safe to paste client data into a chatbot? Treat that as a hard no for confidential returns. Use an in-tenant tool like Copilot, work locally with formulas, or hand the assistant only the column structure and a couple of fake sample rows — enough to write the formula, none of the real data.

Does this work on huge files? Formulas and Power Query scale to hundreds of thousands of rows; public chatbots don't ingest the whole file. Give them the schema and a sample, get the formula, and run it on the full file in Excel.