Tidy this export

Excel Has example + expected result Works for any team Getting data in shape

Turns a messy bank or practice software export into a clean Excel table on a new sheet, then proves nothing was lost with row counts and totals before and after.

Runs best in: Copilot in Excel, on the open workbook. Use Cowork when you want it run across several files or chained with other skills.

Works in:
Copilot in Excel, Copilot Cowork
Chains with:
Run it first on any export, then hand the clean sheet to Bank statement summary, Reconcile two lists, Aged debtors to action, Variance hunter or Year-on-year comparison.
Version:
1.2 (October 2026)
Note:
Includes a known export quirks table for Xero, Sage, QuickBooks Online, FreeAgent, bank CSVs and practice software.

Who it's for

Bookkeepers, accounts seniors and anyone who works with bank statements and practice software exports. It saves time wherever an export has to be cleaned up before the real work can start.

The problem it solves

Exports from banks and practice software arrive with title rows, merged headers, blank lines, subtotals in the middle of the data, dates stored as text and amounts with currency symbols. Before anything useful can be done the data has to be made into a proper table, and tidying it by hand risks deleting or changing a transaction without noticing.

What it does

What the skill does

Give it the export as Excel or CSV. It describes what it sees, takes row counts and column totals from the raw file, then cleans the data on a new sheet: one plain header row, title, blank and subtotal rows removed and counted, dates converted to real dates and amounts converted to numbers. It formats the result as an Excel table, shows a before and after check with any difference and flags any row it couldn't fully clean. The original sheet is never changed.

Use cases

  • Cleaning a bank statement export before bookkeeping
  • Turning a practice software report into a table you can filter and sort
  • Converting text dates and amounts with £ signs into real dates and numbers
  • Removing repeated page headers and subtotals from a long export
  • Preparing a clean list to reconcile with Reconcile two lists

How to use it

Install it, then use it

Install House style first. It's the one file every skill reads for your firm name, sign-off, voice and format. Fill it in once and this skill's Your firm block only needs the settings for this job. Skip it and the skill asks for your firm name and sign-off once.

In Excel

  1. 1 Download the zip file from this page and unzip it. You'll have a folder called tidy-this-export with SKILL.md, README.md and an examples folder inside.
  2. 2 Open SKILL.md in any text editor and fill in the Your firm block at the top, such as your date format, amount format and preferred column names.
  3. 3 In the Copilot pane, open Settings (...), then Manage skills > Custom skills > Create OneDrive folder > Open skills folder.
  4. 4 Put the tidy-this-export folder there, then select Refresh. The folder name must match the skill name, so don't rename it.
  5. 5 Give it the export file and ask it to tidy it.
  6. 6 Check the before and after figures match, then look at any flagged rows.

In Cowork

  1. 1 Download the zip file from this page and unzip it. You'll have a folder called tidy-this-export with SKILL.md, README.md and an examples folder inside.
  2. 2 Open SKILL.md in any text editor and fill in the Your firm block at the top, such as your date format, amount format and preferred column names.
  3. 3 Open Customize > Skills, select the arrow next to Add, then Upload skill, and choose the zip (recommended) or the SKILL.md on its own. If you changed SKILL.md, upload that, or zip the folder's contents again first so SKILL.md stays at the top level.
  4. 4 Or skip the upload and put the tidy-this-export folder in your OneDrive at Documents/Cowork/skills/. Keep the folder name as it is.
  5. 5 Start a new Cowork conversation so it picks up the skill.
  6. 6 Give it the export file and ask it to tidy it.
  7. 7 Check the before and after figures match, then look at any flagged rows.

The skill itself

Read the full SKILL.md

This is the whole skill. Your firm name, sign-off and voice come from House style. Change the settings under Your firm for anything specific to this job. Everything else follows them.

tidy-this-export/SKILL.md
---
name: tidy-this-export
description: "Use when a bank, practice software or accounting system export is too messy to work with: merged headers, blank rows, totals mixed in with data, dates as text, amounts with symbols. Produces a clean Excel table and proves nothing was lost with before-and-after row counts and totals."
---

# Tidy this export

Exports from banks and practice software arrive with title rows, merged cells, blank lines, subtotals in the middle of the data, dates stored as text and amounts with currency symbols. Before anything useful can be done the data has to be made into a proper table. This skill does that and proves it did not lose or change anything along the way.

## Your firm

Shared settings (firm name, sign-off, voice, how dates and money are written) come from the `house-style` skill in this pack. Fill that in once and every skill uses it. If it is not loaded, ask for the firm name and sign-off once at the start and carry on.

