The channel uh pretty straightforward i mean we did drop the excel vba videos uh um as we had talked about quite a bit so there's five new excel vba videos um these are these videos were originally supposed to run early in the year but the master audio was really really messed up so we had to get them re-recorded and we got them edited and everything like that so now they're up on the page and you you also will see that we have a new video type which is the full course uh going.
Forward um we will release the videos in one giant video if that's what you want uh the chaptering doesn't work so well on that just fyi so it's not going to be as easy to navigate but hopefully the auto chapter on youtube works a little better but that's that's where those are at but you'll start seeing those pop up more and more and more again the chaptering's not the best on them we're kind of trying to work on a solution for uh the chapter and on those but that yeah that's that's as far as the updates uh other than that we would.
Kind of have a set schedule for the next uh couple months again i don't like to give out the schedule way in advance because we do move stuff around a lot uh and then the last thing is uh look out uh for if for all anyone that's a part of our learn at any time and again learn at any time is our ad free version of all our videos and stuff like that you guys will be getting a special exclusive bonus uh content and that's going to be our professional development videos things like time management uh business writing.
How to write better emails stuff of that nature those will be a part of wa learnanytime.com our anytime service and then we are adding exams there as well that's something i've said them numerous times but those will be added to um anytime as well coming up so big updates for uh our anytime members well that's it for the updates joe i i know i always say like oh i have a couple updates and then i ramble on for forever.
Oh yeah and then uh my coffee mug made a quick cameo but just uh this is my real life coffee mug this isn't like one i got for this show and obviously bees don't drink coffee again because i guess by popular demand everyone likes the b yeah everyone likes the b um i'm not going to put the poll up again i think it's just going to keep winning.
So this is going to be the last uh last show we have joe as the bee unless you guys if you want me to be you got to put in the chat that to stay the bee um oh let's just say good morning to everyone where you're getting your screen set real quick um khadijah ananda um that one i cannot k gas um good morning yeah i i love coffee too i can tell you i i live on it so um.
You let your screen all set joe yeah all right i'll put it on the screen here all right let you take it away all right let's do this okay so really excited about data analysis it's one of the top things that everyone talks about in the business world the corporate world analyzing data right because when we analyze data we're able to then of course find solutions to problems or find problems that we didn't even know existed.
So with that said what we're going to do is we're going to go over to data analysis open up my file and the first thing we're going to talk about is actually dealing with the data so right here you'll notice that i have this circa company payroll information data nothing too crazy here just a numbering system which i always recommend with data sets um i also really have some simplistic information like first name last name employee number division what department they work on and so forth and.
What we're gonna do is we're gonna actually pull more data and the reason why is because what we have here is a great data set but we also have more data inside of this data it's known as metadata information from information and what we're going to do is we're going to start to datum mine which means we're going to pull more of that information from the data that already exists because it could be important information like for instance my boss comes up and.
Posts Related:
Says to me you know joe this is great that we have the date of hire but we want to do some analysis on how many
People we hire per year is there any way that we can create a year of hire then i'm like yeah that's a great idea because once again that's something that we want to analyze how many employees have we hired per year not just the date so the first thing i'm going to do is create a new column here i'm going to call it year of hire.And of course i want it to look the same as the rest of these headers so i'm just going to click on one of the headers go up to the home tab and click on the format painter which will copy the look of one to another so i'm going to copy the look and apply it to here and look at that it looks beautiful okay so now that we have this the next thing we're going to do is we're going to pull out the year by using a function called the year function so i'm going to do equals year press tab on my keyboard to open up the parentheses and i'm going to just.
Simply select the date of hire that i want to pull a year from which is this first one and when i press enter i now have 1986 perfect now of course i'm not going to want to have to do that for all of them so i'm going to use my friend autofill here and you'll notice if you hover over your white crosshaired cursor over that little square in the bottom right hand corner you'll then get the fill handle which will turn into a black crosshair cursor now if you have data to the left or right you could just simply do a double click instead of clicking and dragging.
If you do a quick double click it fills it in all the way down for the rest of your data set which is perfect and now i can do a quick analysis of how many people i have whether i want to create a chart to visualize it or just do some sort of sum if or do some sort of subtotaling i can now do that now what else can we pull from here well maybe we can pull something that's not as i would say um out in the open as the year of hire of course that was an easy one because i.
See the year of higher here i just wanted to pull the year of higher but what happens if i want to pull something like well full-time versus part-time staff well in that case which column would i even use for that well what really determines a full-time staff versus a part-time staff is the hours they work and we do have that information right here so we already have the information about the hours now we're going to get more information.
From this information that already exists which is the hours so i'm going to say okay i see here i have the hours and and we'll make a fake example in my company anything that's more than 36 hours is considered full-time anything less than 36 hours is considered part-time so what i'll do is i'll create a little full-time part-time i'm not gonna write full-time part-time i'm just gonna write ft slash.
Pt and now what i'm going to do is copy the look of this once again i'll use my format painter and copy it to this so i get that nice little line now for this one we are going to do a another function it's called the if function we've done this a couple times on the show but if you're new here welcome and let me explain it the if function what it simply does is this it says if something happens right so.
If three we'll say is greater than two if that's true right if true then write yay if it's not true if it's false then write something else so i would write if it's false ooh right so now it's going to check for that throughout all the different things and it's going to say if it's true say yay.
If it's false say boo so we're going to do the same thing here we're going to say okay if we're going to do equals if press tab on our keyboards to open up that parentheses and it's going to ask us for three things what is the logical test which means what what are you trying to ask excel what is the question you're asking and if the question is answered true what do you want us to write if the question is answered false what do you want us to write but we're just simply asking is this number.
Right here in i5 greater than or equal to 36
Because if it is greater than or equal to 36 if that's true then we're going to write ft for full time and of course we have to put it in a string of text which means put it in quotes so that you're able to actually understand english right excel doesn't understand english it's a computer system or a computer application.So now we're putting it in quotes we're saying okay if that number 35.5 is greater than or equal to 36 write ft if it's not true then just write pt and then we'll close the parentheses when we press enter it tells us it's pt because of course 35.5 is not greater than or equal to 36. now i don't want to have to do that all over again so i'll use my fun little autofill double click and look how awesome this is we now have a complete analysis of whether it is.
Part-time or full-time and you'll see here that we have part time here part time the minute we reach 42 hours full time full time full time so now we can compare that so that is data mining which is really amazing there's always more data out of data that already exists okay so what else can we do well let's go over and let's say that we want more data that we don't even know exists like for instance.
There's hidden data here in the date of hire see if you look over here in the date of hire let's say i want to see how often do i hire someone on mondays versus thursdays well the first thing i need to do is i need to figure out what is december 24th is it a monday is it a tuesday or wednesday who knows so we're actually going to use a really cool function that will allow us to pull the day of the week now to do this the first thing we're going to do is put in.
A of higher and i'm going to just do that same old thing where i copy the look there we go now for this one this is going to actually be what's known as the text function and the text function what it does is this it allows you to pull in the day or the month of a date because in and this is different for everyone in the american format it goes month.
Day year even if we're not in the american format even if we're in let's say the united kingdom format it goes day month here so even though they're different formats they still use the same code for it d for day m for month so let's say for instance i wanted to pull the day out if i use 1d let's use the let's use this one as an example july 5th if we use 1d here what it's going to do.
Is it's going to say okay that's the fifth day right july 5th if we do two ds here say okay it's zero five if we do let's say three days it's gonna say okay we'll pretend it's a monday i don't know what it is abbreviated and if we did for these it's gonna say okay joe you want the full name of the day which is awesome same thing for month if you put 1m it would be for july it would be a 7 for 2 m07 for 3ms it would be.
Abbreviated july and for 4ms it would be july fully not abbreviated so what we're going to do is we're going to do the text equals text select the value which is the date of fire put our comma and it's going to ask us what we want now you do have to put it in quotes still so if i put it in 1b watch what happens it gives me 2 4 because it's pulling the 24 from the 24. if i put in let's say i go back to my formula bar and i put in 2 ds.
It gives me 24 still because it's 2 digits if i go back to my formula and i put in three though watch how cool this is ready it gives me one stay abbreviated if i want the full day i'm gonna just simply put in four d's now i can go now i have that data look how amazing that is and that was hidden data who knew that the days of the weeks were in there right but they are they are in there so data mining to.
The max i love it more information pulling more information so let's go on to the next worksheet here where i talk about data mining made easy now sometimes when we're analyzing data we need it to look a certain way and what happens if i told you all right now hey i want you to take the last name put a comma and put the first initial which is c and i want you to do it for all of them you're probably going to be a little upset you're going to say wow why are.
You making me do this it's going to take me forever well not with excel excel actually has an amazing feature called flash fill now we have featured this on this channel before but i do want to share share it with you for anyone that's new or just joining see flash fill what it does is it follows a pattern but you have to set the pattern first so i'm going to type in last name put a comma space first initial c and i'm going to.
Press enter now i can go over to the data tab and inside of the data tab over to the data tools command group there's something called flashville and you'll see it kind of looks like harry potter's lightning bolt for all my harry potter fans out there now i'm gonna click on that or you can use control either as a keyboard shortcut as you can see there and when i click on it watch what happens ready magic just like harry potter see.
So it does everything for you as long as you given an example and i could have done that differently too i could have done the full name maybe i have to combine them together don't use concatenation anymore that's old just go kara wilson press enter control e boom i'm done not only can i do it this way but i can also pull data apart this way for instance i have the city in the state i want the city now.
I know most of you probably know text the columns and you've probably used this before it is an amazing feature don't get me wrong especially for csv files comma separated value files but what i really like is that i can just use flash fill to pull things what's the city bolder press enter control e i'm done look at this state colorado co press enter control e.
I'm done how awesome is that it saves us so much time i could even pull the zip code if i wanted to pretty amazing so this is just data mining made easy when you want to put more information maybe split or combine columns so yeah this is great okay we're not done yet let me show you one more example of a great way to data mine or analyze data let's say for instance your data is not as neat as these past two ones like the.
Dealing with data here this is all one put together that was nice or the data mining this was all one piece of data but what happens if you have multiple data sets you have to analyze let's say for instance that you need to analyze this data here now it's split into three different pieces we have this data right here that has the products what we're selling and what the price is we then also have a secondary where it shows us who's the sales rep and what region they sell in.
And then we have this fax table this is the actual report where it tells us the date the sales person sold who the sales person was what they sold if they gave a discount and how many units they sold well you know would be really amazing if we can get more information on this table here for instance okay they sold two units but how much did they make total well i don't know let's see how much doublers are doublers are.
79.95 okay seven nine nine five and i have to do equals this multiplied by this okay they made 159 that would take me forever if i had to do that for all of them i don't have to i can actually use some data mining techniques so let me show you the first thing i want to do is pull the price here because i want to know how much did christy sell for what is the price of doublers.
What is that well i don't want to have to look over here and type it in every single time because look at this this is a long list of data that would take me forever so instead right and i mean that's a long list watch how far it goes we're talking about 10 000 items sold so instead of doing that which would take us weeks to do we can do it within seconds and i'm just going to type in price over here now the hard part about this is that.
How do we get it so that anytime it says doublers it will write 79.95 we could do an if function but then we have to do and if it's doublers write 79.95 if it's sunshine write this if it's that would take forever i'm actually going to show you one of the newer functions for a lookup function i know some of you probably have used vlookup or hlookup or index and match in the past i'm going to show you x lookup it's a newer function and it's easier to.
Use and it actually replaces vlookup and hlookup so the first thing i'm going to do is equals x lookup and i'm going to press tab on my keyboard i will go to the fx and click on it over here next to my formula bar this will bring up the function arguments box i also think of it as the help box because then we don't have to worry about commas and all that other stuff so i click on it and the three things i know there's a lot of different things you could fill out here but we only need to fill out.
The three bolded things what are we looking up where are we looking for it and what do we want to return once we find it okay so let's take a look what are we looking for well we're actually looking for this product doublers where are we looking for it well we're looking for it in this list of products right here and what do we want to return well we actually want to return the price because that's what we're looking.
For the price here so i'll just select the prices that's all you need three things and you're done magic ready let's actually do it together what are we looking for couplers i2 where are we looking for it from a2 to a13 because that's where all of the products are what do we want to return the prices from b2 to b13 and when i press ok.
How cool is that and now what we can do is we can do a double click but what happened uh oh does anyone know what happened well yeah we've talked about this so many times before we have to absolute reference these cells here and you can use the f4 on your keyboard just like that and that's it press enter and now what i can do is double click.
And yes it's that easy so once again all you have to do is this equals x lookup plus the fx what are we looking for doublers where are we looking for it in this list right here where what do we want we want the prices right here make sure to put the dollar signs in okay make sure the dollar signs in in so we.
Lock these two so they don't move press okay and autofill and you're done now what we can do is we can create this subtotal we can do equals the units multiplied by the price how amazing that is now all you would have to do is just select all of this turn it into a currency so it looks like an actual numeric currency and look how amazing this is we now have.
More information x lookup is so amazing it's one of my favorites you don't even have to worry about vlookup there's no limitations and things like that the last thing i might do here just because i'm crazy like that is i'm gonna select all of this and just make sure that i have borders so it all looks good and now we have awesome prices and we can do that again i can pull more if we wanted to we can let's say for instance.
Regions we want the regions too like where is christy olson selling where is she selling okay christy olson the west do i really want to have to write west here and then fill all of that out no so i'm going to type in region once again i'm going to do equals x lookup i'll use the fx just for help what am i looking for well i'm looking for christy's name h2.
Where am i looking for her name in this list of sales reps names what do i want to return whatever region they're in so i'm going to select all the regions make sure to absolute cell reference put your dollar signs in so it doesn't move and press okay and it tells me that christie's from the west and then i just use autofill and now i have them all pretty amazing the last thing i'll do is.