Who else is watching this thinking... just when I thought I knew excel. Lol
@passapassa3567
3 жыл бұрын
Omg SMH
@GrobariNBGD1970
2 жыл бұрын
Lol same
@atlienacct5868
2 жыл бұрын
I’m crying 😂 I have an assessment tomorrow for a job
@Bashir_Kano
Жыл бұрын
🤣😂
@Bashir_Kano
Жыл бұрын
@@atlienacct5868 Goodluck mate. How was it?
@amow7215
3 жыл бұрын
This video helped me ace an excel test and get a new job! Thanks so much for making it!
@toivokarttunen8187
3 жыл бұрын
I've got one tomorrow, wish me luck!
@JE-im4im
2 жыл бұрын
@@toivokarttunen8187 update
@Luv-dq5th
2 жыл бұрын
@@toivokarttunen8187 update?
@jaberabedin5487
2 жыл бұрын
So basically Pivot Table is the answer to all of life's questions 😂
@Nova_Nora5
Жыл бұрын
Lol 😂 yup! It’s good to know
@julienielsen4462
4 ай бұрын
When I took Excel they never taught us pivot tables, glad to see its a short cut.
@Nick-xu2hh
4 жыл бұрын
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
4 жыл бұрын
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
2 жыл бұрын
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
2 жыл бұрын
@@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.
@notesfromleisa-land
Жыл бұрын
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.
@iamsunnysideup7115
2 жыл бұрын
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
@pokernadlac
4 жыл бұрын
What about using rounddown() formula instead of round() at question no 8 and 9? This way the ages will be always rounded correctly
@AnnKEmery
3 жыл бұрын
Didn't even know that existed! Thanks for teaching me about rounddown. :)
@manojkarra3306
3 жыл бұрын
Use formulas and pivot to survive or forget excel😂 Such a good way..Thanks for helping 😊
@danieseadams1642
3 жыл бұрын
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.
@BrittneyJon
4 жыл бұрын
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!
@keithjones8139
3 жыл бұрын
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.
@tonyn8644
8 ай бұрын
You might not be a master at excel, but you definitely the best Teacher 👍🙏
@GeoffLilley
2 жыл бұрын
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))
@alex-sd
Жыл бұрын
The INT (integer) and TRUNC functions will round the ages to the correct numbers you desire. Great video!
@crisredic321
3 жыл бұрын
Thank you for posting this...its a huge help for recent graduates
@Obyvvatel
2 жыл бұрын
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
2 жыл бұрын
Yes, agreed! Thanks for thinking through the nuances.
@Squash81
4 жыл бұрын
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
3 жыл бұрын
could you do this with Vlookup?
@excelgun8915
3 жыл бұрын
@@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
2 жыл бұрын
@@excelgun8915 But Xlookup would.
@jakerockznoodles
2 жыл бұрын
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.
@m1chal20
25 күн бұрын
@@jakerockznoodles exactly, noting wrong with using filter, so much faster
@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.
@tingkagol
2 жыл бұрын
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
@dhy566bdh47g
3 жыл бұрын
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
3 жыл бұрын
I think the misspelled cell may have suppose to been "Paradise" not "Parkside".
@kvin9210
2 жыл бұрын
I wouldn't pass this ever on the first try.
@casmartin790
Жыл бұрын
I think a LookUp is your best bet for question 7
@briarrose5428
3 жыл бұрын
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
@saphire1975
7 ай бұрын
I'll have my exam next Monday, thank you for sharing, it was very useful!
@lovepiano4616
3 жыл бұрын
You can use "Group" and "insert slicer" for the pivot table to answer some of the questions faster.
@AB-nv8tj
4 жыл бұрын
Thank you for uploading this. It really helped.
@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.
@elieatpeace9379
3 жыл бұрын
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.
@juhimalhotra7786
3 жыл бұрын
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
@aholmes1397
3 жыл бұрын
Easy , I wish I could get a SQL Query test like this.
@rahulpkpres
3 жыл бұрын
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
3 жыл бұрын
could you do this with Vlookup?
@keifer7813
2 жыл бұрын
@@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
@user-ct5bj5ws1c
2 жыл бұрын
the summary - Pivot Table is the best way to answer every question)))
@danielcervantes3779
2 жыл бұрын
For question 7, use conditional formatting and under the "includes text" you could put "8"
@weswheeler3331
2 жыл бұрын
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.
@luissbeast9662
2 жыл бұрын
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!
@cococnk388
11 ай бұрын
Pivot tables help a lot, but sometimes formulas give errors. Thanks for showing me another perspective
@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
@LauraD228
2 жыл бұрын
Super, I hope I will do well on my upcoming Excel test. Keep fingers crossed !:)
@zipp2371
Жыл бұрын
Pivot tables are by far the excel feature responsible for the most excel errors.
@AnnKEmery
Жыл бұрын
Whaaat are you being sarcastic? It's so easy to make mistakes with formulas. Pivot tables are just drag-and-drop.
@oe10s024
3 жыл бұрын
In question 8, countifs() could do with greater than 18 and less than 19 age.
@o.christ_5th496
3 жыл бұрын
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
2 жыл бұрын
could you not also use the spelling feature?
@mzgemini0006
2 жыл бұрын
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.
@johnadair4979
3 жыл бұрын
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.
@analucero9905
3 жыл бұрын
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.
@montwinjack
3 жыл бұрын
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
3 жыл бұрын
Howd it go? I am taking one Wednesday
@mosesking2923
3 жыл бұрын
@@A3L2A3N How did yours go?
@Cam-sl8ve
3 жыл бұрын
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
2 жыл бұрын
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.
@patriciam.750
2 ай бұрын
This was really helpful. Thank you so much!
@halowaffles
2 жыл бұрын
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.
@keifer7813
2 жыл бұрын
Thanks for the video. Commentary really helped. Never realised how much you can do with pivot tables lol
@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
@tomd5678
4 жыл бұрын
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
3 жыл бұрын
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.
@slaytheon
3 жыл бұрын
Watching this ahead of a work sample I will have to do for an analyst interview tomorrow... super helpful!!!
@databaseforbeginners
Жыл бұрын
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
@TT-gz5wr
2 жыл бұрын
Thank you, I will definetley walk away with some new ways to do things. And you keep me entertained!
@annagrace1159
2 жыл бұрын
"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.
@octavioenriqueromerocorona1997
7 ай бұрын
i cant download the spreadsheet excel, can you fix the link please, there is only a picture there
@suswann3605
6 ай бұрын
the data is only showing as an image? anywhere to find the actual file?
@alastermyst
2 жыл бұрын
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.
@abdulrahmanmuslih141
Жыл бұрын
for question number 8 , you use Datedif function to determine the ages . - Datedif(date birth, today's date, " y" )
@Suspectshot_21
4 ай бұрын
Can you show us the right path to learn excel. I'm so confused with all these videos available in the internet
@drew3030
2 жыл бұрын
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?
@o.christ_5th496
3 жыл бұрын
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 ;)
@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.
@matattz
Жыл бұрын
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
@AnnKEmery
Жыл бұрын
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!
@patb3750
2 жыл бұрын
TOTAL Hilarie Burton vibes. Love her energy. Would want a coworker like you any day 😔
@theterminaldave
7 ай бұрын
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.
@mawas6801
4 жыл бұрын
moral of the story use pivot tables or formulas
@AnnKEmery
4 жыл бұрын
lol YESSSSSSSSSSSSSSSSSSSSS
@aprilm752
2 жыл бұрын
@@AnnKEmerymoral of the story check your work.
@lsjohnson20
3 жыл бұрын
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
3 жыл бұрын
Hope this helps! This was for a position for a Bachelor's degree + 2 years of experience. Not entry level, but fairly beginner.
@sherrip6445
2 жыл бұрын
Best way to find out an age is the yearfrac formula!
@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
@drew3030
2 жыл бұрын
With a pivot table on the 18 year old question you use the min calculation.
@tutsecret499
2 жыл бұрын
You are too cute, vibrant and sassy. All the blessings for you.
@larrytony9782
5 ай бұрын
can you share the dataset? and i dont know if its the video resolution thats affecting the visibility of the video
@marialuisalichia94
Жыл бұрын
For the age group , you can group them in the pivot table. effectively resulting in the total quantity
@sya8109
3 жыл бұрын
This is really helpful, thanks for posting this.
@danikrupl4090
Жыл бұрын
In question 8, I think you can do a countif(and()) formula, 1st criteria more than 18, 2nd less than 19.
@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.
@edravtv4367
Жыл бұрын
If were to do it, ill format it to table, then I can add slicers. It works like a charm.
@tutsecret499
2 жыл бұрын
I wonder if used the spelling F7 from excel to find the misspelled.
@zahrab.9446
4 жыл бұрын
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
@aishajoseph6184
4 жыл бұрын
Would you give the full formula? I don't understand what you mean.
@Bashir_Kano
Жыл бұрын
Pretty Cool.
@danutab4549
2 жыл бұрын
Pivots are cool but not always the best solution :)
@chengyiwang853
3 жыл бұрын
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.
@roderickmose4691
3 жыл бұрын
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))
@Kevn_96
3 жыл бұрын
could you do this with Vlookup?
@abhishekm3752
3 жыл бұрын
So, just excel the pivot table to excel at Excel😂
@mgt739
10 ай бұрын
Your answer to question 2 is incorrect. the one row with a blank zip code is still in fact part of 20019, just the field is missing in the data, but the zip can be inferred from the neighbourhood column (kenilworth courts is always 20019 in other rows). so the correct answer is 3, not 4. I'd have expected you to clean the data like this if i were the director hehe.
@trevorfultz
3 жыл бұрын
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.
@trevorfultz
3 жыл бұрын
Could also use Index and Match but those are more complex.
@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. :)
@AL-yt1kn
2 жыл бұрын
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?
@bemnetwoldu7105
2 жыл бұрын
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.
@tutsecret499
2 жыл бұрын
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.
@realiaticPerspectives
2 жыл бұрын
Great content, loved every minute of it.
@samadkhan-tw4gq
3 жыл бұрын
Thanks Ann, it's so helpful.
@aichabargo3949
3 жыл бұрын
i am having trouble downloading the spreadsheet to follow along ... Any help?
@Zenithity
Жыл бұрын
What's the point of using pivot tables when you've got the filter anyway?
@AnnKEmery
Жыл бұрын
Filters work perfectly fine for one-time analyses, but pivot tables and formulas will save time for recurring analyses (e.g., weekly, monthly, or quarterly reports at work).
@Megabites46
3 жыл бұрын
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
3 жыл бұрын
I didn't learn pivot tables in college or grad school, so I simply hadn't encountered them yet 10 years ago.
@j.d.singer8517
2 жыл бұрын
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
2 жыл бұрын
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).
@yogeeshgv2001
Ай бұрын
I can practice excel sheet ,please u can share ur working above Excel sheet ,
@rahuldas6777
2 ай бұрын
you could possibly use an xlookup for #7
@mikewebb8196
3 жыл бұрын
=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)
@user-pr1ie6bh3x
Жыл бұрын
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.
@AnnKEmery
Жыл бұрын
You might like finding an "Excel for beginners" tutorial on KZitem first, and then come back and look for "Excel tests" to assess your progress. Best of luck on your learning journey!! :)
Пікірлер: 258