[CAPTIONS ARE AUTO-GENERATED] 10 years ago, I was interviewing
for an analyst position and I remember sitting in
the interviewer's office, the director's office. And he was asking me about all
of my Excel skills, because it was a really fast paced office. So the expectation level was
pretty high that you could work. Totally independently,
no handholding. And as I'm going on and on and on
about all these projects and all my great skills, because that's what
you would do in interviews, right. You, it's not the
time to be humble. It's the time to really
advocate for yourself. I'm like going on and on. And then the director is
like, okay, Prove it you've got great Excel skills. Come on, come with me. And he led me to the other
office across the hall and what was waiting for me and
nice glowing screen with. And Excel test and my
heart just about stopped. And I was replaying like, oh my
gosh, what did I say in the past? Like our, in the interview? What? Oh, no. It's like, what did I say? I'd do have to do again. I hope I can actually
do this things. And I can't panicked
a little bit now. Spoiler alert. I got the job and I'm still very
good friends with that director. And since then, we've laughed
about this, this idea of this Excel test and Isaac, and I thought
it would make a really fun blog post and a really fun tutorial. So I'm going to try to see if
10 years later I can still pass. This could be. Um, really exciting. And I could be like, oh
my gosh, I still got it. Or this could be really humbling. And I could realize that I've still
got room to grow, which, you know, you know, is true for all of us. So my goal with this recording
is to record it in one, take all the way through and no
matter how much I get stuck. Or not just keep going and try to
show you what somebody like, even somebody who does this for a living
and teaches on this for a living. I want to show you that even
we get stuck sometimes and like where I might know the answer
right away and how I might solve it or where I might not
know the answer right away. I wish I didn't record this
whole thing, uh, at all. Okay. Are you ready? You ready to see this Excel test? Let's do it. All right. So on my screen, I have got
the Excel test, the famous Excel test from 10 years ago. So first things first, I am
going to resave this because just because it's a test, doesn't mean
it's time to lose your manners. Let's save this as
a new file names. I'm not accidentally
saving over stuff. And where should we put this? Let's put it somewhere. I'm sure to find it later. Like, uh, my one drive folder
with a million other files in it. Okay. Let's stick it in there
and we will call it. Uh, we'll just call it a
K E what's downloading. I don't know. I already have this downloaded. It's open, dumb computer. Okay. Let's save as. Here's the data it's demographic
data, fake data, but a similar or structure to this NGO and
what type of data we had. Here's the data. And here is the test. The questions. Now I remember I asked Isaac
the director of the interview, uh, interviewer, how long did
this take the other candidates? And he was like, everybody
finished in 30 minutes or less. And I was like, oh my
gosh, I'm going to be sitting here for awhile. So we'll see how long this
recording lasts, because I'm going to be talking
to you as I solve things. So it might take 30 minutes. Might take 10 minutes. I don't know. We'll see. You should challenge yourself
and give it a, try to, you know, use your, your phone timer
and see how long it takes you. This shouldn't take all day. That's what I'm
trying to get across. It should be like pretty quick,
even with me talking to you. Okay. So let's see what
these questions are. I'll try to enlarge this and
wrap this so you can actually see what it is I'm doing. So first things, first
question number one, how many total participants
are included in this data? Set? There's a couple of ways you can
do this the bad way, the better way and the ultimate best way. Let's see if I can figure out
the best way, the most efficient correct way where you're not
really likely to make typos. So, oh, first things
first, my manners. I'm going to rename this as raw. I don't want to touch this
one and let's move it. Two, I'll just put
it before this one. Let's copy it. Call it clean because I'm
probably going to have to do some calculations and recoding in here. Is this frozen yet? No, let's freeze the top
rows view, freeze, panes top. And let's add some filters
in case we need to sort or filter and bold the top row. Make it actually visible. What are we dealing with? Unique IDs address, zip
neighborhood, race, ethnicity, date of birth, number of children. Okay. Okay. I can do that. Right. I hope so. I hope I can do that. Let's find out. Okay. What are we looking for? Number of children? So the bad way is you would count
like this one, two, three, four. I know you're not doing that, but
as I go around the country slash world training on this, I see a
lot of people kind of pointing. But their fingers or try
not to point and just like looking at it with their eyes. So that's the bad way. We're not doing that in
big data sets and it's impossible to do it that way. The better way. This is probably how I did it. 10 years ago, I probably
scrolled to the bottom. Oh wait, wait, let me
show you another bad way. This is probably the default way. I see a lot of people doing. If you haven't been trained on
this, you wouldn't know, like, this is what most people lean towards. They highlight this whole area
and they either look at the bottom and they're like, count one 14. Cool. Or they say, well,
this started as one. So I'll scroll down
and this is one 15. So. One 15 minus one, 114,
or somewhere around here. If you kind of like hover your
mouse in the right way, it'll tell you 114 Roosevelt selected. Yeah. Not the best way. Okay. That's bad. What I probably did was a
count a back in the day. This was my, like before I used
pivot tables to, to regularly phase of life, I probably did
account a and got one 14 and I probably would have checked for. Duplicates. I hope I would've. That's another thing you gotta do. Let's color code. Any duplicates in red? Do I see any let's sort? I can't start by color. There's no red. Any duplicates? No. Okay, great. Okay. So one 14 the best way would
be a pivot table. So let's insert a new
pivot table, insert pivot. Do I have contiguous
rows and columns. Everything's touching. Yep. Let's rename this no time to
get lazy after all it is an interview, you got to show your
best side and all of your data management skills and stay tidy. So the best way would be you
take ID, put it and values. It shows you a sum. We don't want to
sum what do we want? We want a count and you get one 14. Okay. Bad counting by hand,
better a formula, just a pivot table, because you
can make typos on formulas. You can probably still do some
type of typo in a pivot table, but it's a little bit harder. There's more friction. Okay. Question number two. This rate, I'll be doing
this for 5 million hours. How many participants do not live
in the two zero zero one nine. Zip code. Okay. What would the bad way to do
this B the bad way would be, well, you, you eyeball it and
you're like, well, this is not two zero, zero one nine. And maybe there's some more when
they're mostly one nines, you know, and you'd say, oh, here's
some, another bad way would be you sort another bad way would
be that you filter and you say, well, there's four people. One of whom is blank. Those are bad because as
you have a huge dataset, It takes too long to do that. Or you make a typo or it's
not replicable, there's no work to show to show your
colleagues on the projects, like how you found your answer. There's no work to show your future
self, how your past self did it. You can't like redo the same
analysis every month quickly. It's going to take the same
amount of time every month. And in this particular agency,
we had so many reports. Every single month we had like. Dozens of reports to foundations
and government donors every month. So doing things by hand is like,
you'd be doing this all day. You'd be working on the weekends. Okay. That's bad by hand better. I wonder what I would
have done 10 years ago. I probably would of done a formula. I probably would have done. Let me zoom in so you can
see my awful formulas. I'm about to do. I probably would have
done something like this. Like I count a. And I'd say, okay, here's
like everybody, right? 113, actually everybody is 114. So maybe I would have
done something like count, uh, if, and count all the
two zero, zero one nines. What I've done this, I probably
would've done something like this the long way. And then I would've said,
well, one 14 minus this. And there's four. Yeah, the better way pivot table. Look how fast this is. We'll just take zip code here
and we've got one, two, three, four, or you could filter it if it
was like more complex than that. And you could say, okay, these
are all the people who are not into zero, zero one, nine. There's four of them, just
a simple drag and drop that. One's pretty quick. Number three, how many
participants live in the Lotus square neighborhood? The bad solution you eyeball at
one, two, or you Stuart, you might sort a to Z find all the Lotus
squares someplace down here, and you say there's 15 of them bad. You filter and you say, Well,
there's, there's this many people there's 14 or 15, see who even
knows that's not going to work. Okay. We're not going to sort
and filter an eyeball. You're just going
to make a mistake. What I probably did 10
years ago, I loved formulas. I thought they were like awesome. I mean, they are awesome. Okay. I probably would have done
this really, really quickly Lotus square and we get 15. But the best way. Guess what? It's a pivot table. It's so quick. Oh, wait, am I still filtered? Hold on. Let me unfilter this before I move
on, I don't want to accidentally leave out some of these people. Okay. Filter is off. What am I looking at neighborhood? Whereas, low to square. 15. Wait a second is a 14 or is it 15? Mm. Wait that said 15 and that said
15 didn't they get a 14 earlier? What did I do to get a 14? I feel like I did a sort
didn't I get a 14 earlier or did I mess that up? Just want to check? No that's as 15,
maybe I imagined a 14. Okay. I've checked three ways. The answer is 15 Lotus squares. Question number four, there
is a typo in one of the neighborhood data fields. What is the typo? Hmm. I guess bad would be you eyeball
this, you just read down the list and you kind of say where's
the type of, where's the type of, where's the type of, where's
the typo and you, you hopefully find something and that's like
impossible in a big dataset. Oh, wait, is it that. Pairs dice, but if you have a real
data set, like tens of thousands of entries, you're never going
to find it better would be. How would you do
this with a formula? I don't know. I don't know if there
is a formula solution. Let's just do a pivot table. I think I would just do a pivot
table, just like this neighborhood and ID number and probably
just eyeball the pivot table. So at least then you're not
looking at all the entries. You're looking at the
summarized entries. I don't know. This seems pretty easy. There's the typo. If you have a better
way, let me know how many participants have blink. Missing race, ethnicity data. Okay. Bad is eyeballing sorting,
filtering any type of counting by hand 10 years ago, I would have
done a count blank, probably. I think I would have done account
blank on this whole column. And how many empties do we have? Four. And I probably would've sorted
to verify that just to make sure. Cause sometimes empties aren't
really empties one, two, three, four, sometimes empties. When you download your data
from some type of database or a website, sometimes they
look empty, but they're not, there's a space in there. Look, I'm going to
touch the space key. Okay. And see how it looks blank and
it like is there's no actual race, ethnicity data in there,
but then your formula gets off. So, you know, I do usually
like to check at least a couple of these just to
make sure my numbers add up. Okay. So bad doing something by
hand, eyeballing it better account blank, formula. Best the good old pivot table to
the rescue unchecked neighborhood. Check race, ethnicity, blank four. You know what maybe the
best is you do both. You solve it at least two
ways to check your work. You kind of do a mix of eyeballing
and formulas and pivot tables. That's probably the gold standard. Hmm. I'll have to think about that
when I'm not so trying to record this and one, one sitting. Okay. How many African-Americans
live in Kenilworth courts? Bad sorting filtering
eyeballing best. No, no, no, no better. You know what? I probably would have done. I would've wanted to show off my
count ifs skills in the interview. So I would have done
something like this. I would see out of all the
race, ethnicity data, how many say African-American. And out of all the neighborhoods,
how many also say Kenilworth courts, courts, plural, I guess. So I think it was kind of worth
courts and then you get 23 and that's like reasonably quick to do,
if you know how to do a Countess. But a pivot table is
going to be better. Okay. I've already got race, ethnicity. Let's put neighborhood in there. Oops. That's too wide. Let's put neighborhood over here. Maybe. What neighborhood
are we looking for? Kenilworth courts. African-American 23. Is that the answer? I just got 23. Okay. At least we're going to have
right answers on this test. There is one participant
that has eight children. What is their race? Ethnicity? Hmm. How do you solve that one? There's somebody with eight kids. What is their race, ethnicity? You want to know what
my default instinct is? Oh, I see the eight right year. Probably sorted it. I hope I didn't do
this on the test sorta. And then just say
there are other, Hmm. What would be the
better way to do this? Could you do this with a formula? Like if the number of children
variable a equals eight, then show me their race, ethnicity,
then count if they have this, this formula it's going to work. I don't think so. Where's the bottom of this column. Oops, too far. I don't think this works. No, because I'm not
counting by anything. Am I counting the others? No. I only know it's the other,
because I just eyeballed it. I don't know if there's
a formula for that. I know there's a
pivot table for that. The pivot table would be,
you could filter by number of children and say, just show me
the person at the eight kids and what are we looking at? Race, ethnicity of them. And they are other, I don't know
if there's a formula, probably. There's probably a
formula for that. How many participants
are 18 years old? Do we have ages? No. Ooh, it's getting a little juicy. We have to calculate this. Okay. Let's insert a new column. And let's find their age and
we will find it as of today. I'm recording this one 31,
20, 20 Friday afternoon and let's do, Hmm let's do you
today gives us today's date. I'm going to try to walk you
through this because this is something that, um, It was probably
gonna be a little bit tricky. Okay. So today is Excel
stores dates funny. Okay. This is like Excel language. We are 43,861 days past January
1st, 1900, which is how Excel codes days, which means that I can
do like today minus a birth date. And that person is 9,564 days
old divided by let's figure out years divided by 365 days. Technically there are leap years. So technically there are
365.2, five days in a year. Is this person 26 as of today? Gosh, that's crazy. Those kids are old. They're grownups. Is this right? Let me eyeball some somebody
born in 79, almost 40. Would they turn 40 soon? Let me check. Oh my gosh. Am I turning 35 this year. Oh my gosh. Okay. Age as of today. So now we need to find what
are we trying to find 18, 18 or older, or exactly 18. I think we want to
find 18 or older. My instinct on this one
is to do it the lazy way. Is this bad? Doesn't anybody else having this
instinct or I would just say how many 18 year olds are there. There's four of them
and just call it a day. But I remember we're
taking this Excel test. I remember thinking what if this is
like a high school math class or a college math class where the answer
matters, but your process also matters and it's like you get bonus
points for actually doing it the right way and showing your work. Probably I remember, um,
Like really trying to show my work on this interview test. Okay. So this is like, there's
no work to show that's that's a bad, sloppy way. At least I have a
cool formula in here. Okay. How would I find out
if they are 18 though? Could I do a, maybe
I'll do a new column. So like, if this is
18, then give me a yes. Otherwise give me a no. But I've got a decimal
places in here. Is it going to capture
these decimals? Nope. So let's go back
in and around this. Ooh, this is going
to be a cute one. Look at this round, this
whole thing to zero decimal places rounded to the nearest
whole number that round the eighteen-year-olds up or down. Hmm. Well, they still count as 18 or
will some of them be counted as 19? Still get four. No, no, no. There's fat. No. Now there's six. Hmm. Let's compare today. Minus F two. Let's take off the rounding. Let's do a quick comparison
ages are so tricky. This is something that I
used to have to calculate all the time and I don't know. Today's okay. So the decimal place answer has
these people and the rounded. Yeah, it is round. Okay. It's rounding. Not the, not how I want
to like these people. Do you see what's happening? What's happening. That's not what I want. How would I do this as a formula? I have to do some type of like, if this answer is
like less than 19. Yeah. Then. Then also, if it's some type
of nested, if I can't do that on a Friday, you know what
the hell or answer is anyway, the better answer is a pivot
table, because then you don't have to deal with the formulas. You see a time-consuming
that is okay. I've added some new columns
to my pivot table, which means I have to refresh to oops. I left a blank column,
not a good thing to do. Okay. Got to have contiguous data. Let's refresh so that the columns
I just made show up in my bank right here that is not rounded. Got to keep everything
labeled nicely. It's so critical. You can't get sloppy. Otherwise your future self
has no idea what you did. Wish I had this test
from 10 years ago. That would have been so cool. I doubt it was saved or
it's on like some computer that's doesn't exist anymore. Okay. Let's see. Let's unfilter this
for good measure. Just select everything again. And what are we looking at? Let's look at age as
of today and maybe. Filter, can I filter this now? Hmm. There's some faster way to
do this with pivot tables. There must be. It's not coming to me now. I know. As soon as I stop recording, I'm
going to be thinking about this stupid question all afternoon
and evening until I solve it. And I'll probably figure it out. The second I stop record. So if I do, I'll do like. Uh, part two, if for when I figure
it out, I guess now I have to figure it out since I said that. Okay. Oh, what's the answer again? Is it four or six people? I feel like this
Excel Tesco harder. Okay. It's four people. Okay. Four people as of today, when
I'm doing it, January 31st, how many participants that are
between the ages of 14 and 19 have four or more children? What? That is a long sentence there. Okay. Between 14 and 19. Including 14. Yeah, it must include
14 year olds, 19 years. Okay. And have four or more children. Let's just skip right
to the pivot table. Let's keep it simple. Let's do. Let's see, let's just
filter by number of kids. My brain's getting tired. This is too much. What am I looking for? Four or more? I think four more children. And it's one, one person. Ages 14 and 19. Is that right? Yeah. Have four more children, right? Four, five, six, eight one person. How old is the participant
that lives in Parkside that has only one child. That's a long sentence, too. How old is the participant
that lives in Parkside and has only one child that skipped
right to the pivot table. Let's just filter. As long as we're in here,
let's just look at one child and it might as well just
filter by Parkside too. I'm doing what I
told you not to do. How great is that? Okay. We'll see what answers
you come up with. Parkside. One kid Parkside. They are 33. Point eight. They're not 34 yet. There are 33. Okay. That's that? I'm gonna go think about
these answers and try to come up with some like sexier
solutions, not just like some sorting and filtering and
pivot tables towards the end. Um, I remember in real life
I did this really quickly. It was less than five minutes. And I remember the director,
Isaac was like, I'm just going to go get some coffee, like. Call me in 30 minutes when you're
done, unless you finish early. And I remember finishing
in a few minutes and I was like, I think that's it. I just did some counter gifts. Maybe I had some summits
in there someplace. I, this is like very, very close
to the original test though, that I took and I went to get them
and I was like, I think I'm done. And he was like, okay. And then I got the job,
which is pretty cool. I know that the test was a
big part of the interview process to make sure I could
like stand on my own two feet. Um, So even if I didn't do it
like perfectly, I did a pretty good, I wasn't counting by hand. So this is your chance comments. Let me know. What solutions did you use? How did you approach this? How long did it take you? I can see my timer's on like
a million hours to do this. However, I was talking
to you as I did it. So I like to think that in
real life, if I was just doing it really quickly, if it was
like a testing scenario, I could do it faster than this. I don't know. I don't know. We'll see. Maybe I'll try to get on Monday
morning or Monday afternoon or something with like a fresh brain. Okay. I look forward to hearing from you. Bye.
