1. Zahra says:

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")

2. Niclas says:

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!

3. Jay says:

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)

4. Lnda says:

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.

5. Linda says:

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″)

# A Real-Life Excel Test from a Job Interview: Can You Pass??

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!

Here 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.

1. How many total participants (number of participant unique IDs) are included in this data set?
2. How many participants DO NOT live in the 20019 zip code (include anyone that does not have 20019 as a zip code)?
3. How many participants live in the Lotus Square neighborhood?
4. 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):
5. How many participants have blank (missing) race/ethnicity data?
6. How many African Americans live in Kenilworth Courts?
7. There is one participant that has 8 children.  What is their race/ethnicity?
8. How many participants are 18 years old?
9. How many participants that are between the ages of 14 and 19 have 4 or MORE children?
10. How old is the participant that lives in Parkside that has only one child?

## Stuck? Here’s the Answer Key

1. How many total participants (number of participant unique IDs) are included in this data set?  114
2. 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
3. How many participants live in the Lotus Square neighborhood? 15
4. 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
5. How many participants have blank (missing) race/ethnicity data? 4
6. How many African Americans live in Kenilworth Courts?  23
7. There is one participant that has 8 children.  What is their race/ethnicity? Other
8. How many participants are 18 years old?  4
9. How many participants that are between the ages of 14 and 19 have 4 or more children?  1
10. 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.

These are the slowest of the slow.

The most inaccurate of the inaccurate.

And… the most common of the most common.

• 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.

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.

### Join the Interest List

Simple Spreadsheets: From Spreadsheet Stress to Superstardom with Microsoft Excel is only open once a year.

1. Zahra says:

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")

2. Niclas says:

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!

3. Jay says:

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)

4. Lnda says:

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.

5. Linda says:

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″)

## Soar Beyond the Dusty Shelf Report

Most “professional” reports are too long, dense, and jargony. Transform your reports with these practical tips.