How to Clean Up a Messy Employee List in Excel

Ask any HR coordinator or recruiter about their employee list and watch the eye twitch. Names arrive in ALL CAPS from one system and lowercase from another. Phone numbers show up in five formats. The same candidate is in there three times because they applied to three roles. And when you merged the two branch rosters, everything picked up trailing spaces. To clean up a messy employee list in Excel by hand, you're looking at half an hour of reformatting before the real work even starts.

This walks through the cleanup in the order it actually happens, using free macros where the manual fix is fiddly and Excel's built-in tools where they win.

TL;DR: clean up a messy employee list in 5 steps

• Fix the name casing. Turn PRIYA SHARMA and john doe into Priya Sharma in one pass.
• Split full names. Break a single name column into First and Last for mail merges and sorting.
• Clean the phone numbers. Strip the brackets, spaces, and dashes down to one consistent format.
• Remove duplicate people. Dedupe by email or name plus phone, so nobody gets counted twice.
• Trim the stray spaces. Kill the leading and trailing whitespace that breaks your VLOOKUPs.

The whole pass takes about two minutes with the macros loaded. By hand, on a 500-row roster, it's closer to 30 minutes, and the case-fixing alone will test your patience.

Why employee lists arrive this messy

A roster is almost never typed by one person in one sitting. It's an ATS export here, a payroll download there, a branch manager's spreadsheet someone emailed, and a block of names pasted straight out of LinkedIn or a job board. Every source has its own casing, its own phone format, and its own idea of whether the name column holds "First Last" or "Last, First."

None of it is wrong, exactly. It's just that Excel treats Priya Sharma and Priya Sharma as different people, and a PivotTable of headcount will happily split one employee across two rows because of a space you can't even see. Microsoft's own top ten ways to clean your data is a good primer, but for a recurring roster you want it faster than a ten-step manual checklist.

Step 1: Standardize the name capitalization

First, get every name into the same case. Run Convert Text to Proper Case on your name column. It capitalizes the first letter of each word and lowercases the rest, so PRIYA SHARMA, priya sharma, and Priya SHARMA all become Priya Sharma. It skips formula cells, so it won't touch a calculated column by mistake.

One honest limit: Proper case follows a simple rule, so it writes Mcdonald instead of McDonald and O'brien looks off too. For a roster with a lot of Mc and Mac surnames, fix those few by hand after the bulk pass. It also lowercases acronyms, so a department like HR becomes Hr. Run it on the name columns, not on code or ID columns.

Step 2: Split full names into first and last

Mail merges, sorting by surname, and payroll uploads all want first and last names in separate columns. Insert two blank columns to the right of your name column first, because Split Full Names Into First and Last writes its output there and will overwrite whatever it finds.

Run it, then tell it the format: 1 for "First Last" or 2 for "Last, First." A middle name goes into the First Name column, so Ana Maria Lopez splits into Ana Maria and Lopez. That's the sane default for mail merge, where the greeting line is the whole given name anyway.

Step 3: Clean the phone numbers

Phone numbers are where consistency goes to die. +1 (415) 555-0100, 415.555.0100, and 4155550100 are the same number wearing three outfits. Run Clean Phone Numbers on the phone column. It strips out spaces, brackets, dashes, and dots, leaving a clean digit string. It asks whether to keep a leading + for international numbers, so answer Y if your roster spans countries.

It also forces the column to text format, which matters more than it sounds. Without that, Excel drops the leading zero on a number like 0208 555 0100 and mangles anything long enough to hit scientific notation. Cleaned to a consistent string, the numbers finally match across systems and import cleanly into your HRIS.

Step 4: Remove duplicate people

Now dedupe. Select your data range including the header row, then run Remove Duplicates by Multiple Columns. It asks which columns to match on, entered as numbers like 2,5. A row counts as a duplicate only when every chosen column matches an earlier row, and the match is case-insensitive, so PRIYA@X.COM and priya@x.com are correctly seen as one person.

For a candidate list, dedupe on the email column alone. For an employee roster with no email, match on last name plus phone number together, which is far safer than name alone (you may well have two people named Rahul Verma, but not two with the same name and number). It keeps the first occurrence and removes the rest.

Step 5: Trim the stray whitespace

Finish with the invisible stuff. Run Trim Whitespace From All Cells across the whole range. It removes leading and trailing spaces and collapses double spaces to one. This is the fix that makes headcount PivotTables stop double-counting and VLOOKUPs stop returning #N/A for names that look identical to you but not to Excel.

Do this last, after the other steps, so any spaces introduced by splitting or cleaning get swept up in the same pass.

The hot take: don't trust Flash Fill on people's names

Flash Fill is the feature everyone reaches for first, and on a demo it looks like magic. Type one name the way you want it, and Excel fills the rest. The problem is it guesses from a pattern, and names don't follow patterns. Give it a few "First Last" rows and then a "Last, First" row, or a two-word surname like "Van Der Berg," and it quietly gets them wrong. You won't notice until an offer letter goes out addressed to "Dear Der Berg."

A macro follows an explicit rule you chose, and it reports how many rows it changed. For anything that ends up on a payslip or a contract, predictable beats clever.

Common pitfalls

• Splitting names without inserting blank columns first. The split writes into the two columns on the right and overwrites them. Insert space first, or you'll lose data.
• Running Proper case on ID or code columns. It lowercases acronyms and country codes. Keep it to name and address text.
• Deduping on name alone. Two different people can share a name. Match on email, or name plus phone, so you don't delete a real employee.
• Cleaning phone numbers stored as real numbers. If a column is genuinely numeric and you need it that way, know that the macro converts it to text. For phone numbers that's what you want; for a numeric field it isn't.

Frequently asked questions

How do I clean up a messy employee list in Excel fast?

Run five quick passes: proper-case the names, split them into first and last, clean the phone numbers to one format, remove duplicate people, and trim stray spaces. With the free macros loaded it takes about two minutes on a 500-row roster, versus roughly 30 minutes doing each fix by hand.

How do I fix names that are in all caps or all lowercase?

Select the name column and run the Proper Case macro. It rewrites PRIYA SHARMA and priya sharma as Priya Sharma in one pass. The one caveat is Mc and Mac surnames and acronyms, which its simple rule gets wrong, so fix those few by hand afterward.

How do I remove duplicate employees or candidates?

Select the data with its header row and run the multi-column dedupe macro. Match on the email column for candidates, or last name plus phone for employees without email. It compares case-insensitively, keeps the first record, and deletes the rest, so one person is never counted twice in your headcount.

Should I use Power Query for an employee list instead?

If the same export lands every week in the same shape, Power Query is a fair choice because the steps are reusable. For a one-off roster cleanup or a merge of a few branch files, the macros are faster end to end and leave nothing to maintain inside the workbook.

Will these macros work on Excel for Mac?

Yes. All the linked macros run on Excel 2016 and later on both Windows and Mac. The dedupe macro is written to avoid Windows-only features, so it behaves the same on either platform.

What to do next

Load the five macros once, and cleaning up a messy employee list in Excel stops being a chore. Run them in order on your next roster export and hand off a clean file instead of a warning about the spaces.

Browse the full set of Excel tools for HR and recruiters, or start with Clean Phone Numbers, the one that saves the most squinting. New to running macros? The 30-second setup gets you going.