Building Dynamic Dashboards with Pivot Tables & Pivot Charts

A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
Rating: 4.6 out of 5Instructor rating
30 courses
812,040 students
Building Dynamic Dashboards with Pivot Tables & Pivot Charts

Lecture description

In this demo, we'll practice building a dynamic Excel dashboard using Pivot Charts, slicers and timelines.

Learn more from the full course

Microsoft Excel - Data Analysis with Excel Pivot Tables

Master Excel pivots & learn data analysis w/ advanced Excel cases from a top Excel & business intelligence instructor

06:32:18 of on-demand video • Updated August 2022

Take your data analysis skills from ZERO to PRO with Excel Pivot Tables
Learn how to use Pivot Tables and Pivot Charts to streamline and absolutely revolutionize your workflow in Excel
Master unique Pivot Table tips, tools and case studies that you won't find in ANY other course, guaranteed
Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
Test your skills with 10 real-world Pivot Table case studies (weather, shark attacks, wine tastings, burrito ratings, and more!)
English [Auto]
All right. So we've covered quite a bit in this section. We've inserted a number of different pivot table views, built out different types of charts and practiced adding slicers and timelines. And that's all great. But things do still look a little bit messy and disorganized here. So in this lecture, I'd like to demonstrate how we can take all of these elements that we just learned and combine them into a clean, dynamic and powerful user facing dashboard. Now, keep in mind that there are a number of different ways to approach dashboard design, and there really are no right or wrong answers. So what I'm going to do is show you some of my personal tips and tricks, and you can either follow along exactly or use them as a direction to help create your own modified approach. So in this case, my first step will be to adjust the properties of all of my elements, all of the charts, plus my slicer and timeline. And I can do this in one fell swoop by control, selecting all of them at the same time. And then once I have all of them selected, I can right click on the edge of one of the shapes and drill into size and properties. And when I drill down into properties, I can go ahead and select that second option for move but don't size with cells. And again, the reason I like this option is that now I can be really deliberate about how I align these objects without having to worry about things shifting around each time a column arose adjusted and then next I can actually hide my raw pivot table views so I can select columns A through E which contains all of those pivot table views and right click to hide them. Because in this case I don't want my actual source pivots to be visible. I want my focus to be entirely on the visualizations, and I don't want users to accidentally change or edit those pivot table views. Keep in mind, you could also use workbook protection settings to accomplish a similar thing, but typically what I'll do is make a separate tab available with a pivot table built out that users can dig into if they want to slice and dice and explore that data any further. But in this case, I want this view to be very visually focused and very clean without the actual raw data views. So next up, I'll jump into my view menu and just deselect the grid line option. And that just makes this look less like a spreadsheet and more like a dashboard. And then from here I can just start dragging and moving different elements to come up with a rough layout of how all of these pieces will fit together. So in this case, I kind of want everything in one consolidated view. I don't want the user to have to scroll much, so let's put my controls or my filters in the form of slicers and timelines, kind of over to the left, drag the chart elements to the right. I'm going to resize this column chart a bit. Let's pull my stacked bar up here. And in this case, my stacked bar and my donut are telling kind of the same story. It's just that my stack bar is kind of adding a layer of of additional insights. So let's get rid of the donut chart, simply delete it and then drag my stacked bar right up here to the right of my columns. I'm going to get rid of the field list just so we have more room to work with. And then that just leaves me with my area chart, which I kind of want to keep as a central focus of this dashboard. So I'll actually enlarge it a bit, make it as wide as I can, and then I'll pull the timeline. I want to position the timeline kind of right beneath that area chart, since that's where you see the changes most directly taking place. Even though again, this timeline is tied to all three of my visuals here. And then to fill this little slot here, let's actually insert a second slicer. So pivot chart tools, insert slicer, let's add a slicer for country here as well. So I'll position it kind of roughly beneath my first slicer and then kind of just stretch it down so that we have this nice, compact, rectangular shape here. And now one last nice little touch that I'd recommend is creating some sort of a header or a title for the dashboard so that users know what they're looking at. In this case, I'm going to try to select everything using Shift Select until I have all of my elements and I'm just going to drag everything down a bit so that I create a little bit of room for a title up here. So to do that, I'll insert a new shape, just a rectangle and click and kind of drag it out and place and I'll make this IMDB. Movie Dashboard in bold. Go into my home menu center that text and make it quite a bit larger and for the shape properties itself. I don't really like the blue, so I'm going to give it a dark gray fill with no line. So there's my kind of header title and now that I have my layout in place, it's as simple as going through piece by piece to lock in the alignments and make any final formatting tweaks that I need to. Again, this is going to take a few minutes so you can follow along if you'd like. But the point is to demonstrate some of the tweaks that I would personally make for a dashboard like this. So I'm going to just kind of go piece by piece and do some polishing and alignment of these elements. So starting with my column chart, there are some pieces I don't really need. I don't need the title here so I can delete the title. I can delete the legend and in my pivot chart, analyze tools. It can hide those field buttons as well. Now let's just make the Axis title bold. My series Labels Bold Right click the series itself. I can add data labels here as well. Let's make these italic control I and then right click the actual series and I'm just going to change the fill from this light blue to kind of this darker grayish blue color. Here and in my series Options, I don't want quite as much of a gap between my series, so I'm going to make that adjustment as well. And there we go. Moving on to our stacked bar chart, I don't really like the positioning of this legend, so I'm going to move the legend to the top of our chart, kind of drag it out to resize it a bit. I'm going to make my vertical axis labels bold, and I do need an axis title here on the horizontal to really tell the user what metric or what numbers they're looking at. So pivot chart tools, design, add chart element, axis titles, primarily horizontal. And in this case, just like the column chart, we're looking at the number of titles so I can make that bold, drag the edge to resize it, move it anywhere I choose. Now I'm happy with my stacked bar chart. So moving on to area, this one is pretty close. I do need to go into design and add a vertical axis title for this one because in this case we're not looking at the number of titles like the first two charts. We're looking at gross revenue here. So again, add that title, make it bold. And now one other tweak that I want to make to the area chart is with the actual value labels. So right now we're looking at gross revenue, which includes numbers in the billions. So Excel by default is applying a general number format, which is kind of translating it into this scientific notation, which is very tough to read, especially for a casual user. So a little pro tip here in a right click and format, the axis all the way in the bottom of our axis options. You'll have this number drill down and from here you can change the category. So you've got custom number formats like you've seen before, currency, date, percentage, etc.. In this case, I'm actually going to show you a really cool custom number format to turn these into shorthand billion numbers. And so what you want to do is type this exact code here in the format code line, it's a dollar sign, hash mark, comma, hash, hash, zero, three consecutive commas and then a, B and quotes. And what that does is it shrinks those billions numbers with all of those zeros down to a shorthand version and A pens a label B at the end. So now you can read it as ten B, nine B, eight B or 10 billion, 9 billion, 8 billion and so on and so forth. And if you want to apply the same trick for millions, for instance, instead of three commas, use two and change the B to an m If you want to use this custom format for thousands, then only use one comma and change the B or the M to a K. So this is a really helpful custom number format that in our case really helps readability quite a bit. So I can close that format pane, change the format of some of these slicers. I kind of like this option with the bluish gray shading, so I'm going to make that change to both of my slicers, and now I'm almost there. One thing I do want to do is kind of be deliberate about how I'm aligning things, so. To select the edge of my slicer, select the edge of this slicer and select this top label. Basically everything where I want to align the left edge to make this nice and clean. From there, I can go into drawing tools. Align, align left. And then I can do this in a number of different places as well. I can select these three elements drawing tools, a line top, and I can kind of just. Drag pieces as well. If we want to distribute these three elements equally horizontally can use a different align option, which is distribute horizontally and that creates equal spacing between them. So obviously not totally critical stuff, but having a dashboard that's clean and consistent and buttoned up really does go a long way. So almost there. And now we've got a pretty nice layout. Everything's kind of in this one clean view, everything's aligning nicely. We've got all of our chart elements in place. And now one thing that I had forgotten to do is this new slicer here for country. Let's go ahead into tools, port connections, make sure that's tied to all of our visuals as well. So now both our slicers and our timeline will drive all of the charts in the dashboard. So, for instance, let's drag the timeline out to 2015. There we go. And then again, we can enable multi select here. So removing gee, now we're only looking at PG, PG 13 and R removing PG allows us to only look at PG 13 and R and so on and so forth. So really cool, totally dynamic dashboard that now looks really tight and really polished. So now that I'm ready to share this with the world, I want to make one final touch, which is to go to page layout and customize some of the page setup and print options so that if users want to print out a snapshot of this dashboard, it will automatically scale really nicely onto one page. So for margins, I'm going to choose narrow margins. I want my orientation here to be landscaped since it's wider than it is tall. And then to set the print area, I can just make a selection that contains the entire workbook and all of the elements of my dashboard. And then once I have the selection in place, I can drop down into print area, set print area. Now last but not least in this scale to fit pane, I can click this little blow out here and say Fit this to one page by one page and press. Okay, so this is going to be just a subtle but really nice touch that a lot of people end up overlooking. But now if I wanted to print a view of this, as you can see, it fits really nicely onto just a one pager. So there you go, a little bonus tip with the page layout options there. But there you have it. Those are some of my personal favorite tips and tricks for building custom dynamic dashboards with pivot charts.