Excel PivotTables Part 2: PivotCharts & Dashboards | Chris Dutton | Skillshare

Excel PivotTables Part 2: PivotCharts & Dashboards

Chris Dutton, Founder, Excel Maven

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
11 Lessons (54m)
    • 1. Intro to Pivot Charts

      2:26
    • 2. PivotChart Demo: Column Chart

      4:13
    • 3. PivotChart Demo: Pie/Donut Chart

      5:46
    • 4. PivotChart Demo: Clustered Bar Chart

      3:25
    • 5. PRO TIP: Prevent Charts from Resizing with Cells

      4:45
    • 6. Changing Chart Types on the Fly

      3:18
    • 7. PivotChart Demo: Stacked Area Chart

      5:46
    • 8. PivotChart Layouts & Styles

      4:03
    • 9. Moving PivotCharts to New Sheets

      2:09
    • 10. Applying Slicers & Timelines to Multiple Charts

      5:01
    • 11. Building a Dynamic Dashboard

      13:22

About This Class

This class is Part 2 of a three-part series covering data analysis with Excel PivotTables and PivotCharts.

PivotTables are an absolutely essential tool for anyone working with data in Microsoft Excel. Pivots allow you to quickly explore and analyze raw data, reveal powerful insights and trends otherwise buried in the noise, and provide fast, accurate and intuitive solutions to even the most complicated questions.

In this part of the course we'll focus on data visualization techniques using PivotCharts, slicers, timelines, and custom, interactive dashboards. 

Finally we'll wrap up with Part 3, where we'll tie it all together and explore several advanced, real-world case studies!

Requirements: 

  • Microsoft Excel (2010, 2013, 2016), ideally for PC
  • Mac users are welcome, but note that the PivotTable interface varies across platforms
  • Basic experience with Excel functionality (charts & formulas a plus!)

Transcripts

