Microsoft Excel PivotTable and Dashboard QA with Kyle Pew - Office Newb

all right looks like we're live i just want to make sure that everything's set up properly i appreciate you all coming out and joining me looks like we got let's see we got about 115 people here oh numbers are changing so i appreciate you coming and joining me we're going to be getting into a little bit of excel here uh specifically about a week ago i sent out an announcement that we we were going to be doing this and i asked for some feedback and some questions that you all had and there was a common theme that came up and it dealt with excel pivot tables and dashboards and that's really we're going to focus our time here today and i'm going to sprinkle in some other stuff inside there that relates to those two topics so let's just make sure first looks like some of you found the chat window in there feel free to communicate with one another and communicate with me i'll try to keep my eye on it if you got a question or you want to point something out go ahead and jump in there and i'll i'll try to get to that we'll see what happens here so do me a favor i just want to make sure that the audio and video is sounding good so if you jump into the chat window really quick let me know if you can hear me i'll give it just a moment here uh winston the live session we're going to spend about an hour together i won't take everybody's full day from ya i know we're all busy sounds good all right cool nice good thank you ted elite travel journeys all right let's go ahead and get into it so i'm going to change my screen up here just a little bit let's go a little bit smaller for my face and i'll take up all the screen there all right there we go so here's what we're going to get into and i've provided an exercise file there's actually two exercise files that you can download you'll find them in the description the video description right below not just if you want to follow along or if you want to re-watch the video later which i'll be posting up onto the office new youtube channel so you can go back there and you can re-watch it you can pause it you do whatever you like there all right cool great thank you it looks like everybody can hear me there all right and it's going to be the exact same files that i'm going to be working with here before i get in and start to demonstrate creating excel pivot tables and ultimately a dashboard an interactive dashboard i want to show off what you're going to be seeing here and then we'll jump in and we'll create it from scratch so open in front of you i've got the completed version of what i'm going to go through and demonstrate this morning so inside of here you can see i've got a few charts i've got a line chart a pie chart a donut chart i've got one that you don't see a whole lot about i just don't think a lot of people are aware of it and that it's built into excel but i've got a map chart inside there as well i've got a bar chart so a few different types of charts but then i've also got buttons inside of here that users can can activate and they can interact with to be able to change the data within this dashboard so you can see here at the top i've got some years and i give as i give those buttons a click it'll go through and it'll update just about every piece of information on this dashboard on this worksheet you can see as i pick 1996 it updates not just the chart here but the chart on the left but also the pie charts as well as the little map down below i've got some buttons on the map i can click on the different states or regions that my data relates to and as i do that it updates everywhere within my little dashboard i can use some shortcut keys here these little boxes i'll just highlight them here this one here this one here those are both slicers that was a feature that was introduced in the 2010 edition of excel they brought them into pivot tables when 2013 was released excel or microsoft also introduced them into tables if you ever format a list as a table you now get slicers inside your tables as well so 2010 you got them for pivot tables 2013 you got them for tables as well just plain old tables but really they're just interactive filters and you can tie them to your pivot charts or your pivot tables so slicers there right down below the year slicer i've got two little buttons and these are just i went to the insert tab and i inserted some shapes and i put some text inside those shapes so they're not really truly buttons but i'm treating them as buttons and when i give them a click they'll change the chart here so i'd be either looking at sales data right now i'm looking at what that just org in there let's get everybody there's everybody's sales data and if i click on units now i'm looking at the total units for orders during a specific time period so i got some little buttons there that are actually tied to a little bit of vba code nothing big a couple of little lines of code that go through and just swap things out inside this dashboard based on which button i press so this is what i'm going to go through and demonstrate this little dashboard here this all started with the source data worksheet i got a tab down there for source data so it's just a plain old list it's got column headers along the top we got what do we got we got customer id company name bunch of dates some shipping address information and so on and then we've got several rows this isn't a huge list but we've got roughly 122 minus the headers there 122 records here that make up this source data now the source data is great we need that information but it's not very visual right for me if somebody were to throw this up instead of a presentation and say hey and start to talk about the data i'd slowly start to fall asleep it's it's nothing exciting it's it's actually pretty boring right looking at it's just data right which is good but it's just a bunch of text we want to create something more interactive something more eye-catching something that's really going to grab the audience's attention and bring them into the data so let's jump in and start to do this i'm gonna i'm gonna put this one away remember this is the completed one i'm gonna open up just a new copy there we go so again i'm offering this exercise file right here you can download it for those that weren't here earlier now you can download it inside the description area of this video and it is called customer orders dashboard zero one all right yeah is it in nikhil tableau right very very similar but now we're utilizing excel and just the built-in features there no additional software needed right if you're familiar with pivot tables or even if you're not we're going to see actually how quickly you can build these little interface elements and treat it more like a dashboard so we've got the data there i've already created a handful of worksheets i'm going to give you a little tip here when you're generating a dashboard you want to be able to create multiple pivot tables you want to be able to create charts you want to be able to create buttons all sorts of different types of charts and whatever else you want to add to that dashboard it's a good idea to create a worksheet for each individual element so i'm going to have one in there for a pie chart well i'm going to create its own sheet where i can manage that pie chart and its pivot table its structure on its own sheet rather than creating one sheet trying to throw everything into there right trying to manage that not the easiest thing to do so create individual sheets and then we can just borrow from those individual sheets into our dashboard and that's really all these sheets are here we got the dashboard that's the one that i'm going to build here but then we've got individual sheets for the different types of data that i want inside of my dashboard all right now something i did here if i actually open up that original the completed document i tried to spice it up a little bit i added a background image i created some boxes right like these guys right here to be able to hold the charts or hold the data just to give it a little more personality and then a little more eye-catching graphics now i actually created this inside of powerpoint i created all the boxes i put an image in the background and then i took a snapshot of it i used the snipping tool built into my windows system and i saved it and then i used it as a background element inside of excel so that's actually the first thing i'm going to do is i'm going to drop in my little background graphic as a background and then i will start to build the pieces that make up my dashboard all right so i'm going to go into my exercise file and i'm going to go to the dashboard tab here i'll go up to page layout and inside of page layout i'm going to go into page setup and here i'll go to background we'll give it just a moment here i'm going to go search for a file on my computer so i'll just browse there it is i called it bg skyline 0 1. now i've also included the powerpoint file that i used to create this background image you can find it again in the description below if you want to download it but once you open that up inside a powerpoint you can then go to file save as and save it as a jpeg that you can then use that jpeg image as a background element inside of excel so i'll grab bg skyline 0 1 and i'll insert that now something to be aware of my desktop resolution is is pretty big uh 1920 by i can't remember the exact 1920 by 1080 i gotta go back in and look um so it's actually pretty big if yours is quite smaller uh you might want to raise it up or you might want to shrink the image before you bring it into into excel but now i've got a background image there now i've got all the little grid lines inside there you might recall when we were looking at the completed version the gridlines weren't there so i can go up to my view tab here inside of excel view and towards the left i'll find show and i'm going to turn off grid lines now eventually i'm also going to turn off headings that would be like the abcd g headings along the top and the one two three numbers down the left i like to get rid of those when i'm working within a dashboard just makes it a cleaner environment but for right now i'm gonna leave them there until i'm all done but i will get rid of grid lines so i'll just deactivate that and all those little grid lines all right horizontal vertical lines are gone all right so the next thing i'm going to do here is i'm going to put a title along the top i just want it to say customer orders now right now we've got what three rows that make up that data source right there or that range right there so i'm just going to grab the number two right in between the two and the three and i'm just going to drag that down and tell us the size of that box there and maybe i'll shrink the a column i'm just trying to make it line up make my excel document line up with my little grid my little fancy graphics there so now i'm going to grab from b2 to s2 and on my home tab i'm going to use the merge and center command so i'll take all those cells merge them down into one and then i can put a title let's just say customer orders i'll format it you know that make it look good bump that font size up there's there's always something new to learn excel's a big application i'll change that font size impact or change the font as well i like impact nice bold font all right that's looking pretty good fits nicely inside that box now all right so first one i'm going to start out with as far as my data is concerned is i'm going to build the chart that shows off the sales for the time period for the different years for the different months so remember we got the source data inside of here we've got the order date from 97 to 98 it's a bit old data but we got some data so i'm going to take the this data here i'm going to create a pivot table that i can then eventually create a chart based off of now if you're not as familiar with pivot tables just kind of a quick introduction pivot tables allow you to take something like this right here a list it could be a handful of records it could be hundreds or thousands of records and you want to quickly summarize that data in a little nutshell and that's a little nutshell that's a pivot table give you the ability to summarize a list and that's what i'm going to do here my first pivot table i'm going to base it off this source data i'm going to use the order date as one data point and then i will use the order total column which is this m column here as the value that i want to summarize for the different dates so first step on my source data i'll click into my table and then going to go to my insert tab and i'm going to go to pivot table all right create pivot table so very important and here's a nice little tip for you you can see my source this is this is what i'm basing my pivot table off of it's a table range and it's looking at something called customer orders now this is very important i'll tell you why here customer orders is the name of this table in the background we do that i'm just going to cancel here for a moment we do that by clicking into your list going to home and using the format as table command and once it's formatted as a table you get table design tab and you've got the table name and i've just called it customer orders now why is that important well let me get back in there insert pivot table you create the pivot tables you create the charts you create the various pivot tables they're all pulling from this table called customer orders occasionally you'll see pivot tables that are based off of a range like a1 to d2000 something like that well the pivot table looks at that specific range what happens if somebody adds records to it or deletes records from it well the pivot table is looking at that range still then you have to update the pivot table's source what range is it looking at well by using a table you can then increase the size of the table add rows add columns and the pivot table will automatically still look at that same data source that same table name there's no update needed from you you still have to refresh the data but you don't have to update the data source okay because it's looking at the table instead so tables are great i get the question all the time why use a table why should i use the format as table command well this is one right here it's more dynamic it's going to update for you tables all right so select table it's already selected customer orders then i'm going to put this into an existing worksheet and i'll put this on the yearly sales pt i'm just going to drop it into like a4 all right so let's move this out of the way just so we can see it a little better all right so i'm just going to do two things this is going to be a simple little pivot table from my field lists i'm going to grab the order date and drop that into the row section now remember the whole purpose of a pivot table is to summarize data so as soon as i drop the order data into there it's already starting to group summarizing my data it's now grouped it by year you can see it's actually got three inside there's got year quarters and order date if i expand those out you see the quarters and then i got the months in there now for me personally and this is more of a preference and what is it that you actually want to display i don't care about the quarters so i'm just going to drag that out of there excel did it for me automatically but i'll drag it out drop it and now i've just got the years and the months inside there all right so that's the first part that was order day and set of rows so now how do i want to break the data down how do i want to now summarize it for those years and months well for this one i'm going to grab the order total and i'll drag that one into the value section and now i've got the data grouped by year and month and i've got the total amounts there right for those various groups or individual months very quick right this is one of the reasons i love pivot tables so much it's just drag and drop how quickly you can begin to summarize all that data right that was just over i think it was 122 records if my if i remember right it's got a bit of data in there but thousands or hundreds of thousands of records you can quickly summarize using a pivot table all right so again that was just order date down below and the order total which is being summed by default all right let's push this back so now i want a chart that's ultimately what i want on my pit on my dashboard as a chart so i'm just going to click into my pivot table i'll go up to the pivot table analyze tab and i'm going to go to pivot chart all right let's just do let's do a line chart i'm going to grab this one here we got a line chart with markers just those high or low points i want a nice little marker where the data actually lands within the chart and i'll hit ok all right there we go there's my nice little chart now remember earlier i said we want to break the data down right what is it that you're putting on the dashboard uh and you build various worksheets to organize yourself and make it easier for you to manage make edits to and so on now this chart is connected to the pivot table if anything changes here the chart updates as well but i'm going to take this chart with it selected i'm going to cut it out so i'm going to do control x on my keyboard just cut that out i'm going to go to my dashboard and i'm going to paste it in control v be like victor i'll paste it in there now i want this thing to sit somewhere along here this will be a work in progress as i'm adding other elements i'll be moving stuff around trying to get it to look good and fit in there just right now i'm gonna tweak the chart a little bit uh right now it's got that solid white background in there it's got some buttons on there that i really don't care about at this point so i'm gonna get rid of the chart title i'll just select that box i'll delete it i'm going to get rid of the legend we only got one data point out there so this really isn't helping me a whole lot so i'll select that hit the delete key these buttons these filters don't want them i'm going to right click on one of them and you'll find an option for hide all field buttons it's going to get rid of them all kind of opens up my chart makes it a little more presentable there all right so now i want to do a little bit of formatting make it look good make it blend in with the rest of my nice graphics here so with my chart selected i'm going to right click on it and i'm just going to go down into format chart and right now i got chart area selected this will open up my little format window just make sure my head's not in the way all right so here i'm going to go into fill this is the little paint bucket and i'm going to tell it no fill i don't want anything that i want to see the nice background i got there i think right there just just that by itself looks pretty good so i'm going to keep going i'm going to grab the data series just click on the line chart there someplace this will give me the format data series options now you can always right click as well go to format data series and here see it's a blue line but you know what i want to change the line let's change this line color to white let's uh bump up the width a little bit i like them a little bit thicker there that looks nice we've also got because i put the markers on the line chart those little dots anywhere your data points land and go to the marker option and let's say i don't want them to be blue i want them to stand out i'm going to make them orange but now let's see they got border maybe i'll put a white border on it this is all preferences it's all just formatting trying to make it look good and this is really where you're going to spend a lot of time as you build the dashboard trying to make it look good it's all about presentation you've got your data you're creating elements visual elements to show off that data but now you gotta you gotta make it look good so let's see what else can i do here i'll bump up i'm gonna go into marker options and i'm gonna bump up the size of that marker let's make them pretty big let's get them to stand out maybe that border the line could be a bit thicker but wrong one there we go border here let's bump that one up all right i'm going to click off my chart just so we can see it i think that looks pretty good we got a nice white line inside there some orange dots to highlight those areas and then maybe i'll change the fonts on these these uh y and x access points so i can just click on my y axis there i'll go to home and i'm just going to change the font color let's make it simple maybe make them bold just to get them to stand on a little bit more from that background bold all right i think that's looking pretty good it's got a border around the chart right now we can get rid of that let's say no line i've got the format chart area i selected my chart and i'll say no line i think that just looks a little bit cleaner we've already got a border on that graphic that i placed back there all right so there's one done right so the next one that i want to do here is this is supposed to be an interactive dashboard i want some way that the users can then start to filter the data that i place on this dashboard so when i was showing off the completed version earlier we had some little buttons just right over here on the right hand side or right hand side of that chart we got years i want to build a filter by the different years that are part of this chart or part of the data source so really simple to do i'll just click on the chart just with this selected i'm going to go up to my pivot chart analyze tab and inside of there i'm going to go into the filter section and i'll use insert slicer all right if you haven't used slicers before they're really just filters but they're connected to your pivot table so here i'll grab the order date oh actually i'm not going to do that one because this is based off of that pivot table you might recall when i dropped the order date into the row section it generated one called years so that's actually the one i want i'll grab years i'll hit okay here's my little slicer i can drop it over here i can make it look a little bit better resize that thing looks like we got some additional dates in there i don't need those if i right click on it i can go into slicer settings i'm actually going to do two things here one there's an option for height items with no data so i'm going to turn that on that'll get rid of these extra buttons here and then i'm also going to turn off this display header the display header although i like it it's nice it's this guy right here and i just don't think it looks as clean so i like to get rid of it so i'm going to turn on hide items with no data and i'll turn off the display header and i'll hit ok all right here's my nice little slicer resize that so now interactive if i click on a year this will then update my chart look at that very nice all right love it really simple to do i haven't done a whole lot yet right i've created a chart based off the data source tried to make it look semi-presentable and now i've got a nice little interactive button press slicer to be able to filter the results there now in the background notice i got 1997 selected if i go back to the yearly sales pt worksheet this guy here is also being filtered so the data that you're looking at your dashboard is connected to the original pivot table which is connected to the original data source it's all connected to this guy back here all these records if somebody updates this they add additional records there's more years in there for order date you refresh your pivot table and your dashboard updates as well all right so there's one of them what should we do next year i had to go back and look at the original one um let's try out the world map i think that's one that a lot of people will like so here i'm going to put in the little map that pulls the data from the data source inside the data source we've got some address information we've got the ship address we've got the city we've got the region in this case these are all usa records yes these are all usa records so they're all the states here right handful of states here so we've got some address information i can use that to my benefit to create a map chart now i'm going to hop over to my customer map tab remember i've created that one to organize myself currently it's empty we need to create the pivot table that's going to show off the data that we want to plot on that map so i'm going to go back to my source i'll click into my source doesn't matter where i'm then going to go to insert and i'm going to insert another pivot table all right same deal it's getting customer orders remember that table earlier i'm going to put it on an existing worksheet and we'll put it into the customer map i'll just drop it into a4 and i'll hit ok alright that's what we saw earlier right but now let's do i'm going to grab the region ship region and i'm going to drop that into the row section so now we've got all those those regions all those states then i'm going to grab the i just want to know the density of orders that are being placed within these different regions so i'm going to grab the customer id and i'll drop that into the value section now by default this is a text value it's automatically going to count it so you can see here i've got okay for alaska there were 10 orders during the whole period right from 96 to 98 you've got 10 orders for alaska california's got four idaho's got 31 and so on good information there all right let's put our field list back so now that's great information but i want it to be visual i want to show off this data right here in map format now take a look at this if i highlight this data that's what i want a5 to b12 i'm going to attempt and i say attempt because it's actually going to it's going to bark at me here i'm going to attempt to create a map so with that selected i'll go up to my pivot table analyze tab i'm going to go to pivot chart all right inside the insert chart window i'll go to map and uh what do we got field map you can't create this chart type with data inside of a pivot table what a bummer they can't do it for whatever reason limitations built inside of excel won't allow us to create this type of map and there's actually a few different types of map or excuse me a few different types of charts that you can't use based off of a pivot table for whatever limitation that might be there but here's a workaround so what i'm going to do so i'm going to grab that chunk of data that i want to include inside the map i'm going to copy it ctrl c i'll just hop up over here to the right someplace and i'm going to paste it in control v there's the data i want now with that highlighted we got what is that d5 to e12 i'm going to go to insert i'm going to go into my charts i'll go to map and i'm going to grab the field map there it is nice little chart right i could do that because it's not based off of the pivot table but this data right here this is static it's static data meaning that this is not going to change based off of the source data if somebody updates the source data then i have to go back in and update these numbers as well this will update right here though a5 to b12 that will update so now that the chart is selected it's created i'm going to right click on the chart i'm going to go to select data and i'm going to change the chart data range so i'm going to change this from d5 to e5 to point to this data i'm going to do a5 to b12 so we just kind of faked it here it wouldn't allow you to create the map based off the pivot table so we copy that data out create the map the chart and then we just change the chart source to point back to the pivot table data this way if the source data updates alaska gets more records oregon gets more records whatever happens the pivot table can then be refreshed which would then update the chart pretty cool so i'll hit okay so that now looks at that data there so same story that i did earlier i'm now going to cut this chart out and i'm going to go put it on my dashboard so with this selected control x we'll cut it out i'll go to my dashboard and i'm going to paste it in now there's some tweaks that i want to do just like the chart earlier that that i went through and kind of reformatted to make it more presentable i'm going to delete the title don't need it uh this is showing me all of the u.s all of the states here i i don't need them all it looks like for my particular data it's really just the data or just the states uh let's see what we've got washington oregon california idaho and so on that i want to include so i am going to get into the chart i'm going to get into the uh format options so i'll right click and i'll go into format whatever i got there in this case plot area so once i'm in there i need to change it so it only shows me the states that are related to my data so i'm going to go into my plot area options let's change that i'm going to go to series one that's really my data and i'm going to go to the series options so map area right now it's set to automatic it sees that oh well you got you got us us records in there you got united states records in there so i'm going to show you north america the united states so i'm going to change that from automatic to only regions with data so remember we've only got things like washington and oregon and idaho and montana and a few others in there so i'm just going to grab only regions with data and there we go look at that cut out the rest of those i don't care about them they don't fall within my restriction that my area so i just need the data that relates to mine so now i can clean it up i can get rid of the background like i did before i'll say no fill would do no line for the border i could change the colors maybe i'll just go to my chart design tab let's use some orange colors here blue and orange they look good together right all right now change the font color use the home tab change that to white maybe i'll make it bold and bump it up a little bit i'm looking at here hey nice good i'm looking at the comment section here so if there's another state added nice if there's another state added to the chart right or to the data source what's going to happen here remember our original chart looked at this data here now we pointed it over here and told it that we're looking at what is it a5 to b12 well if another state is added that becomes b13 or if for some reason a state is deleted then our range our pivot table range or the area that it takes up is modified so there is still some tweaking we need to do it's a work around to get the map chart to work for us but because the limitations built in no there might be another way we can do it and i can see here and think about it for a little while but so something to keep in mind and thank you for pointing that out good catch good catch all right so it's looking pretty good we got a nice little chart inside of there now watch this if i select a remember the slicer if i select a year let's say i say 96 my little line chart up here updated but my map didn't update i pick another year 98 nice little interactive element here but this chart didn't update here's one of the beauties of slicers this slicer right now is connected to this pivot chart which is connected to a pivot table on the yearly sales pt worksheet i also want this slicer to be connected to this chart right connected to the customer map pivot table now something i should do here before i go any further i'm going to go back to the yearly sales pt worksheet and i should go to the pivot table go to the pivot table analyze tab and give it a name you always want to name your content because at some point you're going to want to go in and reference the content by its name you want to be able to distinguish your different parts or your different elements of content so this one i'm going to rename this one i'll call it yearly sales i'm going to go to my customer map i'll click into there and i'm going to go to my pivot table analyze and i'll call this one customer map okay so name your contents and here's why i go back to my dashboard i need this slicer to also update this little map so if i right click on the slicer i'm going to go down into report connections all right report connections so currently we can see that it's already connected to the yearly sales that was default i had this one selected when i created the slicer but here's my customer map this is this guy right here i'm going to turn that one on i'll hit ok we saw a little bit of a flash there but now as i pick a year you'll see my chart updates there's a big difference you can see it in the values there for 96 we had a low of two orders we had a high of 23 and we got the range in between there we go nice interactive elements and updating your dashboard all right what do you think of that pretty cool all right let's see that was the little map ooh something else i'm going to do here so the customer map this data here this little pivot table i like the information and i think it can actually be beneficial if i put it next door to my little map so i'm actually going to go back to my customer map i'm going to click into my pivot table and i'm going to move it i'm going to move it to the new dashboard that i'm creating that new area inside there so i'm going to click on my pivot table i'm going to go to my analyze tab and here i'll go to where's that to move pivot table i'm going to tell it an existing worksheet but i'm going to put it on the dashboard and i'm going to put it somewhere over here i'm probably going to have to move it how many cells do i got maybe i got enough i'll put it right there i'll hit okay all right and there's my little pivot table now i can do some cleaning up here make it more presentable maybe i want to get rid of the grand total down there i don't care about the label up above it's all stuff you can modify through the formatting of the pivot table for right now just to make it look a little more tight in with the rest of the data i'll click into the pivot table design tab and i'm just going to change the formatting let's go let's go there yeah let's do that one there i don't know about the black at the top but i'll grab that one and i can change the column sizes get that to be a little bit more a little more stand out a little more presentable oh cutting off new mexico and and washington there maybe i'll shrink up the r column so i would want to get rid of the label maybe get rid of the grand total if we didn't want that rename the header there make it a little bit smaller a little more presentable uh and you could you could still tweak this is one of the reasons why i left the column headers and row headers still here we can still tweak the width there to try to get stuff to be more presentable for us and this chart i can always move that over if i need some additional space yeah that's good let's resize it a little bit as well i'll shrink it down all right like i said earlier this is where you'll spend a lot of the time as you build a dashboard the pivot tables they're quick drag and drop you create your summarized data you create your charts but then you're going to spend a lot of time formatting it trying to make it look good right making it presentable stuff's got to match right i'm using orange i like orange i like blue right i'll throw those colors in there and try to get them to to blend to be behind be able to mix them together all right something else i'm going to add here i want some more interactive elements so we've got the years buttons up here and i can hold down my control key and select multiple years ooh i didn't like that see how my my pivot table all of a sudden kind of blew up on me i resized everything there and then i got all wacky when i started picking different years here this guy changed size so to stop that from happening i don't want the pivot table to resize when it gets refreshed right or filtered so i'm going to right click on my pivot table i'll go into pivot table options and inside the options window looks like the layout and format tab down at the bottom we got uh autofit column widths on update i don't want that to happen so i'm going to turn that off i'll hit okay and now i gotta spend a moment here let's shrink this back up all right looks a little better i could do some more tweaking there maybe i'll move that over to give this some more space but whatever the case now if i start to pick from the slicer that pivot table should stay its normal size it's going to shrink in vertical because you're removing perhaps regions through your filters but the column width shouldn't change anymore again that was just a right click pivot table options and turn off the autofit column widths on updates that's a good one all right now that i've got a little bit more room down here at the bottom i can put another slicer i want something more interactive here i want to build a filter by the different regions so if i click into my pivot table we solve this earlier i can go to pivot table analyze and i can insert a slicer and here i'll insert the region ship region all right so now i'm just going to resize it make it a little more presentable it's pretty long right i need that thing to fit somewhere down here so i'm going to do once again a couple of things i'll right-click the slicer i'm going to go into settings and i'm going to turn off the display header and i don't think that there's anything with no data but i could turn that one off all right that looks a little bit better but it's still too big so with the slicer selected i'm going to go up to the slicer tab and here i've got an option for columns i'm going to bump up the number of columns i have two looks good maybe maybe i'll do that out of three let's make it even smaller shrink that up i can pull that back down and that looks pretty good all right so now as i pick a a region in here i'll pick alaska it updates my little chart there oh needs geographical data such as country or region let's try another one here sometimes sometimes the the the map chart doesn't pull the data in right away sometimes sometimes it deserves a little bit of a kick let's try getting more years here let's see if we oh there we go i got more of them in there oh i'm missing some i need to make that a little bit bigger maybe i can even just add additional columns we did three let's do four that looks good all right right now my little map is not picking up on the individual ones but when i grab them all then we're getting a nice complete map there again like i said sometimes it just needs a little bit of a kick it's got to make a connection to be able to find that data uh map it appropriately and then show it up inside of your dashboard here so sometimes if i close excel reopen make sure you save your work but close reopen that gives it a little bit of a kick and gets it working again for you but i'm going to put that off for right now but i've got a little interactive element but when i select a state such as alaska it's only trying to update the map at this point what i wanted to do is also update my chart here so we saw this one earlier if i right click on the slicer i'll go into the report connections and i'm going to make sure it's also connected to the yearly sales so now as i pick a state there's california and i'm getting california sales washington oregon and so on and it's updating my little pivot table here very cool interactive elements anytime you can add button presses on your dashboards like this right it's going to get your your users excited and it's going to create some more interactive elements that really drive your data right i don't need to see everything i'm only concerned with the specific region well hit the button and you'll get that region or i only need a specific year or specific years i can use the control key to select multiple elements inside the slicers don't need i want alaska idaho and oregon right i can see that data that's just control key on my keyboard you can also use your shift key to select from a start to an end that works as well but we're starting to get there nice little dashboard built off of a couple different pivot tables and we got some nice interactive elements to help us out as well now like i said formatting you're gonna spend a bit of time doing it you can also format the slicers with the slicer selected i can go to the slicer tab and i like the darker ones i wanted to stand out a little bit more let's just repeat that one that looks pretty good nice all right so we've got we've got just about 10 more minutes here i'm gonna send take a moment to look through the chat window here how to present all the years together it's cool so i can get in there i can select individual years just by utilizing the slicer but then you can use the control key to build the select multiple from that that slicer you can also i i turned this off earlier but if i right click on it i can get back into slicer settings and you can turn on the display header for me personally i just i don't think it looks as clean but it does have this option here i have to make that a little bit bigger now it does have this option right here this is your multi-select if i turn that on this will allow you to turn stuff off and turn it back on as well so if you like that one plus it's got the clear filter button so if i pick a year and then i decide that oh you know what i want all the years back then i can hit the clear filter button and it'll bring them all back for you that's especially helpful as your slicer list your filter list gets bigger and bigger i don't think of the states here the regions you know if i pick a pick a region and i want to get them all back again then the clear filter button would be nice in this case or you can use your controller shift key great question i just for me personally again it's a preference oh wrong one i uh i tend to turn it off but at the same time when you do that then you have to message to the users on how to work with these things it's not it's perhaps not as intuitive so you're kind of weighing the pros and cons here yeah no problem stu good question so stu is asking how would you update the source data without affecting the dashboard so good question the nice thing here is these are all pivot tables or based off of pivot tables if if i go into my source data and i add a record i update records whatever it is the pivot table looks at the whole data source because it's looking at the table name but there's two steps you gotta update the data and then you have to refresh the pivot tables if you don't refresh the pivot tables they have no clue right they're blind at that point and they won't update with the updates based on your source data it's not until you actually hit the refresh button so if i go back to my dashboard i can click into a pivot table or chart or whatever i got there on the analyze tab you'll have a refresh button so your refresh is an automatic and it's connected to the source but you've got to hit the refresh button in order to get that updated data it's a great question cool there's so there's so much in there well let's see it is it is amazing what excel could do there's so much that you can do inside of excel cool all right now let's do let's do one more here we've got oh what was the other one that we had in there and look back to the original one let's do that let's do the pie chart this one it's this is just going to kind of repeat the steps so we can go back through the motions again seeing how you create the pivot table how you create the chart and how you're going to bring it into your dashboard so it's really things that we've done but it's going to reinforce those those elements those key elements you know and i'm always going to look at this thing and think oh there's something else that we can do here and i can shrink that up maybe i'll make this chart a little bit bigger at some point you gotta say okay okay that's enough all right so let's see we got the shipping pt currently it's empty just like before but i'm gonna use this one to create the pivot table create the chart and then bring the chart into the dashboard so i'll go back to my source data i'm just going to do insert pivot table based off customer orders existing sheets let's put it in the pt all right shipping pt worksheet there we go all right don't need that right now so the fields so for this one i want to get a nice little pie chart that shows off some information about shipping results for these orders so i'm going to grab the ship via this is three values just the shipper names we got federal shipping speedy express united package and then i want to get the freight so this is the total freight for each of those shipping methods there we go right there all right really simple a couple hundred records a couple of drags of the mouse and you got a nice little summarized set of data so now i need something more visual so i'll click into that pivot table go to my analyze tab pivot chart and let's grab a pie chart i'll just grab the standard 2d flat pie chart all right so now i've got my chart created it's connected to the pivot table so i'm going to cut that out go to my dashboard and i'll paste it in all right it's going to go over here somewhere but now just like before i'll start to clean it up right oh i'll get rid of the the titles there i'll get rid of the buttons don't need the the chart title i'm going to right click this one i'll remove the filter buttons or the field buttons i'm going to move the legend it's taking up a bit of space over there get a little plus sign legend i like the legend i want to keep it around but i'll put it down here at the bottom and then i can start resizing and formatting my contents chart area let's say no fill no border just repeating those same steps that i've done before i do want some data labels i'm going to do more options and let's see my label options we got the value i think that looks good i can always reformat them let's get a currency inside there i don't care about the decimal places so maybe i'll zero that out and that's looking pretty good oh maybe i should resize that chart a little bit just to make it a little bit larger make it easier to read and change the font for the legend all stuff that we've done bump that up in size a little bit stretch that out that's looking pretty good they probably go a little bit bigger there cool so now if i pick a region if i pick a year it's going to update right now it's going to update my line chart and my little map but i also needed to update my pie chart so i'm going to right click my slicer report connections oh what did i say earlier i should rename that right now it's just called pivot table three so if i go back to shipping pt go to my pivot table pivot table analyze and we'll call this uh shipping info i think that's fine go back to my dashboard so once again right click the slicer report connections and i'll turn on the shipping info and i need to do that for both of my slicers if i pick a year it's updated everywhere but if i pick a region it's not updating my little pie chart there so i'm going to right click on that report connections and make sure i turn on the shipping info this is a neat one you can have all these slicers i've seen dashboards where they've got probably more slicers than they have charts or data out there just to give the users all the options all at their fingertips so you want to make sure you create those connections between the slicers and your various data sources your various charts or pivot tables so now it's updating looks like alaska only used one shipper federal shipping washington used a couple and so on all right let's get all of those there there we go all right so we've only we've only got just a few more minutes here i'm going to take a quick glance back at the uh the chat window there uh i'm going to make this available you can always go back to the youtube uh office new channel so you can go to youtube you can type in office noob and you'll find the office new channel and all the videos including the q and a session this one here that we're going through live right now and the one that i did a couple weeks ago are up there and available for you to view uh at your convenience a lot more fun good good i'm glad you you you're getting into excel and whatnot i appreciate it so uh what's the grace good question what's the yellow triangle at the top of the map where you click on year so when i click on a year or even one of the the regions here sometimes you get a big message box in there sometimes it's just a little triangle that shows up that's going to give you some information the map like when i want it to happen doesn't happen the map isn't always precise something you want to watch for but if i give that a click it'll tell you we plotted 70 of your locations from your row labels data with high confidence all right 75 percent of them so we've got a good representation of the data but the map tool it is connected to an online source you've got to have a connection there now it's got to be reliable connection and no interference inside there so that's what that one is let's see john let's see how do you remove the label on the pivot table in the dashboard good question so we've got some labels inside of there i even got the grand totals you can get rid of stuff actually a couple different ways if i click into the pivot table i can go to the design tab and i actually want to get rid of the grand total and go to grand totals here and i can turn that off don't want it get rid of it and preference again what is it that you're trying to message you're welcome grace now for the label i'm going to right click on the pivot table go to pivot table options and let's see this one is totals and filters can i look in the right spot nope excuse me display tab we're going to have show expand nope that's not it display field captions and filter drop downs so again i right clicked on the pivot table went into the pivot table options display tab and i'll turn off display field captions and filter drop downs and that'll get rid of that one this one stays around but you can rename it you can increase your your size move it around try to position it just right so it looks good inside there now cool all right and a lap it's it's available i'm i'm making it available in the office noob channel on youtube you can always re-watch it the nice thing is you can go back and re-watch any of the videos that i put up on youtube pause rewind i provide the exercise files so you can download them follow along with me so this has been awesome looks like we hit our time i appreciate you all coming out and joining me during this session hopefully you picked up something new something you can start to use inside of excel and become more productive and start to show off your data all right so i appreciate you coming out if you haven't already do me a favor subscribe to the channel you'll get updates about future videos and future q a sessions that i'll be offering on excel as well as other office applications so you're welcome i appreciate you guys you guys are awesome thanks a lot and i'll talk with you later bye-bye

Channel: OfficeNewb.com

Views: 6,425

Rating: 4.9358287 out of 5

Keywords:

Id: f3qqBlTtzJs

Length: 60min 21sec (3621 seconds)

Published: Wed Jun 24 2020