How to build a Well-Designed and Interactive Excel Dashboard with Pivot Tables and Pivot Charts

hi this is Alex from the office lab and today I want to show you how to create this beautiful and interactive bass book in Microsoft Excel in five easy steps [Music] now to give you a general idea of what you can do with this dashboard this - what allows you to filter your data by different perspectives for example by years and as you can see all the charts in the dashboard above are updated immediately according to the selection you made another pretty cool thing is that these filters down here are working together so once you made a selection in one filter all the other filters only display two data points that are still available with this previous selection made so I think this is a pretty helpful - to display whatever data you're interested in in my case I chose some say it's better I generated randomly just some dummy data but you can choose whatever data you like and whatever filter you like so let's get right into it and see how this is both step by step so the first thing you need is data for this example I generated some hypothetical say it's data randomly but you can use whatever data you like as soon as they have this certain shape like my data so the different dimensions have to be in the header row and your entries have to be beneath that and in my case I guess I have about 2,000 entries in here like with order ID a day a customer ID is some generic customer name company name then a salesperson the region the salesperson works in some generic item names and the price quantity for this item for this order in this case and the resulting revenue so in the next step we're gonna build our five pivot tables that are required to create our dynamic charts for the dashboard the first chart we want to create is the sales trend line chart so let's go to the new tab and insert a pivot table now we have to select a certain range of data so we go back to the Saints data and select a whole range exactly like this and now for the pivot table we have to choose a dimension for the rows and as we won't have a time series which you state and now you can see it's summed up also on a quarter basis we don't want that so we delete that field and as you want to have a look at revenue this is what we drop down into the values field yes exactly and now we're able to create a pivot chart just with one click and since we want to have a line chart we're gonna change the design of this chart and change chart type into a line chart with some beautiful dots so that's it for this first chart now the second chart we're gonna create is the sales by region chart and for this we want to use the Maps chart function which is a pretty cool feature in Microsoft Excel and PowerPoint so we've got to create another pivot chart by selecting the whole range and then we drop the region fields into the columns area and the revenue feeds into the values area just like before there we go and for some reason we are not able to create the Maps chart directly from the pivot table so we have to copy these values for the state names we can leave them as they are but for the values as we want to have a dynamic later on we have to create a reference to the pivot table cell for all four of these cells California New Mexico and Texas Texas Texas there we go and now with this copied and adjust the table we can always change that to revenue yes now we can create our Maps chart let's see there it is so it automatically reads what states or what countries you inserted so that's pretty cool and it's even cooler when it gets dynamic so let's head on to the next one let's continue with the sales by employee chart for this we create just another pivot table just like we did before with the same data so we select the same range like before from the sales data tab and what we want to do now is compare the performance of different sales persons but only between these persons but also over time so that we can see if the performance dropped or they improved and since revenue is still the main measure of performance we drop that field into the valleys area just like before and now we are able to create our pivot chart with just one click there we go there we have our pivot chart and as you see right now it's pretty colorful looking not so nice in my opinion but we got a deal with that later on when we start to design our dashboard so for our fourth Chara we want to have a pie chart that visualizes the share of each item in the total revenue so we create just another pivot table from all the data and this time we drop the item field into the rows area and the revenue field into the values area and that we can just use this one click option for the pivot chart and there we go and as we want to have a pie chart we have to change the chart type and we choose that donut I think it's the most beautiful pie chart by far and now we can continue with the last chart for our last chart we want to have our customer revenues displayed in descending order in a horizontal bar chart so for this we just have to create another pivot table and use the customer name field for the rows area and the revenue field for the values area and then we have to sort our pivot table in an ascending order by sum of revenue let's say there we go sum of revenue right because when we create that bar chart it will be the other way around you will see it here we have just a normal a vertical bar chart basically a column chart and there we go switch it to bar chart and as you can see and that's exactly the result we wanted and so we just finished the creation of all pivot tables and charts so now we can continue with the next part which is the most fun part I guess all right so let's start building the dashboard I'm really excited to show you how to do this so the first thing we're gonna do is getting rid of these gridlines because they're going to look so nice for our dashboard and now we need a nice background and I choose this picture I hope you like it because I really like it and now it's start by inserting our header line so we choose a text box and we have to choose a form I really like Helvetica light because I think for this purpose it's like the most beautiful form you could think of now let's just call it performance dashboard all right and let's make it a bit bigger 45 should work yeah I guess it looks nice so now let's delete the filling and the line making white perfect and now we want to have a little sup header so we put this up here and create a line to separate those two had us from each other let's create this line and I don't think it's perfectly horizontal at this point so let's make it white and then adjust the height to zero there we go zero and now we can insert our sub header which we just gonna call let's put it there but you're just gonna call let's think of something the office slap enterprise in corporation just some random name but I think it should look nice okay it's may like make it a little bit smaller 28 in the center alright and now let's just reorder these elements so that they look good together let's make this a bit shorter and put it in the center all right perfect so now we can start building the key elements of our dashboard which are basically some simple rectangles that we need to create our so-called tile design this is what I want to do having different tires with the charts in it now let's make this black with a black filling and the line and then the key element of my dashboard design is working with the transparency of these tiles because I think this looks really beautiful and in this case I guess 60 should just work fine this looks good to me and now I just have to duplicate this one tile and adjust the shapes for the other four tiles to create the basic structure of my dashboard so let's just resize this and copy it two times there we go one time let's make this a little bit bigger three or this and copy it next day we have to make this bigger as well so that they more or less have the same size and now I guess we have to readjust the upper one like this yeah Gazza looks nice okay and now we have to create the last of the tiles which will be on the right side and go from top to bottom like this yeah okay perfect and now let's just move all these tiles together more or less into the center manually okay there we go we have to keep in mind that we still have to keep some space below these elements because later on we want to insert some filters below there now let's continue with creating the titles for our tiles so let's make it a bit smaller 20 it should be a good size and for this first time you want to insert our time serious later so there's gonna be our sales trend tile now let's just position that and then just copy it to the next tile which will be our sales by region ties so there we're going to insert our nice-looking Maps chart later on let's rename this sales by region yeah we have to make it a left alignment little bit bigger perfect now we can take this one for the next tile which is the sales by employee tile so let's rename this one sales by employee a bit bigger copy it to the fourth tile which is the item share tile oh nice pie chart donut chart and last but not least we have our customer revenue tile so called customer revenue and there we go so let's insert some icons okay so where are the icons actually they should be right next to the shapes up there I have no idea so I guess I just have to copy the icons I need from another file so it just give me a moment ah there we go beautiful I can't I really love icons I think everybody should use icons so yeah I think that makes like everything more beautiful as long as you use them the right way okay let's so let's place them away should be and perfect so the next step is to insert the charts we created earlier into the dashboard and adjust their design in order to make them fit well with the general dashboard design so let's start with the first chart which is the sales trend chart let's simply copy it from this tab to the dashboard and let's be honest right now this chart just looks like utter trash so we have to do some adjustments to make that look better so let's start by setting this to nofo and change the color of the chart to white and then we're gonna smooth the line which is a pretty cool feature in my opinion makes it look much better now let's get rid of all this unnecessary stuff yes and now let's just set the color the general color of this to watch and I we just have to oh of course we want to get rid of this border line right here don't want to have this in there and now we just have to adjust the size of the chart a little bit so that it looks nice and now this should be good enough for this first chart so let's continue with the second shot which is our beautiful maps chart for the sales by region tie let's just copy and paste it insert it and bear not that many adjustments we have to make some general stuff delete this title [Music] resize it and just move it down whoops no that's not what you want you to move want you to move the chart let's do it and just adjust the size to fit into the tile make this wide and that's pretty much it I guess let's move on to the third chart the sales by employee chart copy it paste it and the general adjustments to make it fit into this tile and I think this chart is way too colorful in my eyes so there's a bit confusing might be subjective but I think with all these colors it's not really pleasant to look at it so let's change the colors to some shades of blue and of green first of all because I really like these two colors are pink their weight mobilising to look at them some for example yellow or red and the second reason is that we already have this different shades of blue on the left side so I think that fits very well together it's not that much contrasting in the chart so there we go yeah it was already white let's make this white and this one as well and yeah let's make it a bit bigger so that you it's better to read no not that big it should be fine let's adjust this one as well to a size of 10 and yes maybe then we should also adjust these XS titles axis descriptions - chan yeah guess that's good so for our item share chart let's just copy that and for this one we're going to change the colors just to these different shades of green we already have in the Saints by employee chart so that now we have on the left with the sales by region shop we have these shades of blue in the middle we have the different shades of blue and green for the item sheet we have different shades of green and then it's kind of like symmetric so it's like this one this one [Music] yeah I think that looks pretty good now we just have to readjust the size to bring it more to the center of this tile and so now let's continue with the customer revenue chart copy that one pasted and just do with general adjustments we already did for the for the other ones make it fit in there yeah like this get rid of these lines just this to ten and set it to no Phil no line of course and just down to white and size 10 and there already a 10 yes and maybe we should think about yeah closing these gaps a little bit so make this gap with all of us smaller of course we change the color to white right it's also beautiful so wide blue and green against these colors work really fine together let's just adjust this one bit yeah that should be good now one thing we have to do is to format these numbers into some numbers format without any decimal places and let's change this major unit number I guess we have to change this Meiji unit number right or the minor one I'm not sure now let's do a major one 240 maybe yeah that's good and now of course we have to adjust the number format for all the charts for this one as well in equivalent way that's good for this one okay there's no option let's just leave it that way for now and yes I guess we have just finished the general chart design for this dashboard the last step of this tutorial is about implementing the filter function so that we will be able to filter by different dimensions to do this we first have to click on one of the charts let's take the sales thread chart and then we have to insert so called slicer so select slicer and then we can choose what dimensions we want to filter by let's choose year's region salesperson and item for my case and now let's put these slices beneath all the other elements of the dashboard so I can show you so now with these slices we are able to update the sales drench I'll only to say this wrench on right now because these slices are only connected to this chart in order to update all the other jobs as well we have to let's just quickly reset those photos we have to do a right click on this and go to report connections and then we can choose all the pivot tables respectively charts that are connected to these shop other charts to all the charts up front here so we have to do this for every slicer let's do this for sales person dimension slicer and for the last one as well and once we finished doing that we're now able to select some filter here and all the charts will be updated according to that filter according to that selection you made which is pretty cool works perfectly let's reset - there's one voices well an item should work just as fine perfect let's reset this and now we just have to integrate them into the dashboard so let's copy this rectangle from the tile up here move it down and just adjust it in size yeah that should be fine and now we want to change the color respectively the transparency back to zero in order to make it stand out from the rest of the dashboard so it doesn't look exactly the same and that the user knows there's some functionality down here and that we just named it filter so yes yes that should work just fine and now of course we need an icon because icons are just awesome let's place it here what we have to do now is to place the slices in there and of course adjust Verisign in order to fit into this filters area down there so for this I just created a custom design before and let's bring it to the front what we're going to do now is we change the number of columns for the three left sliders to four so that we have four elements next to each other horizontally because that's that makes it much better fit into our filter section and now we just have to reorder them in a more or less symmetric way and as you can see right now in the region China Mexico wasn't totally visible now it's visible so we have to make sure that all the elements are completely ways abaut now let's move that a little bit up here and then we have two justice eyes for the sails paresis finder so that all the names of wiz Abell as well perfect let's move you down here and now for the item slide so we choose three columns because we have five elements in there and in my opinion it wouldn't look too nice if we have only one element in the second row so let's do it this way maybe make it a little bit bigger like this yes and maybe we move all the elements all the slices oops a bit up but I think that's it so we just finished this beautiful - for design so eventually let's have a look at the final result in action and if you liked this video don't forget to hit that subscribe button leave a like comment check out the other videos in my channel and see you soon until then have a great time [Music] [Music] [Music]

Channel: The Office Lab

Views: 1,429,991

Rating: 4.9714065 out of 5

Keywords: excel, excel dashboard, pivot table, pivot chart, dashboard, how to build interactive excel dashboard, interactive dashboard, build dashboard, excel tips, excel tutorial, excel dashboard tutorial, excel pivot table tutorial, excel pivot chart tutorial, excel dashboard template, kpi dashboard, create dashboard, how to pivot table, pivot tables, pivot table excel, excel dashboard templates, excel dashboards and reports, excel dashboard design, the office lab

Id: 20zDV9MNE0s

Length: 30min 51sec (1851 seconds)

Published: Sat Aug 25 2018