Excel Data Analysis: Sort, Filter, PivotTable, Formulas (25 Examples): HCC Professional Day 2012

welcome to Highline Community College professional development day October 19 2012 hey we're gonna do Excel data analysis basics we're gonna look at some sort filter pivot tables charts and formulas hey if you want to download this workbook and follow along you can click on the link below the video not only that but this will be a pretty long video so there's a there's a show more button below the video you click on that and there'll be this table of content with minute marks so you can jump to any part of the video tight alright so here it is and if you download this this is a link so if you click on sort by color it jumps to that sheet right there we're going to cover sort filter these topics pivot tables charts formulas a little bit about the difference between formulas and pivot tables and getting data into excel all right let's start off on the sheet this is topics let's go to D a what is data analysis now you know we hear this every single day all over the place and most of us don't even think about what it is but it's really simple right it's taking raw data and creating useful information so for example here's a table by doing something as simple as sorting this column we get this now we have some useful information we can see the of the four fastest times for assembling Joe is in the you know has three of those times so that's useful information about who's doing the assembling the fastest another example that that's using the sort feature using formulas and we'll do this one later we have two lists and it's often the case that one list is different so we'll see how to do a formula that tells us true when this name is also in this other list will also see how to do the reverse but again this is a formula taking some raw data here and creating useful information we'll also see how to use a pivot table to create useful information here is our data set of students classes are major classes in grade here we've calculated the average gate grade the the GPA the grade point average in a cross tabulated table right so for nursing right click hide Fiona's nursing math right we have two criteria here right the grade point average is three point four for accounting majors that are taking English 101 the grade point average is three point two so going from raw data to useful information that's what data analysis is now let's go over to the sheet D very important that we talk about how data must be set up so for the data analysis feature like sort filter pivot table you got to have a proper data set all right so field names at the top records in rows you don't want to have data sets like this you can't have a blank all the way through that's with a column and so you don't want a row either and you don't want any blanks in the field names right if you have blanks in any of those situations than doing sorting filtering pivot tables with the built-in features just won't work you also want to try and not how have blanks in the data set the features will still work like sort filter pivot table with sometimes it will cause problems control-z they're now very important notice I have some notes here look at that you if you have a data set it's got to be completely surrounded by blanks or it can be touching either the row headers or the column headers I'm going to control Z gotta have blanks all the way around or row headers or column headers for the data analysis features to work all right so now that we know that that how data should be set up in essence this is called a table format right fields or variables or column headers remember the olds column headers variables they tell you what kind of data goes in this column records and rows blanks all the way around not no blanks inside then you're good to go all right let's go over to s1 and look at our first sorting s1 all right sorting sort with one criteria again we have a proper data set it's very easy we want to get these records sorted with the fastest times at the time now the first time you do sort it gets it's kind of disconcerting so check this out I'm actually gonna highlight one of these go up to home and add a fill color that way when we sort it will see that the integrity of the data set is true after we sort meaning this record will remain together I mean the three elements in our record will remain together now when you sort you simply have to click in one cell in the column you want to sort and the whole table we be sorted now you can go to the home and there's the sort button over here a to Z you can go to the data ribbon in fact let me there's a couple ways to show your ribbon you notice mine aren't showing right here you could come over here there's that you could also right-click on minimize and then there's a keyboard shortcut ctrl F 1 control F 1 is a toggle and then there's a twosie those are generally the ones I use there's also sort dialog box that we'll look at in just a moment you can also right-click so right-click sort and then a to Z so I'm gonna say A to Z here that'll bring the smallest ones to the top and there it is so Joe Wow Joe has three of the four fastest times and notice our record our whole table remains intact even know what we sorted on a single column alright that's sorting with one criteria let's go over to s2 and we'll see how to sort with two criteria and this applies to two or three or four or five or how many however many you would look like now I would like to have all the accounting majors together and then all the nursing records and then within those I'd like to have the grade column sorted from biggest grade to smallest so the way you do this if you want what's called the major side and you know this is funny when I did this this just happens to be major but the name for the final sort is called major sort so if I was going to have my final sort as class it would be called the major sort alright so if this is the final column we want sorted remember we want all the accounting together than all the nursing and within all of the accounting I don't want these numbers sorted if that's the final sort then you do that one last all right and I'll show you two ways to do this now we can I have the data ribbon show and so I'll just do z28 because I want the top of the biggest ones on top notice one two columns one two criteria this is the final one so I'm going to start with this one I'm going to leave the final one for last so I'm going to say Z to a biggest one on top notice they're all sorted biggest to smallest now I come here and now I'm going to do a to Z notice all the accounting are together which is what we want but because we sorted this one first it's sorted from biggest to smallest exactly with in accounting and then nursing okay so that's one way to do it I'm going to undo you know there's this button undo up here but control Z control Z is undo controls easy all right we can also do the sort dialog box so I'm going to click on this and a lot of people like this because they can't remember which one to do first this one will just allow us to select add levels and the one on top is obviously going to be the major major sort so I'm gonna say grade that's the field again you gotta have field names right I'm gonna say values and we want largest to smallest so I'm going to click okay so you can see oops I didn't mean to click okay but you can see now they're sorted they're come back up here it remembers that it's there now I'm going to add a level and I'm gonna say major whoa wait a second this is not quite quite what we want but no problem we can fix it values a tizzy now if I did this right now the major sort is on top so it would be great right but that's not what I wanted no problem I click the sort I can simply click on one of these and move it up or down right so I'm gonna use it I'm sorry this one right here so when you're using the sort dialog box the major sort is on top that means the final sort right you want all do those items to go so now I'm going to click OK and boom we have sorting with two criteria or on two columns accounting together and within that these are sorted so really you how you would say this is the this is wrong this should be sort grade sort grade within major all right now let's go over to s3 now sorting by color pretty cool wasn't around in 2003 or earlier but in 2007 and ten sorting my color is here now you could use your sort dialog box and I'm going to say sort by date and check this out it'll know it looks in there it says values cell color I'm going to say cell color there's also font color and cell icon I'm going to say cell a color and then it knows which colors are there so I'm going to say red and all of the Reds are sorted together notice all the yellows are not sorted but let's do use the sort dialog box and try to add a level so what it's what's going to happen is the the Reds are going to come to the top and now I'm going to say date again by cell color now I'm gonna pick yellow so the Reds will be on top and the yellows so and people it is common that people use color to indicate that somehow these records need to be looked at further right so this is a huge data sets that someone went through manually sorted by color and now they need to sort them get them all in one place all right our next topic is filter sheet f1 now filter is similar to sorting but it has one great advantage now what do we do when we sorted we brought things together the entire data set is still there we just brought some particular records together filter doesn't do that it actually reduced temporarily reduces the size for whatever criteria and brings all the records so if I filter by accounting right if we look back over on a numbered s2 we were able with sorting to bring all of the accounting records together right what filter does is it will bring all these together and hide all the other ones and what the great use of filter is that if you want to take just some of the records from a data set copy and paste them some somewhere else filter is great in essence you're extracting just some of the records out and putting them somewhere else a type of data analysis a very common task given you know the huge data sets that people have these days all right filter you can click on this button there's a keyboard shortcut ctrl shift L the filter button if you look up here I'm gonna use the keyboard shortcut it's a toggle you can click it on and off and what happens is this is in the old days we call this Auto filter it was under the the data menu filter auto filter I think that was it alt D for data F for filter and then F for auto filter so alt D F F that was the old wait all right so what is filter do I'm going to click the drop-down there's a bunch of things that will see the filter what is is that it will look through and list a unique list here it's fines accounting and nursing I can unselect all select just accounting and when I click OK it shows only a County now it's hidden the other records you can tell for a few but you can tell that it's filtered a few different ways one is there's missing rows so six is missing the row headers are blue there's also a little filter there now this is what I love about filter and I love doing these wild array formulas to do data extraction but nothing built beats filter in terms of ease of extracting just some of the records based on criteria I'm gonna highlight ctrl C now notice copy usually just does the outline but because these are there's some hidden rows here the dancing ants are dancing around just the visible cells when I copied it got just those not the ones that are hidden so now when I come over to my pasted sheet and ctrl V just like that I get just the accounting records now I can come here and use my smart tag and say hey give me column widths please all right and then - unto unfilter you can either come here and click clear that clears just this one filter you could oh how do I turn off the dancing ants I'm gonna click the Escape key you can also click this so for example if I had this on and I filtered by English right and I copied and pasted it somewhere else and now I not only wanted to unfilter but I wanted to get rid of these auto filter buttons I just click up here not only does it unfilter it but it gets rid of the buttons all right how about click it on sheet f2 let's see how to filter with two or two criteria or more now let's go ahead and turn our filter on I'm gonna click in a single cell ctrl shift L whoops so control strip del is that toggle and let's do a counting and then just math 101 so here we have two criteria are we have two criteria for the records we want to take and extract from this data set we can simply highlight now this is a small data set and all the data sets in this video are small so it's easy to see and learn but there's a keyboard shortcut to highlight the current table or the current region especially if it's huge it's a great keyboard ctrl asterisks and I'm going to use asterisks on the number pad so I've highlighted it and then ctrl C and now I can come over to paste and I'm gonna paste just those records and now I'm gonna come over here I can click escape to turn the dancing ants off if I want to I'm gonna leave that filter let's go look at another example f3 can we filter by color you betcha look at that filter by color and boom control asterisks on the number pad control C and then paste over here you know there's lots of fancy formulas for doing data extraction but nothing beats filter for ease of use alright let's go over to f4 well talk about different filters for text numbers and dates now here we have a data set a bunch of transactions we've made some sales and I would like to filter on this sales column well filter knows that these are numbers let's go ahead and turn this on control shift L and you can come here to this drop-down and sure enough number of filters in the bunch of a bunch of amazing filters specifically for numbers equals does not equal greater than greater than or equal to I'm going to say greater than or equal to and I'm going to type 100 bucks so only equal to or greater than 100 and just like that you can see all the records are filtered there we have it I'm gonna unfilter this click clear filter and then I'm gonna do a different number filter oh this is great look top ten oh how about above average or below average I'll do top ten but I don't want to see top ten I want to see just the top six and there's the bottom two so you can do bottom and just like that I have the top six all right control asterisks on the number pad control see that was like two hundred records there right I come over to my pace sheet boom I have pasted it extract adjust the top six sales let's go back over to f4 I'm gonna unfilter this and click escape to get those dancing ants to stop dancing oh it looks like I have a date here but the wrong format let me show you a scary trick here I want to quickly format this whole column I'm gonna double click and send it down now immediately that tells it to copy but I'm gonna come to the smart tag oh and you can't see it it's off the screen let's see if I can pull this up oh the smart tag is oh there it is so fill without formatting oh sorry I fill formatting only is what I want I know that's a crazy way to do that it would have been much easier to highlight the column and go up to font color black right but that's one way so you've always wondered what fill formatting only was that's an example there let me pull this back down here okay so date Oh date I remember when this came out in 2007 date filters now check this out the filter knows that these are dates before after between tomorrow today yesterday next week this week last week let's do last month only so I'm shooting this in October which is ten so now it shows me only last month so the date filter is just amazing all right I'm gonna undo that filter there click on the clear and then text well we already saw it text but text you can do equals does not equal begins with so it begins with the letter or whatever it's pretty amazing now let's go over to our sheet f5 for our last filter example example I'm going to turn the filter on control shift L and this is filter by partial text I absolutely love this look at this text filters I'm going to type calm for a compressor alright maybe you just want to I'm gonna just gonna type that and click OK and sure enough this was a small enough list with no other words that start with C om PR and it brought up just the compressors there it is I have the telephone numbers I've filtered by partial text okay so filter totally amazing for extracting records how about ctrl C paste it over here ctrl V now our next topic is going to be a pivot table I'm going to come over here now pivot tables so many people think pivot tables are hard big and scary no way they are one of the most amazing features and they're amazingly easy but before we see how easy they are what is a pivot table it's helpful to just have a simple definition and this is it a feature that can easily make calculations with one or more criteria so really all you need to remember is pivot tables make doing calculations with one or more criteria unbelievably easy some of the calculations that pivot tables do they can do the same thing that formulas can do but pivot tables are so much faster now we'll see we'll compare and contrast pivot tables and formulas later but here it is here's an example here's our little data set student aid major class and grade I have in this cell right here calculated the grade point average for nursing students who have taken English 101 now what is this oh it's a calculation we've done average we've averaged the grades but check this out we didn't average all the grades in the columns just some of them well what determined which ones we used in our calculation nursing was one criteria English 101 was the second criteria so this is an average with two criteria notice every single record meaning this number here we used had met both criteria nursing was the major class of English 101 so to do this calculation with the formula you know takes a big formula and takes a lot more time to create than a pivot table alright so what is a pivot table calculations of the moment one or more criteria now let's see that doing this takes about three clicks I'm going to come over to PT - all right here's our data set and oh we just saw there's the picture that's what we're after here right all right and I have some notes up here for the one two three now pivot tables just like sorting and filtering you click in a single cell then you go up to insert pivot table or just click on the pivot table button and it opens this dialog box now I'm going to do a bunch of these so insert pivot table now let me teach us something about the Alt key board shortcut if you're in Excel and you hit the Alt key what happens ah 2007 and 10 they have these great screen tips so in order to get to the insert ribbon tab I need to click n if I was going to page layout I'd click P so N and I'm gonna hit in now and now watch what happens when I tap n who all of the items in the insert ribbon tab now have screen tips so I have to hit V and then T so the keyboard shortcut for pivottables remember single-cell alt NVT now the rule of alt keyboard shortcuts is you teach yourself whichever things or features you do day in and day out that's why they have those screen tips so alt NVT now this dialog box is great remember or know it guessed right why because our data set is a proper data set with blanks all the way around it'll always guess right if you have a correct table format for your data I'm going to use oligo to a new sheet but just to make it so we can see the original data set I'm going to put it on this sheet now pivot tables you have to be careful if you put it on this sheet I'm gonna say right here if you have anything below sometimes it'll erase it right I'm gonna click OK now it's really there's a picture here notice there's a row headers and column headers and then some sort of calculation in between and it's kind of reminding you with this picture that you can come up here and click these but really this whole table field list here is all you need to do now what's what makes a pivot table so easy well one thing is if you actually have a picture in your head before you start the pivot table meaning I need the majors as my row headers and the class as my column headers then the pivot table is easy right okay so step one is we invoked the pivot table step two well our table has to have row headers and column headers so step two is you simply drag your field names to the right location so I'm going to drag major down to the rows oh look at that that's already amazing the clasp to the column and then gray to the values all right so step two you it's it's three clicks there but you drag you simply drag to the location and what makes this easiest they call this row labels column label so if you imagine in your head first you picture it then you know exactly where to drop which fields the values whatever you're doing the calculation upon always goes in the values area now the final step is by default it will sum so we need to change it to an average all right so I'm going to click in one of the numbers inside the table and right-click summarize values as and there you have it average and just like that you've created your table now there's some format so 1 2 3 what makes it so powerful is that these calculations are harder to do with formulas proper data set you know that inserting a pivot tables on the insert menu once you get this filled lift the field list you drag and drop and if you want to change the calculation you can right click and summarize values by now formatting this is kind of annoying it says row labels instead of what class that's it I mean what major this is it's just column labels not the class so we're going to go up to design report layout and show in tabular I do that every single time major class there's that note right there now this is called a cross tabulation why because there's an intersection here there's a variable up here in here cross tabulate in between these two we've done an average now these grand totals are actually quite nice it says it'll tell you what the average is just for accounting so you can compare here's the average for the nursing so you can compare here's the average for English here's the average for math 101 alright so pivot table in three clicks now I'm gonna scroll over using these scroll arrows and I'm going to go over to PT three now more than one calculation here we have a slightly different data set here's our end goal we want to list from this department one instance each of our departments in our business and then from this column this is ours work last year i want to see total the average per department the standard deviation of our department and the count meaning how many employees were in each department right so that's three calculations all done now remember what does the pivot table do we're doing some calculation not on all the numbers but with one or more criteria so what's the criteria department we're gonna add with one criteria department take calculate the average based on one criteria department now that's a little picture I took when I did this before I'm gonna scoot this over here I'm gonna click in the DAT one cell in the data set alt n VT I'm gonna drop it in this sheet again just to make everything so we can see the data set and our pivot table normally I'd put it on a different sheet now I'm gonna click OK alright so what are we gonna do here well we had all those calculations okay we're just gonna drop department down to row so that's easy now notice what does that do that's a totally amazing use of the pivot table to get a unique list of items from a column filled with lots of duplicates right now we had what one two three four we're gonna drag this hoursworked down to the values areas one two three and four all right so that's kind of counterintuitive we dragged it there four times but no problem we'll go and change the calculations one two three now I'm actually I don't like these wide columns so I'm gonna come here and I'm going to type the word sum and then change the Trojans down and I'm gonna take type average write this over here and then I'm gonna type standard I'm gonna do SD - P for population this is all of the records for last year so it's a population population means all the records standard deviation for the population and then count all right so what am I going to do well it's already some by default I'm gonna right-click summarize values as and show average I'm just summarize that is by average oh look at that now I would like to show you there's an alternative way to change the name change the calculation and the formatting I'm going to right click value field settings this is kind of like the dialog box though has everything we need we could have typed our name there we could definitely change the function and this is where we find our number formatting so I'm going to click that I'm going to go down to a number and change it to zero and then click OK click OK right so this the average oh I'm sorry I was falling asleep at the wheel that is not the total hours work 13 hours I can already see a 3,000 there I need to change this too I'm gonna right-click summarize values by and do some all right standard deviation I'm gonna right click go to value field settings again this is kind of like the one-stop shopping I've already changed the name I'm gonna scroll down here standard deviation of the population and then I'm gonna go to number formatting I'm gonna say number show me is zero decimals click OK click OK and finally the account now looks like it did that by default you can check it by going summarize values as and sure enough it's got that count okay so one two three four calculations each one each calculation being done with one criterion not all the numbers that's just the sum for accounting just the average for accounting all right let's go to PT four we want to talk about grouping now here's a picture of our end result here we would like to group so we have all these employees who work varying hours for an entire year so we need to group between 100 and 599 hours worked how many employees nine count of nine how many between 1600 and 20,000 99 15 alright so again this it is would be tough to do with formulas but with the pivot table Oh totally amazing alt nvt I'm gonna put on this existing worksheet I'm gonna click right and so f5 and click OK alright so now the trick is if we want a group we need to drag the number down to our row now that's ridiculous I just got all the numbers there but now we can simply right click and there's group and it'll kind of try and guess for you I'm gonna go change this from 100 and I'm thinking about increments right to 3600 and I want to group by increments of 500 and click OK and just like that it builds those labels I can do I've done this with Florida's I can I tell you that gets pretty tricky right you got to look through and find out what the men and maxes and all sorts of things all right now we drag our hours down to those and by default it's counting I'm gonna put a number and then change the width right so very quickly we have our report let's go up to Oh what happened to our ribbons notice I'm clicked outside of the pivot if I want to click back inside the table my pivot table ribbons come back up I can go to design report layout and tabular alright let's go over and look at filtering on PT 5 now here I did this same pivot table I I didn't change the name here but I'd like to show you how to filter this report now what is this filter right now this is the count for all the departments we just grouped this one column and then counted everybody so this this count of 15 between 1600 and 2000 9 to 9 hours work last year is for all the departments no problem there's something called a report filter I'm simply going to drag department down to report filter up here Oh I guess I did this once before so it remembers the the filter I'm gonna come up here and select all and pretend that that didn't happen by default it comes up if you haven't created it worked off of this data set before it comes up and it creates there is no filter in essence but you click up here and select all I'm gonna say finance so now I filtered that entire report now there's another way to filter I'm gonna drag this off excellent let me drag this back down here in unfilter it I'm gonna say all and I'm gonna drag this off and there's a great feature called on the options insert slicer it all it is is a formatted report filter I'm going to select Department click OK alright so I can drag this down here I can size it I can point to the edge I want to point to the edger and drag it down and that you can format it notice this is a slice or option so you can format it whatever color you'd like it says columns if I change this to two so now I have two so maybe it's a nicer fit that way so now we can simply click on finance accounting and our pivot table absolutely updates if you want to do accounting and marketing you have to hold the ctrl key and click on marketing and now that's accounting and marketing or how about accounting and finance right accounting and marketing and finance so absolutely beautiful filtering using a slicer hey that's a lot about pivot tables one last thing about pivot tables let's go over to this and it's just a want to mention one chart now here's the picture of what I want and it's a column chart it shows me hours worked in 2012 number of employees and it's a nice visual portrayal so charts are absolutely data analysis also what are we doing we're taking a bunch of raw data creating useful information now I've already created this pivot table I'm going to create my chart from a pivot table totally amazing now you could go up options pivot chart but you can also just go to insert they base they both access the same charting engine so I'm gonna go this chart right here there happens to be a keyboard shortcut for the default chart so whatever default chart you have on your computer if you use it all the time alt f1 so alt f1 puts a chart on this sheet the f11 key puts it on a new sheet so I hit if I hit f11 it actually inserts a chart if I hit alt f1 it puts it on this sheet now I'm gonna make this a little bit oh the beautiful thing about the chart and the pivot table is if you change anything up here it will automatically reflect this chart is connected to that pivot table now I'm not going to filter it but that is a great aspect I'm gonna make this a little bit bigger I don't want this here so I'm going to right click and hide field buttons I'm gonna come down here and right click hide oh it's off the bottom of the screen here right click it's still looking at it right click hide axis now I don't want that one right there I don't need that right there that's chart junk I'm going to add under layout I'm going to add axis titles so on my vertical now let's do horizontal first title below I'm just typing notice I opened up I said give me a horizontal axis label I don't see I still see the word axis title but as soon as I started typing look at that it's up in the formula bar so hours worked hours worked and then I hit enter so I typed it now I'm gonna go up and do the same thing axis and vertical I'm going to do this horizontal now notice it's axis title but as soon as I start typing and then hit enter it finally shows up I'm gonna get rid of these lines get rid of this axis right here and then I can come up here and do data labels I'm gonna say all outside and there we have it our chart data analysis based on the pivot table based on that raw data all right we're gonna go over to sheet number formula 1 sheet f4 for formula 1 we've talked about sorting filtering pivot tables but absolutely formulas are great for doing data analysis now we just saw pivot tables that were able to take this raw data and very quickly do a pivot table where we list a unique list and then we can do some calculation like add up all of the hours now formulas can do the same thing and let's see how to do this now what are we doing we're adding not the entire column but we're adding just the ones that are for the accounting department so really I'm gonna need this number this number on all the way down the accounting line so adding with one criteria now there's a great function that came in in 2007 there's the old some ifs but I like some if that's been around for a long time and in 2007 and 10 there's this new function Sumit's I like to use the sum if all the time whether I'm doing one two three or four criteria now the sum if with just that without the S that only adds with one criteria S does one or more and I like the summit's because the screen tips are polite it says some range and criteria range if you go look at the sum ifs it's the the screen tip argument names aren't is explicitly named all right so what are we adding for some range I'm highlighting here I'm going to use the keyboard shortcut ctrl shift down arrow to highlight down to the bottom and I need to lock that range so I'm going to hit the f4 key right locking means when I copy the formula down it will absolutely be locked on this range it's not a relative cell reference right so there's the sum range comma the criteria range well I need to highlight the entire department column ctrl shift down arrow and then the f4 key the f4 key locks it so as we copy the formula down those ranges do not move relatively comma and for criteria since we only have one it's a relative cell reference it's always going to look one cell to my left all right so we have our sum ifs ctrl enter and I can cut double click the little box in the corner is called the fill handle that little crosshair I like to call it the angry rabbit I can double click and send it down now when I come to the bottom and hit the f2 key notice those blue and green ranges are locked down there but that purple cell reference there moved relatively so as I copied the formula the cell reference is always looking directly one cell to my left right so formulas I don't know that took a little bit longer than doing a pivot table now the beauty of formulas is that they automatically update when your criteria changes so if you have DET är your data changes so if you have data or tyria that's regularly changing like I could was able to change this if I change this to 5,000 watch over that wretch over here when I hit enter it immediately updates pivot tables don't immediately update now it's easy in a pivot table you just right click refresh to update but some people absolutely want have dynamic data and so formulas for certain types of data analysis are the preferred solution why because formulas update immediately when any criteria or a raw data changes I'm going to control Z and get that back to the original number alright so some we can also do if we go over to formula 2 we can do counting with one criteria and we'll use the count if in this case I'm going to use the count if it just wants the range in that criteria so I'm gonna highlight this entire column right here control shift down now and then the f4 key come up and click on my criteria so it's not going to count all of them just accounting notice locked or absolute cell reference this is a relative cell reference so when I control enter and double click and send it down I can see very clearly my phone it'll work the blue ones locked and the green one is relative the count if counted with one criteria another example of a formula to do data analysis on formula 3 sheet a great example we saw this at the beginning of our video sometimes we want to compare less so here we have one list here and a second list here and there are some differences now this is a small data set here but you can imagine a gigantic data set how in the world to figure out if this particular item is over in this data set here so I'm checking to see is the name in list 2 also in list 1 well we're going to use the match function the match function is a lookup function that can look up something so I'm going to say hey match lookup babs comma within the lookup array is the range we're looking it up in so I'm going to highlight that and I'm going to hit the f4 key because I need to lock that now what the match will deliver it's looking this up and if it finds it it will tell you the relative position so babs is number 1 if down here when we copy the formula down it looks up Jeff T since it will not find it it will report n/a meaning not available all right so what does match do it reports the relative position if it's not in the list it reports n/a I'm going to take a comma there's three types of matches we're gonna do exact match because we're looking up exactly that word and you know we've put a zero though so the match type is exact match all right ready control-enter double click and send it down the numbers tell us that in fact the item was found in the other list now if our goal is to have a true and false and we don't want to see numbers and n a's we can change this but right now we could do we could sort this right all the numbers would come together all the na s would come together we immediately know have grouped together all the records that are in this data set and this data set again the numbers are what we are after but in some cases you want a true or a false so we're going to wrap put the match function inside a second function and it's called is number now this is cool because his number it's only job is to say true I found a number or false the thing is not a number so I put the match inside is number control enter and double click and send it down so instead of numbers and n A's I get trues and falses now what if we wanted to do something different now we are interested in the ones that are not so Jeff T is here but not in this list so we'll do the same thing match so I'm going to look up this item here comma within what range that there f4 to lock it comma zero close parentheses control enter double click and send it down now what are we interested in we're not interested in the numbers we're interested in the n/a so can you believe it there's an is an a function what does it say true if it's an N a false if it's anything else so control enter double click and send it down it's a great use a great example of how to use a formula do data analysis a common task that people every do every day do comparing to less alright so there was four three formula examples again the advantage in the sum ifs example is the immediacy of a formula it immediately updates if criteria or raw data updates the third example here however I don't know how to do that with a pivot table I'm comparing two lists so I went ahead and used a formula so formulas have a bunch of uses for data analysis now our last topic is how do we we're going to go over to GD one how do we get data into Excel now I'm going to go over to Windows Explorer and I you could see here I have an Access a text file with comma-separated data and I need to get that data into Excel so there's two ways if you have a c vs file I think that's it's CSV comma separated values I spelled out long enough to fix that it's it's on the master sheet you could see that huge formula there let's see come over here if I change it here I'm are separated values then when I come over to here it'll be okay all right so one way to do it is to actually open the file so I'm gonna go over to Windows Explorer and open it and you can see it's in a notepad here I'm gonna copy this and try and paste it over in Excel control V now it didn't quite work you could see it's all in one cell but notice that there's a smart tag so use text import wizard so I'm gonna go ahead and click on this now this is the way you should that you should go to data import data text and then it opens this up but that's that's a kind of a cool smart tag I don't remember what year in Excel hakeem it but now we can use this wizard right delimited means there's some character separating the data so that's what we want I'm gonna click Next now it's thinking that it should be a space that's why I got its messed up in our preview down here but no problem this is comma separated values and just like that we can see the preview works perfect click Next in this area we could format or even skip columns we don't need to it's looking good I'm going to click finish and just like that we have imported our data just to show you that you could do it a different way I'm going to go up to data and we have a bunch of different options under get external data click text I'm going to double click that and it opens up the same exact wizard and then click finish so there we have it and click OK final example what if we had some data in access we're going to use our data I get external data I'm going to click from axis I'm gonna have to find my axis database it's gonna say where do I want to put it I'm gonna say right there now actually that was not a good example of access because you could have many tables it would ask you if there were multiple tables which table you want to import that one only had one table so it only imported this one item the table comes in the data comes in as a table feature built in Excel feature with dynamic ranges and it automatically has a sorting and filtering at the top so it's relatively easy using the data get external data to get data from outside of Excel in various formats into Excel wow that was an epic video again you can click on the bottom for not only the link to download this workbook but for the table of content to jump to any section you want alright we'll see you next video

Channel: ExcelIsFun

Views: 1,882,476

Rating: 4.9015765 out of 5

Keywords: Excel, 2007, 2010, 2013, Highline, Community, College, Professional, Development, Day, 2012, Michael, Mike, excelisfun, Girvin, Text, Numbers, Dates, Partial, What, is, PivotTable?, Cross, Tabulation, Change, the, calculation, More, than, one, Value, Field, Settings, Grouping, Slicers, Column, Charts, from, Pivot, Tables, SUMIFS, COUNTIF, Compare, Two, Lists, ISNA, MATCH, Import, CSV, Access, file

Id: i5WiYh2jmG8

Length: 55min 13sec (3313 seconds)

Published: Fri Oct 19 2012