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.
Want to follow along? Download the spreadsheet here: depictdatastudio.com/a-real-l...
00:00 My First Real-Life Excel Skills Test
01:33 Can I Pass 10 years Later?!
02:36 The Excel Test
04:30 Question 1
08:28 Question 2
10:59 Question 3
12:44 Question 4
13:51 Question 5
15:45 Question 6
17:01 Question 7
18:42 Question 8
25:52 Question 9
26:45 Question 10
27:26 Finished! My Final Thoughts
28:24 How Did You Do?
-----
Most "professional" reports are too long, dense, and jargony. Soar beyond the dusty shelf report with our complimentary mini course: depictdatastudio.teachable.co... You'll never look at reports the same way again.

[@msfangster]

Who else is watching this thinking... just when I thought I knew excel. Lol

[@passapassa3567]

Omg SMH

[@GrobariNBGD1970]

Lol same

[@atlienacct5868]

I’m crying 😂 I have an assessment tomorrow for a job

[@Bashir_Kano]

🤣😂

[@Bashir_Kano]

@@atlienacct5868 Goodluck mate. How was it?

[@amow7215]

This video helped me ace an excel test and get a new job! Thanks so much for making it!

[@toivokarttunen8187]

I've got one tomorrow, wish me luck!

[@JE-im4im]

@@toivokarttunen8187 update

[@Luv-dq5th]

@@toivokarttunen8187 update?

[@jaberabedin5487]

So basically Pivot Table is the answer to all of life's questions 😂

[@Nova_Nora5]

Lol 😂 yup! It’s good to know

[@julienielsen4462]

When I took Excel they never taught us pivot tables, glad to see its a short cut.

[@keithjones8139]

Loved this presentation Ann. Today I learned about pivot tables for the first time - I could have used them so often in the past if I had bothered to find out! I spent two hours practising as I was going through your video - you have such an engaging style. Now I feel much more confident for my excel assessment this week, thank you.

[@BrittneyJon]

Great video - I enjoy watching people do things they are really good at, and learning from them. I'm not near your level of expertise in excel so it was interesting to hear you working through the problems out loud while also demonstrating. Excel can be daunting for those of us who don't quite grasp the extent of its power, but you make it look easy (and fun). More like this!

[@crisredic321]

Thank you for posting this...its a huge help for recent graduates

[@AB-nv8tj]

Thank you for uploading this. It really helped.

[@pokernadlac]

What about using rounddown() formula instead of round() at question no 8 and 9? This way the ages will be always rounded correctly

[@AnnKEmery]

Didn't even know that existed! Thanks for teaching me about rounddown. :)

[@iamsunnysideup7115]

Today I learned that I know how to format Excel, but know nothing about Pivot tables and they are really cool! LOL! Thank you for a great video! I noticed that you created this video right before our lives changed in 2020. I am watching two years later and preparing for a test for a job that I really want. Wish me luck! xoxo

[@luissbeast9662]

Thank you so much I really needed this I followed along with my own project, and I learned a lot. I'm using pivot tables all the time now!

[@mariacampbell1743]

Watching this ahead of a work sample I will have to do for an analyst interview tomorrow... super helpful!!!

[@elieatpeace9379]

This is really helpful, every moment of learning has it own impact in life and this tutorial has made it own in my life too.

[@TT-gz5wr]

Thank you, I will definetley walk away with some new ways to do things. And you keep me entertained!

[@sya8109]

This is really helpful, thanks for posting this.

[@kvin9210]

I wouldn't pass this ever on the first try.

[@samadkhan-tw4gq]

Thanks Ann, it's so helpful.

[@notesfromleisa-land7893]

I would recommend that the very first step be to format the data first as a table. Then add totals (table design). Using the filter button for searching/filtering, one can easily use count function in the totals drop down to get counts (inclusive and exclusive using filtering) for several of the questions.

[@briarrose5428]

pivot tables the best way to go. I'm now understanding pivot tables more after watching this video. I'm in Spreadsheet class preparing for tests . Thank You please post more videos

[@tonyn8644]

You might not be a master at excel, but you definitely the best Teacher 👍🙏

[@johnadair4979]

