top of page

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

2 minutes ago
10 min read
Cover with the title Chart of accounts mapping to a new ERP in Excel with Copilot or Claude over a night city skyline, Data Studios original cover

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


bottom of page