Build an Excel Drop-Down List From Your Chart of Accounts (So the Journal Import Doesn't Bounce)

How-to · Updated July 2026 · ~14 min read

Forty coded lines, one import, and QuickBooks hands back a list of rows it will not take. Travel & Subsistence is spelled Travel and Subsistence in the ledger. Motor Expenses was made a sub-account in March, so it now has to be written Motor Vehicles:Motor Expenses or nothing matches. Two accounts were archived after year end and no longer exist. Every one of those rows was typed by a competent person who knew the chart of accounts — just not the version of it that is live today.

A drop-down list is the fix, but not the version Excel tutorials build. A picker typed out of somebody's memory of the chart of accounts is the same failure wearing a nicer hat: it will still offer the archived account, still spell the name its own way, and still let a row through that the import rejects. The list has to come out of the ledger itself — the account export you can already run — carry the exact string the destination system matches on, drop anything archived or non-postable, and be rebuilt when the chart of accounts moves.

This post builds that sheet: account, tax code, tracking category and entity columns that only accept values your accounting system will take, the dependent pickers that make coding fast, and the pre-flight check you run before you generate the import file. The mechanics are plain data validation — Data > Data Validation, Allow: List, a rule on the cell rather than a control you draw — so everything here works from Excel 2016 onwards. If the damage is already done and you are consolidating Travel and travel after the fact, start with cleaning messy data in Excel and come back here to stop it recurring.

What your accounting system actually matches on

Settle one question before you build anything: when the import file reaches the accounting system, what string does it compare against the chart of accounts? Get that wrong and a perfectly validated sheet still bounces, because the picker offered the right account in the wrong notation. This is also the reason a dropdown copied from a general Excel tutorial does not survive contact with a ledger — it optimises for what reads nicely on screen, and the import does not read.

FreshBooks is deliberately absent from that table: its import surface differs by plan, and a guess in this column is exactly the error the whole page exists to prevent. Treat the table as the shape of the problem rather than the last word on your own file — open your system's import template, read the column headings, and import two lines before you plan a month-end around it. Chart-of-accounts conventions also get changed locally: a file migrated from desktop software often carries codes in the name field, and a practice that renames accounts for reporting has quietly broken the match string for everyone downstream.

Two consequences shape everything below. First, the string the coder should see and the string the import needs are frequently not the same string — 6200 - Travel & Subsistence is readable, 429 or Motor Vehicles:Motor Expenses is what gets sent — so the sheet needs both, one shown and one emitted. Second, a few accounts are not freely postable whatever the notation: a receivables or payables control account normally expects a customer or supplier on the line, and systems commonly restrict manual journals against tax control accounts and retained earnings. Which accounts are locked, and how hard, depends on the system and on how your file was configured, so test one line rather than assuming.

Start with the columns that never change: status, VAT code, Dr/Cr

A handful of coding-sheet columns are owned by you rather than by the ledger — the review status of a line, whether it is a debit or a credit, sometimes a short fixed set of VAT codes. Those are the ones to type straight into the dialog.

1. Select the cells that should get the dropdown — say E2:E200 on an expense claims sheet.
2. Go to the Data tab > Data Tools group > Data Validation. (Keyboard: Alt, A, V, V.)
3. On the Settings tab, set Allow to List.
4. In Source, type the values separated by commas: Draft, Submitted, Approved, Paid.
5. Leave In-cell dropdown ticked and click OK.

Click any cell in the range and an arrow appears at its right edge; Alt+Down Arrow opens the list from the keyboard. The classic dropdown shows eight entries at a time with a scrollbar for the rest.

Two things to know before you standardise on typed lists. The separator is your regional list separator — comma in UK and US settings, semicolon in much of continental Europe. And the Source box is capped at roughly 255 characters, separators included (a long-standing documented limit, but worth checking Microsoft's current Excel specifications if you are close to it), which a real chart of accounts blows through immediately. Typed lists are for short fixed sets: statuses, Yes/No, VAT codes, month names. The status column of a close tracker is exactly this pattern — the month-end close checklist workbook uses one per task.

The dividing line is ownership. If you decide the permitted values and they change when you say so, type them. If the accounting system decides them — accounts, tax rates, tracking categories, customers, suppliers — a typed list is already a copy of something that moves without telling you, and the next two sections are about where that copy comes from instead.

Why a pasted chart of accounts is wrong within a month

The obvious next step is to paste the chart of accounts onto a sheet and point the validation at it.

1. On a separate sheet — call it Lists — paste the accounts from your chart-of-accounts export in one column, one per cell: A2:A230, say.
2. Select the entry cells on your working sheet, open Data Validation, set Allow to List.
3. Click in Source and select the range. Excel writes an absolute reference: =Lists!$A$2:$A$230.
4. OK.

A named range is cleaner. Select Lists!A2:A230, type AccountNames into the Name Box (left of the formula bar), press Enter, then use =AccountNames as the Source. The name is self-documenting when you audit the workbook a year later.

The weakness of a plain range is growth, and on a chart of accounts growth is not hypothetical. A new marketing cost centre is opened in the ledger on the 4th. The validation still reads $A$2:$A$230, so the account never appears in the dropdown — and what happens next is the part that matters. The person coding does not stop and ask finance. They conclude the list is out of date, type the account name into the cell over the top of the picker, and the validation is gone from that cell for good. One missing account costs you the column. Inserting a row inside the range stretches the reference; adding to the end does not. Do not paper over it by pointing at $A$2:$A$500 and leaving 270 blanks either — a blank entry in an account picker is a row that codes to nothing. Fix it with a Table.

The deeper problem is that a pasted export is a photograph. It was true at the moment somebody ran it, it carries no record of when that was, and nothing in the workbook goes red when it ages. The fix is the same structure as any other list, plus a discipline about where the values come from.

Generate the list from the ledger's own export

A dropdown is trustworthy in proportion to where its values came from. Typed from memory, it is one person's recollection of the chart of accounts. Generated from the account export, it can only offer accounts that exist — which is the entire difference between a picker that speeds coding up and a picker that produces a bounced import faster than typing would have.

Pull the accounts, then strip them back

1. Export the chart of accounts from the accounting system with, at minimum, code, name, type and status — whatever those columns are called in your file. Better still, pull it with Data > Get Data so it re-pulls on a refresh instead of being re-pasted by hand; Power Query for beginners covers getting a query to that point.
2. Filter to accounts that are both active and postable. An archived account in the picker is a rejected row waiting to happen, and a heading or grouping row that your export includes for presentation is not an account at all.
3. Clean the name column. A single trailing space survives every visual check and defeats an exact-match import: =TRIM(SUBSTITUTE([@Name],CHAR(160)," ")) takes out both ordinary padding and the non-breaking spaces that web-based exports leave behind.
4. Sort by code, so the finished dropdown scrolls in the order the coder already thinks in.

Keep the raw export on the sheet beside the cleaned version rather than overwriting it. When a row looks wrong, the first question is always whether the export or the cleaning did it.

Make it a Table so the list grows

1. Click anywhere in the list on the Lists sheet and press Ctrl+T (tick My table has headers).
2. On the Table Design tab, rename it to something meaningful: tblAccounts, with a header of Account.
3. Here is the catch: type =tblAccounts[Account] straight into the validation Source box and Excel rejects it — the dialog does not accept structured references directly.
4. Route it through a defined name instead: Formulas tab > Define Name, Name AccountList, Refers to =tblAccounts[Account], OK.
5. Set the validation Source to =AccountList.

Now the list is dynamic: type a new account under the last Table row, the Table auto-expands, and the dropdown picks it up with no reference editing. Sort the Table by account code and the dropdown order follows.

The alternative you will see online is =INDIRECT("tblAccounts[Account]") typed directly as the Source. It works, but INDIRECT resolves text at run time — rename the table later and the dropdown dies silently. The defined-name route survives a rename, because structured references update themselves. Prefer it.

Two columns: the one they read, and the one you send

Here is where the first section pays off. A picker showing 6200 - Travel & Subsistence is far friendlier than a bare code, and a bare code is what Xero wants in the file. Both are true at once, so the Table carries two derived columns and they do different jobs.

The display column is the one the dropdown shows, and it is the column headed Account in the walkthrough above: =[@Code]&" - "&[@Name]. Sorting the Table by code puts it in ledger order for free.

The emit column holds the exact string the destination system matches on, and its formula depends entirely on which system that is. For Xero it is simply =[@Code]. For QuickBooks Online it is the name, or the colon path where the account has a parent — =IF([@Parent]="",[@Name],[@Parent]&":"&[@Name]) if your export gives you the parent in its own column, or the path column itself if the export already provides one. Build only the emit column you are actually importing to; two half-maintained ones are worse than none.

On the coding sheet, the emit value is looked up from whatever was picked. With the picked display value in B2:

=IF($B2="","",INDEX(tblAccounts[Emit],MATCH($B2,tblAccounts[Account],0)))

That lookup replaces the obvious trick of splitting the code back out of the display string with LEFT and FIND. The split works right up until an account name contains the separator — 6215 - Motor Expenses - Fuel — and then it quietly returns the wrong thing on exactly the rows nobody checks. Matching the whole display string back to its own row cannot do that. It also fails loudly: if the display value in the cell is not in the Table any more, you get #N/A rather than a plausible code, which is the behaviour you want from a column that feeds an import file.

Two rules about the emit column. The import file is generated from it, never from the picked cell — the picked cell is for humans. And nobody edits it by hand, because a hand-edited emit value is a string that exists in your workbook and nowhere in the ledger. Building and maintaining exactly this kind of structure is core toolkit — see the Excel skills that actually matter for accountants.

Dependent pickers: entity first, then that entity's accounts

A group workbook has a problem no general Excel tutorial addresses: every row belongs to a company, each company has its own chart of accounts, and the account picker therefore has to change from row to row. Choose Acme Trading in one cell and the next cell must offer Acme's accounts and nobody else's. It takes named ranges plus one function.

Step 1: lay out the lists in columns

On the Lists sheet, one column per entity, with the entity as the header:

Two entities with an account called Insurance on different codes is not an error to tidy up — it is the reason the picker has to be per entity in the first place.

Step 2: name each column after its header

Select the whole block including headers, then Formulas tab > Defined Names group > Create from Selection (Ctrl+Shift+F3), tick Top row only, OK. Excel creates one name per column — and because names cannot contain spaces, Acme Trading becomes Acme_Trading automatically. That underscore is why step 4 needs SUBSTITUTE.

Step 3: the first-level dropdown

Validation on the entity column (say B2:B200): Allow = List, Source Acme Trading, Northgate Services, Riverside Property — or better, a range holding the headers themselves, so the two levels cannot drift apart.

Step 4: the second-level dropdown

Validation on the account column (C2:C200): Allow = List, Source:

=INDIRECT(SUBSTITUTE($B2," ","_"))

Row-relative $B2, not $B$2 — select the whole column range before opening the dialog and each row reads its own entity cell. SUBSTITUTE maps the on-screen Acme Trading to the actual name Acme_Trading; INDIRECT turns that text into the range. If B2 is empty the second dropdown offers nothing until an entity is picked, and Excel may warn "The Source currently evaluates to an error" when you click OK while the first cell is still blank — answer Yes; the formula resolves per row at entry time. One caveat for very large sheets: INDIRECT is volatile, recalculating on every change, so keep it on coding sheets rather than 100,000-row data dumps.

The same machinery, one level further down: account then tax rate

The second useful pair on a coding sheet is account and tax treatment. Not every rate is available on every account — a zero-rated or exempt code on an account that should carry standard-rated VAT is a return adjustment later, and the coder had no way of knowing.

You cannot lay out one column per account when there are six hundred of them, so group instead. Give the accounts Table a TaxGroup column with a handful of values — Standard, Zero, Exempt, Out of Scope, Reverse Charge — lay out one list column per group exactly as in step 1, name them with Create from Selection, and let the rate picker find its group through the account that was chosen:

=INDIRECT(SUBSTITUTE(INDEX(tblAccounts[TaxGroup],MATCH($C2,tblAccounts[Account],0))," ","_"))

Same warning on blank rows, same answer: click Yes. The lookup is doing the job the entity header did before, which is the general shape of every dependent picker — a value in one cell resolving to the name of a range in another. Account type then account, the classic two-level version, is the same build with the type column in place of the entity: worth having on a sheet where coders think in Fixed Assets and Overheads before they think in codes, and worth skipping on a sheet where the 600-account picker already filters as you type.

The row that was re-coded and never re-picked

Change row 7's entity from Acme Trading to Northgate Services and the account cell keeps Acme's account. Validation checks entries as they are made, not continuously. The consequence is worse here than on a single-company sheet: the row still carries a real, valid-looking account code, so it passes every eye on the way to the file, the import succeeds — and it posts to the wrong company. Add a check column and scan it before generating anything:

=IF($C2="","",IF(COUNTIF(INDIRECT(SUBSTITUTE($B2," ","_")),$C2)=0,"re-pick",""))

Clearing the account automatically when the entity changes needs VBA; the check column needs nothing.

Messages that tell a coder what to do instead

Both live in the Data Validation dialog, on their own tabs. Neither exists to scold: a coder who hits a wall with no route past it retypes the cell, and you have lost the column.

Input Message shows a tooltip when the cell is selected. Keep it operational and put the pull date in it — title Account, message Pick from the chart of accounts pulled on 1 August. Missing an account? Ask for a re-pull — do not type over. The date tells anyone coding whether the list predates the change they are looking for, and that last sentence heads off the workaround culture that kills validated workbooks.

Error Alert decides what happens when someone enters a value not on the list. Three styles:

The choice between them is not about strictness, it is one question: could a row carrying this value be posted at all? An account that is not in the ledger cannot, so the account picker gets Stop. A supplier name during a migration, while the master list is still being completed, can — the row will need fixing but it is not nonsense — so that field gets Warning. Anything you would be willing to send to the accounting system and let it reject gets Warning; anything you know it will reject gets Stop.

Either way, write the message for the person seeing it — That account is not in the chart of accounts pulled on 1 August. Check the code, or ask for a re-pull. beats the default "This value doesn't match the data validation restrictions defined for this cell". One honest limitation: the error alert is a property of the range, so every cell in the column shows the same words. Account-specific guidance — Archived in Xero on 31 July — post to 6210 instead — cannot live there. Put it where it can be per-account: a Replaced by column on the Lists sheet beside the archived accounts, and let the check column return that text rather than a bare "re-pick". The coder then gets the answer instead of the refusal.

The honest limit: validation polices typing, not pasting. Copy a cell from anywhere and paste it over a validated cell, and the paste both lands and replaces the validation itself — the pasted cell brings its own (absent) rule along with its value. Ctrl+Z straight after reverses it; discovering it three weeks later means auditing (two sections down). This is the single most common way clean workbooks rot, and it is why coding columns that feed downstream formulas deserve a periodic check — the bank reconciliation template walkthrough shows how much its COUNTIFS matching assumes about clean, consistent columns.

Protecting the source list — and re-pulling it, not editing it

A dropdown is only as trustworthy as the list behind it, and the list is one accidental sort from scrambled.

1. Keep every list on the dedicated Lists sheet — never in a spare column beside live data.
2. Hide the sheet: right-click its tab > Hide. Anyone can unhide it, so to stop that, Review tab > Protect Workbook locks the sheet structure. VBA's xlSheetVeryHidden goes further still — the sheet stops appearing in the Unhide dialog at all.
3. On the working sheet, unlock the cells people should type in: select them, Ctrl+1 > Protection tab > untick Locked. Every cell is Locked by default; the setting only bites once protection is on.
4. Review tab > Protect Sheet, password if warranted, OK.

Now the entry cells accept picks while the formulas, headings and validation rules behind them do not. Sheet protection also blocks paste-over damage on locked cells — though not on the unlocked entry cells themselves, where pasting still wipes validation. There is no complete defence short of VBA; auditing is the compensating control.

Protection is only half of it, and the smaller half. The operating rule is that nobody adds an account to the Lists sheet by hand. It is the most natural thing in the world to do — the account is missing, you know its name, the sheet is right there — and it produces the single worst outcome available: an account that exists in Excel and not in the ledger, offered confidently by a picker, on a row that the import will reject. If an account is missing from the list, either the list is stale and needs re-pulling, or the account genuinely does not exist yet and somebody has to create it in the accounting system first. Both of those are somebody's job; neither of them is typing.

So give the list a cadence. Re-pull the chart of accounts at the start of each close, and again after anyone tells you the chart of accounts has changed — an account added, archived, renamed or moved under a parent. If you pulled it with Power Query, that is Data > Refresh All and you are done; if it is a manual export, it is a five-minute job once a month.

Record the pull. One cell at the top of the Lists sheet holding the date the export was run, typed with Ctrl+; — not =TODAY(), which rewrites itself every time the workbook opens and destroys the only fact you were trying to keep. Put the same date in the input message on the account column, as above, and echo it in the header of the coding sheet. A list whose age is visible gets re-pulled; a list whose age is invisible gets overtyped.

The pre-flight check before you generate the import file

Validation stops bad values going in. It does not prove the sheet is fit to post, because it never re-examines a row after the moment it was typed, and because the list it checked against may be three weeks older than the ledger. The last thing you do before generating the file is re-check the whole sheet against a freshly pulled chart of accounts.

Re-pull the list first — that is the point of the exercise — then put a check column beside the coded rows, column J here:

=IF($C2="","",IF(COUNTIF(tblAccounts[Account],$C2)=0,"not in CoA",""))

Then reduce it to one number, in a cell at the top of the sheet where it cannot be missed:

=COUNTIF($J$2:$J$500,"not in CoA")

That cell has to read zero. Conditional-format it red on anything else. It catches the three things that get past a dropdown: rows typed before the validation existed, rows where somebody pasted over the picker, and rows picked correctly in July against an account archived in August. Add a second count over the emit column — column K here — since an account that has left the Table makes the lookup fail rather than lie:

=SUMPRODUCT(--ISNA($K$2:$K$500))

To see the offending rows rather than count them, use Data tab > the arrow beside Data Validation > Circle Invalid Data, which rings every cell whose current value fails its own rule.

Two checks validation cannot make, and both stop an import dead. Debits must equal credits — for the file as a whole, and for each journal in it if you are sending more than one:

=ROUND(SUM(tblCoding[Debit])-SUM(tblCoding[Credit]),2)

=ROUND(SUMIF(tblCoding[JournalNo],$A2,tblCoding[Debit])-SUMIF(tblCoding[JournalNo],$A2,tblCoding[Credit]),2)

And every line on a receivables or payables control account needs a customer or supplier, because those accounts are maintained by the subledger and a line without a contact has nowhere to land. Flag it from the accounts Table with a NeedsContact column:

=IF(INDEX(tblAccounts[NeedsContact],MATCH($C2,tblAccounts[Account],0))="Yes",IF($F2="","contact missing",""),"")

Worth adding a date check in the same block while you are there: every transaction date inside the period you are posting to, and none of them in a period that has been locked. A journal dated outside an open period is refused by some systems and silently accepted into the wrong month by others, and you would rather find out in Excel.

Four cells, all reading zero, all visible without scrolling. Only now do you generate the import file — from the emit column, not from the picked one. The guide to importing journal entries into QuickBooks Online from Excel picks the job up from there.

Auditing a coding sheet you inherited

The other half of this work is the workbook you did not build. Somebody left, or the close pack has been handed over, and you need to know three things before you trust a single column of it: which columns are genuinely validated, which rules point at a list that no longer exists, and which cells have quietly had their validation pasted away. All three are quick to answer.

Find every validated cell. Home tab > Find & Select > Data Validation selects them all. For finer control, F5 > Special > Data validation, then All — or Same, which selects only cells sharing the active cell's rule, the fast way to find every cell pointing at one broken list.

Find invalid values that got in anyway — pasted over, or typed before the rule existed. Data tab > click the arrow beside Data Validation > Circle Invalid Data draws a red ring around every cell whose current value fails its rule (the stale-value trap above shows up here too). Excel stops drawing after a few hundred circles — 255 in the versions this was tested on as of 2026 — so on a large sheet fix a batch, re-circle, repeat. Clear Validation Circles removes them, and they do not survive a save.

Remove or copy validation. Select the cells, open Data Validation — if the selection mixes rules, Excel asks whether to erase the current settings and continue — then click Clear All. To copy a rule onto new cells instead, copy one correctly validated cell, select the targets and use Paste Special > Validation (Ctrl+Alt+V, then N): the rule alone arrives, values and formats untouched.

Which list type when

How HISAB 360 helps

Setting up a workbook's worth of validation — the Lists sheet, the Tables, the defined names, the INDIRECT sources, the error messages, range by range — is exactly the kind of structural work HISAB 360 does from a chat request. It is a paid Excel add-in (Windows desktop Excel, Microsoft 365 or 2016+) with an AI panel docked inside Excel that reads the open workbook and applies real changes to it: ask for "a Lists sheet from the accounts I just pulled, a Table on it, and a validated account column on the coding sheet with a dependent tax-rate picker" and it builds the pieces rather than describing them.

The part no manual method matches is where the values come from. HISAB 360 connects to QuickBooks Online, Xero, Zoho Books, Odoo and FreshBooks with read and write access, pulling invoices, bills, general ledger activity and customers into sheets — so a picker can be built from records that genuinely exist in the ledger rather than from someone's retyped list, and a fresh pull refreshes it. Coded rows travel the other way too: it creates invoices, bills and journal entries back in the accounting system from the sheet. It is a copilot you review, not something that posts on its own — nothing goes to the ledger until you have read it.

Honest limits: Windows only — not Mac, not Excel on the web — and paid, with a 15-day full-feature trial (50 AI credits, no card). And a dropdown only constrains new entries; whatever was typed before it existed still needs reviewing.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo and FreshBooks. The 15-day trial is the full product, no card required.