Love the video. Thoroughly enjoyed it. Thanks for posting. The Pivot tables are a great method to answer the questions. 10 years ago, we didn't have Power Query. But Power Query would make some of the last questions very easy.

[@realiaticPerspectives]

Great content, loved every minute of it.

[@violondon8776]

Super helpful to prepare for my assessment test! Thanks :)

[@manojkarra3306]

Use formulas and pivot to survive or forget excel😂 Such a good way..Thanks for helping 😊

[@LauraD228]

Super, I hope I will do well on my upcoming Excel test. Keep fingers crossed !:)

[@freesoul4715]

Thanks a lot for your effort, I had watched a lot of learning pivot tables but didn't really get it until now , it's really was useful

[@danieseadams1642]

To get the age I think the easiest way was to use the datedif function with the today function nested inside and "y" for years and it would have returned to the year without the decimals.

[@lovepiano4616]

You can use "Group" and "insert slicer" for the pivot table to answer some of the questions faster.

[@Nick-xu2hh]

To those new to Excel or any other analytics tools or languages (SQL etc) for that matter - please don’t stress out. It may seem like a lot at first but you’ll get it...if you keep at it. Keep building my friends :)

[@AnnKEmery]

YES! This was an interview for a job that required 2+ years of experience, not an entry-level position. I'd already been working with Excel for 2 years before taking this test. Not all day every day, but I did have some experience already.

[@zlatte2594]

I have an assessment test this coming week for a job even though I told a panel during my interview that I did not have much experience in excel. I am a nervous wreck and I don't even know if I can pass it. Any tips because I'm terrified. 😨

[@aprilm752]

@@zlatte2594 This test is intermediate to advanced level. You won't get this kind of test. You will get a basic Excel test.

[@kcwestoutdoorclub3232]

Depends on where you are applying. I know for sure in many Engineering and Accounting positions these are all extremely simple questions at the most basic level. Im not trying to be mean or scare anyone, Id just rather you get a reality check from some guy on the internet than at a job interview.

[@keifer7813]

Thanks for the video. Commentary really helped. Never realised how much you can do with pivot tables lol

[@saphire1975]

I'll have my exam next Monday, thank you for sharing, it was very useful!

[@juhimalhotra7786]

That age formula is too tough for me ☹️. Thank for the amazing video and it really helped me in understanding it. You really did this such a funny manner . Loved it

[@letspotato7163]

Very informative and style of teaching is so cool. Impressed

[@lsjohnson20]

Thanks for the answer levels and the honest commentary. We want to develop an excel test to prevent future hiring catastrophies. This is so helpful.

[@AnnKEmery]

Hope this helps! This was for a position for a Bachelor's degree + 2 years of experience. Not entry level, but fairly beginner.

[@alex-sd]

The INT (integer) and TRUNC functions will round the ages to the correct numbers you desire. Great video!

[@GeoffLilley]

Number one thing I'd say is put the data in a table. For the "blank" cells, I might use a LEN function and check for cells where TRIM(LEN(cell))

[@casmartin790]

I think a LookUp is your best bet for question 7

[@chrisokereafor1391]

you did really well

[@danielcervantes3779]

For question 7, use conditional formatting and under the "includes text" you could put "8"

[@easyboy1950]

Loved the video. Just subscribed.

[@aprilm752]

😍Love it!

[@analucero9905]

Love your videos, thank you! I often grapple with the ages and often do it not the right way... From a quick look at the blank zip code, you can infer that it is a 20019 address. The correct answer would then be that 3 people don't live in that Zip code.

[@john.h.richards1]

Great video! For the 18 age question, I would have used =INT((TODAY-G2)/365.25) and that would have truncated the answer to the year without rounding and copied it down then used pivot table to get the rest of the answer.

[@cococnk388]

Pivot tables help a lot, but sometimes formulas give errors. Thanks for showing me another perspective

[@alastermyst]

25:00 ish. Int instead of round. This will round down to the nearest integer. RoundDown can also be used. Due to the type of output being looked for though, int would likely be better since that is the form it is typically represented in.

[@crob2636]

Thanks didn’t tot of using pivot as fastest solution. Gotta test now wish me luck and will update you. Thanks

[@wha91590]