Settings specific to this skill. Edit before first use. If a setting is blank but has a default, use the default and say so in one line. If a blank setting has no default and the job cannot be done without it, ask for all such settings together in one message at the start. If the job can be done without it, leave it out and say so. Never make up a value for a firm setting.

- Date format for output: dd/mm/yyyy
- Amount format: numbers with two decimals, no symbols, negative as -1,234.56
- Standard column names the firm prefers (for example Date, Description, Reference, Money In, Money Out, Balance), if any:

## What you need from the user

The export file. Excel or CSV. Nothing else is required; ask only if the structure is genuinely ambiguous (for example two tables on one sheet).

## Method

1. **Look before touching.** Describe what you see in three or four lines: how many rows, where the header row appears to be, what the columns are, what the problems are (blank rows, merged cells, subtotal rows, text dates, symbols in amounts, trailing spaces, mixed case).
2. **Take the before figures from the raw file**: total number of non-blank data rows (excluding title, header, subtotal and total rows, and say how you identified those), and the sum of every amount column. Write them down.
3. **Clean on a new sheet.** Never overwrite the source. Rules:
   - One header row, plain text, no merged cells. Use the firm's standard names if the columns match, otherwise keep the export's names tidied up.
   - Remove title rows, blank rows, page headers that repeat, subtotal and grand total rows. Say how many of each you removed.
   - Dates: convert to real Excel dates. If a date cannot be parsed, leave the original text and flag the row.
   - Amounts: convert to numbers. Strip currency symbols, thousands separators and trailing spaces. Brackets or "CR"/"DR" conventions become negative or positive as appropriate, and say which convention you applied.
   - Text: trim spaces, fix obvious double spaces. Do not change case or wording.
   - Columns that are entirely empty: remove and say so.
   - If the export has money in and money out as two columns, keep them as two columns. Do not net them without being asked.
4. **Format as an Excel table** with a sensible name (for example tbl_BankExport).
5. **Take the after figures** on the clean sheet using formulas: COUNTA on a key column for the row count, SUM on each amount column.
6. **Reconcile.** Before row count must equal after row count. Before sums must equal after sums. Show the check as a small table with Before | After | Difference. If anything differs, do not declare the file clean; list the rows that explain the difference.
7. **Flag rows** you could not fully clean (unparseable date, ambiguous amount) in a Flag column, never silently.

## Known export quirks

Most UK firms see the same handful of sources. Recognise the source from the layout and apply the matching rules before the general method. Formats change between versions and settings, so treat these as what to expect, not a guarantee, and always confirm against the file in front of you.

| Source | What to expect | What to do |
|---|---|---|
| **Xero** bank or account transactions | Separate Spent and Received columns. Dates as text like "02 Sep 2026" or dd/mm/yyyy depending on the organisation's settings. Reference and Description as two columns, sometimes with the payee repeated in both. | Keep Spent and Received as two columns. Parse the text dates to real dates. Do not merge Reference into Description. |
| **Xero** reports (Aged Receivables, Aged Payables, P&L, Balance Sheet) | Three to five title rows (organisation, report name, date), a blank row, then the header. One row per contact with its invoices beneath and a contact subtotal. Ageing buckets labelled Current, < 1 Month, 1 Month, 2 Months, 3 Months, Older. Account names may carry the code ("200 - Sales"). A Total row at the bottom. | Drop the title block and every subtotal and Total row. Keep the contact name on each invoice line (fill down). Split the account code into its own column if present. |
| **Sage 50** and **Sage Accounting** | Debit and Credit columns rather than in and out. Transaction type codes (SI, SC, PI, PC, BP, BR, JD, JC). Aged reports arrive as one block per customer: an account reference row, invoice lines, a subtotal, and a Future bucket alongside the usual ones. Merged header cells. Numbers sometimes stored as text with trailing spaces. Dates dd/mm/yyyy. | Unmerge headers. Convert Debit/Credit to numbers; keep both columns unless told to net. Fill the account reference down onto each invoice line and remove the subtotal rows. Trim and convert text numbers. |
| **QuickBooks Online** reports and transaction exports | A three or four row title block. Column A often empty (used for indenting). Account names indented with leading spaces by level. "Total for X" rows and blank rows between sections. A footer row with the run date and "Accrual basis" or "Cash basis". Negative numbers sometimes in brackets. Dates can be mm/dd/yyyy if the company region is set to US. Transaction detail exports use a single signed Amount column. | Delete empty column A and the footer row. Trim the leading spaces from account names and record the indent level in a Level column if the hierarchy matters. Remove "Total for" rows. Check the date order on a date above the 12th before parsing. Brackets become negatives. |
| **FreeAgent** bank transactions | A single signed Amount column (negative is money out), Date, Description. No running balance. dd/mm/yyyy. | Keep the single signed column unless the firm's standard layout is in and out, in which case split it with formulas and say so. |
| **High street bank** CSV or PDF-to-Excel statements | A title block with account details and statement period. "£" and thousands separators in amounts. Dates like "1 Sep 2026". Repeated header rows at page breaks. Subtotals or "carried forward" rows mid-statement. Opening and closing balance rows mixed in with transactions. Trailing and double spaces in descriptions. | Remove the title block, repeated headers, subtotal and balance rows (but note the opening and closing balances in the reconciliation). Strip "£" and commas. Parse the text dates. Trim spaces. The example file in this skill is one of these. |
| **Practice software** (IRIS, CCH, Digita, TaxCalc, BrightManager) client and job lists | Wide exports with many empty columns, codes rather than names in some columns, dates as text, status fields as numbers. | Remove entirely empty columns and say which. Leave codes in place and add a note that a lookup is needed; do not guess what a code means. |

