Tabellab

Employee List: Clean Names and Look Up Department and Manager

Lesson 1 of 20Lab

All lessons
  1. Employee List: Clean Names and Look Up Department and ManagerLabFree
  2. Headcount by Department with COUNTIF and COUNTIFSLab Locked
  3. Tenure from Start Dates: DATEDIF and YEARFRACLab Locked
  4. Birthdays and Work Anniversaries This MonthLab Locked
  5. Hiring Pipeline: Time-to-Hire and Open RolesLab Locked
  6. Recruiting Cost per Hire by SourceLab Locked
  7. Leave Balances: Accrual, Days Taken and Overdrawn LeaveLab Locked
  8. Timesheets for Payroll: Regular Hours, Overtime and Week PayLab Locked
  9. Salary Bands and Compa-RatioLab Locked
  10. Employee Turnover Rate by Month and for the Half-YearLab Locked
  11. Training Hours Tracker: Hours per Department and per HeadLab Locked
  12. HR Dashboard: Headcount Chart and Turnover AlertsLab Locked
  13. Names and Email Addresses: Splitting Full Names, Building Logins & a Salutation LineLab Locked
  14. Dynamic HR Lists: FILTER by Department, SORT by Start Date & UNIQUE LocationsLab Locked
  15. Pay Scale Lookups with Two Keys: Grade and Step, Part-Time Salaries & Next Year’s Step IncreasesLab Locked
  16. Fixed-Term Contracts & Probation: End Dates with EDATE, Days Left & a 30-Day Reminder ListLab Locked
  17. Headcount on Any Date: Active Employees, Joiners and Leavers per Month from Start and Leaving DatesLab Locked
  18. Salary Changes for Payroll: Percentage Raises, Effective Dates & Back PayLab Locked
  19. Input Rules for the Employee List: Drop-Down Lists, Allowed Hours & FTE, and Counting Old ErrorsLab Locked
  20. Final Project: The Monthly HR Report – Headcount Movements, a Consistency Check, Turnover & a Combo ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • XLOOKUP is in Excel 365 and Excel 2021 or newer. In older versions use =IFERROR(VLOOKUP(D2,$A$11:$C$15,3,FALSE),"Check").
  • When the cleaned names are right, copy column C and paste as values over column B.
Loading the spreadsheet…

Why the employee list comes first

If you are an HR assistant, HR coordinator or the office manager who “also does HR” in a small company, you probably keep some version of the same spreadsheet: one row per employee, with an ID, a name, a department and a manager. It feeds almost everything else you are asked for. Headcount by department, birthday lists, leave balances, the list of new starters for IT, the mail merge for the welcome letters, the export to the payroll provider. When that list is messy, every one of those reports is quietly wrong.

The mess usually does not come from you. New-starter details arrive from hiring managers by e-mail, from an online form, from a recruiting tool or from a colleague who typed them in a hurry. One person writes names in capitals, another adds a space at the end, a third pastes from a PDF and brings two spaces between first and last name. Department names get typed by hand and drift over time: “Customer Service”, “Customer service”, “Cust. Service”. Nobody notices until a COUNTIF suddenly reports 11 people in a department of 12, or the welcome letter says “Dear noah fischer”.

This free first lesson fixes exactly that. In the lab above you are the HR assistant at Alder & Finch, a fictional home-goods wholesaler with 60 employees. Seven new starters have just been added to the list, and their names were typed in every possible way. You clean the names with one formula and fill in each person’s department and manager from a small department table, so that nothing has to be typed twice. It takes about fifteen minutes, and the two techniques you use, cleaning text and looking values up, come back in almost every lesson of the course.

The core idea: clean once, look up everything else

There are two separate problems in a typed employee list, and Excel has a separate answer for each.

The first problem is inconsistent text. To a person, “ GRACE LIN ” and “Grace Lin” are the same name. To Excel they are different values, because one has spaces at both ends and different capitals. Sorting puts them in different places, a lookup by name fails, and a duplicate check misses them. The fix is a formula that rebuilds the text in a standard form. TRIM removes spaces at the start and end and reduces any run of spaces inside the text to a single space. PROPER then gives every word a capital first letter and makes the rest lowercase. Nested together, =PROPER(TRIM(B2)) turns all seven typed names in the lab into the same clean pattern.

