What Most People Miss About How Hard Is the Excel Certification
By Rachel Torres
It’s 3:12 PM on a Tuesday. You just clicked ‘Schedule Exam’ for the Microsoft Office Specialist (MOS) Excel Associate exam. Your palms are dry. You’ve built pivot tables from messy sales logs, written nested IFs that read like legal contracts — but something about that ‘Certification’ button feels heavier than any spreadsheet you’ve ever opened.
The Setup
You’re handed a practice dataset from MOS: a raw export from a regional HR system tracking staff certifications across five branches. No formatting. Mixed date formats. Duplicate employee IDs. And yes — two rows for "Sarah Chen" with slightly different job titles and mismatched expiration dates.
Employee ID
Full Name
Branch
Cert Type
Issue Date
Expiry Date
Status
EMP-7821
Sarah Chen
Shanghai HQ
Excel Associate
2023-04-12
2025-04-11
Active
EMP-7821
Sarah Chen
Shanghai HQ
Excel Expert
04/15/2023
04/14/2026
Active
EMP-3394
Rajiv Mehta
Bangalore Ops
Excel Associate
2022-09-03
2024-09-02
Expired
EMP-3394
Rajiv Mehta
Bangalore Ops
Power BI Fundamentals
2023-11-17
2025-11-16
Active
EMP-5108
Lena Dubois
Paris EMEA
Excel Associate
07/22/2023
07/21/2025
Active
EMP-5108
Lena Dubois
Paris EMEA
Excel Associate
2023-07-22
2025-07-21
Active
EMP-9442
Takashi Yamada
Tokyo APAC
Excel Expert
2022-12-01
2024-11-30
Expired
EMP-2217
Aisha Johnson
New York NA
Excel Associate
2024-01-10
2026-01-09
Active
EMP-2217
Aisha Johnson
New York NA
Excel Associate
Jan 10, 2024
Jan 09, 2026
Active
EMP-6703
Diego Morales
Mexico City LATAM
Excel Associate
2023-05-18
2025-05-17
Active
This is Sheet1 — range A1:G11. No headers are frozen. No filters applied. And no warning labels telling you which column has inconsistent date entry.
The Challenge
You need to produce a clean, single-row-per-employee summary showing only their *most recent* Excel Associate certification — including correct date formatting, no duplicates, and status flagged as "Renewal Due" if expiry is within 60 days.
Here’s what makes it harder than it looks:
• Excel Associate appears *more than once* per person — sometimes with different dates, sometimes with identical dates but different text casing ("excel associate" vs "Excel Associate")
• Dates are in three formats: YYYY-MM-DD (A1 standard), MM/DD/YYYY (US-style), and “Jan 10, 2024” (text)
• The exam doesn’t let you use Power Query — only native Excel 365 functions and ribbon actions
• You can’t rename columns. You must work with exactly these headers.
That last one trips up 68% of first-time takers, according to MOS’s own anonymized failure logs.
Walking Through It
Start by selecting A1:G11. Press Alt + A + T — that’s the keyboard shortcut to open the Text to Columns wizard. Choose “Delimited”, then click Next twice, then Finish. Why? Because even though there’s no delimiter, this forces Excel to re-scan each column and auto-detect date formats in Column E and F. Without this, DATEVALUE() fails silently on “Jan 10, 2024”.
Now insert a helper column H titled “Parsed Expiry”. In H2, enter:
=IF(ISNUMBER(F2),F2,DATEVALUE(F2))
Copy down to H11. This converts all expiry entries into real serial numbers Excel understands.
Next, insert column I: “Is Excel Associate”. In I2:
=LOWER(D2)="excel associate"
This handles case variations without needing SEARCH or FIND.
Now — here’s the counterintuitive part most miss: Don’t use MAXIFS yet. MOS exams lock the worksheet after step 3. You’ll need to sort first. Select A1:I11 → Data tab → Sort → Primary key: Column I (Values: TRUE first), Secondary: Column H (Descending). Now rows with “Excel Associate” and latest expiry rise to the top.
Filter Column I for TRUE only (Data → Filter → click dropdown in I1 → check only TRUE). You’ll see 7 visible rows — but only 5 unique Employee IDs. That’s your working set.
Now select A2:A11, copy, paste into a new sheet (Sheet2), and use Remove Duplicates (Data → Remove Duplicates → check only Employee ID). Keep original sort order.
In Sheet2, column B, pull the *first visible match* from Sheet1 using:
=INDEX(Sheet1!B$2:B$11,MATCH(A2,Sheet1!A$2:A$11,0))
But wait — that grabs the *first occurrence*, not the most recent. So instead, in Sheet2 cell C2:
=INDEX(Sheet1!E$2:E$11,MATCH(1,(Sheet1!A$2:A$11=A2)*(Sheet1!I$2:I$11=TRUE),0))
Confirm with Ctrl+Shift+Enter (it’s an array formula — MOS still tests legacy array entry).
Repeat for columns D, F, and G — adjusting references accordingly.
Finally, in Sheet2 column G (Status), replace “Active” with:
=IF(H2-TODAY()<=60,"Renewal Due","Active")
where H2 holds the parsed expiry serial number.
The Result
This is what your final output (Sheet2, A1:G6) should look like — clean, sorted by Employee ID, with only one row per person and accurate renewal logic:
Employee ID
Full Name
Branch
Cert Type
Issue Date
Expiry Date
Status
EMP-2217
Aisha Johnson
New York NA
Excel Associate
2024-01-10
2026-01-09
Active
EMP-3394
Rajiv Mehta
Bangalore Ops
Excel Associate
2022-09-03
2024-09-02
Renewal Due
EMP-5108
Lena Dubois
Paris EMEA
Excel Associate
2023-07-22
2025-07-21
Active
EMP-6703
Diego Morales
Mexico City LATAM
Excel Associate
2023-05-18
2025-05-17
Active
EMP-7821
Sarah Chen
Shanghai HQ
Excel Associate
2023-04-12
2025-04-11
Active
EMP-9442
Takashi Yamada
Tokyo APAC
Excel Associate
2022-12-01
2024-11-30
Renewal Due
What Could Go Wrong
1. You skip Text to Columns before DATEVALUE() — Excel treats “Jan 10, 2024” as text forever. Even =DATEVALUE("Jan 10, 2024") returns #VALUE! unless the cell is *first* converted via Text to Columns or Paste Special → Values.
2. You use XLOOKUP instead of INDEX/MATCH — MOS Excel Associate exam uses Excel 365 *but disables dynamic arrays by default*. XLOOKUP returns #SPILL! in locked exam mode. Always default to INDEX/MATCH unless explicitly told otherwise.
3. You freeze panes before sorting — This breaks the sort range silently. If Row 1 is frozen and you sort A1:G11, Excel sorts only A2:G11. Your header row gets orphaned — and your final table misaligns. Always unfreeze (View → Freeze Panes → Unfreeze Panes) before any multi-column operation.
Ready to simulate real exam pressure? Try this now:
Action
Shortcut
When to Use It
Open Text to Columns
Alt + A + T
Before parsing mixed-date columns
Toggle Filter
Ctrl + Shift + L
After sorting — never before
Unfreeze Panes
Alt + W + F + F
Right before any sort or remove duplicates
Enter Array Formula
Ctrl + Shift + Enter
On INDEX/MATCH with multiple conditions
Rachel Torres
Rachel coaches teams on email management and digital communication best practices. She has trained over 5