Thankyou Ann, I enjoyed watching this just for fun. I was going through the same various answers in my head so that was good to see what you showed. One other thing to watch out for is leading and trailing spaces along with the text in a cell. I would just like to add that what really impressed people was that I vary rarely use the mouse. I have memorised most of the keyboard shortcuts and this enables a far greater speed. I found that I had to explain what it was that I was doing as the 'watcher' would try to follow mouse actions which were not being used. I forced myself to learn these by hiding the mouse around the back of the PC. One trick is to announce (quietly to yourself) what the shortcut is as you are typing it. eg, If you are calling a Pivot Table, just think 'ALT D P' as you type it. I taught this same method to all new staff and they loved it.

[@user-ct5bj5ws1c]

the summary - Pivot Table is the best way to answer every question)))

[@jeremyhelm9505]

Great video thank you

[@andrioktavianto7604]

Thank you for the video. I watch your video to prepare for Excel & SQL test for analyst position

[@beautycabelloaccesorios1638]

How did it go?

[@marialuisalichia94]

For the age group , you can group them in the pivot table. effectively resulting in the total quantity

[@aholmes1397]

Easy , I wish I could get a SQL Query test like this.

[@Cam-sl8ve]

8:27 - that count will also include duplicates if they exist, so you'll need to count "distinct" values, its an option in pivot tables.

[@matricus]

There is no distinct count in pivot tables. You are referring to a power pivot via Excel data model. Completely different thing and not required to know unless you are applying for a data engineer position as its an expert feature.

[@weswheeler3331]

If you're worried about spaces in blanks you can select them, control F to hit replace, in find what just hit space, in replace what leave it empty. it will delete all spaces in the selected cells. For the number of kids one a quicker way would be selecting the number of kids column. Control F, 8 to find specifically 8 and search across for associating information.

[@jessiejoseph1093]

Thank you

[@juliewambua4577]

Thank you :)

[@oe10s024]

In question 8, countifs() could do with greater than 18 and less than 19 age.

[@edravtv4367]

If were to do it, ill format it to table, then I can add slicers. It works like a charm.

[@danikrupl4090]

In question 8, I think you can do a countif(and()) formula, 1st criteria more than 18, 2nd less than 19.

[@ranyaa2699]

Ann this is an awesome review for Excel test. You made the tutorial very interesting and efficient. I could not download the Excel format of the spreadsheet file to practice. The download of the spreadsheet was a image. How can I download the Excel spreadsheet in Excel format to practice? Thanks

[@AnnKEmery]

Hi Ranya- so glad you enjoyed the video! Sorry you're having issues downloading the spreadsheet. Make sure to turn off any pop-up blockers you may have and you can try again here: annkemery.gumroad.com/l/bqJvsF

[@Squash81]

I'd think that Index/Match would be a great candidate for the question about 8 children. You could put the formula in G116: =INDEX(E2:E115,MATCH(G117,G2:G115,0)) and then type 8 into G117.

[@Kevn_96]

could you do this with Vlookup?

[@excelgun8915]

@@Kevn_96 Hi Kevin, If you wanna do this with vlookup formula, you have to drag the "Number Of Children" column between neighborhood and race columns. Unless you drag this column as I told you, vlookup doesn't work in this case.

[@youtienne3829]

@@excelgun8915 But Xlookup would.

[@jakerockznoodles]

This is one of those few occasions imo where the "bad" solution (filters) is still better than the "better" solution (formulas). After all, what if the question writer was wrong and there were multiple people with 8 kids? Xlookup and index-match are only going to search for one value. Sounds nitpicky, but in a real world scenario this could actually be a very significant oversight.

[@Megabites46]

Why didn't you use pivot tables during your job interview? Also, thanks for the instructional video. I'm taking my excel exam on Tuesday for an entry-level data analyst and I hope that the test would be similar to this.

[@AnnKEmery]

I didn't learn pivot tables in college or grad school, so I simply hadn't encountered them yet 10 years ago.

[@halowaffles]

For question 5 (determining how many blank fields there are): Could you use the TRIM function to make sure any blank cells are actually blank (no spaces) and then use a pivot table to analyze the column? I'm new to working with excel but hoping to land a relevant job by the start of next year, at the very least. Thanks for the video(s)!! Massive appreciate to anyone who uses their free time to educate the world :)

