Excel Database Part 2 - How to Use Excel as a database - Excel 2010, 2013, 2016 tutorial

I'll go back to excel and I'll go back to the workbook that I was working with right over here good right now this is just normal data we would even call that raw data let me show you how to format as a table I'll pick on the home menu up top home now I'm going to come over here and I'll pick in the word format as a table see it right there and then you have all these different styles so you can choose whichever one that you like they all work the same way it's just a matter of how you like to see it I'm going to check on this one over here because then every other row will be that shade of color I'm going to click right there and I'm going to click on OK and now this is a formatted table first of all it's easier to read at least the format that I chose is easier to read that's one benefit of the format a table second of all notice how the um the pull Downs are there why I'm going to undo that is before I do the formatted table the pull Downs are not there let me just go ahead and redo it up here of course redo and apply the table now when you have the formatted table the pull Downs are automatically there at the top of the column which like I just showed you facilitates the sort in the filter so that's another benefit of using the table now let me show you a couple big benefits of using the table this is going to grow as your data grows so that it really facilitates large amounts of data so I'm going to come over here to cell 2 a211 and you can see right now it's blank uh let's say I want to have today's date in a Cell here's a keyboard shortcut for you I'll do control semicolon control semicolon will give us today's date and I am recording it on September 27 2017. so that is today's date now if you do control semicolon and it gives you a different date than what you're expecting it's because your computer clock needs to be reset but usually control semicolon will give you today's date now you can kind of see that the table extended to that row now I'm going to type in the word I'm going to type in the letter u and notice how that completes that for me so another benefit of the table is that it has the autocomplete feature when you're typing in your data that that's very helpful for data entry now I'm just tabbing over really I just need the US dollar amount let's say I type in 12 there now come over here and type in uh L for lunch and notice how it did it autocomplete there but I'm going to go to the next one everybody the next row I'll do uh control semicolon there's today's date I'll type now notice how the table really extended to that row so my point there is when you start to type in your data uh at the bottom of the table or in the right hand side it'll automatically expand it I'm going to type in you for United States good I'll type in 15 and I'll type in dinner D for dinner and the dinner all their completes like clearly you can see that the table expense extended to those new rows as we can see now let me show you one of my favorite features of these tables I'm going to come over here and I'll gonna sell G1 and let's say I'll type in something that's called sales tax so I'm recording this from Pennsylvania in the United States and in Pennsylvania we have a six percent sales tax so watch what I'm gonna do nurse first of all that the table expanded the column G so it really does expand when you add more rows or more columns now here's one of the great features of one of these tables it helps you with your formulas I'm going to type in the equal sign like I always wear the startup formula I'm going to click on that number right there which is usually cell E2 but now the table says it has the field name of US dollar amount so I didn't have to do anything different I'm just going to go with it now I'll type in times six percent now uh instead of saying E2 times six percent has the US dollar amount the field name but I didn't have to do anything different I did it the same way now when I hit the enter key watch column G notice how they all filled in that's really a great benefit of these tables it's going to save you a lot of time when I when I did that formula it automatically wanted to put that all the way down the column that's a great feature now let's go and format column G I'm gonna uh have a column G and of course I'll make a currency format good let's try that again I'm going to insert a new column a column h I'm going to right click in column H and pick on insert here I'll type in line total it'll be the column title uh clearly the table just expanded all the way down as you can to that column right so that's a nice feature I'm total good watch How We Do a formula again on this table let's say equals I'm going to click on the 2477 which is really so E2 but it says US dollar amount because it's one of those tables I'll type in a plus sign and then I'll click on the 149. and now it says the sales tax so the formula says US dollar amount plus the sales tax I'm going to hit the enter key and notice how it goes all the way down so that's a great benefit of these tables so if we go back to our slides now notice how this slide is showing you how to make your table at the beginning by saying format as table on the home menu now the next screen would allow the range should already be selected and you click on OK from there and then you can see how the next slide has what we call the formatted table uh let's keep on going now I'm going to skip a couple of these slides just in the interest of time we can come back to that one later that's not a big deal it shows you how to make a quick data entry form so I can come back to that one a little while all right now the next feature I want to show you to manage large amounts of data is called the vlookup so let's go back to our Excel workbook and I'm going to go back to a different workbook over here which is the vlookup examples all right now here I have sheet1 and I have a list of customers and you can see how sheet1 has the customer ID now I'm going to pick on sheet two notice how she too has a list of the orders and she too also has the customer ID field but notice how on sheet2 I don't have the company name I just have the customer ID well I'd like to share the company name here on this sheet so what we'll have to do is we'll take customer ID and look it up on the other sheet when we find the right one we'll have to return the company name back to this sheet that would be like a textbook example of when to use the vlookup the vlookup is really great when you have to look up and retrieve information from a different sheet let's see how it's going to work first of all I'd like to insert a new column in column C I'm going to right click on letter C and I'll pick in the word insert and now you can see that we have a new column so I'm going to call this column company name that's just the column heading that's not a big deal all right so let's start the vlookup formula now you can always type it in but I'm going to type in the equal sign like any other formula will have the equal sign and then follow my mouse right over here we're going to pick in this FX DFX allows you to do is open up this window that's called insert function this is such a powerful screen in Excel because it shows you a list of all your available functions you can search for them up here or you can find them by category and notice how there's many categories of the functions well I happen to know that the vlookup is under the lookup and reference category if you didn't know that you would search for it up here but anyway I'm going to pick in the lookup and reference now I want to scroll down on this list towards the bottom and we find that's called vlookup all right so under the lookup and reference category of the insert function window and it's called vlookup now you can always go to your help screens here and see what the help screens have to say but I'm going to show you how to use it right now I'm going to click on OK now this screen is called function arguments or function parameters the vlookup needs four pieces of information as you can see for function arguments the first one is the lookup value that means what are you trying to look up I'm trying to look up that customer ID so for the lookup value I'll pick on Cell B2 all right that's what I'm trying to look up the customer ID now I'm going to pick where's this table array the table array is also called the lookup table and many times it's on a different sheet like it is now it doesn't have to be on a different sheet but many times it is so follow my window I'm going to pick in this space where it says table array and I'm going to come down here and pick on sheet1 the bottom good now I'm going to highlight the entire column A all the way over to D and that'll be my lookup table now the lookup table can be many many columns across and many many rows down uh and it certainly could be on a different sheet like it is here the most important part about the lookup column with the lookup table is the First Column that's what has to match remember how we were looking for customer IDs and notice how you have customer IDs over in the first column it doesn't have to be column A it just worked that way this time it just has to be the first column of the table that you choose for the table array uh that's the one that has to match the first column now let's see how we're going to calculate the column index number what you do is you count from the left side of the table on the table this is column one two three and four I want the company name the company name is the second column on the table one two so for that reason for the column index number I'll type in two and that means when I find the appropriate Row in the lookup table based on the customer ID I want the second column of information which is going to be the company name now for the range lookup you almost always type in the word false it says to find an exact match type in false probably like eight out of ten times if not more you'll type in the word Foster to get an exact match if you're looking for a PO number you want to find that exact PO number if you're looking for a part number you want to find that exact part number so for the lookup value I have B2 which is the customer ID over here the table array is the one that's on the other sheet sometimes it could be on a different workbook here's the sheet one column A through column d the number two means when I find the appropriate row based on the customer ID I want the second column from the table which will be the company name and then I have the word false style there if you look over here it says false means you want to find an exact match I'll click on OK now look at the formula that it gives you after a while you can just type that in but the way we got to it we picked this FX over here within the insert functions now it looks like it returned a company name based on that customer ID so it took that customer ID looked it up on the other sheet found the proper one and then returned the second column which is the company name let's see if it found the right one notice how the customer ID says v i n e t and then we can see the company name now I'm going to go back to sheet1 these happen to be in alphabetical order and here's the one that we're looking for on row 86 v-i-n-e-t and it looks like it got the proper company name so it looks like the vlookup really worked now I'm going to go back to sheet2 now watch what I want to do I'm going to copy that all the way down here's another shortcut for you as promised I'm going to go ahead and click on this cell and get the black cross in the corner of that cell now some of you are used to using the black cross and dragging it all the way down there's a shortcut I can double click on that black cross when I double click it goes all the way down so that's a really good shortcut instead of having to drag it notice how it gave me a company name for each different customer ID based on the vlookup that's like a textbook example of when and how to use the vlookup notice how the vlookup says the word false right there that means we have to find an exact match let me show you what that means I'm going to come over here and I'll type in vvvvvv and notice now it says n a n a means not available or not found all right so uh it says that because I was looking for an exact match it didn't find an exact match so it says n a now let's fix that again I'm just going to undo up here and put that to the original value and now it does find the company name so take a look at the vlookup vlookup B2 is the cell that we're trying to look up which is right here now the second parameter of the vlookup usually that's where it's on a different sheet or maybe even a different workbook here it says sheet1 column A through column D you it's better to do the whole column it'll really work out much better for you the two means I'm going to get the second column from that table which is the company name and the false means you want to do an exact match so you can see how the vlookup would be very helpful to help you look up information on a different sheet let's go back to our slides again there's the vlookup just like we just did it and then you can see it would return for us the uh the company name based on the uh the the other sheet now if you look at the next slide here we have a sort remember how we already did this sort so we could just skip that one then this is another sort and now this is where we picked on the word sort on the data menu so we can sort by more than one field right so the the slide deck is really helpful uh go go back to the forearm where you're watching this video and they can send you the Powerpoint slides but those are available for you so um now notice how this is showing you where we did sort by more than one field now this screen this slide is showing you the filter see how to show you the location of the filter up there on the data menu and then the result of the filter is then we have the pull Downs on the top of each column and you can start to pick and choose them as you need to so notice how the slide deck has the sort and the filter which we already went over but that's a good reference for you uh here's how you did a um a filter on the number Fields remember how we did we picked on the US dollar amount pull down then we went over to number format by number filter and then you can see how we um we were able to get that done and this is the filter for the dates remember how we picked on the date field we picked on date filter and they have all those built-in dates um the date ranges and then you can see in this case uh oh next thing that we're going to do is a grade feature that's called a subtotal so let me go back to our data now you can see how the slide is talking about something that's called a subtotal I'd like to show you that one I'm going to go back to excel and in this case we'll pick on this workbook that's called countif and some if now I'm going to go to a different sheet here I'm going to pick in the sheet that's called Europe expense uh expenses or Europe expenses right okay now I want to show you something that's called a subtotal really great feature so again you're going to use that same kind of data it's important that the field names are up top and we have one consistent block of data to use the subtotal feature that's built right into Excel you have to sort your data first very important let's say I want to subtotals by expense type we'd have the sort by expense type first let's do that so remember how we did that I'll pick on one cell and the expense type column I'll pick in the data menu and I'll pick on the A to Z right there and now you can see how all the breakfast items are together and all the dinners are together and so on so it's very important to sort before we do this next step otherwise The Next Step doesn't work right I'm going to go ahead and click on one of those cells that's not blank and then here come the subtotals I'll pick in the data menu up top and I'm going to come over here and I'll pick in the word subtotal right there good now we get this window you see where it says at each change in that has to be the same field that you just stored it so for that reason I pick on expense type it's important that you sort by one of your columns and it's also important that you pick the same field over here that we sort it and that's why I chose expense type now I'm going to click on this pull down where it says use function then you have different mathematical calculations some count average minimum maximum product standard deviation and variance so we have some good ones in there I'll pick on some now it says add the subtotal to so in this case you can pick as many as you wanted to I'll pick on currency amount and then US dollar amount in fact I'm going to uncheck it where it says expense type because that's not a number field but we could have chosen as many as I wanted to here here's what I have at each change in expense type use the function sum and add the subtotal to currency amount and US dollar amount what are we asking for we're saying for each different expense type get us some of this column and get us some of this column the way we got to this window was we picked in the data menu up top and we came over here and picked in the word subtotal I'm going to click on OK let's see what we have notice now I have the breakfast total right that wasn't there two minutes ago if I scroll down now I have the dinner total entertainment gifts you have the total for each different category and if I go all the way down to the bottom we even have a grand total so in just a couple minutes we have subtotals for each expense type and a grand total as well let me show you a great feature of the subtotal so I'm going to scroll back to the top notice over here on the left this column was not there before the subtotals put that there for us I'm going to pick up this number two right there and watch for the curse now I just have the subtotals only you see so that is really helpful I'm going to pick up the number one and I just have the grand totals by itself I'll pick in the number two and you get the subtotals a pick of the number three and you get all the detail so that column is built right in when we do our subtotals this way you can easily collapse or expand your subtotals with the one two or three watch how we did the subtotals again such a great feature of course you have your your big list of data with the field names up top I'm going to pick on one cell that's not blank and also notice how the expense type column is sorted I'll pick in the word subtotal on the right side of the data menu right there now we're back to the screen we sort it by expense type and that's why we're going to choose expense type here it's important that you make that connection that's the same field that you sorted this time I'll pick on the pull down and I'll pick on count how many was in each category now look at my analysis okay to count those two columns that's fine but I'm going to uncheck it right there where it says replace current subtotal I'm going to uncheck it this way we'll see the sum and the count at the same time so this time I have at each change in expense type use the function count for those two columns and I unchecked it where it says replace current subtotal I'm going to click on OK notice now for each category we have the count and we have the total and that happened all the way down there's the count for dinner entertainment all the way down and then at the very bottom I have a grand count and a grand total so it really gives you some great results that's called the subtotals now let's say you want to remove the subtotals I'm going to pick on one sub that's not blank I'll pick in the word subtotal right there now one more thing I want to show you about this screen it says page break between your groups if you were to click on that and then print each different each different expense type will be on a separate page so that's a nice feature also when I'm all done with it I'll click on remove all and now we're back to normal as you can see a couple of really important steps to make that work you have to have this big list of data it's really important that the field names are up top it's continuous all the way down to the bottom with no black rose and to the bottom and then of course you want to uh sort by one of your columns we sort it by expense type once we did that it fell right into place so if we go back to our slides this slide is starting the subtotal feature out notice how it says you have to sort by one of our columns I will go to the next slide then from here we picked on data and then subtotal and you get that window that we talked about and then it starts to give you your subtotals as you can see now in the slides I did it by country but it would work for any field and then here's where I did the summary uh based on that one two and three over here in the corner I'm going to go to the next slide okay now the next slide is going to talk about some built-in formulas that help you do your subtotals as well so let's go back to excel so you can use the subtotal feature I just showed you and then you can also use some build in formulas notice over here I want to get the count for the breakfast items only then you can use a built-in formula that's called countif a regular account would count all of the data if I only want to count certain Ones based on the criteria then you use a countif let's see how it's going to work I'm going to type in equals type in count uh let's go back to the F action maybe it's easier to follow that way so I'll type in equals and I'm picking this FX right there good now uh in this case I'll just type in the word count to search for it [Music] and then when I picked in the word go you see the word countif right there I'm going to choose that and I'll click on OK count if now the countive has two parameters so for the range that's where all the data is so highlight the entire column f that's where I'm looking the criteria is what I'm looking for and that's why I have the word breakfast in cell I too so the range is column F and the criteria is I2 which is the word breakfast that's what I'm looking for I'm going to click on OK and it's going to give me a number here it says 16. so if we looked at column after 16 breakfast items look at the formula over here you could always type it in if you wanted to equals countif open parenthesis F colon F comma I2 close parentheses give me a count of all the breakfast items in column f now watch what we're going to do we're going to copy that right on down so we'll get the black cross and double click on the black cross again and now they go all the way down now just like that in a couple minutes now I have a count for each category so that's a great one to know about countif another one that's similar is sum if I want to get a sum of all the categories so once again I'll start the formula for the equal sign I'll pick on the FX now this time on this screen I'm picking the word sum right over here and I'll pick on go now notice when we have one that's called sum if and I'll click on OK here's a function arguments again so the range is still going to be column F that's where we're looking the criteria is still going to be the word breakfast right there which is I2 now the sum range means what column do we want to sum for the breakfast items I want to snap the numbers in column e so if the submarine shall had a column e for the range I have column f for the criteria have I2 which is the word breakfast and for the sum range I have column e that's the number I want to sum up for the breakfast items I'll click on OK and we should have some other kind of number we get 73.85 so in other words all the breakfast items totaled up to be 73.85 I'm gonna go get the black cross and double click there now I have the sum for all the categories can you see how useful these would be in large amounts of data the countif and the sum if really get you some nice results now of course there's one more that kind of goes along with it it's the average F I want to get the average of the categories type in equals once again I'll click on the FX and I'll type in the word average good and then we see one that's called average if I'll click on that and you can see it's going to be very similar to the sum if the range is still column f the criteria is still the word breakfast over here and the average range is still going to be column e so the average if is somewhere similar to the sum if now I want to take the average of the breakfast items I'll click on OK and then there's the average of the breakfast items of course look at the black cross and double click and now I have the average for the other categories now I would never hand it in like that of course I would like to format that to make it look better well we know about formatting probably I'm going to highlight from column K to column l now a lot of your formatting can be found under the home menu I'll click on home and come over here and instead of General we'll make it currency format and you can see that makes it look a whole lot better so three great functions in there count if sum if and average if they're really great for this large list of data so you can get specific results based on this categories uh let's see what the next slide says here you can see the countif and sum if and the average F oh look at this slide here's how I did that see how you can actually see the formulas within the cells let me just show you a quick way to do that since the slide has that I'll go back to the Excel spreadsheet which is over here now watch I'm going to pick in the view the formulas menu formulas now you come over here and you pick on show formulas and then notice how it really does expand to show you the actual formulas in the cells all right so sometimes it's important to see that now you can really see which cells have the formulas when you're done with that you're picking the word show formulas again on the formulas menu right there and then it goes back to normal as we can see so that was that one slide in the slide deck so so since that happened I might as well show you how to do that now next we can get into pivot tables now we we're going to have a whole session sometime in the future it's just on pivot tables but here let's kind of get started with the pivot table and show you some nice results that you can get pretty quickly I'll go back to my Excel spreadsheet in this case I'm going to go back to a workbook that's called pivot table examples it's just a different data but notice how it still has the feelings up top and it's still one continuous block of data so the same kind of data that we use for storage and filters would work on a pivot table as well a pivot table can summarize the data in all kinds of different ways it's a really powerful tool all right so we start with data like this these happen to be Airline transactions so each row is a different flight I'm going to pick on one cell that's not blank I'll pick up the insert menu up top insert and I'll pick in the word pivot table right there now usually on this screen we don't have to change anything usually has the uh the proper data range this one does basically it goes down to the next blank row and the next blank column if you wanted to pick a different range you can do that but usually that is the correct range now typically I'll put the pivot table on a new worksheet you could put it onto an existing worksheet but usually put it onto a new worksheet so you hardly ever have to change anything on this window this is not the screen where people get confused on with pivot tables I'm going to click on OK now this is the screen where people get confused and we're pivot tails let's see what's going on we have a lot going on here first of all we're on a different sheet my data is on sheet1 as you can see but we asked for a new sheet and that's where we're on sheet 8 or the next available sheet notice how you have your field names over here that's why I kept on saying it's so important to have your field names the pivot table viewer does use this you're going to move your mask down to these different sections so let's say I want to cross reference for each office for each Airline that's going to give you some big Clues a pickup office and drag it down here into the rows now notice how each row over here became a different office so the pivot table is really over here on the left hand side on the right hand side over here we're just building the pivot table each row became a different office because I moved the office into the rows right there now I'll pick up Airline and drag that over here into the columns now notice what happened now each column is a different Airline now they get some numbers in your pivot table you're going to move something into the value section so you'll usually use a number field in the value section I'll pick up the word total and drag it over here into the value section and now just like that after three steps I have a cross reference for each office for each Airline all right so notice how each office gives me a cross reference for each Airline each office gives me a row total over here each column gives me a column total at the bottom and I even have a grand total this is called a pivot table gives us some amazing results you know they have Road totals and column totals and a grand total now any field can be in the row section and any other field could be in the column section let's say I want the same report by destination watch what I'll do I'm going to pick up office and drag it back up to the fields now we just have a summary for each Airline now I'll pick up destination and drag it down here into the Rose now I have it broken down by destination by Airline so just move your Fields Back and Forth into these different sections and you're going to get some great results this is called a pivot table so any seal could be in the row section and the other field could be in the column section now what if you wanted to have multiple layers of information so look I'll pick up a pickup Commission and I'll drag it down here to the to the rows now the commission is going to say yes or no and now each destination is broken down into the yes or no's you see so you can have multiple layers of detail by adding more than one field into the row section or more than one field into the column section plus you could have as many as you wanted to now I'm going to pick up the word commission and drag it back to the top like I said sometime in the future we'll have a whole maybe a whole hour or 75 minutes on pivot tables just by itself but I'm just getting you started here now what if I wanted to do this by month or by quarter here's a really going I'm going to pick up the word Airline from The Columns and drag it back to the fields now I just have a summary by office as we can say so just move your Fields Back and Forth these different sections and you'll get some nice results in your pivot table this time I want to pick up to our flight date and drag flight date over here into the columns now when we use the date field it might break it down by date or by year or by uh by month now let's have this time it broke it down by year watch what I'm gonna do I'm going to right click on that year number 2013 right click and then I'll pick in the word group and then I can either put a date range into this window uh you can pick as many as you want to down here this time I'll just do it by a month and I'll click on OK notice now it's broken down by a month now we're getting some real results from the pivot table so watch again I have a date field specifically in the column section I'm going to right click you right click either on the date or on the month or on the year this time I'm going to right click on the uh January and I'll pick in the word group now your data has to be fairly consistent for you to be able to pick on the word group all of them have to be dates back on the other sheet if they weren't all dates it wouldn't let you group that so I'll pick another group now I want to pick month and year this time month and year I'll pick an okay and now we have the Year back and notice that we have the year total now when you do it on yours if you don't have the year total here's the way you can get it on I'm going to right click where it says 2014. you right click on one of your year numbers then I would pick in the word subtotal years I mean it's not going to uncheck it so now as I notice I don't have the year total anymore I'm going to right click where it says 2014 and I'll pick on subtotal years and now the years the year totals are back I mean look at the results that you really get with your pivot table so that's another way to a great way to analyze your data and uh hopefully you got some ideas just move your feels back and forth over to these sections and you can start to get some great pivot tables let me go back to the slides here 's how we got the pivot table started I have this big list of data you pick on one cell that's not blank then up here you pick on insert and then pivot table usually on this window you'll just click on OK now we're on the next screen notice how I'm demonstrating how you move maybe a field to the column section a field to the row section and then a field to the value section and that will give you your first pivot table then you can kind of move your fields around and experiment after that but that gets you started with your pivot tables then on the this is uh with the pivot table looks like after you move your fields around uh now the pivot table can have a chart and uh we're going to start talking about charts now so in this case I'm going to go back to the Excel spreadsheet and I'll go back to our pivot table right there what if you want to have a charge from this data now in this case I'm just going to summarize it by a year so watch what I'll do I'm going to right click where it says may and I'll click on group now I'll just pick year instead of month all right so I'll pick on the word year and now summarize by year all right let's see how we can make a chart of this I'll pick on one side that's not blank on the pivot table now when you pick up the pivot table you get two extra menus up top you get analyzed and you get design but you only get this when you click in your pivot table so I'll pick another analyze and I'm picking the word pivot chart and now you have all your chart types I'm just going to go for the column chart type and I'll pick in the first one and I'll click on OK here's a chart that comes from the pivot table so it's called a pivot chart I just made that bigger now it works like a pivot table and it works like a chart at the same time now in the pivot table I can come over here and do the filter see how the filter is built right in right there and right right over here I can do the filter on the pivot table but I can also filter on the chart so I'm going to click on the pull down where it says destination then I can pick the uh the desk the destinations right from there or I'm going to come over here and pick on the pull then where's this flight date then I can choose those from the chart as well so it works like any other chart but it's also uh works like a pivot table so we call it a pivot chart if you change the pivot chart the pivot table will change and if we change the pivot table the chart will change they're going to be tied to each other let's do some quick things with this chart I would probably like it to say chart title up there so let me show you how it works like any other chart I'm going to click on that chart now when you click on the chart you get some extra menus up top in this case I'm going to pick on the design menu now you come over here and you say add chart element and I'll pick in the word chart title and I'll say above Chart good notice how we have the chart title there now you would want to type over that so I'll type in um destinations let's say uh sales by destination by year sales by destination by year when I hit the enter key notice how the chart title now says that now another one that people like to see is something that's called a data label now there's a lot of data in this chart let's see if we can make a little bit bigger I'm just going to stretch it out using the sizing handles good now to do a data label I'll pick on the design menu I'll say add chart element data label and I like the one that's called outside end and notice how to put the numbers on each bar that's not so bad right so a lot of times we want the numbers and the bars that's called a data label as we can see so now we have a pretty nice chart maybe another feature I'd like to add to that chart is something over here a title that would be called the axis title on the left hand side so once again I'll say add chart element on the design menu I'll say data label and I'm sorry in this case I'm with the axis title I'll say primary vertical which is going to be on the side there and notice how it says access title on this side so in this case I'll type in dollars when I hit the enter key notice how it says dollars on the left this guy up here is called the title the chart title here's these are called data labels all right so the chart works like a pivot table also so for example if I come over here and pick on destination I can just pick and choose those so I'll just pick on Boston Cancun Chicago and so on now notice how the pivot table changed and the chart changed at the same time now I can just come back here and uh I'll go back to select all and now the pivot table changes in the chart changes what if I want the same chart and the same information by office watch we're just going to flip our Fields over here so pick up destination from the rows and drag it up pick up the word office and drag it down and now now we see the chart by office instead of destination Okay so you can really get some great results with the pivot table with the charts of course let's go back to our slides for a second so you can see here it was starting to make the pivot chart on the slides under the analyze menu and here's the chart that we just saw now let me show you another great feature of a pivot table it's called a slicer a slicer is a new way to do a filter on the pivot table all right so I'm going to go back to the Excel spreadsheet and we'll go back to our pivot table and our pivot chart now I'm going to move the chart up here because I know I'm going to need some extra room okay this should be good now I'm going to come over here and I'll pick on the pivot table right here good now we'll pick on the analyze menu then you pick one that's called insert slicer your slicers are made up of your fields and you can choose as many as you wanted to so I'll pick on tickets and commission just as an example I could have chosen more than that but those would make the point that I'm trying to make these are slicer Windows now you can move these you can resize them whatever you want to do so I'm going to move these over here so it's a new way to do a filter except it's very visual so I'll pick on four tickets notice how all the data changed again because now it's just showing the records with four tickets and I'll pick on yes over here and the data changed again because now it says four tickets with yes you see so now I can get the exact request that I'm looking for you combine that with the filters that are already on the pivot table in the chart and really you can get the exact records that you're looking for this is pretty powerful now to get more than one from each column I'm going to hold down the control key so I'm holding that control key now pick on two three and four I can pick more than one because I'm holding a control key to lift my finger good now I'll pick on uh no and yes by holding a control key here look at this I'm getting instant results get the exact records that we're looking for it's very visual people will love this back at work all right so really um the the slicer is a great way to analyze your data as well hey man Excel 2010 let me show you one more slicer that came in Excel 2013 we'll start wrapping up this one's called a timeline so look look I'll pick on the pivot table over here I'll pick on analyze and here I'll pick on uh insert timeline a timeline is based on a date field so we could do a date range so I'll pick on flight date and I'll pick on OK this is a timeline I'm going to move it right over here now notice how it's organized by month right now I'm going to pick on the word month and I'll pick on the word year now it's organized by year I'm going to scroll over here and I'll just pick on the blue space blue 2014 and notice how it just showing me 2014. or I can stretch that over to 2015. and you can see how that's changing the data as well all right so just amazing amazing results that we can get with the pivot table and the slicer and the chart and the timeline this is what Microsoft is calling a dashboard we have these components working together the way we got all these windows open with the pivot table is you click on the pivot table and then we picked on the word analyze here's insert slicer insert timeline and the pivot chart