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.
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 :)
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.
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. 😨
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.
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
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.
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!
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.
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
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.
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
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.
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
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.
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 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.
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.
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))
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.
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.
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.
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
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
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.
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.
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.
I appreciate the effort but as a beginner, I just got too frustrated especially halfway through the end. Probably would help a lot if it's just straight to the point and stated the best way and not the bad way as we (speaking for myself) are here trying to learn the right way as Excel seems intimidating enough. Thanks.
You might like finding an "Excel for beginners" tutorial on RUclips first, and then come back and look for "Excel tests" to assess your progress. Best of luck on your learning journey!! :)
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.
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 :)
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.
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 ;)
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.
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
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.
So basically I need to master pivot tables. Gotcha because I am a formula girl and I would have never used a Pivot Table. Everytime I used them it never turned out right.
The problem is we know the tools, but not using too much with projects, then we get rusty, and they this surprised testing catching us off guard without warming up.
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.
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!
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).
For the ages: did you try the "Floor" function? Or YEAR(TODAY()-Date)-1900 and transform the field to number (because YEAR gives an answer between 1900-1999 for some reason, unless it's an option I forgot to check) And your if formula if you don't round the ages: =IF(AND(years>=18,years
This doesn't guarantee accuracy at all. For example, in a customer survey people may put their City as "SLC" and "Salt Lake City". Without any context, the computer wouldn't know that this represents Salt Lake City and is a ln acronym, so word check does not help to verify. Using a pivot table would be the easiest method
Thanks for posting, but as someone who's used Excel on the job only a few times, I wish you would've explained things like what a pivot table is or how to access Count A.
I’m sorry Vic but if your excel knowledge isn’t the best I recommend to learn the fundamentals step by step before watching some kind of interview questions as these are too complicated for beginners. This video was perfect for intermediate level users as it showcased the real thinking process in a situation like a test. Don’t let yourself get intimated by things you didn’t learn yet! Keep improving
For question 7, an Xlookup might've been ideal. You could type 8 in a cell, then, in the cell next to it, perform an xlookup that searches for 8 in the "# of children" column but grabs its corresponding data from "Race/Ethnicity" column.
Basically the pivot table is the ultimate cheat code for people without knowledge of excel formulas or a programming language mindset. The first thing I did was remembering all the different formulas and connect them in my head only to find out that you just had to create a pivot table for all these problems. Now I’m wondering why I learned formulas to begin with? Kind of unnecessary and a waste of time
I like this video for 1 reason, why waste time with formulas when you can just Pivot Table everything. From a database standpoint, I used to think why waste time on Excel when I an just write an SQL
For the ethnicity question (using a formula), I’d copy and paste the number of children column to the left of the Race/Ethnicity column then use a simple vlookup.
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.
![Which Graphs Can I Make in Excel?? [TONS OF DATAVIZ OPTIONS]](http://i.ytimg.com/vi/yJubOfeIdqM/mqdefault.jpg)
![I.N "HALLUCINATION" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/n5B5q1Hwt_U/mqdefault.jpg)
Who else is watching this thinking... just when I thought I knew excel. Lol
Omg SMH
Lol same
I’m crying 😂 I have an assessment tomorrow for a job
🤣😂
@@atlienacct5868 Goodluck mate. How was it?
This video helped me ace an excel test and get a new job! Thanks so much for making it!
I've got one tomorrow, wish me luck!
@@toivokarttunen8187 update
@@toivokarttunen8187 update?
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.
So basically Pivot Table is the answer to all of life's questions 😂
Lol 😂 yup! It’s good to know
When I took Excel they never taught us pivot tables, glad to see its a short cut.
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 :)
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.
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. 😨
@@zlatte2594 This test is intermediate to advanced level. You won't get this kind of test. You will get a basic Excel test.
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.
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
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.
What about using rounddown() formula instead of round() at question no 8 and 9? This way the ages will be always rounded correctly
Didn't even know that existed! Thanks for teaching me about rounddown. :)
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!
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.
The INT (integer) and TRUNC functions will round the ages to the correct numbers you desire. Great video!
You might not be a master at excel, but you definitely the best Teacher 👍🙏
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))
Use formulas and pivot to survive or forget excel😂
Such a good way..Thanks for helping 😊
Super, I hope I will do well on my upcoming Excel test. Keep fingers crossed !:)
You can use "Group" and "insert slicer" for the pivot table to answer some of the questions faster.
Thanks for the video. Commentary really helped. Never realised how much you can do with pivot tables lol
Thank you for posting this...its a huge help for recent graduates
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
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!
I'll have my exam next Monday, thank you for sharing, it was very useful!
For question 7, use conditional formatting and under the "includes text" you could put "8"
Ma I love ur video,ur sense of humour is 😂 and fantastic, I was laughing d whole time especially the age part❤
Thank you, I will definetley walk away with some new ways to do things. And you keep me entertained!
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.
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.
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
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.
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
Yes! I recently learned about =rounddown.
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.
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.
Pivot tables help a lot, but sometimes formulas give errors. Thanks for showing me another perspective
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.
could you do this with Vlookup?
@@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.
@@excelgun8915 But Xlookup would.
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.
@@jakerockznoodles exactly, noting wrong with using filter, so much faster
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))
could you do this with Vlookup?
@@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
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.
I think the misspelled cell may have suppose to been "Paradise" not "Parkside".
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.
Yes, agreed! Thanks for thinking through the nuances.
the summary - Pivot Table is the best way to answer every question)))
Thank you for uploading this. It really helped.
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.
Watching this ahead of a work sample I will have to do for an analyst interview tomorrow... super helpful!!!
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
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
Easy , I wish I could get a SQL Query test like this.
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
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.
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.
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.
For the age group , you can group them in the pivot table. effectively resulting in the total quantity
I appreciate the effort but as a beginner, I just got too frustrated especially halfway through the end. Probably would help a lot if it's just straight to the point and stated the best way and not the bad way as we (speaking for myself) are here trying to learn the right way as Excel seems intimidating enough. Thanks.
You might like finding an "Excel for beginners" tutorial on RUclips first, and then come back and look for "Excel tests" to assess your progress. Best of luck on your learning journey!! :)
This was really helpful. Thank you so much!
Thanks didn’t tot of using pivot as fastest solution. Gotta test now wish me luck and will update you. Thanks
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.
Hope this helps! This was for a position for a Bachelor's degree + 2 years of experience. Not entry level, but fairly beginner.
In question 8, I think you can do a countif(and()) formula, 1st criteria more than 18, 2nd less than 19.
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.
Howd it go? I am taking one Wednesday
@@A3L2A3N How did yours go?
If were to do it, ill format it to table, then I can add slicers. It works like a charm.
I think a LookUp is your best bet for question 7
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.
You have to remember that sometimes the computer where you will do the test, might set up the bottom visible formulas results into invisible
In question 8, countifs() could do with greater than 18 and less than 19 age.
I wouldn't pass this ever on the first try.
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 ;)
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 :)
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.
Floor and ceiling function would help with the ages. This is where a background in discrete math is useful.
discret math? LOL. Anyway what floor and ceiling does?
"If you can't explain it to a six year old, you don't understand it yourself"
Rvery where I turn the convos about data and analytics is convoluted.
This is really helpful, thanks for posting this.
Great content, loved every minute of it.
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 ;)
could you not also use the spelling feature?
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.
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
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.
So basically I need to master pivot tables. Gotcha because I am a formula girl and I would have never used a Pivot Table.
Everytime I used them it never turned out right.
It's so complicated
The problem is we know the tools, but not using too much with projects, then we get rusty, and they this surprised testing catching us off guard without warming up.
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.
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.
You are too cute, vibrant and sassy. All the blessings for you.
So, just excel the pivot table to excel at Excel😂
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!
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).
Can you show us the right path to learn excel. I'm so confused with all these videos available in the internet
TOTAL Hilarie Burton vibes. Love her energy. Would want a coworker like you any day 😔
Very informative and style of teaching is so cool. Impressed
Super helpful to prepare for my assessment test! Thanks :)
With a pivot table on the 18 year old question you use the min calculation.
For the ages: did you try the "Floor" function?
Or YEAR(TODAY()-Date)-1900 and transform the field to number (because YEAR gives an answer between 1900-1999 for some reason, unless it's an option I forgot to check)
And your if formula if you don't round the ages: =IF(AND(years>=18,years
Would you give the full formula? I don't understand what you mean.
Pretty Cool.
I think the best way to check the value that is spelled differently is by using word check.
This doesn't guarantee accuracy at all. For example, in a customer survey people may put their City as "SLC" and "Salt Lake City". Without any context, the computer wouldn't know that this represents Salt Lake City and is a ln acronym, so word check does not help to verify.
Using a pivot table would be the easiest method
Indeed there's a formula for Q7. "There is one participant that has 8 children. What is their race/ethnicity? "
=INDEX(E2:E115,MATCH(8,G2:G115,0))
could you do this with Vlookup?
Thanks for posting, but as someone who's used Excel on the job only a few times, I wish you would've explained things like what a pivot table is or how to access Count A.
I've got lots of other videos on those concepts. Hope you find them useful!
I’m sorry Vic but if your excel knowledge isn’t the best I recommend to learn the fundamentals step by step before watching some kind of interview questions as these are too complicated for beginners. This video was perfect for intermediate level users as it showcased the real thinking process in a situation like a test. Don’t let yourself get intimated by things you didn’t learn yet! Keep improving
can you share the dataset? and i dont know if its the video resolution thats affecting the visibility of the video
Pivot tables are by far the excel feature responsible for the most excel errors.
Whaaat are you being sarcastic? It's so easy to make mistakes with formulas. Pivot tables are just drag-and-drop.
I wonder if used the spelling F7 from excel to find the misspelled.
you could possibly use an xlookup for #7
I really wanted to see the formulas....but your screen shots didn't always show..but thank you!
You can download the Excel file I used in this video. :)
moral of the story use pivot tables or formulas
lol YESSSSSSSSSSSSSSSSSSSSS
@@AnnKEmerymoral of the story check your work.
Best way to find out an age is the yearfrac formula!
Thank you for the video. I watch your video to prepare for Excel & SQL test for analyst position
How did it go?
For question 7, an Xlookup might've been ideal. You could type 8 in a cell, then, in the cell next to it, perform an xlookup that searches for 8 in the "# of children" column but grabs its corresponding data from "Race/Ethnicity" column.
Yeah, but when you are doing test, they might not have installed the 365 version, then has to use the old function
@@tutsecret499 Recorded this in February 2020... xlookup was added the next month, in March 2020. It's still new-ish.
=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)
Basically the pivot table is the ultimate cheat code for people without knowledge of excel formulas or a programming language mindset. The first thing I did was remembering all the different formulas and connect them in my head only to find out that you just had to create a pivot table for all these problems. Now I’m wondering why I learned formulas to begin with? Kind of unnecessary and a waste of time
Might depend on when you learned Excel. Pivot tables weren't added until Excel 2013. I started using Excel in 2008. So I had to learn formulas, darn!
the data is only showing as an image? anywhere to find the actual file?
Thanks Ann, it's so helpful.
I like this video for 1 reason, why waste time with formulas when you can just Pivot Table everything. From a database standpoint, I used to think why waste time on Excel when I an just write an SQL
For the ethnicity question (using a formula), I’d copy and paste the number of children column to the left of the Race/Ethnicity column then use a simple vlookup.
Could also use Index and Match but those are more complex.
for question number 8 , you use Datedif function to determine the ages .
- Datedif(date birth, today's date, " y" )
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.
I didn't learn pivot tables in college or grad school, so I simply hadn't encountered them yet 10 years ago.