[@mmaimmortals]

One easy method is to apply an auto filter and then use the drop down to see if there are apparent blanks and include them with the actual blanks. After filtering using this method, you will see the total record count in the lower left of the screen.

[@rahulpkpres]

Question 7: You can use the combination of Index and Match formula where Match give you the reference of adjacent row number from "No of Children" column and Index will give you the value of "Race" column from that row. =Index(A1:Z200, Match(8, no of Children column like E1:E200,0), Match("Race column name", A1:Z1, 0))

[@Kevn_96]

could you do this with Vlookup?

[@keifer7813]

@@Kevn_96 Not unless you shift the "number of children" column to the left first. X lookup or just Index/Match saves you the trouble

[@tingkagol]

I think the best is to do both formulas AND pivot tables. It's like driving manual first before going automatic. I would not hire someone who relies solely on pivot tables without knowing any formulas

[@montwinjack]

I have to take an Excel test today and this makes me feel more confident. Thank you. I hope this is the kind of test I get.

[@A3L2A3N]

Howd it go? I am taking one Wednesday

[@mosesking2923]

@@A3L2A3N How did yours go?

[@AL-yt1kn]

For the 18 age one, could you add a column and just do the LEFT formula for 2 to truncate the numbers (since you don’t want to round up to 19, if you’re 18.75 years you’re 18) and then repivot and filter for 18 then?

[@deeptigupta518]

Which formula to use for the last question parkside with 1 kid?

[@j.d.singer8517]

Question for you: what tasks in Excel would you consider to be basic excel, and when does it become "intermediate"? Thanks very much! Great video and very informative!

[@AnnKEmery]

Great question! Depends on the industry/sector for sure, and even on the specific job role. For example, I used to think pivot tables were advanced. Now, I consider them to be beginner (because they let you bypass formulas, which are harder than simply dragging and dropping).

[@Obyvvatel]

Technically speaking you can't even divide by 365.25 to get the years in a timespan, because it assumes that all years are smoothly 365.25 days long, and not how it actually is - with 3 years shorter and 1 longer. This can introduce a small mistake depending on what kind of year it was at the start and end of the calculation. It only makes a difference in very rare cases when the numbers sort of work out against you. Basically you're assuming that the average is 365.25, which is correct in general, but might not be in some specific situation, eg. if the span of time both starts and ends with a 366 day year and has 3 normal years in between, the average is 365.4., so then if you use 365.25, you will get an answer that is slightly off, and maybe the fact that it's slightly off makes some IF statement somehwere not work properly, etc. EDIT: So I think the rock solid approach would be to actually split days, months and years from the date and work with that.

[@AnnKEmery]

Yes, agreed! Thanks for thinking through the nuances.

[@aichabargo3949]

i am having trouble downloading the spreadsheet to follow along ... Any help?

[@nairahyusuf2501]

Can pivot work on every test???

[@sinansakic2370]

when i put an item into a row for the second question it doesnt count how many there are there?

[@o.christ_5th496]

Question 1 : you can just select all the column by clinking in the letter above and then just take the count minus 1 for the header ;)

[@larrytony9782]

can you share the dataset? and i dont know if its the video resolution thats affecting the visibility of the video

[@workoutjakubowski1954]

On the question for age ... there must be a function that simply truncates the ages to zero decimal points ... so 18.3 = 18 ...28.67 = 28 Once you do that then your pivot table gives you the answer quicky

[@AnnKEmery]

Yes! I recently learned about =rounddown.

[@bemnetwoldu7105]

You went the hard way in age calculation but it was simply to take integer after date subtraction. Intv=int(Decimal Age). After that count if intv is 18.

[@suswann3605]

the data is only showing as an image? anywhere to find the actual file?

[@xinyuhe861]

Maybe for the age one, you can use floor formula?

[@dhirajpatel5170]

For that 18 problem we can use if+and I feel to match age>=18 and age

[@chengyiwang853]

Hello, Ann, I used excel long time, but now I have one problem, I can't resolve, I need your help: I used excel 2016 version, before it is normal, but since one week, for ex: when I put "500" in the case, it shown just "5" only, if I put "5000" in the case, it shown "50", I don't know how to resolve this problem? would you please give me an indication? thank you very much.

