Cellwise Hire · Office and admin preset
Excel test for HR coordinators
Candidates do the list work HR runs on: tidy names and emails pasted from a form, split "Last, First" exports into first and last names, flag people who appear twice, and answer counts and totals that follow a value in a cell. Every answer is checked by what it calculates, on the sheet they see and on hidden versions of it.
Create a free HR coordinator testSee a sample task
Free for your first 3 candidates. The Office and admin preset is preselected for you.
The test at a glance
- Workbooks
- Pasted contact list + Sales log
- Tasks
- 8 hands-on tasks at the level you pick
- Levels
- Basic, Intermediate or Advanced
- Time
- You choose; 20 to 40 minutes suits most
- Grading
- By result, on the visible sheet and 2 hidden versions
Good for: HR coordinators and HR assistants, People operations coordinators, Recruiting coordinators, HR administrators who keep employee lists.
What HR coordinators actually do in Excel
An HR coordinator keeps the lists everyone else relies on: new starters for IT and payroll, candidates from the applicant tracking system, attendees for training, names for badges and email accounts. Most arrive as exports, often with names as "Last, First", mixed capitals and stray spaces, and the same person in twice.
They also answer the quick questions: how many people in one location, how many applications from one source, what a figure comes to for the team typed in a cell. A formula that follows the cell is worth more than a number that was right once.
- Cleaning names and emails from HR system and applicant tracking exports
- Splitting "Last, First" into first and last name, and joining them back for letters and badges
- Flagging people who appear twice, whatever the capitals
- Counts and totals with COUNTIF and SUMIF, with the criterion in a cell
- Locking a rate with $ so one formula fills down a whole list
What the test checks
The Office and admin preset uses two workbooks: Pasted contact list (contacts pasted in from a sign-up form export: inconsistent capitals, extra spaces and mixed phone formats) and Sales log (october orders: one row per order, with the rep, region, product, units and unit price). Here is what that covers for HR coordinators, with the real task names and the level each one appears at.
Names from an HR export
"Last, First" with uneven spaces around the comma, split into first and last name columns, cleaned and capitalized, then joined back as "First Last". Intermediate level.
- First namesIntermediate
- Last namesIntermediate
- Full namesIntermediate
Tidy names and emails
TRIM and PROPER for names, LOWER for emails, and the domain after the @, which is how you tell personal addresses from company ones.
- Clean up namesBasic
- Lowercase emailsBasic
- Email domainBasic
Duplicates and a true head count
People who appear twice with the same email in different capitals, flagged, and the number of different people on the list. Advanced level.
- Flag duplicate emailsAdvanced
- Unique contactsAdvanced
Middle names and initials
The last word of a name that has a middle name in it, and initials: what badges, email aliases and matching one list to another need. Advanced level.
- Last word as last nameAdvanced
- InitialsAdvanced
Counts and totals that follow a cell
The second workbook is a sales log, not HR data, but the questions are the ones HR answers about its own lists: a COUNTIF for one category, a SUMIF for whatever is typed in a cell, a rate locked with $ so it fills down, and a count over a threshold.
- Orders from EastBasic
- Revenue for one regionIntermediate
- Commission per orderIntermediate
- Large ordersIntermediate
Every task in this test, by level
| Level | Pasted contact list | Sales log |
|---|---|---|
| Basic | Clean up names Lowercase emails Email domain Gmail addresses | Revenue per order Total units Average order size Orders from East |
| Intermediate | First names Last names Full names Phone digits only | Commission per order Revenue for one region Desk lamps in that region Large orders |
| Advanced | Last word as last name Initials Flag duplicate emails Unique contacts | Running total Rep with the biggest order Rep revenue since a date Different products sold |
Each test uses one level: four tasks from each workbook.
Which level to choose
- Basic · 20 minutes suggestedHR assistants and entry-level coordinators: tidy names and emails, the email domain, a count with a wildcard, and quick totals and counts.
- Intermediate · 30 minutes suggestedHR and recruiting coordinators who prepare lists for others: "Last, First" split and joined, phone numbers reduced to digits, and totals that follow an input cell.
- Advanced · 40 minutes suggestedPeople ops coordinators and HR administrators who own the data: middle names, initials, duplicate entries, unique counts, running totals and date-based totals.
A real task from this test
This is one of the tasks in the Office and admin preset at the Intermediate level, on the sheet candidates see. The answers below were run through the same grader that marks real tests: on this sheet, and on two hidden versions with different data and an extra row inserted.
Sample task · Pasted contact list · Intermediate
First names (in D2, filled down to D11). The names came out of the system as surname, comma, first name, with uneven spacing. Pull out a clean, properly capitalized first name for every row, with one formula filled down.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Name (as pasted) | Phone | First name | |
| 2 | chen, maya | [email protected] | (555) 014-2290 | |
| 3 | ORTIZ, Luis | [email protected] | 555-014-7731 | |
| 4 | nair,priya | [email protected] | 555 014 3384 | |
| 5 | Okafor , SAM | [email protected] | (555) 014-9012 | |
| 6 | whitfield, dana | [email protected] | 555-014-5547 | |
| 7 | LEE, jordan | [email protected] | 555 014 6620 | |
| 8 | lopez,ana | [email protected] | (555) 014-1185 | |
| 9 | carter, BEN | [email protected] | 555-014-8843 | |
| 10 | kim, grace | [email protected] | 555 014 2671 | |
| 11 | haddad, omar | [email protected] | (555) 014-3309 |
| Formula in D2 | Sheet they see | Hidden checks | Result |
|---|---|---|---|
=PROPER(TRIM(MID(A2,FIND(",",A2)+1,100)))Everything after the comma, then TRIM and PROPER | 10 of 10 rows right right | Right on both | Solved |
=PROPER(TRIM(TEXTAFTER(A2,",")))TEXTAFTER, then TRIM and PROPER | 10 of 10 rows right right | Right on both | Solved |
=PROPER(MID(A2,FIND(",",A2)+1,100))No TRIM | 2 of 10 rows right wrong | Not needed: already wrong | Not solved |
=PROPER(MID(A2,FIND(",",A2)+1,100))The first names look right on screen, but most of them start with the space after the comma and some end with one. A first name with a leading space will not match the same name in payroll or the HR system.
Row by row
| Row | Name (as pasted) | =PROPER(TRIM(MID(A2,FIND(",",A2)+1,100))) | =PROPER(TRIM(TEXTAFTER(A2,","))) | =PROPER(MID(A2,FIND(",",A2)+1,100)) |
|---|---|---|---|---|
| 2 | chen,··maya | Maya | Maya | ··Maya (wrong) |
| 3 | ·ORTIZ, Luis | Luis | Luis | ·Luis (wrong) |
| 4 | nair,priya | Priya | Priya | Priya |
| 5 | Okafor ,··SAM | Sam | Sam | ··Sam (wrong) |
| 6 | whitfield, dana· | Dana | Dana | ·Dana· (wrong) |
| 7 | LEE, jordan | Jordan | Jordan | ·Jordan (wrong) |
| 8 | ·lopez,ana | Ana | Ana | Ana |
| 9 | carter,··BEN | Ben | Ben | ··Ben (wrong) |
| 10 | kim, grace | Grace | Grace | ·Grace (wrong) |
| 11 | haddad,···omar· | Omar | Omar | ···Omar· (wrong) |
In the row-by-row table, red dots show the spaces left behind. The hidden version has different names with the spaces in different places, so typing the first names in by hand fails there.
Results calculated by the Cellwise formula engine. The hidden versions stay on our server, so this page shows only whether each answer passed them.
See the screen your HR coordinator candidates work in: try a 3-minute sample test and read the report you would get. No signup.
What the report shows
When a candidate finishes, you get an overall score and every task: solved or not, the exact formula they wrote, the time spent and the number of tries, plain-English notes, and a replay of every edit with the sheet as it looked at each step. For HR coordinators, two things are worth a look:
- If you chose Advanced, look at the duplicate flag. A COUNTIF over the whole list, locked with $, flags both copies of a duplicate; one that only looks upward flags the second copy only. The formula shows which one the candidate wrote.
- The notes say when a candidate typed cleaned names in instead of using a formula. That works for ten rows and not for a 400-person export, and the hidden version has different names.
You can also add 8 multiple-choice knowledge questions at the same level, scored separately. Candidates never see their score.
What this test does not cover
- It does not test HR knowledge, employment law, payroll calculations, an HR system or an applicant tracking system. It tests the Excel work around them.
- Turnover, time to hire and other HR metrics are not in the test. Our guides to employee turnover rate and time to hire have worked examples to use in an interview.
- It is the same Office and admin preset as the Excel test for administrative assistants, described for HR work.
Pricing
Free for your first 3 candidates, no card needed. Then Starter is $79 a month or $790 a year for 25 completed candidates a month, and Agency is $199 a month or $1,990 a year for unlimited candidates. Only candidates who complete a test count.
Founding price: locked for as long as you stay subscribed (first 10 customers)
FAQ
What Excel skills does an HR coordinator need?
Keeping employee and candidate lists clean: TRIM, PROPER and LOWER, splitting and joining names with TEXTBEFORE and TEXTAFTER or LEFT, MID and FIND, flagging duplicates with COUNTIF, counting and totaling by a category with COUNTIF and SUMIF, and filling a formula down a whole list without breaking it. Many roles add a lookup to pull details from a second list.
Which level should I choose for an HR role?
Basic suits HR assistants. Intermediate suits most HR and recruiting coordinators, because it includes splitting "Last, First" names. Choose Advanced for people ops roles that own the employee data, where duplicates and middle names matter.
Does the test use HR data?
The first workbook is a contact list pasted from a form export, which is the same work as an employee or applicant list. The second is a sales log, used for counts and totals by a category. All of the names and figures are made up.
Does it test HR metrics like turnover or time to hire?
No. If the role reports HR metrics, add a short turnover or time-to-hire question in the interview; the guides linked on this page have worked examples with checked answers.
Is it fair to give HR candidates an Excel test?
A short test that reflects the real work is a common part of hiring. Give every candidate the same test and time, follow local hiring law, and keep tests job-related.
Related
Looking to learn Excel yourself? Start the free Cellwise course.