My heart just about stopped.
…
Ten years ago, I was interviewing for an analyst position. The official job title was “Learning and Evaluation Specialist.” Nowadays, you might hear it called “Quality Improvement Specialist,” “Research Analyst,” or even “Data Scientist.”
I remember sitting in the interviewer’s office. He was asking me about my Excel skills. It was a fast-paced office, so the expectation level was pretty high: that you could work totally independently.
I was going on and on about all my great spreadsheet skills.
Because that’s what you do in interviews, right?
It’s not the time to be humble.
And then the interviewer was like, “Prove it. You’ve got great Excel skills? C’mon. Come with me.”
He led me to another office across the hall.
And what was waiting for me?
A glowing Excel screen!!!
With an Excel test!!!
My heart just about stopped. I was replaying everything I had just said in the past hour. What did I say I knew how to do again??
I hoped I could actually do those things!
Here’s what happened next.
…
Re-Taking the Excel Test from Ten Years Ago
Spoiler alert: I got the job.
Isaac (the interviewer) and I thought this would make a really fun blog post.
I’m going to see if, ten years later, I can still pass!
This could be really exciting. These days, I spend most of my time training others on data visualization—which means I spend less time inside spreadsheets. Maybe I’ve still got it!
Or, this could be really humbling.
And I might wish I never recorded this video at all. 😊
I recorded this video in a single take.
I want to show you that even somebody who does this for a living still gets stuck sometimes.
Your Turn: Take the Excel Test!
Download the spreadsheet here:
Download the spreadsheetHere are the ten questions.
Some of the questions require using a date. I recorded this lesson on January 31, 2020, so my answers are dependent on that date.
- How many total participants (number of participant unique IDs) are included in this data set?
- How many participants DO NOT live in the 20019 zip code (include anyone that does not have 20019 as a zip code)?
- How many participants live in the Lotus Square neighborhood?
- There is a typo in one of the Neighborhood data fields – one value is spelled differently than the rest. Please write out the misspelled value below (exactly as it appears in the data – with the misspelling):
- How many participants have blank (missing) race/ethnicity data?
- How many African Americans live in Kenilworth Courts?
- There is one participant that has 8 children. What is their race/ethnicity?
- How many participants are 18 years old?
- How many participants that are between the ages of 14 and 19 have 4 or MORE children?
- How old is the participant that lives in Parkside that has only one child?
Stuck? Here’s the Answer Key
- How many total participants (number of participant unique IDs) are included in this data set? 114
- How many participants DO NOT live in the 20019 zip code (include anyone that does not have 20019 as a zip code)? 4 – 1 of which is blank
- How many participants live in the Lotus Square neighborhood? 15
- There is a typo in one of the Neighborhood data fields – one value is spelled differently than the rest. Please write out the misspelled value below (exactly as it appears in the data – with the misspelling): Parsdise
- How many participants have blank (missing) race/ethnicity data? 4
- How many African Americans live in Kenilworth Courts? 23
- There is one participant that has 8 children. What is their race/ethnicity? Other
- How many participants are 18 years old? 4
- How many participants that are between the ages of 14 and 19 have 4 or more children? 1
- How old is the participant that lives in Parkside that has only one child? 33
Bad, Better, Best: A Range of Correct Solutions
Throughout the lesson, you’ll hear me refer to bad, better, and best solutions.
Bad Solutions
These are the slowest of the slow.
The most inaccurate of the inaccurate.
And… the most common of the most common.
Bad solutions include:
- Tallying by hand (by pointing at the screen)
- Eye-balling (mentally tallying by looking at the screen)
- Highlighting a range of numbers and counting that way
- Anything that can’t be replicated by your Future Self or someone else on your team
- Anything that doesn’t show your work
This is what most people do before we’ve been exposed to time-saving strategies.
Better Solutions
Formulas! You’ll see countif, countifs, counta, countblank, etc. in this video.
Better solutions are replicable.
But you can also make typos. They’re not error-proof.
Which brings me to…
The Best Solutions
I was personally very slow to adopt pivot tables.
I’d been taught—informally, on-the-job, by fellow coworkers—to use formulas in Excel.
Before that, I’d also been taught—formally, in undergraduate and graduate classroom settings—to use formulas in SAS and SPSS.
I had a good five years of all-day-every-day number-crunching experience under my belt before I even discovered that pivot tables existed.
After that, it took another five years to realize that pivot tables are faster than formulas for 99% of people. For 99% of people, it’s also much harder to make mistakes in pivot tables than in formulas.
Your Turn
Download the spreadsheet. Time yourself. How fast did you finish? Which solution(s) did you use to approach each problem? Comment and let me know.
Zahra 
Niclas 
Jay 
Lnda 
Brittany 
Tony 
Jennifer 
Tinashe 
Rasmus Harboe 
Roy 
Courtney
15 Comments
For the ages: did you try the “Floor” function?
Or YEAR(TODAY()-Date)?
And your if formula if you don’t round the ages: =IF(AND(years>=18,years<19),"Is 18","Is not 18")
Hi!
Great video!
I think that the DATEDIF-function could help during the age-questions. And I would have formatted the data as a table. Makes it easier to read the formulas.
Keep up the good work!
Loved the video – so quick to answer each question 3 times!
For question 7, you could replicate column E values in column H, then use the formula =VLOOKUP(8,G:H,2,FALSE)
Hi a better way to find the misspelled word in the Neighborhood column is to select the column by clicking at the top of the column and then use Spell Check. This is only good for an actual misspelled word not for a word that is just spelled differently but still spelled correctly.
Dateif was not available in my version so I used =INT(YEARFRAC(F113,TODAY())) to get the ages, this gave me whole numbers and then I used =COUNTIF(G2:G115,”=18″)
For number 8 I would have Converted to Text to Columns in the birthdate with “/” as the divider and then used the current year – the birth year. Then used the pivot table
Great insight! Did not across my mind of using pivot tables as quick way of getting answers over formulas, was thinking of pivot tables as more of use for final presentations. Not sure if most tests would allow freedom of different methods, maybe the tests would locked you in the predefined methods test designer want…
I have much more I can show with Excel than other applications needed for jobs I’m trying to land. Excel is like the highlight of my resume. But rarely even get any chances convincing to get the Excel Tests and Job Interviews.
Someone else already offered the solutions
Q7 Use combination of the Match Index functions. I would likely lock the range though.
Q8 Like some question is bit vague I guess, I would have simply extract the ‘year part’ of 2 dates to work out the difference to get the age.
Other questions nice tip about using Pivot Tables, :), or some complex formulas that might take more time to build up initially.
Thanks for weighing in, Tony!
I loved this video Ann. I had to do an Excel test for a consulting firm I worked at over 7+ years ago and wonder how I would do re-taking it. I really like the real-time recording that captures how you think through a question to find answer. Would love to see more videos like this in the future if there are other common Excel tests for jobs you’ve seen.
I haven’t come across any Excel job tests recently, unfortunately! After that particular job interview ~10 years ago, I actually starting working part-time as an Excel certification instructor, so having that on my resume was more than enough to help employers trust that I had mastered Excel.
That being said… I’ve never actually taken an Excel certification test myself, despite working as an instructor, ha! I’m scheduled to take an exam tomorrow — January 5, 2021 — so stay tuned to hear how it goes. I’ll likely share my insights about the test-taking process on my newsletter and inside the “Simple Spreadsheets” Full Course.
Thanks Ann, i am practicing excel tests these days in anticipation of an interview. I was a bit hesitant in using pivot tables as i viewed them as complex . After going through your presentation , i felt more confident in myself and can face interview tests now. I now believe that learning is a process that will continue in our lives if we are willing to take it!
I am probably a minority, but I disagree on using pivot tables is better.
for smaller dataset, by using formulas, you get much more transparency in where the numbers come from, and the output is easier to work with, if you at a later stage need to make some additional calculations, or if your underlying dataset is structurally changing
For larger dataset, where formulas tend to take too much time to recalculate, I much prefer to put it all into Access or maybe PowerBI. To a large extent, I think too many people are using overusing excel, and do not use better suitable tools enough
Thanks for weighing in, Rasmus!
Ann, how to download the file?
For age, you could have used ROUNDDOWN().