Building Dynamic Dashboards with Pivot Tables & Pivot Charts

A free video tutorial from Maven Analytics
Empowering everyday people with life-changing data skills
Rating: 4.6 out of 5Instructor rating
41 courses
1,282,016 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 Pivot Tables for data analysis, EDA & business intelligence. Learn from a top MS Excel instructor!

06:30:12 of on-demand video • Updated May 2024

Take your data analysis & EDA skills from ZERO to PRO with Excel Pivot Tables
Learn how to use Pivot Tables and Pivot Charts to streamline and simplify your workflow in Excel
Master unique Pivot Table tips, tools and case studies that you won't find in ANY other course
Explore fun, interactive, and highly effective lessons from a best-selling MS 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 [CC]
Instructor: 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 or row is 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. Now, 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 gonna 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 stacked bar is kind of adding a layer of additional insight. 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 gonna 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, and I wanna 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 gonna try to select everything using shift, select until I have all of my elements, and I'm just gonna 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 in 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 gonna 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 gonna 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 gonna 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, I 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 gonna 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 gonna 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 gonna move the legend to the top of our chart, kind of drag it out to resize it a bit. I'm gonna 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, primary 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. Okay, now I'm happy with my stacked bar chart. So moving on to area, this one's 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. Now, one other tweak that I wanna 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, it's 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. You're gonna 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, et cetera. In this case, I'm actually gonna 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 in quotes. And what that does is it shrinks those billions numbers with all of those zeros down to a shorthand version and appends a label B at the end. So now you can read it as 10B, 9B, 8B, 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 gonna make that change to both of my slicers. And now I'm almost there. One thing I do wanna do is kind of be deliberate about how I'm aligning things. So I'm gonna 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, align top, and I can kind of just drag pieces as well. If we want to distribute these three elements equally horizontally, we 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 G, 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 wanna 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 gonna choose narrow margins. I want my orientation here to be landscape, 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 blowout here and say fit this to one page by one page, and press OK. So this is gonna 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. 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.