The second problem is repeated information. A department name and a manager’s name belong to the department, not to the employee. If you type them into every employee row, you type the same words 60 times, and the day a manager changes you have to find and fix every one of those rows. The better design is a small department table with one row per department: a short code, the full department name and the manager. Each employee row only stores the code, and a lookup formula fetches the rest. Change the manager in the table once, and every employee row follows automatically.

XLOOKUP does the fetching. You give it the value to find (the employee’s code), the column to search (the codes in the department table), the column to return (department or manager) and, optionally, what to show when the code is not found. That last argument matters more than it looks. Without it, a code that is not in the table shows #N/A, which looks like a broken formula. With "Check" as the fourth argument, the row tells you in plain words that someone needs to look at it.

From a typed name to a clean, looked-up rowTyped (B2)"··noah··fischer"code WHTRIM"noah fischer"spaces fixedPROPER"Noah Fischer"capitals fixedXLOOKUPWH → WarehouseDaniel Osei· marks a space. Each step is one formula, nothing is retyped.
The cleanup chain for one row of the lab: TRIM fixes the spaces, PROPER fixes the capitals and XLOOKUP adds department and manager from the table.

Worked example: the seven new starters at Alder & Finch

Here is the lab data with the results you should see when every formula is in place. Spaces in the typed names are kept so you can see them. The department table sits below the list in A10:C15 with five codes: WH Warehouse (Daniel Osei), SA Sales (Priya Raman), CS Customer Service (Laura Becker), PU Purchasing (Elena Russo) and FA Finance & Admin (Marcus Hill).

ID (A)Name as typed (B)Clean name (C)Code (D)Department (E)Manager (F)
E014"  noah  fischer"Noah FischerWHWarehouseDaniel Osei
E022"GRACE LIN "Grace LinSASalesPriya Raman
E031"mateo   silva"Mateo SilvaCSCustomer ServiceLaura Becker
E037" hannah kowalski"Hannah KowalskiFAFinance & AdminMarcus Hill
E045"OMAR HADDAD"Omar HaddadQACheckCheck
E052"chloe dubois "Chloe DuboisPUPurchasingElena Russo
E058"ben  carter"Ben CarterWHWarehouseDaniel Osei

Three things are worth noticing. First, the formula does not care how the name was typed: leading spaces, trailing spaces, double spaces, all capitals or all lowercase all end up in the same form. Second, Ben Carter and Noah Fischer both have the code WH, so both get Warehouse and Daniel Osei from the same table row. Third, Omar Haddad has the code QA, and QA is not in the department table. That is not an error in your formula. It is real information: either the code was typed wrongly, or the company has a quality department that is missing from the table. The word Check in E6 and F6 is the formula doing its job and pointing you at the row that needs a human decision.

These are the formulas behind the table. You type each one once in row 2 and copy it down to row 8:

ColumnFormula in row 2Copy down to
C Clean name=PROPER(TRIM(B2))C8
E Department=XLOOKUP(D2,$A$11:$A$15,$B$11:$B$15,"Check")E8
F Manager=XLOOKUP(D2,$A$11:$A$15,$C$11:$C$15,"Check")F8
E, older Excel=IFERROR(VLOOKUP(D2,$A$11:$C$15,2,FALSE),"Check")E8
F, older Excel=IFERROR(VLOOKUP(D2,$A$11:$C$15,3,FALSE),"Check")F8

The dollar signs in $A$11:$A$15 are what makes copying safe. They lock the department table, so when the formula moves from row 2 to row 3 only D2 changes to D3, while the table reference stays where it is. Without them, the table range would slide down one row with every copy, and by row 8 the formula would be searching empty cells.

Extra spaces TRIM removes per name (lab data)Noah Fischer3Grace Lin1Mateo Silva2Hannah Kowalski1Omar Haddad0Chloe Dubois1Ben Carter101234
How many extra space characters TRIM removes from each name in the lab, worked out with =LEN(B2)-LEN(C2). Omar Haddad has none: his name only needed PROPER.

The chart is a good reminder that you cannot see most of these problems. A trailing space in “GRACE LIN ” is invisible in the cell. The quickest way to prove it is there is =LEN(B3), which returns 10 for a 9-character name. A helper column with =LEN(B2)-LEN(C2) shows, row by row, how much the cleaning changed.