[@tutsecret499]

You are too cute, vibrant and sassy. All the blessings for you.

[@theterminaldave]

I do wish you had typed in a way so that we could see what the formulas consisted of, and the way you added data to them.

[@octavioenriqueromerocorona1997]

i cant download the spreadsheet excel, can you fix the link please, there is only a picture there

[@emmaliu1597]

For counting age of 18, we can use the pivot table and group function!

[@mikewebb8196]

=ROUNDDOWN(DAYS(TODAY(),F2)/365,0) works for the ages then you could do =COUNTIF(G2:G50,18) to find how many are 18.(G50 is just an example)

[@tutsecret499]

I wonder if used the spelling F7 from excel to find the misspelled.

[@dhy566bdh47g]

I understand how you got 33 for the final question, but remember that 1 "Parkside" was spelled incorrectly, so the desired answer would really be 34 as the question doesn't preclude misspellings.

[@MrKreKay]

I think the misspelled cell may have suppose to been "Paradise" not "Parkside".

[@StopWhining491]

A good reminder for me was that if specific data doesn't exist in the original data that I can add columns to create the data to use in a pivot table. Don't know enough about Power Query yet to see if there's a solution there.

[@Suspectshot_21]

Can you show us the right path to learn excel. I'm so confused with all these videos available in the internet

[@emperoy8]

I can't download the spreadsheet

[@benndenlabre]

To calculate the age you could: Assuming the birthdate is in cell A1 formatted correctly as date, in cell B1 you could use =INT((TODAY()-A1)/365) and you get the current age of the person. The INT formula will round DOWN to the nearest integer - meaning that a person who is 30.93 years old would still be 30 years old

[@patb3750]

TOTAL Hilarie Burton vibes. Love her energy. Would want a coworker like you any day 😔

[@hankook82]

Isn’t there spelling chech in Excel? Thanks for this vedio!

[@raza88193]

Unable to download spreadsheet

[@drew3030]

With a pivot table on the 18 year old question you use the min calculation.

[@jenihimmer8384]

I really wanted to see the formulas....but your screen shots didn't always show..but thank you!

[@AnnKEmery]

You can download the Excel file I used in this video. :)

[@tomd5678]

I learnt Excel when I had to write a system with a report showing the loss of Alcohol in a Brewery. Many tanks and the report needed to show the concentration of alcohol (or the potential of the sugars to turn into alcohol). Used Excel to workout what the right answers would be, as a crosscheck. I once signed up for an 80 mile cycle. I decided that I would be able to walk for 8 hours easily and cycling at 10mph is almost the same as walking. As long as I ensured that I didn't finish in less that 8 hours then I'd be able to complete the journey. I used Excel to create way points so that I'd get an idea of where I should be on the course and would not go faster than 10mph. I completed the 80 miles

[@p_roduct9211]

Excellent! I love planning my long trips in Excel as well. Scenic rest points and alternate routes in case bathrooms are needed or a cafe or museum, in the case of traveling. What role were you in for the Brewery reporting position? You noted you wrote a system, so it sounded like a coding-focused role. I'm finishing my supply chain post-grad program, but I think aside from business and operational concepts... analytics and communication will be the most needed skills to succeed day to day. Understanding data,and auditing it as well as knowing how to interpret the results. Transferable skills across industries.

[@drew3030]

Floor and ceiling function would help with the ages. This is where a background in discrete math is useful.

[@tutsecret499]

discret math? LOL. Anyway what floor and ceiling does?

[@PetzFirearms]

Question 1 - the best way is to select Column A header.. that auto selects the entire row and then look at the count in the lower right hand of the screen. A single mouse click = answer. No formulas, no highlighting, etc.

[@tutsecret499]

You have to remember that sometimes the computer where you will do the test, might set up the bottom visible formulas results into invisible

[@o.christ_5th496]

Question 4 : if there’s only one typo that means it’s should be the only single value, so you just have to look for the duplicate values and color them in red for instance, the non colored value will be the typo ;)

[@Luv-dq5th]

could you not also use the spelling feature?