Pivottable is excellent... as long as you don´t need to do things totally automatic. We use to have loads of pivot table with my last employer and i needed to make them automatic to extract information from them. And that turned out not to be really a very good way
Hmm we'll have to "agree to disagree" here. Pivot tables are super automatic (?). I wonder how yours are set up, in such a way that they're not automatic?
@@AnnKEmery well we had a issue with them in my last workplace when exttacting information from the table become very buggy when used for additional calculation. There might be a solution to this, but.. well at the time there was nobody able to fix it so we made sort of a warningsystem in stead
@@AnnKEmery well.. we kind of didn't. We just developed a automatic system to warn about it so we could go in and reset it manually. Not a great solution.
Ann K. Emery fails to identify the lesson. While Ann's technical prowess and personality are commendable, she fails to take on the effectiveness of pivot tables and make it a teaching point. She has two videos here, basic formulas and pivot tables.
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.
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!
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
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.
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.
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.
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.
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.
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
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
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.
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
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.
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
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 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.
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.
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
Could use lookup or lookup for the race ethnicity for the family with 8 kids, hnstly eyeballing might b the best because they tell u it's the only value, but maybe if they say find the race / ethnicity with the largest or smallest number of kids u could use a mix of v or xlookup and max/min to get that or large or small if they ask for the third largest or smallest number of kids
I think. Communication skills should be as important , there is lot of people that can even speak English , and that it’s frustrating. Don’t hired people with No English .
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.
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 ;)
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.
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 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 RU-vid first, and then come back and look for "Excel tests" to assess your progress. Best of luck on your learning journey!! :)
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.
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).
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).
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))
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
unfortunately when you are taking an assessment exam online you cannot use different methods to solve. you are counted against every time you click somewhere. so you have to know how to use one method to get the answer and you have to be quick. these are timed tests. i would love it someone could make a video on the test genius method of job assessment testing. Interesting video about pivot tables though.
I've got blog posts and newsletters about taking online exams, which are a different style from this test I took. Lots more resources available if you're curious to learn more.
Exactly what i was thinking. I have an Excel test tomorrow and have already been told it's a timed test. So, if filtering will give me an accurate answer the quickest, that's what I'm going to use instead of spending time on a pivot table trying to figure out if the item I'm looking for is a value or in a column or what.
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.
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.
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.
