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.
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 Fischer | WH | Warehouse | Daniel Osei |
| E022 | "GRACE LIN " | Grace Lin | SA | Sales | Priya Raman |
| E031 | "mateo silva" | Mateo Silva | CS | Customer Service | Laura Becker |
| E037 | " hannah kowalski" | Hannah Kowalski | FA | Finance & Admin | Marcus Hill |
| E045 | "OMAR HADDAD" | Omar Haddad | QA | Check | Check |
| E052 | "chloe dubois " | Chloe Dubois | PU | Purchasing | Elena Russo |
| E058 | "ben carter" | Ben Carter | WH | Warehouse | Daniel 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:
| Column | Formula in row 2 | Copy 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.
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.
- Click C2 and type
=PROPER(TRIM(B2)), then press Enter. C2 shows “Noah Fischer”. - 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).
- 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. - Fill E2 down to E8 with Ctrl+D. E6 shows Check, all other rows show a department.
- 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. - Highlight the rows that need attention: select E2:F8, then Home > Conditional Formatting > Highlight Cells Rules > Equal To, type
Checkand pick a fill colour. - Count what is still open with
=COUNTIF(E2:E8,"Check")somewhere outside the list. In the lab it returns 1. - 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.
#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.

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:A21instead 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)withoutFALSEuses approximate match and can return a wrong department instead of an error. Always addFALSEfor codes and IDs.
Quick reference: functions and shortcuts from this lesson
| Function or shortcut | What it does in an employee list | Example |
|---|---|---|
TRIM | Removes leading and trailing spaces and turns runs of spaces into one | =TRIM(B2) |
PROPER | Capitalises the first letter of each word, lowercases the rest | =PROPER(TRIM(B2)) |
UPPER / LOWER | All capitals or all lowercase, e.g. for codes or e-mail addresses | =UPPER(TRIM(D2)) |
LEN | Counts 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)," ")) |
XLOOKUP | Finds the code in the department table and returns another column | =XLOOKUP(D2,$A$11:$A$15,$B$11:$B$15,"Check") |
VLOOKUP + IFERROR | The same lookup for Excel 2019 and older | =IFERROR(VLOOKUP(D2,$A$11:$C$15,2,FALSE),"Check") |
COUNTIF | Counts the rows that still say Check | =COUNTIF(E2:E8,"Check") |
| F4 | Adds the $ signs to the selected reference (Windows; on a Mac Cmd+T) | A11:A15 → $A$11:$A$15 |
| Ctrl+D | Fills the formula from the top cell down the selection | Select C2:C8, press Ctrl+D |
| Ctrl+E | Flash Fill: Excel copies a pattern you typed once | Type "Noah Fischer" in C2, press Ctrl+E |
| Ctrl+T | Turns the list into a table that grows with new rows | Click 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.
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.
- Headcount by Department with COUNTIF and COUNTIFS
- Tenure from Start Dates: DATEDIF and YEARFRAC
- Birthdays and Work Anniversaries This Month
- Hiring Pipeline: Time-to-Hire and Open Roles
- Recruiting Cost per Hire by Source
- Leave Balances: Accrual, Days Taken and Overdrawn Leave
- Timesheets for Payroll: Regular Hours, Overtime and Week Pay
- Salary Bands and Compa-Ratio
- Employee Turnover Rate by Month and for the Half-Year
- Training Hours Tracker: Hours per Department and per Head
- HR Dashboard: Headcount Chart and Turnover Alerts
- Names and Email Addresses: Splitting Full Names, Building Logins & a Salutation Line
- Dynamic HR Lists: FILTER by Department, SORT by Start Date & UNIQUE Locations
- Pay Scale Lookups with Two Keys: Grade and Step, Part-Time Salaries & Next Year’s Step Increases
- Fixed-Term Contracts & Probation: End Dates with EDATE, Days Left & a 30-Day Reminder List
- Headcount on Any Date: Active Employees, Joiners and Leavers per Month from Start and Leaving Dates
- Salary Changes for Payroll: Percentage Raises, Effective Dates & Back Pay
- Input Rules for the Employee List: Drop-Down Lists, Allowed Hours & FTE, and Counting Old Errors
- Final Project: The Monthly HR Report – Headcount Movements, a Consistency Check, Turnover & a Combo Chart