How to do it in Excel, step by step

You can follow these steps in the lab above or in your own copy of Excel. The cell addresses match the lab.

  1. Click C2 and type =PROPER(TRIM(B2)), then press Enter. C2 shows “Noah Fischer”.
  2. Select C2:C8 and press Ctrl+D to fill the formula down. Alternatively, double-click the small square at the bottom-right corner of C2 (the fill handle).
  3. Click E2 and type =XLOOKUP(D2,. Select A11:A15 with the mouse and press F4 once so it becomes $A$11:$A$15. Type a comma, select B11:B15, press F4 again, then finish with ,"Check") and Enter.
  4. Fill E2 down to E8 with Ctrl+D. E6 shows Check, all other rows show a department.
  5. In F2 enter the same lookup but return the manager column: =XLOOKUP(D2,$A$11:$A$15,$C$11:$C$15,"Check"). Fill down to F8.
  6. Highlight the rows that need attention: select E2:F8, then Home > Conditional Formatting > Highlight Cells Rules > Equal To, type Check and pick a fill colour.
  7. Count what is still open with =COUNTIF(E2:E8,"Check") somewhere outside the list. In the lab it returns 1.
  8. When the clean names are right and you want to get rid of the messy originals, select C2:C8, copy with Ctrl+C, click B2 and use Home > Paste > Paste Values (or Ctrl+Alt+V, then V and Enter). Then delete the helper column.
In Excel 365 and Excel 2021 or newer, XLOOKUP is available. If a colleague opens your file in Excel 2019 or older, XLOOKUP shows #NAME?. For shared files that must work everywhere, use =IFERROR(VLOOKUP(D2,$A$11:$C$15,2,FALSE),"Check") for the department and the same formula with column 3 for the manager. The FALSE at the end is essential: it asks for an exact match.

Flash Fill is a useful alternative for one-off cleanups. Type the clean version of the first name in C2, move to C3 and press Ctrl+E (or Data > Flash Fill). Excel guesses the pattern and fills the column. The difference is that Flash Fill produces typed values, not formulas, so it does not update when a new starter is added. For a list that keeps growing, the formula is the better habit.

New starters per department after the lookupWarehouse2Sales1Customer Service1Finance & Admin1Purchasing1Check (code QA)10123
The seven new starters by department once the lookup is in place. The one Check row is counted separately until someone fixes the code or extends the table.

HR assistant cleaning an employee list in Excel and looking up department and manager
The course starts where every HR report starts: a clean employee list with one source for departments and managers.

Common mistakes and how to spot them

