Chart of accounts mapping to a new ERP in Excel with Copilot or Claude

A chart of accounts mapping is the table that tells an ERP migration where every legacy general ledger account lands in the new chart. Each old account number gets exactly one destination account, the mapping is applied to the closing trial balance, and the mapped balances are loaded as the opening balances of the new system. If the table is wrong, the new ledger opens with misclassified assets, expenses or liabilities, and every report built on it inherits the error from day one.
Controllers usually build this table in Excel because the legacy chart arrives as an export, the target chart arrives as a spreadsheet from the implementation partner, and the finance team needs a place to argue about the edge cases before anything is loaded. Copilot in Excel and Claude for Excel can both read the two lists, propose a destination for every legacy account and write the lookup formulas that apply the mapping. Both vendors also state in their documentation that output must be reviewed, which in a migration means one specific thing: the mapped trial balance must prove that no balance was lost and no balance changed its account type.
The worked example below uses an illustrative 27-account legacy trial balance and a 23-account target chart. A first AI draft of the mapping balances to zero once the missing account is fixed and still overstates expenses by 12,600 USD, which is the error a type check catches and a total check does not.
··········
WHAT A CHART OF ACCOUNTS MAPPING HAS TO DELIVER.
The mapping table has a simple structure: legacy account number, legacy account name, legacy account type, new account number, new account name, new account type and a column for the reviewer's comment. The rules behind it are stricter than the layout suggests. Every legacy account with a balance or with history that will be converted needs a destination. Each legacy account maps to one new account, so that a lookup returns a single answer. Many legacy accounts can map to one new account, which is the normal result when a new chart consolidates bank accounts, prepaid categories or payroll accruals.
The opposite case, one legacy account that must be split across several new accounts, cannot be handled by a lookup table. It needs an allocation key agreed by the controller, for example a split of a single legacy "Salaries" account into cost of services and operating expense by department, and it should be documented as a separate journal entry in the conversion workpapers. AI tools tend to treat a split as an ordinary mapping and pick one destination, so splits deserve their own column in the table.
The mapping also has to respect account type. An asset maps to an asset, a liability to a liability, equity to equity, revenue to revenue and expense to expense. A mapping that sends a balance sheet account to a profit and loss account changes net income on the opening balance sheet of the new system, even though the trial balance still sums to zero. Teams moving to a multi-entity ERP face the same exercise once per entity, which is one reason the target chart is often designed around the consolidation structure discussed in our comparison of NetSuite and Sage Intacct for multi-entity consolidation.
··········
COPILOT IN EXCEL AND CLAUDE FOR EXCEL: DOCUMENTED REQUIREMENTS.
The requirements below come from Microsoft's Copilot in Excel FAQ and data privacy page and from Anthropic's Claude for Excel documentation, verified on November 20, 2026. Plans and supported builds change often, so they should be checked again before a project starts.
........
Item | Copilot in Excel | Claude for Excel |
Access | M365 Personal/Family with AI credits, M365 Premium, commercial Copilot, or Copilot Chat-eligible business plan | Claude Pro, Max, Team and Enterprise plans |
Form | Built into Excel | Add-in from Microsoft AppSource or admin deployment |
Platforms | Edit, plan and chat on Windows, Mac and web | Excel on the web, Windows (M365 build 16.0.13127.20296+), Mac 16.46+ |
Not supported | Strict Open XML files; manual calculation mode | Data tables, macros and VBA, Excel 2016/2019 perpetual, iPad, Android |
Workbook actions | Formulas, columns, sort, filter, highlight, charts, PivotTables | Multi-tab reading, formulas, cell-level citations, sort, filter, validation lists |
Vendor caution | Review, edit and verify anything Copilot creates | Not for audit-critical calculations without verification |
........
Two operational details matter for a mapping file. Microsoft states that Copilot editing works only when calculation is set to Automatic, and that files saved as Strict Open XML Spreadsheet must be saved again as a standard .xlsx workbook. Microsoft also notes that Copilot in Excel can use Claude or GPT models, and that in enterprise tenants an administrator must enable Anthropic as a Microsoft subprocessor before Claude models are available there. Anthropic recommends using Claude for Excel only with trusted spreadsheets because workbook content can carry prompt-injection text, which is a practical concern when the legacy export comes from an outside implementation partner.
··········
THE WORKED EXAMPLE: LEGACY TRIAL BALANCE AND TARGET CHART.
The data below is illustrative. It represents the December 31, 2026 closing trial balance of a small US services company moving from a four-digit legacy chart to a six-digit ERP chart. Debit balances are positive and credit balances are negative, so the column sums to zero. The last column is the reviewed mapping, which is the result the AI draft should reach.
........
Legacy | Name | Type | Balance (USD) | New account |
1000 | Checking - Operating | Asset | 182,400 | 101000 |
1010 | Savings | Asset | 50,000 | 101000 |
1200 | Accounts Receivable | Asset | 96,750 | 112000 |
1250 | Allowance for Doubtful Accounts | Asset | -4,200 | 112900 |
1400 | Prepaid Insurance | Asset | 7,800 | 115000 |
1410 | Prepaid Software | Asset | 12,600 | 115000 |
1450 | Employee Advances | Asset | 1,500 | 119000 |
1500 | Office Equipment | Asset | 64,000 | 160000 |
1510 | Computer Equipment | Asset | 38,500 | 160000 |
1590 | Accumulated Depreciation | Asset | -41,300 | 169000 |
2000 | Accounts Payable | Liability | -58,900 | 201000 |
2100 | Accrued Wages | Liability | -21,450 | 215000 |
2110 | Accrued Payroll Taxes | Liability | -3,280 | 215000 |
2400 | Line of Credit | Liability | -75,000 | 250000 |
3000 | Common Stock | Equity | -10,000 | 301000 |
3900 | Retained Earnings | Equity | -194,450 | 350000 |
4000 | Consulting Revenue | Revenue | -412,000 | 401000 |
4010 | Software Resale Revenue | Revenue | -96,500 | 402000 |
5000 | Subcontractors | Expense | 148,300 | 501000 |
6000 | Salaries | Expense | 210,600 | 601000 |
6010 | Payroll Taxes | Expense | 18,700 | 602000 |
6200 | Rent | Expense | 42,000 | 621000 |
6300 | Software Subscriptions | Expense | 15,400 | 631000 |
6400 | Insurance | Expense | 9,200 | 641000 |
6500 | Depreciation | Expense | 12,800 | 651000 |
6900 | Miscellaneous | Expense | 2,150 | 690000 |
7000 | Interest Expense | Expense | 4,380 | 701000 |
........
Total debits and total credits are both 917,080 USD. Net income for the year, revenue of 508,500 less expenses of 463,530, is 44,970 USD. The target chart has 23 accounts, from 101000 Cash and cash equivalents to 701000 Interest expense, and each target account carries its own type column so that the type check can be done with a formula.
The prompt for the first draft should describe the rules explicitly. A version that works in both tools reads as follows, with the sheet names adjusted to the workbook.
The sheet LegacyTB lists legacy GL accounts (number, name, type, balance).
The sheet NewCOA lists the target chart (number, name, type).
Create a sheet Mapping with one row per legacy account and columns:
Legacy, LegacyName, LegacyType, New, NewName, NewType, Confidence, Comment.
Rules: every legacy account gets exactly one New account; the New account
must have the same type as the legacy account; if no target fits, write
REVIEW in New and explain why in Comment. Do not invent target accounts.··········
APPLYING THE MAPPING WITH XLOOKUP AND SUMIFS.
Once the mapping sheet exists, the conversion should run on ordinary Excel formulas that a reviewer can audit, so that the AI's role ends at proposing destinations. With the legacy trial balance and the mapping stored as Excel tables named LegacyTB and Mapping, a helper column in LegacyTB pulls the new account and flags anything the mapping does not cover. XLOOKUP's optional if_not_found argument returns the text UNMAPPED instead of an error.
=XLOOKUP([@Legacy], Mapping[Legacy], Mapping[New], "UNMAPPED")A second helper column compares types. It returns TRUE when the destination account has the same type as the legacy account, and FALSE when the mapping moves a balance across the balance sheet and income statement boundary.
=XLOOKUP([@NewAccount], NewCOA[Number], NewCOA[Type], "MISSING") = [@Type]The mapped trial balance on a separate sheet lists the 23 new accounts and sums the legacy balances that point to each of them.
=SUMIFS(LegacyTB[Balance], LegacyTB[NewAccount], [@Number])Three control totals then decide whether the mapping can be loaded: the count of UNMAPPED rows, the count of FALSE results in the type column, and the sum of the mapped trial balance, which must be zero. The same pattern supports the opening-balance journal entry that the new ERP imports, a step covered in our guide to drafting and checking journal entries with Copilot, Claude or ChatGPT.
··········
EXPECTED RESULT OF THE CHART OF ACCOUNTS MAPPING.
Applying the reviewed mapping gives the opening trial balance below. These are the figures an AI-built mapping sheet must reproduce exactly; they were computed independently from the illustrative data.
........
New account | Name | Mapped balance (USD) | Legacy sources |
101000 | Cash and cash equivalents | 232,400 | 1000, 1010 |
112000 | Trade receivables | 96,750 | 1200 |
112900 | Allowance for credit losses | -4,200 | 1250 |
115000 | Prepaid expenses | 20,400 | 1400, 1410 |
119000 | Other current assets | 1,500 | 1450 |
160000 | Property and equipment | 102,500 | 1500, 1510 |
169000 | Accumulated depreciation | -41,300 | 1590 |
201000 | Trade payables | -58,900 | 2000 |
215000 | Accrued compensation | -24,730 | 2100, 2110 |
250000 | Revolving credit facility | -75,000 | 2400 |
301000 | Common stock | -10,000 | 3000 |
350000 | Retained earnings | -194,450 | 3900 |
401000 | Services revenue | -412,000 | 4000 |
402000 | Product revenue | -96,500 | 4010 |
501000 | Cost of services | 148,300 | 5000 |
601000 | Salaries and wages | 210,600 | 6000 |
602000 | Payroll taxes and benefits | 18,700 | 6010 |
621000 | Occupancy | 42,000 | 6200 |
631000 | Software and IT | 15,400 | 6300 |
641000 | Insurance | 9,200 | 6400 |
651000 | Depreciation | 12,800 | 6500 |
690000 | Other operating expense | 2,150 | 6900 |
701000 | Interest expense | 4,380 | 7000 |
........
Debits and credits remain 917,080 USD each, and the totals by type are unchanged from the legacy trial balance: assets 408,050, liabilities 158,630, equity 204,450, revenue 508,500 and expenses 463,530 USD. Net income stays at 44,970 USD.
Now consider a plausible first draft. In this scenario the AI matched names and mapped 1410 Prepaid Software to 631000 Software and IT because both names contain the word software, and it skipped 1450 Employee Advances because no target name resembled it. The table compares that draft with the reviewed mapping.
........
Check | First draft | Reviewed mapping |
UNMAPPED rows | 1 (1450, 1,500 USD) | 0 |
Mapped TB sum | -1,500 USD | 0 |
Type mismatches | 1 (1410 asset to expense) | 0 |
Total assets | 393,950 USD | 408,050 USD |
Total expenses | 476,130 USD | 463,530 USD |
Net income | 32,370 USD | 44,970 USD |
........
The unmapped account shows up immediately, because the mapped trial balance no longer sums to zero. After the reviewer maps 1450 to 119000 Other current assets, the trial balance balances again, yet the prepaid error is still there: assets are understated by 12,600 USD, expenses overstated by the same amount and net income falls from 44,970 to 32,370 USD. Only the type check flags it. A zero-sum trial balance is a necessary condition for a correct mapping and never a sufficient one.
··········
REVIEW CONTROLS FOR AN AI-DRAFTED ACCOUNT MAPPING.
The errors AI tools make in this task are predictable. Name matching produces the prepaid-to-expense problem shown above, and similar traps appear with "Deferred Revenue" mapped to a revenue account, "Accrued Interest" mapped to interest expense, or contra accounts such as allowances and accumulated depreciation mapped to the gross asset account. Accounts with unusual names, such as employee advances, suspense accounts or old clearing accounts, are often left blank or forced into a "miscellaneous" destination with a high confidence score. Splits are mapped to a single account without any warning.
A workable control set for the controller signing off the conversion is short:
........
Control | How to run it in Excel | Owner |
Completeness | Count of UNMAPPED equals 0 | Preparer |
Balance | Mapped TB sums to 0 | Preparer |
Type integrity | Count of FALSE in type check equals 0 | Preparer |
Totals by type | Assets, liabilities, equity, revenue, expenses equal legacy | Reviewer |
Low-confidence rows | Filter Confidence below agreed level; review each | Controller |
Splits and REVIEW rows | Allocation key documented in workpapers | Controller |
Sign-off | Mapping version frozen before load | Controller |
........
Two further steps protect the reporting after go-live. The mapping should be applied to at least one prior month's trial balance as well as the conversion date, and the mapped income statement should be compared with the reported figures for that month, which reveals errors in accounts that happen to have a zero balance at year end. The final mapping table should also be kept with the year-end workpapers, since auditors who tested the legacy balances will trace them into the new ledger in the following year, as described in our article on year-end close under US GAAP.
··········
DATA HANDLING WHEN THE LEDGER GOES TO AN AI TOOL.
A full trial balance with vendor-level subledgers is confidential financial data, so the vendor terms deserve a reading before the first prompt. Microsoft's data privacy documentation for Copilot states that prompts, responses and data accessed through Microsoft Graph are not used to train foundation models, that Copilot only surfaces organizational data the user has at least view permission for, and that models provided by Anthropic as a subprocessor are currently excluded from the EU Data Boundary. European subsidiaries running Copilot with Claude models should therefore check the tenant settings with IT before the migration file is opened.
Anthropic's documentation for Claude for Excel states that signing in sends conversations to Anthropic, including the workbook content Claude reads, that inputs and outputs are deleted on the backend within 30 days with the exceptions listed in Anthropic's policies, and that chat history is stored locally in the browser. It also states that the add-in does not inherit custom organizational retention settings and that its activity does not appear in Enterprise audit logs, while sessions are included in the Compliance API where that is enabled. For a migration file, a practical approach is to send the account lists and balances, which are enough for the mapping, and keep customer and vendor detail out of the workbook shared with the tool.
··········
CHART OF ACCOUNTS MAPPING WITH AI: WHAT TO AUTOMATE AND WHAT TO SIGN.
Both tools are well suited to the first draft of a mapping: reading two account lists, proposing a destination for every legacy account, flagging low-confidence rows and writing the XLOOKUP and SUMIFS formulas that apply the result. On a chart of several hundred accounts this saves hours of matching names by hand, and it produces a table that the team can filter, comment on and version.
The decisions that change the financial statements remain human work. Choosing destinations for ambiguous and suspense accounts, setting allocation keys for split accounts, confirming that contra accounts stay contra and approving the frozen mapping version belong to the controller. The three formula checks in the example (no unmapped rows, a zero-sum mapped trial balance and no type changes), together with totals by type that match the legacy ledger, are the minimum evidence that the opening balances of the new ERP are the closing balances of the old one. Teams still choosing the target system can compare plan limits and multi-entity features in our review of Sage Intacct and QuickBooks Online Advanced.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org