1. Intro to Pivot Charts: all right, Welcome to Section six pivot charts, and I want to give you a quick intro and set some expectations before we dive in. Basically, pivot charts operate exactly like normal charts. In Excel, you've got your same chart types, same formatting options, same tools. The only difference is that pivot charts are dynamically tied or linked to a specific pivot table. And what that means is that you can't edit either the table or the chart independently, since the two are essentially joined at the hip. And adjustments that you make to filters or fields in your pivot will also be reflected in your chart and vice versa. Now, one important caveat is that this section will be pretty brief. We won't die very deep into all of the chart specific functionality. To be honest, we could spend hours diving into data viz specifically, and I want to make sure that this course stays focused on pivot tables first and foremost. That said, if you are interested in diving deeper into advanced charts and graphs, you can check out My Excel date of his course, which covers every single chart type in Excel 2016 along with some pretty cool. Advanced demos. So quick Intro to pivot chart options. Once you insert a chart, you'll see three custom tabs nested under the pivot chart Tools category analyzed Tab the design tab in the format TEM. Now the analyzed tab has a lot of options that should look familiar much of the same options that apply to your pivot table. You can insert slicers and timelines from this menu. You can refresh or update your data source can clear. Remove your chart at calculations and show or hide the field list or any field buttons. The design and format tabs are all about the look and feel of your pivot chart, so the design tab allows you to customize specific chart elements like access titles, labels, trend lines, etcetera. It includes quickly out templates, color palettes, chart styles which will dive into, and then options for transposing your data, changing your chart type or moving your chart. And then, finally, the format tab really just offers some additional formatting options that my opinion or lesser used this allows you to change things like the font style or the shape outline of your chart as a whole. Text Phil text effects things like that. You can also align the chart with other objects in the worksheet or customize the size properties as well. So with that, let's go ahead and dive in. 2. PivotChart Demo: Column Chart : All right, go ahead and open up your imdb workbook. And if you've been following along up to this point, you should see a view that looks something like this. Now, as we shift into pivot chart mode, I kind of want to start with a blank canvas and clean starting point. So what I want to do is actually clear all of the content from this pivot table view. Now, when I clear it, it will also clear things like calculated fields and items that we had added. So if you do want to preserve those, I would recommend either duplicating this pivot tab or saving your workbook as a separate file because once we do clear the content, there's no undo to bring it back. So I'm gonna go into my pivot table, analyzed tools clear, clear all. And now I've got my clean canvas to work with just a blank pivot table template that's tied to the exact same source data that we've been working with all along. So this what kind of service? Their starting point. As we begin to explore pivot charts specifically now, one tip that will offer when it comes to working with pivot charts is to focus on your pivot table view first. Since the two are tied together, it's often easier and more intuitive to get your table into the right format first and then simply insert a chart toe. Add that visual layer. So in this case, let's start simple and practice inserting a basic column chart. And since column charts are a great tool for showing relative volume across categories or dimensions, I'd like to start by exploring the number of titles released broken down by genre, so I can pull number of titles in the values field and then drag genre and dropping in as my role labels. Now, one thing you may have noticed is that our view is kind of shifted down all the way to Row nine, and that's caused by adding and removing different filters. So if I go ahead and just delete the 1st 7 Rose, if this bothers you, you can pull it back up. But again, it's it's caused by dragging filters in which creates a new row, and then when you pull them out, that blank rows kind of preserved up there, so just a little bit of a side note. But if it does bother you, it's a very easy thing to adjust. So there we go. We've got some of the number of titles broken down by genre, and a second tip that's important to keep in mind is that pivot charts work best with relatively simple views. So looking at one or maybe two metrics no more than one or two dimensions, the idea really should be to focus on using pivot charts, to tell one clear story at a time. So in this case, now that I have my simple view and my one clear story, which is the breakdown of titles by genre, I can go into my pivot table, analyzed tools and Click Pivot chart, which will launch the Insert Chart Dialog box. And Typically Excel will start you off with a suggested chart type, which may or may not be what you want. But in this case, a column chart or Clustered column. I will work just fine, so press OK and boom, There you go. Now that we have our chart in place, watch out, reacts is I change things like sorting and filtering options in this case, easy to a versus a dizzy. Why don't we go ahead and sort genres descending by the number of titles, And as we do that, you can see that the two are tied together. So now we get this nice trend from high to low in terms of number of titles by genre, and we can do the same thing with filters so we can apply a value filter Top 10 for instance, Toe on Lee show the top 10 genres by titles released, and you may also notice that I have a row labels button right here in the chart itself. This is called a field button, and using these, I can apply the exact same filters. So, for example, I can drill into this genre filter button. I can see the filters and sorting options that have already been applied through the table , and I can go in and just modify these settings. So let's change from top 10 to top five items. And as you can see, both the chart and the table update. So there you have a basic pivot column chart 3. PivotChart Demo: Pie/Donut Chart : Okay, so our column chart gives us a good sense of titles by genre. And now let's say we want to explore how those titles are distributed across ratings instead. Now, because each pivot chart needs to be tied to its own separate pivot table view, I can't just add rating to the same pivot and adjust which piece of the viewer chart is reading from or which call him. It's kind of all or nothing. So one entire view is tied to one pivot chart, and that means we have two options here. We can either replace our existing column chart by swapping out genre and pulling rating in and then changing the chart type to a pie or doughnut. Or we could create a second pivot table view either on this sheet or somewhere else, so that we don't have to replace what we've already built. Now I like the second option, so let's go ahead and select a cell in our first pivot, going to a pivot table. Tools select the entire pivot table and with the entire table selected, it's a simple is pressing control. See to copy, and I'll select some blank road down beneath a column chart and press control v to Paste. Now remember that when you create a second copy of a pivot table, it will work independently from the first. So that means in this case, I can swap genre out into the filters box, for instance, and dragon rating as my new role labels. And since we're now dealing with multiple pivots on the same sheet, let's go ahead and name this pivot in our pivot table tools titles by rating. And then we can select our first pivot. And instead of pivot Table five or whatever number you're looking at, Weaken title that one titles by genre. Now it's extremely clear which charts are tied to which pivot tables and what those pivot table views are actually representing. So now, here in our second pivot table, let's go ahead and drill down to just the ratings we care about using the manual selections . This case just are kind of core. Four ratings G PG, PG 13 and R press. OK, now we'll just go ahead and sort these descending by the titles and in terms of sorting, I'm typically I want to do any sorting that you need to do in the pivot view itself rather than trying to do sorting through the chart interface. So in this case, that's why I wanted to assort them descending by title here they'll be visualized in that order through the chart. So now that we've got our simple view that tells one clear story, are going to pivot table tools, pivot chart tow, launch that in certain chart dialogue. And now instead of the column, which is the default or recommended chart, let's go into our high options. We can select doughnut right here at the end, which is essentially a pie chart with a hole in the middle. So press OK and boom. There you go. Just drag it down here beneath our column chart, and now we have a pretty clear sense of which ratings contain the most titles. So overall we see a good chunk of titles. The largest share in this blue segment R R rated titles, followed by PG 13 PG and finally a small number of G titles here in the yellow segment. And you might have noticed that since I kept genre in my filter list instead of dragging it out of the table, entirely. This pivot chart reflects that right up here. So now I can adjust the donut chart to reflect any genre or in a subset of genres rather than sample as a whole. And I can do this by adjusting the filter, either in the pivot chart here or in the pivot table itself. So one in the same. So, for example, I can easily see how the distribution of titles looks for horror movies, specifically, which are almost entirely R rated versus something like adventure films for much more heavily skewed towards PG films. And then one thing to call out if I right click and choose select data. This is where I can tell exactly what pivot table this chart is reading from. In this case, it's the titles, but rating Pivot table and I could do the same with our first chart. So like data titles by genre. So it's a good way to kind of keep track of what's tied to what now one word of warning about structuring worksheets like this with multiple pivots stacked on top of each other. This approach is totally fine, but it really works best when you have a relatively small number of fixed structured pivot table views. And the reason is that things get messy pretty quickly when you have a bunch of different tables on the same sheet that are all changing or growing. In fact, you can't have pivots that overlap, which, for instance, I can demonstrate by trying to drag something like country into our first pivot table. That would create another column that would extend well beyond five or 10 rows and therefore overlap with our second pivot. So I get this error that says Pivot Table Report cannot overlap. Okay, so the point is, your best bet is to lock in the specific views that you care about or want to visualize and then try to keep things relatively stable from there. So, personally, I prefer to have one version of my pivot. That's actually a separate standalone worksheet, which I can then use for manipulating and exploring the data and then a second sheet like this one with simple, preset views that drive my visualizations. So that said, there are a number of different approaches that you can take here, so it's really up to you 4. PivotChart Demo: Clustered Bar Chart: So we practice building basic column and pyre doughnut charts to show titles by genre or rating. Now let's try showing both dimensions together. So it's planned to add 1/3 chart to this sheet by selecting our second pivot table, going into our tools, selecting the entire table control, see to copy it and then control V to paste 1/3 independent pivot table. And this one we can go ahead and name titles by genre and rating, just to make it super clear what we're trying to show here again. Exactly what I'm trying to show is the breakout of titles by both genre and rating in the same chart. So in this case, using it Clustered column or a clustered bar chart, I will be a really great option for us. So let's go ahead and drag genre out of the filters view and into our column labels while keeping rating as a row labels. Now when we do that scroll up, you'll see that it totally squished our existing charts. And that's happening because Excel is auto fitting thes column wits in our pivot table view , and I'm gonna show you two really useful tips in the next lecture to show you how to prevent this from happening. But for now, it's just click both of these charts in a shift click to grab both and then just dragged to extend them back to a full view. Let's select rating and edit are sort option instead of descending by the number of titles actually want, in this case, ascending by the number of titles so low to high as opposed to hide alot. And why don't we go into our genre sorting and filter options and apply a value filter here to just show the top five items by the number of titles? Nice little compact view. It's kind of a two dimensional tabular view that shows ratings along arose and top five genres along my columns. So from here, with any cell in the pivot selected, gonna go into tools pivot chart. And as you can see, it suggests it clustered column, which we could certainly use in this case, which want a slight variation, which is clustered bars, basically just the horizontal layout of a column chart so I can press OK and drag it down here. Now check this out. This chart gives us a really nice way to visualize how many titles fall into each genre for each specific rating. So we can see that there aren't many titles overall with G ratings, but among them, the majority are categorised under the adventure genre shown here in the Yellow Bar, whereas our films are much more prevalent in general and skewed more toward action in orange and comedy in blue. So that's certainly getting the job done. Last little tip that I want to cover here if you don't care about maintaining the ability to filter your fields in the chart itself, you can always go into your pivot chart tools, analyze options and select hide all from this field buttons drop down and there you go. Personally, I prefer this option, since it keeps things looking much cleaner within the chart itself and since I will always have the ability to continue to filter the data through the pivot table itself. So there you go basic clustered bar chart 5. PRO TIP: Prevent Charts from Resizing with Cells : in the last demo. When we dragged the genre field into the column labels on our third pivot, our worksheet automatically resized a bunch of columns due to a default setting called Auto Fit. So what happened was that all of these columns, all the way out to our got crunched down to the minimum with necessary to display the data in all of those genre columns, which in turn shrunk the width of any charts that happen to be sitting on top of those columns. So the good news is that there are at least two ways to avoid this issue, both of which are a little bit sneaky. So this was something that bugged me for years before I finally figured it out. And lucky for you, I'm feeling pretty generous today, so I suppose I'll share my secrets now. Solution One is to adjust the chart properties for any of the charts that we're getting resized, and this is a default setting and excel. So when you drop in a new chart like this column chart, for instance, you'll see that it's essentially tied to the height of the underlying rows and the width of the underlying columns. So if columns get stretched or resized, for instance, any chart sitting on top of that column will also be stretched to resized accordingly. And this could get really annoying when you want to create a nice, clean and organized view and changes that are made elsewhere in the worksheet. Keep skewing all of your alignments. So here's the deal. We can just select one of the charts that's getting squeezed right click Drill into the format chart area, and that will open up the formatting pane here. And this third option here, size and properties allows me to drill into the property's options to change the way this chart responds to sell sizing, noting that the first option is the default, which says move and size with cells. So in this case, and to be honest, in most cases, you'll likely want to choose either the second or the third option here. Now, the second will allow the chart to move around or shift if columns arose are added or resized. But it won't allow the chart to stretch or distort at all, and the third option basically allows the chart to remain completely disconnected from row or column changes in this case, either option will work just fine, But let's select the second and show you what it does. So it's close out of the formatting. Pane had selected the second option here for this column chart. So now when I drag column widths, you can see that this second chart, which we didn't change the properties for, continues to stretch while our first chart no longer does. But if we insert a new column, both will shift entirely. And that's kind of the difference between the second and the third properties options that we talked about. So let's go ahead and select our 2nd 2 charts. Weaken, Can Aditi's together, right? Click size and Properties will take us right to that pain, since these air options that apply to both of these different chart types and then right here properties move but don't size with cells. So there you go. That will fix the problem. Solution Number two involves changing the properties of the pivot table itself instead of the chart. So if we select a cell in one of our pivot tables like this 1st 1 for instance, we can go into a pivot table tools options simply uncheck this box that says Auto Fit column widths on update here in the layout in format tab and when we uncheck the box are columns won't resize as we adjust our pivot table view. And as a result, any charts tied to those columns will continue to be preserved as well. So now, for instance, in this first pivot, if we dropped something like language into columns, you can see obviously the chart updated, because now it's trying to show the data by language. But the columns themselves didn't change with they stayed preserved, which is exactly what we want. So it's drag language back out now. Another setting, where this often comes into play is when you have data columns that contain very, very long strings of text. Now, under the default pivot table mode, the pivot will constantly adjust to try to fit that long field as long as it's included as part of your view, which gets really annoying if you have to manually shrink it back down or manually change the column with every time you make an adjustment to the pivot and I'll actually walk you through. An example of that in one of the case studies at the end of the course. So there you have it, too. Pretty, elegant solutions to prevent charts from re sizing with cells. 6. Changing Chart Types on the Fly : so earlier in the chapter, we created this clustered bar chart to show the breakdown of genres as bars rolled up to the rating level as Rose. So now let's say that we decided we'd rather view the breakdown of ratings by bars rolled up to the genre level as Rose. Luckily, Excel makes changes like this incredibly easy to do, using an option called Switch Row and Column. So with my chart selected, I can simply drill into the pivot chart designed Tab and right here in my data pain, I have this switch row column button, and when I press this button, essentially, our data gets transposed, meaning that genre, which originally was our column. Labels or serious, has now swapped out into our access or row labels, and rating has swapped from our row labels into our columns. So it's the same data. It's just kind of flipped on its side. So now we're Visualization tells a slightly different version of this story by showing the distribution of titles buy rating in bars rolled up by genre. Now, one important point to make is that this option the switch row column option works best when you have a single set of Roe levels, a single set of column labels and a single set of values when it gets more complicated than that becomes much harder to use these options and tools in a meaningful way. But that said, the best way to learn and practice is just to get your hands dirty and play around with different options just to see what happens. So to continue with this example, we're happy with the transposed version of the chart now. But let's say we realized that stacking the bars would actually paint the picture a bit more clearly than clustering them like we're doing now. So that's no problem. We can go back into our design tools and click on this change chart type button. This opens up a new dialog box just like the Insert Chart Dialog box, which gives us access to any chart type we choose. And in this case, it's a simple is just swapping from Clustered Bar two stacked bar, or if you choose 100% stacked, which just equates all of the bars up to 100% and shows the distribution or proportions by rating. You also have three D options here. Honestly, just recommend staying away from those Just because the option exists doesn't mean you need to use it. So in this case, let's keep it simple. Go with the stacked bar and press OK, and there you have it. It tells the same stories are clustered chart, but in slightly more streamlined ways and others just a single large bar for a genre, with segments representing each rating as opposed to separate clustered bars. And then one last adjustment I'll make. I'd prefer to see the highest volume genre on top here so I can go ahead into our genres, sorting and filtering options, going to more sword options and just swap to ascending and press. OK, and now that just kind of swaps the order that we can see the highest volume genres at the top of the view. So there you have it a few incredibly easy ways to tweak and change your charts and chart types on the fly 7. PivotChart Demo: Stacked Area Chart: Okay, So, up to this point, we've done a really nice job showing the composition of our data using charts like columns , pies and doughnuts and clustered and stacked bars. Now, let's say we want to give some sense of trending as well, based on our release date data. So let's go ahead and copy one of our pivots. Choose this 3rd 1 here, select the entire pivot table control. See, then paste 1/4 of you down here beneath are stacked bar chart. And in this case, I'm gonna update the view quite a bit. So instead of showing the number of titles now, actually wanna see how gross revenue moves, we're going to use gross revenue as my values field. And instead of showing the data by genre and rating, gonna pull both of those fields out entirely and drag release date into my role labels. As usual, we get our auto grouping here. Since Excel has identified this as a date specific field, and in this case, that's a good thing, since the idea here will be to show data rolled up to the year level. Now, if you're using an older version of Excel and your dates. Don't Auto Group. You should be able to drill into your pivot table, analyze options and manually select the group field button here to roll that data up to months and years. And if that doesn't work, you can also pretty easily create new columns in the raw data tab itself, using functions like year and month. So in this case, because their dates were auto grouped, we've got quarters, release dates and years. Let's pull quarters out and release date out so that we're just summarizing gross revenue by year. And now I do have to caveat that this isn't exactly a traditional Time series analysis. We're not showing how revenue as a whole has trended over time, but rather the total revenue generated based on the year of each film's release. So, for example, since Jurassic Park was released in 1993 all revenue generated by that movie would show up in the 1993 bucket. So even if Jurassic Park generated 10 million in revenue in 1994 all of that revenue is gonna get lumped into 1993 since that's the year that Jurassic Park was released. So if we wanted to show a true time. Siri's. We would need raw data with multiple observations of revenue over time. But don't worry. We do have some great time serious data that will play with in the final section of the course when we dive into some different case studies. So anyway, now that we have a revenue by release date rolled up two years, let's limit the view on Lee Show the past 20 years or so. So quick. Pro tip. Rather than drilling into our sorting and filtering options and manually selecting 20 or so boxes, there's an easier way to do this. And in the view itself, I can actually just click and drag to select the window that I want. In this case, I want 1995 through 2015. I'll exclude 2016 since that just includes a handful of rows of data. And with this selection in place, it's a simple is right clicking, drilling into filter options here and keeping Onley these selected items. Now, if I scroll up, I can see that my views been updated to include just the view that I had selected so nice little tip there, Um and from here, we could go ahead and toss a simple line chart on this and call it a day to go into her tools. Pivot chart, Basic line chart and this is nice. It shows a good little view of how films released more recently after having considerably more revenue than those released in the nineties, for instance. And while we're at it, let's go ahead and name this pivot table revenue by year. So there we go. But instead of sticking with the line chart, I want excuse to use my all time favorite chart type, which is the stacked area chart now, without going on too much of a tangent. The reason I Love area charts is that they packed a ton of information into a really clean , really elegant visualization. So in this case, we can use an area chart to show both trending like we have here and some sort of composition as well. So to demonstrate this, let's grab a ratings field and drop it into our column labels or legend. Siri's options, which will show us if there have been any interesting trends in terms of which types ratings of films are being produced more or less over time or generating more or less revenue over time. Now this is already pretty cool. You can already see a story starting to take shape here as Excel integrates that data as new lines or new Siri's within our chart. But trust me, an area chart will do an even better job. So since we already have a ratings filtered down to what we need, G PG, PG 13 and R, we can head up to our pivot chart tools, design options and select change chart type. From here, let's turn this bad boy into an area chart. Now. The traditional area chart isn't exactly helpful here, since our Siri's air overlapping each other. But in this case, using a stacked area is a really great way to visualize both. How volume as a whole has changed over time, represented by the height of the entire chart and how the composition or distribution of values has changed as well. So let's hit okay, and we can go ahead and to our tools, analyze, get rid of those pesky field buttons and stretch it out a bit. And Walla, we've got ourselves a beautiful stacked area chart 8. PivotChart Layouts & Styles: Now that we've practiced building some different types of pivot charts, let's take a minute to talk about some of the design options available to us specifically chart layouts and styles so we can select one of our charts in this case or column chart up at the top and drill into our pivot chart tools Design options. Now, if you're relatively new to working with charts in Excel, you may want to start by exploring some of the standard layouts and styles that Excel offers. So, for example, you can preview different quick layouts, which provide different preset combinations of basic chart elements like labels, legends, grid lines, access titles, etcetera. You also have different types of color palettes available to you here and finally, a selection of more comprehensive styles, which allows you to preview some additional formatting styling options like Phil's Shadows three D effects. It can drill down to see more about some crazy stuff in here. A swell Now if you do know your way around a chart and aren't afraid to do a little customization, the options air literally, quite endless, and personally, I start by incorporating any particular chart elements here on the left that I think will be valuable to the visualization. So anything from access titles and data labels grid lines in general, I think access titles are best practice. So let's go ahead and select a primary vertical axis. And let's also add data labels outside the end of our columns and press OK, and there we go. I can just give this access title and name this case number of titles, and there you go. So, like I mentioned earlier, thes charts can be formatted and customized exactly like normal charts in Excel. So I really can only begin to scratch the surface here without diving into my entire separate course on data vis. But to give you the quick one minute intro to custom chart formatting, the key is to select any individual component of the chart and right click tow. Launch the formatting options. So with data labels selected, you'll see an option at the bottom called format data labels. With the serious selected, you'll see format data. Siri's with the Axis title selected, you'll see Format access title. It really doesn't matter how you get to that formatting pane. All of these will take you to the same general place now within this formatting pane, have a drop down right beneath the title, and that allows you to isolate or form at any particular chart element. So right now we've selected Siri's. We can also look into options for formatting, the plot area or options for formatting the legend. You can even format the field buttons themselves, and then within each of these areas, you've got tabs or pains with additional information nested within them. So Phil and line options, which kind of help be customized the look and feel of Phil on border effects. You've got additional effects like shadows, glows three D formats and then typically will have some Siri's options, which in this case allow me to update how these Siri's overlap and what the gap between the columns looks like. And these options air dynamics. So they change. Depending on exactly what you're looking at. Sometimes you'll have more options. Sometimes you'll have fewer. In this case, we have some size and properties options as well, so obviously we don't have time here to go into all of these different options. Be honest. There are thousands of different combinations, formatting tools and tricks you can use. The point is, if you want to become a master of building beautiful custom charts, Step one is to get intimately familiar with this format. Pain step to check out my course if you want to see some real next level examples of data, visit Excel. So we'll do quite a bit more formatting of these charts when we talk about building a dashboard towards the end of this chapter. But for now, that's your pivot. Chart design options 101 9. Moving PivotCharts to New Sheets : I want to take just a minute to talk about moving pivot charts specifically onto new sheets , which is something that I actually don't recommend. So you may have noticed that with any chart selected in your pivot chart tools designed Tab , you've got this option for move chart at the right of the pain. Also, if you right click, you can see the same option here, right beneath select data so you can move the chart in two different ways. And when you select that option gives you a dialog box that allows you to either move it as an object within an existing sheet or as a new sheet on its own. So right now it exists as an object within the sheet that we call pivot. But if we move it to a new sheet before I press, okay, one thing to note is that I can't undo this change, and it will remove the pivot from its current location and place it into a new place. So press OK. And now it basically creates a new sheet called Chart One, where this donut chart now resides. And what's a little odd is that kind of locks it into this template where it's not really on a new traditional Excel sheet. It's in kind of its own standalone object template. And then if I go back to pivot, you can see that it's pulled it right out of here. So now my donut chart is missing from this tab, and it only lives in this one standalone sheet. Now, this still is tied to my workbook here. So if I unfiltered genre, you'll see that this updates accordingly. But the problem is that I don't have a lot of flexibility for working with it here. I can easily add other charts to do things like build dashboards, for instance. So actually don't really like this option. I mean, in fact, I'm gonna delete this sheet so that it's gone and then just basically create a new one right here. From my view, which is a simple is selecting the data and going into doughnut. And there you go. I'm kind of back where it started now, but I wanted to call that out and kind of speak to some of the caveats associated with moving charts, especially to new sheets. So again, I don't recommend it, but that option is there. If you choose to use it 10. Applying Slicers & Timelines to Multiple Charts : So like we talked about back in section four of the course, slicers and timelines are essentially just user friendly interactive filters that you can layer onto a pivot table. Now they work exactly the same way. In the context of pivot charts. They'll just be connected to both the table and the chart. But in this video, we're gonna take things to the next level and create slicers and timelines that could be assigned to multiple pivots and multiple pivot charts at once. And what that means is that a single filter can be used to update an entire collection of pivot tables and charts, which, as well cover in the next lecture is an incredibly easy way to build really nice dynamic dashboards. So here's how it's done. Step one is to select the pivot chart that you want to add a slicer timeline to, and keep in mind that it really doesn't matter which one you start with if you eventually want all of your charts to be connected. So in this case, let's elect our first chart or column chart, head into our analyzed tools, insert a new slicer, and in this case, why don't we choose rating as our filter. So there we go. Drag it over here and now watch what happens as I manipulate this filter Onley. My column chart is adjusting well, all of my other charts in the workbook, our remaining static. And that's because when I inserted this slicer, it became tied on Lee to this chart and so actually want to enable Multi select here so that we can go ahead and select more than one filter at a time. So that brings us to Step two, which is selecting the slicer and drilling into the slicer tools ribbon here and selecting report connections to the left. Now this is absolute gold. It's where we can assign that one slicer toe, any other pivot table in the workbook or any pivot table that lives on a separate sheet in the workbook. So here we see the four different pivot tables that we've inserted into the sheet. They all live here on the pivot sheet, and you'll notice that only one check boxes selected, which is titles by genre. That's our first pivot chart here, and all of the others remain un selected, meaning that they're not currently tied to the slicer. This is a good reminder of why it's so important to name pivot tables in an appropriate way , because instead of selecting between pivot table 123 and four now, it can easily see which views these are and what they represent. So in this case, let's go ahead and select all of these boxes to tie this one slicer, toe all four pivot tables and therefore pivot charts in our worksheet compress. Okay, and now, since we only have G and PG selected in the slicer, all of our other charts are updating Toe on Lee Show PG and G ratings. Similar case. If we add PG 13 you can see all three update and so on and so forth. So now we have this one slicer that's controlling all of the charts and our worksheet, which is incredibly valuable, and the same exact process applies for timelines. So I can select our first chart again, going to analyze tools and insert a timeline. And like we talked about, the only option here is release date, since the timeline needs a date specific field and that's our only one, so you can go ahead and press OK, drill into our timeline tools, going to report connections and again check the boxes to tie them to all four views and pivot charts in this sheet. Now let's go ahead and just instead of showing by months, let's show by years and drag it out. So it's a bit more easy to work with, and from here we can drill into a single year, for instance, and this will be most evident in our final chart, which needs some pacing data. So and we drag this out to 2005 or 2010 there you go can extend it back to 2000. And again, all of our charts that are connected are now updating accordingly. Now it's also worth noting that once you connect slicers or timelines, you'll then be able to control those connections from either the pivot table or the pivot charts side as well. So, for example, I can select the chart going to analyze tools and use this filter connections option to determine which slicers or timelines this chart or pivot tables connected to. So essentially, it's doing the exact same thing, just coming at it from the opposite direction as opposed to starting with the slicer or the timeline. So as you can see, this tells me that column chart that I've selected is impacted by both the rating slicer and the release date timeline. So there you have it. A really cool way toe. Add even more functionality to your pivot tables and charts using slicers and timelines. 11. Building a Dynamic Dashboard : 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 he 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 just the properties of all of my elements all of the charts, plus my slicer and timeline, and I could 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 drilled 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 it could 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 rob pivot table views so I can select columns a through E, which contains all of those pivot table views and right click toe. 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 we'll jump into my view menu, just de select 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 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 right? I'm gonna resize this column chart a bit. Let's pull my stacked bar appear and in this case, my stack bar on my doughnut telling kind of the same story. It's just that my stack bars kind of adding a layer of additional insights. So let's get rid of the doughnut chart, simply delete it and then drag my stacked bar right appear to the right in my columns. 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 us a central focus of this dashboard. So actually enlarge it a bit, make it as wide as I can, and then I'll pull the timeline. I want a position that 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 a position it kind of roughly beneath my first slicer, and I'm kind of just stretch it down so that we have this nice, compact rectangular shape here. 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 and try to select everything using shift Select until I have all of my elements and I'm just gonna drag everything down a bit to 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. I am DB movie dashboard in bold Going 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 blues. I'm gonna give it dark Grey Phil with no line. So there's my kind of header title, and now that I have my layout in place, it's a simple is going through piece by piece, toe 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 tweets that I would personally make for a dashboard like this. 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's and pieces I don't really need. I don't need that title here could delete the title. It can delete the legend. And in my pivot chart, analyzed tools can hide those field buttons as well. Now let's just make the access title Bold. My Siri's labels. Bold right click the Siri's itself that can add data labels here as well. Let's make these italic control I and then right click the actual Siri's. And I'm just gonna change the Phil from this light blue to kind of this darker, grayish blue color here and in my Siri's options. I don't want quite as much of a gap between my Siri's, 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. Gonna make my vertical axis labels bold, and I do need an access 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 access titles, primary, horizontal and, in this case, just like the column chart We're looking at the number of titles Make that bold. Dragged the edge to resize it, moving anywhere it shoes. Okay, now I'm happy with my stacked bar chart. So moving on to area, this one's pretty close. I do need to going to 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 1st 2 charts. We're looking at gross revenue here. So again, add that title making bold 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 excelled by default, supplying 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 little pro tip here in a right click and format the axis all the way in the bottom of our access 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, etcetera. In this case, I'm actually gonna show you a really cool custom number format to turn these into shorthand 1,000,000,000 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, 03 consecutive commas, and then it be in quotes. And when that does is it shrinks those billions numbers with all of those zeros down to a shorthand version and appends a label be at the end. So now you can read it as 10 b nine b eight B or 10 billion, nine billion, eight billion and so on and so forth. And if you want to apply the same trick for millions, for instance, instead of three commas used to and change the B to an end. If you want to use this custom format for thousands, then only use one comma and change to be or the end to Okay, 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 pain. Change the format of some of these slicers. I kind of like this option with the bluish grey shading. I'm gonna make that change to both of my slicers and I'm almost there. One thing I do want to do is kind of be deliberate about how the lining things. So 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, a line, a line left. And then I could do this in a number of different places as well. It can select these three elements drawing tools, a line top, and I can kind of just, you know, drag pieces as well. If we want to distribute these three elements equally horizontally can use a different align option, which is distribute horizontally. That creates equal spacing between them. So obviously not totally critical stuff. But having a dashboard that's clean and consistent and buttoned up, it really does go a long way. So almost there and now we've got it pretty nice layout. Everything's kind of in this one clean view. Everything's aligning nicely. 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 are 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 a 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 set up in 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 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 could 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, that can drop down into print area set print area Now, last but not least in this scale to fit pain can click this little blow out here and say Fit this toe one page by one page press. OK, 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 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.