How to Reorder and Rename Columns to Match a Template in Excel
The same file arrives every month and it is never quite the same file. The supplier renamed "Invoice No" to "Invoice Number", moved it three columns left, and dropped "Currency" entirely. Your import expects a fixed shape, so someone spends twenty minutes cutting and pasting columns — again.
Last updated: October 2026
| What arrived | What your template wants | |
|---|---|---|
| Amount · invoice_no. · Internal Ref · DATE | → | Invoice Number · Date · Amount · Currency |
| invoice_no. matched to Invoice Number · Currency added empty · Internal Ref dropped | ||
Method 1 — by hand, or with INDEX/MATCH
- Cut and insert each column into position — fast the first time, tedious the twelfth.
- Or write
=INDEX($A:$Z,ROW(),MATCH("Invoice Number",$A$1:$Z$1,0))once per template column. - That works until a header is renamed, at which point
MATCHreturns#N/A— or worse, quietly matches something else you did not intend. - Either way it is redone, or re-checked, every single month.
Method 2 — the 1-click way (Tellsheet)
Map Columns to Template
Point Map Columns to Template at your template's header row — or a column listing the names down the sheet — and the file is written to a new sheet in exactly that shape. Columns are matched by name, not position: exact first, then ignoring case, spacing and punctuation, then by closeness, with every loose match reported as a rename. A template column the file does not have arrives empty, never guessed at. Columns the template has no place for are dropped or kept on the end, your choice, and always listed.
The pair that covers a recurring import
Import Profile Check tells you what is wrong with this month's file — missing columns, wrong types, values outside the allowed list. Map Columns to Template is the other half: it puts the file into the shape the rest of your process expects. Check first, conform second, and the monthly re-mapping disappears.
What it will not do
- Invent data. A missing template column stays empty rather than being filled from a similar-sounding one.
- Use one source column twice. If two template names both look like "Amount", the first takes it and the second stays empty — duplicating a column would look like data and be none.
- Work without a header row. Names are what it matches on; matching by position would just be a reorder, and silently wrong the month the file shifts.
Frequently asked questions
How do I reorder columns to match another sheet?
By hand, with INDEX/MATCH per column, or in one step by name here.
What about columns not in the template?
Dropped or kept on the end — reported either way.
And columns the template expects but the file lacks?
Added empty, and reported. Never guessed.
Does it handle renamed headers?
Yes, with the near match reported so you can see it.
Related Excel guides
The same shape, every month
Map Columns to Template conforms a file by name in one step — with a full report of what moved, what was renamed and what was missing.
Get Tellsheet free See pricing