Most problems with this kind of list show up as a lookup that returns Check (or #N/A) when you are sure the code is right, or as a count that is one short. These are the usual causes.

  • Forgetting the dollar signs. The formula works in row 2 and fails further down. Click a cell in row 8 and look at the formula bar: if it says A17:A21 instead of $A$11:$A$15, the table range moved. Fix row 2 with F4 and fill down again.
  • Spaces in the code, not only in the name. “WH ” with a trailing space does not match “WH”. If a code that looks right returns Check, test it with =LEN(D2). Cleaning the lookup value too, =XLOOKUP(TRIM(D2),…), removes the problem.
  • Non-breaking spaces from web pages and PDFs. TRIM only removes the normal space character. Text copied from a website often contains character 160, which looks identical. =TRIM(SUBSTITUTE(B2,CHAR(160)," ")) converts it first.
  • PROPER is too simple for some names. It turns “mcdonald” into “Mcdonald” and “van der berg” into “Van Der Berg”. It gets “o’brien” and “anne-marie” right. Scan the result once and correct the few special cases by hand after pasting values.
  • Overwriting the formula with a typed value. Someone types “Warehouse” over the formula in E4 and the row no longer follows the table. Show all formulas with Ctrl+` to see which cells are typed and which are calculated.
  • Hiding the problem with IFERROR(…,""). An empty cell looks tidy but hides the missing department. A visible word like Check is easier to follow up and easy to count.
  • Approximate VLOOKUP by accident. VLOOKUP(D2,$A$11:$C$15,2) without FALSE uses approximate match and can return a wrong department instead of an error. Always add FALSE for codes and IDs.

Quick reference: functions and shortcuts from this lesson

Function or shortcutWhat it does in an employee listExample
TRIMRemoves leading and trailing spaces and turns runs of spaces into one=TRIM(B2)
PROPERCapitalises the first letter of each word, lowercases the rest=PROPER(TRIM(B2))
UPPER / LOWERAll capitals or all lowercase, e.g. for codes or e-mail addresses=UPPER(TRIM(D2))
LENCounts characters, so you can see hidden spaces=LEN(B2)-LEN(C2)
SUBSTITUTE + CHAR(160)Replaces non-breaking spaces that TRIM leaves behind=TRIM(SUBSTITUTE(B2,CHAR(160)," "))
XLOOKUPFinds the code in the department table and returns another column=XLOOKUP(D2,$A$11:$A$15,$B$11:$B$15,"Check")
VLOOKUP + IFERRORThe same lookup for Excel 2019 and older=IFERROR(VLOOKUP(D2,$A$11:$C$15,2,FALSE),"Check")
COUNTIFCounts the rows that still say Check=COUNTIF(E2:E8,"Check")
F4Adds the $ signs to the selected reference (Windows; on a Mac Cmd+T)A11:A15 → $A$11:$A$15
Ctrl+DFills the formula from the top cell down the selectionSelect C2:C8, press Ctrl+D
Ctrl+EFlash Fill: Excel copies a pattern you typed onceType "Noah Fischer" in C2, press Ctrl+E
Ctrl+TTurns the list into a table that grows with new rowsClick in the list, press Ctrl+T

Real-world uses

The same two moves, clean the text and look up the rest, solve a surprising number of everyday HR tasks.

  • Onboarding list for IT and facilities. Every month you send a list of new starters with department and manager, so accounts, keys and equipment are ready on day one. A clean list with lookups goes out in minutes.
  • Org changes. When Customer Service gets a new team lead, you change one cell in the department table and every employee row, report and mail merge that uses the lookup shows the new manager.
  • Merging two exports. The payroll export and the time-tracking export spell names differently. Cleaning both with PROPER and TRIM and then looking up by employee ID instead of by name makes the two lists match.
  • Mail merges and letters. Welcome letters, contract cover letters and certificates pull names straight from the list. Clean names mean no “Dear GRACE LIN ” in a letter that goes to a new employee.
  • Quality check before a report. A quick =COUNTIF(E:E,"Check") tells you whether every employee has a valid department before you send headcount figures to management.
Employee lists contain personal data. How long you may keep it, who may see it and where it may be stored is set by your employer’s policies and by data-protection and employment rules that differ by country. Nothing in this course is legal or tax advice; follow your company’s rules and ask a qualified adviser when in doubt. All names, companies and numbers in the labs are fictional examples.

What you will build in the rest of this course

Lesson 1 is free. The other nineteen lessons are part of the paid course, a one-time purchase of $9.90. Each one is a hands-on lab with the same Coach that checks your formulas as you go, and they all use the Alder & Finch data, so you build up a complete HR workbook step by step.

Headcount by Department with COUNTIF and COUNTIFS. You count all 60 employees by department, count the part-timers separately and add up full-time equivalents with SUMIF, so you can answer both “how many people?” and “how much capacity?”.

Tenure from Start Dates: DATEDIF and YEARFRAC. You calculate how long each person has been with the company on a report date, count the long-servers and find the average tenure without counting days by hand.

Birthdays and Work Anniversaries This Month. You build the monthly list for cards and congratulations: who has a birthday or a work anniversary in November, and which anniversary year it is.

Hiring Pipeline: Time-to-Hire and Open Roles. You track the year’s vacancies, calculate the days to hire for each filled role, keep open roles marked as Open and compare the result with a 45-day target.

Recruiting Cost per Hire by Source. You compare what each recruiting channel cost per hire, find the overall cost per hire and chart the channels side by side.

Leave Balances: Accrual, Days Taken and Overdrawn Leave. You work out how much annual leave each employee has earned by the end of October, what is left after the days taken, and flag anyone who has taken more than they earned.

Timesheets for Payroll: Regular Hours, Overtime and Week Pay. You split a week of warehouse hours into regular and overtime hours and calculate the pay to send to the payroll provider, using example rules kept in input cells.

Salary Bands and Compa-Ratio. You look up the band midpoint for each employee, calculate the compa-ratio and mark who is below, inside or above the band.

Employee Turnover Rate by Month and for the Half-Year. You calculate the monthly turnover rate for the second half of the year and the rate for the whole half-year, and see why the two are not simply added.

Training Hours Tracker: Hours per Department and per Head. You turn a training log into completed hours per department and per employee, and check them against a yearly target of 8 hours per head.

HR Dashboard: Headcount Chart and Turnover Alerts. You bring it together in a one-page dashboard for the management meeting, with a headcount chart and a red alert for departments above a 15% turnover threshold.

Summary

A reliable employee list has clean text and a single source for anything that belongs to a department. =PROPER(TRIM(B2)) removes stray spaces and fixes the capitals, and =XLOOKUP(D2,$A$11:$A$15,$B$11:$B$15,"Check") fetches the department and manager from one small table, with a clear Check for codes that are missing. At Alder & Finch that turns seven messily typed new starters into seven tidy rows and one flagged code, QA, that needs a decision. Scroll back up and try the free lab now: fill C2:C8, E2:E8 and F2:F8, and let the Coach confirm each step.

Frequently asked questions

How do I remove extra spaces from names in Excel?

Use =TRIM(B2). It removes spaces at the start and end and turns several spaces between words into one. If spaces remain, the text probably contains non-breaking spaces; use =TRIM(SUBSTITUTE(B2,CHAR(160)," "))

How do I capitalise the first letter of each name in Excel?

Use =PROPER(B2), or =PROPER(TRIM(B2)) to clean spaces at the same time. Check names like McDonald or van der Berg by hand, because PROPER capitalises every word the same way.

How do I look up an employee’s department from a code?

Keep a department table with codes and names, then use =XLOOKUP(D2,$A$11:$A$15,$B$11:$B$15,"Check"). The dollar signs keep the table fixed when you copy the formula down.

What can I use instead of XLOOKUP in older Excel versions?

Use =IFERROR(VLOOKUP(D2,$A$11:$C$15,2,FALSE),"Check"). Change the 2 to 3 to return the third column, and keep FALSE for an exact match.

Why does my lookup return #N/A even though the code looks right?

The code usually has a hidden space or is stored differently, for example as a number in one place and text in the other. Check it with =LEN(D2) and clean it with TRIM.

How do I replace the messy names with the cleaned ones?

Copy the column with the PROPER(TRIM()) formulas, then use Home > Paste > Paste Values over the original names. After that you can delete the helper column.

Keep going: Excel for HR Assistants: Headcount, Hiring, Leave & Payroll Prep

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Headcount by Department with COUNTIF and COUNTIFS
  2. Tenure from Start Dates: DATEDIF and YEARFRAC
  3. Birthdays and Work Anniversaries This Month
  4. Hiring Pipeline: Time-to-Hire and Open Roles
  5. Recruiting Cost per Hire by Source
  6. Leave Balances: Accrual, Days Taken and Overdrawn Leave
  7. Timesheets for Payroll: Regular Hours, Overtime and Week Pay
  8. Salary Bands and Compa-Ratio
  9. Employee Turnover Rate by Month and for the Half-Year
  10. Training Hours Tracker: Hours per Department and per Head
  11. HR Dashboard: Headcount Chart and Turnover Alerts
  12. Names and Email Addresses: Splitting Full Names, Building Logins & a Salutation Line
  13. Dynamic HR Lists: FILTER by Department, SORT by Start Date & UNIQUE Locations
  14. Pay Scale Lookups with Two Keys: Grade and Step, Part-Time Salaries & Next Year’s Step Increases
  15. Fixed-Term Contracts & Probation: End Dates with EDATE, Days Left & a 30-Day Reminder List
  16. Headcount on Any Date: Active Employees, Joiners and Leavers per Month from Start and Leaving Dates
  17. Salary Changes for Payroll: Percentage Raises, Effective Dates & Back Pay
  18. Input Rules for the Employee List: Drop-Down Lists, Allowed Hours & FTE, and Counting Old Errors
  19. Final Project: The Monthly HR Report – Headcount Movements, a Consistency Check, Turnover & a Combo Chart

Buy course — $9.90 See the full course