How to Create a Drop-Down List in Excel (Chart-of-Accounts Pickers That Keep Data Clean)

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

Open any expense sheet that five people have typed into and count the versions of one category: Travel, travel, Travel & Subsistence, T&S. Every variant becomes its own line in a SUMIFS or PivotTable, the P&L splits one cost across four rows, and month-end starts with an hour of find-and-replace. A drop-down list fixes this at the point of entry: the cell stops accepting free text and offers the approved values instead. In Excel this lives under data validation — it is a rule on the cell, not a control you draw — which is why half the searches for it ("insert drop down box in excel") lead people to the wrong toolbar entirely.

This post builds the pickers an accountant actually needs — an approval-status field, an expense-category coder, a chart-of-accounts picker that grows with the ledger, and a two-level account-type → account pair — then covers the messages, protection and audit steps that keep them working. 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.

The five-step version: a typed list

For a short, stable list — an approval-status field is the classic — type the values 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.

A dropdown from a cell range

For anything longer, put the values on the sheet and point the validation at them.

1. On a separate sheet — call it Lists — type the values in one column, one per cell: expense categories in A2:A30, 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$30.
4. OK.

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

The weakness of a plain range is growth. Add category 31 in A31 and the validation still reads $A$2:$A$30 — the new value never appears in the dropdown, and the first anyone hears of it is a rejected entry. Inserting a row inside the range stretches the reference; adding to the end does not. Do not fix this by pointing at $A$2:$A$500 and leaving 470 blanks. Fix it with a Table.

Table-backed lists that grow with the chart of accounts

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.

One layout decision matters more than any formula: code and name in one cell or two? A picker showing 6200 - Travel & Subsistence is far friendlier than bare codes, so give the CoA Table a helper column concatenating the two — =[@Code]&" - "&[@Name] — and point the defined name at that column. Where a downstream formula needs the code alone, split it back out: with the picked value in B2, =LEFT(B2,FIND(" - ",B2)-1) returns the code. Building and maintaining exactly this kind of structure is core toolkit — see the Excel skills that actually matter for accountants.

Dependent dropdowns: account type first, then account

The two-level picker — choose Fixed Assets in one cell and the next cell offers only fixed-asset accounts — is what makes a coding sheet genuinely fast. It takes named ranges plus one function.

Step 1: lay out the lists in columns

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

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, Fixed Assets becomes Fixed_Assets automatically. That underscore is why step 4 needs SUBSTITUTE.

Step 3: the first-level dropdown

Validation on the type column (say B2:B200): Allow = List, Source Fixed Assets, Current Assets, Overheads — 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 type cell. SUBSTITUTE maps the on-screen Fixed Assets to the actual name Fixed_Assets; INDIRECT turns that text into the range. If B2 is empty the second dropdown offers nothing until a type 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 stale-value trap. Change row 7's type from Overheads to Fixed Assets and the account cell keeps its old overhead account. Validation checks entries as they are made, not continuously. Add a check column and scan it before posting anything:

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

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

Input messages and error alerts that earn their place

Both live in the Data Validation dialog, on their own tabs.

Input Message shows a tooltip when the cell is selected. Keep it operational — title Account code, message Pick from the chart of accounts. Missing an account? Ask finance to add it to the Lists sheet — do not type over. 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:

Stop is right for a chart-of-accounts picker: an account either exists or it does not. Warning suits a supplier-name field during a transition, while the master list is still being completed. Either way, write the message for the person seeing it — That account is not in the chart of accounts. Check the code, or ask finance to add it. beats the default "This value doesn't match the data validation restrictions defined for this cell".

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

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.

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 category dropdown on column E of the expenses sheet, source list on a hidden sheet, and a dependent account picker driven by the type column" and it builds the pieces rather than describing them.

The part no manual method matches is where the source values come from. HISAB 360 connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting with read and write access, pulling invoices, bills, general ledger activity and customers into sheets — so a customer or supplier 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, once you have reviewed them.

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.

Finding, auditing and removing validation

Three jobs turn up constantly on inherited workbooks.

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

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, FreshBooks and Sage. The 15-day trial is the full product, no card required.