BookByte

AI for Accounting

Excel Copilot clean data: fix bank exports before you sort

You download a month of bank transactions into Excel. Your SUMIFS come back wrong, and you can’t see why.

By: Computatio · Last reviewed 2026-11-19

Quick answer. As of November 19, 2026, Copilot in Excel has a built-in clean-data skill. Type @clean-data in the Copilot pane. It looks for extra spaces, inconsistent text, number and date formats, duplicate values and values stored as the wrong data type, explains what it found, and lets you pick which fixes to apply. Keep the raw export on its own sheet, and check that your totals still tie to the bank statement after every fix.

Your bank export looks fine, but the formulas break

A bank CSV looks tidy. Then a SUMIFS skips rows, a PivotTable splits one payee into three, and dates sort in the wrong order.

The problem is usually hidden: a trailing space, a date stored as text, or an amount with a dollar sign typed in.

And overlapping downloads double your expenses

You pulled the 1st to the 15th, then the 10th to the 31st. Now five days appear twice. Your expenses look higher than they are, and your reconciliation won’t balance.

What messy data costs you

  • Wrong category totals. “Office Depot” and “Office Depot ” with a space are two payees to a formula. One of them drops out of your summary.
  • Hours lost at month-end. A reconciliation that’s off by one duplicated week can take an evening to trace.
  • Records you have to defend. The IRS can generally assess more tax for 3 years after you file, or 6 years if you leave out income worth more than 25% of the gross income on your return. Clean books built on the real statement are what you show if asked.

Picture this instead

You drop in the export, run one skill, and read a short list of what’s wrong. You approve the fixes one type at a time. Ten minutes later the data sorts, sums and pivots, and the totals still match the statement.

Who gets Excel Copilot clean data?

Microsoft’s support page says the skill is on by default in Copilot in Excel but is still rolling out, so it may not reach every customer yet. Copilot in Excel needs one of these:

  • Microsoft 365 Personal or Family with an AI credits plan
  • Microsoft 365 Premium
  • A commercial Microsoft Copilot subscription
  • A Microsoft 365 or Office 365 business plan that’s eligible for Copilot Chat

Save the file as .xlsx and set calculation to Automatic (Formulas > Calculation Options). Microsoft says Copilot can’t edit a workbook otherwise.

The keep-a-copy method: clean a bank export in 6 steps

  1. Copy the raw data first. Paste the export onto a sheet named “Raw” and don’t touch it. Work on a copy named “Clean”.
  2. Write down three control numbers. On the Raw sheet: row count, total deposits, total withdrawals.
  3. Strip what Copilot doesn’t need. Delete account number columns. Our guide to keeping financial data safe with AI lists what to remove.
  4. Run the skill. On the Clean sheet, open Copilot and paste the prompt below.
  5. Accept fixes one type at a time. Spaces first, then dates, then amounts. Leave duplicates for last.
  6. Check every suspected duplicate against the statement. Two $15.00 charges on the same day can be real. Delete a row only when the statement shows it once.
Prompt to copy:
@clean-data Review the table on this sheet. It is a bank export with Date, Description, Amount and Type columns. List every issue you find, grouped by type, with the cell or row numbers. Do not delete any rows. For possible duplicates, list both rows side by side so I can check them against my bank statement. Wait for me to choose which fixes to apply.
Example (made-up data). A dog-walking business exports October from its bank. The Raw sheet has 48 rows, deposits $4,200.00, withdrawals $1,386.50.
Issues found: 14 dates written as 10/03/2026 and 9 written as 2026-10-17; three amounts stored as text like “$1,200.00”; trailing spaces on “PET SUPPLY CO ”; and 6 rows from October 10–12 that appear twice, from two overlapping downloads.
After checking the statement: 5 of the 6 pairs were true duplicates. The sixth was two real $22.50 parking charges.
Result: 43 rows. Withdrawals fell by the 5 removed rows, $96.40, to $1,290.10. Deposits stayed $4,200.00. The cleaned ending balance ties to the statement.

How to check the cleaned bank export

  1. Rows: Clean rows + removed duplicates = Raw rows.
  2. Totals: Clean deposits and withdrawals + the removed duplicates = Raw totals. List every removed row on a “Removed” sheet.
  3. Balance: the ending balance still matches the bank statement.
  4. Spot-check 5 rows against the statement: date, payee and amount.
  5. Re-sort by date. If anything lands out of order, a date is still text.

If the totals don’t tie, don’t move on. Undo back to the Raw sheet and apply one fix type at a time until you find the change that moved the number.

What the clean-data skill won’t do

It cleans the data. It doesn’t categorize transactions or reconcile your account. Microsoft also lists separate accounting skills, such as reconciliation, that are rolling out to its Frontier program; this guide doesn’t cover them. Microsoft says Copilot doesn’t give accounting or tax advice, so you check its work before it goes into your books.

Not on Copilot? The same checks work with any AI tool. See AI for Excel and Google Sheets bookkeeping. Once the data is clean, use our AI prompts to categorize transactions.

Clean data is step one. Want the rest of the month handled?

Short on time? Rather skip the cleanup? At Computatio, AI does the first pass on your bank and card data and an accountant reviews and reconciles every account each month. Monthly Books is $500 a month.
Free download: 47 write-offs for the self-employed, a 42-page guide with the IRS rules behind each deduction. Free when you join the Computatio newsletter.

Sources

This guide is general information, not financial, tax or legal advice for your situation. We work hard to get it right, but we can make mistakes, and rules change. Check the official sources above before you act.