If the file does not match any of these, say so and follow the general method.

## Output

1. **The Excel file** with the original sheet untouched, a new "Clean" sheet with the table, and a "Check" sheet (or block) with the reconciliation.
2. **A short report**: what was wrong, what was removed and how many rows of each, which conventions were applied, the reconciliation result, and the flagged rows.

## Guardrails

- Never delete a data row. Only title, header, blank, subtotal and total rows go, and each is counted.
- Never change an amount other than converting its format. If a conversion is ambiguous, flag it.
- Never net, group, summarise or reorder transactions unless asked.
- If the reconciliation fails, say so first, before anything else.

Questions

Questions about this skill

Is the skill free?
Yes. Download it, fill in the Your firm block and use it. There is no email form and no charge.
Does it send anything?
No. It writes a new Clean sheet and a Check sheet in the file and gives you a short report. The original sheet is left untouched.
Could it delete a transaction?
No. Only title, header, blank, subtotal and total rows are removed, and each is counted. Row counts and totals are checked before and after, and if they don't match it says so first.
Will it net money in and money out?
No. It never nets, groups, summarises or reorders transactions unless you ask. If the export has money in and money out as two columns, they stay as two columns.
Is there a sample to try?
Yes. The download includes a deliberately messy bank export in the examples folder, with a title block, blank rows, a repeated header, a subtotal in the middle, text dates and amounts with £ signs. The examples folder also has expected.md, which lists the exact figures, flags and fail conditions the skill should produce on the sample, so you can check the result in a minute and re-check it after Copilot updates.

Firmwide

Want your own firm's procedures built in and kept working? That's a Firmwide install.

A fixed-price install, typically six to eight weeks: Copilot made safe and useful in your Microsoft 365, an agent and a skills pack for every department, handed over with training. Then Run keeps it working.

See Firmwide

Next step

Want this tailored to your firm?

A free 30-minute Copilot Readiness Check with James, online. You leave knowing where Copilot can and cannot see your data, the two jobs to automate first and a rough install price. Your own skills, built from your procedures and handed over to each department on its own work.

Related skills

All skills
  • Excel Has example + expected result Works for any team

    Reconcile two lists

    Give it two lists that should agree. It matches them and builds a reconciliation sheet with formulas, with control totals proving the difference is fully explained.

    Job
    Getting data in shape
    Works in
    Copilot in Excel, Copilot Cowork
    Chains with
    Tidy this export cleans the inputs first. Unmatched invoices go to Aged debtors to action for chasing.
    Version 1.2 Download Reconcile two lists
  • Excel Has example + expected result Works for any team

    Variance hunter

    Builds a variance table with formulas, flags the movements over your threshold, labels them favourable or adverse and drafts the commentary.

    Job
    Month-end and reporting
    Works in
    Copilot in Excel, Copilot Cowork
    Chains with
    Tidy this export and Spreadsheet health check prepare the source. The variance sheet feeds Numbers to board slides. Commentary uses House style.
    Version 1.2 Download Variance hunter
  • Cowork

    Quarter chaser (MTD)

    Before each MTD for Income Tax quarterly deadline, it drafts a personalised chaser for every client still owing records and builds a chase log in Excel.

    Job
    Credit control and deadlines
    Works in
    Copilot Cowork
    Chains with
    Where clients send records comes from New client onboarding and House style. Chaser emails use House style.
    Version 1.2 Download Quarter chaser (MTD)