Excel: Advanced PivotTable Techniques for Creating a Cohesive Dashboard

1. Introduction: For 15 plus years, I have been an Excel power user and over the years I've learned a lot of tips and tricks for how to turn my pivot tables and pivot charts into a nice, cohesive dashboard. Hi there. My name is Al, and I'm currently a solutions architect at Coda, but I've also been an avid Excel user for many years. I started using excel at an internal finance team at Google more than ten years ago. And I'm really excited about teaching this class because I want to show you all my advanced pivot tables and pivot chart tricks that I use to build a cohesive and interactive dashboard. After this class, you'll look like a hero on your team for being able to build a dashboard and also automate it with all the tips and tricks that I'm going to show you in this class. This class is all about how to use advanced features in pivot tables to build a dashboard that is meant to be platelets. You're going to be working on a dashboard that you can eventually show your team. There'll be able to interact with it because it's very dynamic and all the data will update as they're playing with him. A dashboard is all about data visualizations and you're going to learn how to create a bunch of different visualizations including charts, sparklines, data bars, with the goal of being able to communicate and tell a story about your data to your internal stakeholders, to your teammates, and to your colleagues. Another big takeaway I want you to walk away with is automation. I want you to think about all the manual tasks that you have to do to update your current PivotTables and dashboards. And in this class, you can learn some tips and tricks to be able to automate away all of these manual tasks. I don't know about you, but one of the best feelings I get when using Excel as a Nike has click a button or type some something in the cell and everything just updates in one go. In the class project, you'll be given a dataset that you will have to create slicers for pivot tables, pivot charts, and dashboard. And the goal is to be able to make it updated in one go just like we talked about with automation. And please post questions and posterior project in the class forum. And I'll be happy to provide feedback and answer any questions you have. 2. Creating Multiple Pivot Reports: This lesson helps you to create individual reports based off of a filter in your pivot table. And don't need any fancy macros or any fancy formulas. This is a really handy shortcut and tip to quickly build a bunch of reports so that you don't have to spend time copying and pasting and clicking on a bunch of dropdowns is create these reports for you intimidated. Before we get into lesson one, I will quickly review the data on the rod Data tab in the workbook. Because all the PivotTable is we're building in this class, we're using in this class are all driven off of this main dataset. And it's a dataset consisting of customers paying for their Internet service and their phone service for a telecom company. And it just goes by customer ID. There's around 6 thousand rows of data, and I will just go over here to the right. And there's a little legend here in yellow that tells you what each column means. But essentially it's every single rose at customer paying for their Internet or their telephone service with this telecom company. Back to lesson one, this pivot table right here, It's pretty basic. It has the gender of the customer in the rows and also the payment method, basically the way that they pay for their Internet service or phone service or whatever. And the total is the sum of total charges, which is this field right here. So the total amount that was paid to this telecom company, bigender for these different payment methods. Now with PivotTables, you know that you can drag in the any of these columns or fields into the filters section. So I'm going to drag in the contract field into the filters tab. And as you know, PivotTables, once you do that, you can click on this drop-down and select, maybe I only want to look at customers who pay month to month. And you can see the total revenue that was driven by month-to-month contracts or including one year. I'm going to leave all of these three checked off. And the goal of this lesson is to show you how you can quickly create pivot table reports based off of what you have in this country, in this filters section of the pivot table. So on the Mac you can click on PivotTable Analyze, and next to the Options button, there's this little drop-down right here and click on Pivot Table Options. Click on Show Report, Filter pages. And you'll see by default it says Show All pages of contract because we had already dragged in the contract field into the filters section, I hit OK. And Excel beautifully creates these three worksheets that automatically filter to the specific contract. It just goes through every single option and it just creates pivot creates worksheets with PivotTables based off of each option in the drop-down. So remember how like in the contract drop-down we have month-to-month, one year to year. It automatically created three worksheets based off of those three options in the contract field. If you want to quickly create these reports with pivot tables, instead of copy and pasting pivot tables or menu selecting each option. I like to use this option to quickly create reports when I know I wanted to have multiple pivot tables for multiple different options using some specific field in my dataset. So these webpages are quick way to look at the data. 3. Creating Dynamic Data Bars: Sometimes you want to build a bar chart for your pivot tables to show the distribution of values in your dataset. Data bars are also really quick way for you to share with your teammates quick trends about the data in your data set as well. One of the most underutilized features with PivotTables, in my opinion, they are the dynamic data bars because it allows you to quickly show trends in your data to your teammates without them having to build their own charts or graphics. And so with this PivotTable, we're going to build some basic data bars on the total charges column saint PivotTable we use from lesson one. So the first step is select the data and the total column when it's like all these numbers here, just these dollar amounts. Then go to Home Conditional Formatting data bars. And I'm going to select on the green color data bars for now, which is this one down here. And notice how the bars automatically resize based on how much, like how big the number is and that total column. And I would definitely experiment with playing around with the different color bars and variations here. Like for instance, if you go here, data bars. The gradient field is also a nice little touch because you notice how it gets a little lighter near the end, I get just these small little things that make your charts look more like, a little more aesthetically pleasing. But the cool thing is that this is not a separate chart. This is done right inside the pivot table itself. And I would also click on go to Data Bars and then more rules here. This option. And here are different ways you can really customize the look and feel of your data bars. One thing that you can do here is check the show data bars only box. And let's see right here. It is. Right here. It's kinda hidden there. Hit, Okay? And what I like about this visualization is that instead of people having, being focused specifically on the dollar amounts, this focus is only on the actual bar chart itself. And so people who can quickly see that the Mel check was the least used payment method option for this dataset. So I'm just going to undo this for now, just to go bring back the numbers I would play around with the other options here to find the one that matches your taste for different data bars. I would also point to the color scales want, if you don't want to go with a solid color, you can of course, customize the colors and gradients here for a solid color. But if you want to quickly be able to go from green to red, which is a pretty common option to show like good to bad or high to low, is to click on color scales. And I usually use the second option here. Flips, I have to remove all the conditional form. M is going to undo everything here. Go to conditional formatting, color scales. Second option. And I'm sorry about that. I should go with the first option. We're green is good, red is bad. In this case, it shows electronic check is the biggest number out of all the different payment methods. And then Mel check is red, indicating that it has the least amount will actually melt. Check over here is actually the smallest number here. But you can see how you can play around with these different options with color scales and data bars. I personally like to use the data bars more because again, I want to quickly convey how much money we're getting from these different payment methods. And so typically I'll use a gradient fill because it just gives you that nice extra touch that makes it look a little more professional. Manage Rules, data bar is more rules. I would also get rid of the numbers that I really want people to focus specifically on the bars itself. And for positive value, maybe I might go back to this nice little green here. And let's see. I don t think imply that the gradient here, let's hit okay. And so simple bar chart, kinda like a simple bar chart that shows you what that value is. But the important thing is that it happens right into the pivot table itself without you having to create a separate chart. 4. Utilizing the Timeline Filter : The timeline slicer is a unique slicer for pivot tables because it allows you to filter your data based on a specific time-frame that you set in the slicer. Sometimes though, you want to see this specific case which you are filtering your head in the timeline slicer. So this pivot table trick or give away to show your teammates the specific dates that you are setting for your timeline slicer. We have our pivot table here and it's broken down by gender again, but this time you'll see months in the rows as well. And the way you can get these months is when our date field actually every individual date for when these transactions were made. By drag this into the rows. You'll see that when I expand January, I'll see every single date here for January and how much money was made. But when you drag a date field into a pivot table in Excel, you can automatically summarize data by month or by specific date depending on what you're looking for right now. I'm just showing the data by month. It's only shows January through June for my dataset. Now, when you play with dates in PivotTable and sometimes you want to be able to quickly filter between specific dates. And there is a slicer In pivot tables in Excel, which allows you to filter by date, but it's not super user-friendly. I'll show you what that looks like in a second. So we have the start date and end date here, where we will have individual dates that we want to filter by four our PivotTable. But for now everything is just showing all the data right now. So number one, click on PivotTable below and click on PivotTable Analyze and the ribbon. So we'll click on here the ribbon. And then we're going to insert a timeline on the date field and tried to filter the PivotTable using the timeline slicer. So click on Insert timeline here. And we're gonna, we're gonna use the date field from our PivotTable hit. Okay. You'll notice you get this little guy, which is our timeline slicer. And like I said before, it's not super friendly unfortunately, automatically it shows you all the months, even though we only have data from January through June. And if I want to expand this little more so that this scroll bar isn't show up. I can move this over here a little more. One thing I'd like to do is here, click on the timeline, the ribbon, and remove the header. So click on here timeline. And then I just move the header because I don't already know this is the date. You can also remove the selection labeled too if you want, but I'm just gonna leave that in for now. But you can see how you can customize this to your liking by right-click. You can also say Size and Properties, and you can further edit the look and feel of the timeline slicer. But I'm not going to do too much here. I'm just going to keep the default as it is. And you'll notice that when I click on, let's say February and January, when I expand this out a little more, it automatically filter as my pivot table to be the specific dates that I look at this over here and only look at March through April. And things automatically filter, which is great. But what about wants to filter my PivotTable to like February 1st through March 13th. Like I have to go here to February 1st, but then how do I know exactly where marches it's kinda hard to do. I could change this drop-down to be days, but this gets a little too crazy like you to scroll way too much on this thing. I can't even remember the scroll bar is right here. And so it's not very user-friendly as you can see, because I have to like scroll, scroll too far and going back there. So it's not very user-friendly. I usually leave this as months or quarters. Phenomena leave it as months. For this example, we're going to set up a specific filter or we can use to filter this PivotTable. And a more specific way than our timeline slicer, I just want to show you this lesser is available to filter your data if you want that kind of interactive feel. But sometimes you want people to, to be able to dig into your data. So let's move this pivot table over here to the side. For now, we're going to make a copy of this pivot table. And I should just delete this because I don't want to filter my PivotTable anymore. Alright. This is still filtered for some reason. I'm going to remove this filter, choose one. So this goes back to unfiltered State. Select this table units kinda manually select the rows of the pivot table here. I'm going to copy this and put it over here to the side. Again, this is just to demo something. So we're going to remove all the fields from the secondary pivot table and add a date field twice the value section of the pivot table. So let's go here. Go ahead here, show the field list and let's remove everything. Then, like the instructions say I'm going to add the date field twice to the rows, sorry, to undo that, undo that to the values section. And by default it gives you the count we're going to do is right-click the first day and click on Field Settings and summarize the box. Click on men for summarise this date field and do the same thing for day two as well. I'm going to right-click and then go to Field Settings. Summarize by men, hit. Okay? And I'm going to reformat this by right-clicking format settings. Let's see here. Number, then click on date. So now this is a date format. So this is the minimum date of my dataset. The same thing here, but for this one I'm going to say the Mac. So I'm going to find the max state and do a copy paste special divide format. So we can see the max date and our data set is 61, which makes sense. We have January through May 30th data in our raw dataset from our Data tab. Now, in cell C3, we're going to set the cell reference to the mandate and a dummy pivot table of your created. And then cell C42 to the Mac state to the DW created. So what I'm gonna do here is I'm going to write equals and just say cell reference is men right here. Instead of doing the GETPIVOTDATA formula, I'm going to remove this and say H 18. And this will be 18. And now, what's nice about this is I can now look at what specific dates are being filtered to when I have the timeline. So if I click back to PivotTable, Analyze and go to insert timeline, date, okay? Now look what happens when I filter to specific dates and my timeline. So notice how when I change my timeline slicer, I can now say like, okay, like I'm looking specifically at February first to fourth already because when I added this timeline slicer, it automatically changes this pivot table, which is just like a copy of a pivot table, but it just has the max and Min dates from my dataset for what I'm filtering. And then this feeds into these manual to dates, due date fields. So if I wanted to select a specific date or quarter notice, I can be more specific with what I select here with my with my dataset. So just makes it a little more clear what the dates are versus having to look at this PivotTable slicer and try to estimate this is like end of beginning of March through end of May. This just makes it very clear what the data is based on this little PivotTable hack. And it might cover this up like this so that you can't see it. Or you might even just move this helper PivotTable to a separate worksheet where people can't see what it is. But this is how you can make your timeline slicer work for your teammates and colleagues, but also show the exact dates here in cells 1314. 5. Creating a Map Chart: One of the most powerful visualizations in Excel is the map chart. This lesson will teach you how to connect a map to your pivot table so that you can show your teammates or data spirit cross a specific geographic region of a map chart is one of my favorite visualizations for showing data that is based on geography. But the problem is that you can't really build map charts directly off a pivot tables. But you can build a regular pivot chart off of regular data, which you can link back to pivot table. So it's a little trick which I'll show you here, which allows you to view map data. Map chart data pulled off a pivot table so your colleagues and teammates can see the data more clearly. So the first step is dragging the state column into the row section of our PivotTable. Am going to click on here gonna show field list and then drag the state column into the row section. And all we get here is just looking at the revenue for the telecom company by state. And here is the little hack that you can do to build a map chart off of a PivotTable. Copy the PivotTable here. Just this, these two columns copy this and paste it as values over here for now. And I'm going to just convert these numbers into a currency format so that it looks good on my chart. So you have this pasted data, hard-coded data over here. And now select this data and then go to Insert maps and then filled map. And of course you'll have to be connected to the Internet in order for this to show something because this is built off of like Microsoft, like mapping in general, whatever. And check out this really, really cool chart that you get from Excel. And I can rename this chart to be revenue, total revenue by state. But the problem here, so you can obviously customize the look and feel of the legend, the look and feel of the numbers and the colors. I like to maybe play with the background color here you can make an all-black default one is pretty good too. You can change the colors to be a different kind of color set. Again, the default one for now is okay for me. And how do I go back to it? This is another look at the look and feel. I'm just gonna go back to the renal dataset or sorry, the original color. And the problem here now is that this chart, this map chart is built off of this raw like pizza dataset, which isn't really great because we want, as new data comes into a pivot table, we want this chart to dynamically update. Of course, their teammates always get the most up-to-date information. So what do you can do is after you've created this map chart, right-click Select Data. And then all you need to do is just change the chart range to be this dataset from the pivot table. This will double-check them picking the right data. So it's either team through FY19. So I think all you need to do is go here, select data, and then change this chart range just to be everything from here to there. Hit Okay. And yeah, it correctly built correctly remove that state column. But now you can see that this chart is linked to this pivot table. So I can actually delete this data right here that I kinda paste it as values. And now as new data comes into my dataset in the raw data tab, they'll feed my Pivot Tables, which automatically refresh. And then this bar chart will automatically refresh as well because it's coming from the pivot table. Pivot table as the source data. Really neat visualization and you have to be connected to the Internet, of course, to have this show up, because this looks at the whole world and it automatically smartly figures out which part of the world is show your data based on what you have in your location column. 6. Viewing Quick Trends with Sparklines: Sparklines are a quick way to show your teammates daily or weekly trends in your data. You can also keep sparklines up to date using pivot tables so that when your data updates in a pivot table, a spark lines will update as well. We've been doing a lot of different visualizations and charts built off of PivotTables so far in this class. Another one of my favorite visualizations are sparklines. It's a really quick and easy way to visualize your data without having to build out huge costs and line charts to see trends in your time series data. We're going to build some sparklines right now. The first step is to add the date field, the pivot table below, and leave the months field in the pivot table. So I'm going to click on the pivot table, dragon date in the columns. And then I'm going to remove the date field but leave the month. So this is aggregates my total revenue by state and by month. And now I'm going to select cells 017032 and then insert sparklines by going to Insert sparklines, then line. So let's go to other e to 017 and we're going to call this sparklines. It was bold. And select everything from 017032, which is the same kind of height of my PivotTable. And then just go to Insert sparklines line. Now here it tells you it's asking which data range you want for the sparklines. I'm just going to select cells C7 through H3N2, which is just all my dataset available data right now from a pivot table and then hit Okay. And you'll see that you get these nice little sparkler heightens that trend based on the data. Notice how everything kinda dips in January because in general, in sorry, in June, in June we only have one day of data, I believe that's why. So that's why the numbers are so small. But the problem here is if I want to update these sparklines, It's a new data comes in for July and August. As my PivotTable changes. That's the sprite, July, August, September. Just imagine for now that this is like new data that's coming in into our PivotTable button. It's going to manually type this in for now. If I let's say it goes from July goes to $50 thousand and then 60 thousand. Notice how my sparklines aren't changing in this first row because again, we said that the range, the only be from C to column H. So one option is, this is one of the downsides of using sparklines with named ranges, which is we want Excel to automatically dynamically include data if new data comes in, but not include data if there is no new data here, I'm going to clear the sparklines for now by selecting this and then go into sparkline and then clear. And then I'm going to go to Formulas name ranger and then create a new named range called my range. And then I'm going to copy and paste this formula for now and I'll explain what this means in a second. But this is gonna be the formula for my named range. So back to step five, we're going to go to Formulas named range, the manager. And then I share already had the students going to delete this for now. Hit Okay. And then say this is going to be called my arrange. And then refers to I'm just going to remove this and paste what I have down here. Hit Okay, and Close. So just to recap what this formula does and if you want to learn more about offset functions, I would check out my other advanced Excel course. It's all about advanced formulas and features in Excel. This is offset function essentially it tells Excel start in cell C7 teen, and then only look at this first row of data and then count the number of cells that have, they didn't them from C7 there, I'm 17, so it's counting everything from here through, through here. And I actually want this to be n, but I'm going to just ignore that for now. But essentially what I'm doing now is, let's say I want to create sparkline just for this row right here. I can go to now, I can go to Insert sparkline, line. And I'm going to say my range hit. Okay. And notice how I have that sparkline there, which includes July, August, and September. If I delete these numbers, the sparkline automatically resizes to show only the available data. So if I put these numbers back, then it starts to change the sparkline. Well, this is great, but what if I want to make this include, I want this, do this for all this data. The problem is with the way Excel works right now is I'd have to create a named range. Literally for every single one of these cells. I have 16 states I have to create name ranges for. So it's not a good use of time to basically create name ranges one at a time, 15 other times. And then changing the offset function, dislike, tweaking it to count for every single one of these rows. You can't just do one named range for its entire thing, which is unfortunate. So what I typically do in this situation, it's gonna put in some more data here. This example is, I'll just select extra data for my sparklines. So what does that mean? I'm just going to remove this for now. And you can follow the instructions here as well. We're going to add new columns data we should try doing instead of using the named range just like cells C7 to N32 for sparklines in column O. So what that means is I'm selecting my sparkline cells here. Insert sparklines line. Here. I'm just going to select everything from C7 through 32. So basically all this empty data, it's gets included. And now let's look at what happens to my sparklines. So it's not the best visualization for the sparklines because he notice how like for most of these months we're only halfway through the year. So that means the line only goes through the middle of the cell. There's a bunch of whitespace to the right of the line. But the important thing is as I'm starting to add new data here, the sparkline will update as data gets added. And this is the faster way of building a bunch of sparklines one at a time, sorry, in one go. Versus the name range option, which we talked about in steps like eight through six. You'd have to create a named range, one at a time for each cell. Now, if you only have like three sparklines to create, then yeah, using the name range option would make sense because you don't have that many sparklines to show. But I have 16 here. If we had 50 sparklines, it ends up not being a scalable option. So that's why I usually end up just building my sparklines using this kind of hacky method of selecting all the empty columns which will contain data for future months or future quarters. So that my sparklines automatically update when new data gets added for these future months. In my PivotTable. Imagine that this will be coming from a PivotTable versus me manually handwriting these grand entering these numbers into the cells. 7. Customizing Data Labels : We will create some pretty standard pivot charts in this lesson. There are times though when the legend on the chart is not exactly what you want or need. So in this lesson, I will show you how to create very custom data labels for your charts so that you and your teammates will spend less time trying to create the perfect legend or it's labeled for your charts. We're going to create a bunch of new pivot charts off of our PivotTable. Hello In this lesson and also play with a customized data label when you want to just add a little more, more information to your data label on the chart to make it a little more informative than what comes with the default legend in the chart. So we'll see what that means in the very last chart we build next to each pivot table. This is again, this pivot table is built off of our main raw dataset in the raw data tab. All we're gonna do for the first step here is just create charts based that are reflective of what's noted here in the label. So let's just go ahead and go to because when I click on the pivot table, then click on Insert and click on Pivot Chart. And you notice the first thing we will get is by default is a bar chart for this dataset because we have one simple field with a bunch of values for a given field. And I'm not gonna go through the customization of this bar chart right now. But typically what I like to do as a very first thing is I'd like to move the legend Just the very top. I just liked the look and feel so it doesn't crowd out things or move the grid lines as well. And also maybe make these, make these numbers a little more easier to read instead of having it be like fully written out by 1 million to 2 million. So let's do that now. So now for this line chart, notice how the data is time series now. So that's all we're going to do. A pivot chart here as well. But when we right-click, we're going to just change the chart type to a line and just click on the basic line chart. And again, just some super, super basic chart formatting stuff I'm going to do for now. And not go too deep into this stuff, the formatting charts, because that can be a whole new lesson on its own. Then finally, for the pie chart, we have just three categories of data for the contract field which you've seen in the past. Insert pivot charts. We're just going to change this chart type as well to a pie chart, which I don't use too often, but it is there, I want it's there. So this legend simply tells you kind of like what the name of. It helps you figure out which color is which color. And what I'd like to do is maybe add in the data labels here so I can kinda see what the actual value is. But here's what we're gonna do, something a little more creative to get data label. That's a little more interesting for us to read. Instead of having to use the default, the default legend here. So let's go back to the top and look at the instructions. We're going to create a pivot chart for every single type of chart. Now we're going to create user concatenate formula. We're going to write this when Kennedy from somewhere outside the PivotTable that concatenates the series name, the value, the percent of total, and also insert new lines into, insert a new line breaks into that concatenate formula. So the Cook County farmers, and it looks something like this. I'm going to copy and paste this so we can have something to start with. And we'll fill this out down below and you'll start seeing what this looks like in terms of building this out. So I'm just gonna go right here and write equals and then paste them. What I've so far in instructions. And you notice what I have here is I'm concatenating. Let's just walk through this formula really quickly. I'm concatenating B47, which is this month-to-month text with a dash. Then I'm taking the dollar symbol, converting see 47 into a dollar or format. So that's a $1.47. Then dash. And I'm rounding C4, T7, which is this number divided by C5, which is the total. And multiplying and running that to 0 decimal places and adding a percentage sign to the end that any new line break. Let's just hit Enter and see what outputs from this, because it'll be more interesting to be more informative to see what this looks like after we see the result. So what we have here is again the series name plus that dash, plus the actual dollar amount, which matches up with month-to-month here. And then 33%, which is simply 4.4 million divided by 13.7 million. Important thing I want to show is this chart tenting. This chart ten tells Excel to insert a new line break into the formula. So I'm going to do the same thing, but for one year for this specific value in the contract column. So I'm gonna select this data right here. I'm literally just going to write comma and then paste everything. And let's just kind of edit this to be for the next series, which is B48. We're taking a dollar sign. We're taking the dollar format for C4H8, which is for one year, C4H8. And then simply rounding. See 4850. Hit Enter. And notice how you don't see the new line break in the formula here. Notice I goes from 30% directly to one year. And then the value mount. We'll see what this looks like. Want to actually insert a text box and listen to this one more time. To account for the last series. I'm going to edit this to be four before E9, C4H9, round C4H9. And now I have all three month to month, one, year, two year along with the value mount the percentage amount. And now I'm going to insert a text box right here. For now. And in the textbox formula address bar, I'm going to say equals to the formula is credited with the concatenate formula down here, which is F, C6, T2. And look what happens in the text box. If I expand this all the way to the right, notice how there's new lines interred entered into the textbox, which is really cool. Now, that new line is caused by that char ten in the concatenate formula. So let's say if I delete this chart ten and the first one for series and see what happens. Notice how this one-year automatically gets concatenated to the first series. So if I just undo that, undo that now I have the chart tens here. The chart tends basically tell Excel, insert a new line break when you're putting this formula into a text box. And then what you can do here is that this textbox, and it can be a new data label that you can overlay into the chart to make it more informative on what the data means. So this will become more clear later on in this class, but I'm going to delete these, the labels for now. And maybe I'll move this over to the right here just to make it, and I'll make this much smaller because this is way too big. And let's go here. Let's see me this smaller little bit. And then it just gives your, your colleagues, your teammates, your clients a better way of looking at specific data about your pie chart or whatever it is. And we conclude custom calculations, which is this percentage, which tells the audience how much the month-to-month slice takes up all over the total thing. You can imagine putting other various data points in this data label that will help communicate some aspect about your dataset that the basic legend or data labels can't tell you about from the pie chart. If I go to Data Labels, I believe you can change these data labels to include other things I can value from cells. Oh no, I don't wanna do that. I can include the series name, which is not very helpful because that's the series a megabit, the category name which is similar to what I have here. I'd also the percentage which is nice here. But the important thing is that like I can customize this data label to be whatever I want. Whereas this one, if you're constrained to whatever Excel has and the default options. And I typically like to use this one. I want to call out specific numbers, my dataset. So it may not include like one-year and two-year, these two lines and only include month-to-month. But the important thing here is that you can also just like the data labels on a pie chart or whatever charge you have. This one county from an automatically updates based on this pivot table right here. So as this data gets updated, this concatenate formula gets updated as well, which updates this textbox, which references this concatenate formula. 8. Consolidating to One Dashboard: We've created a variety of charts and visualizations in this class so far. So let's tie everything together into one cohesive dashboard that you and your colleagues, your teammates and other internal stakeholders can interact with and play with to visualize your data. If you've been following along in all the lessons in this class, we've created various visualizations charts from these pivot tables and we're going to put it all together into one main dashboard that we can share with our colleagues and teams to better collaborate on our data. And this lesson will also feed into less than eight and how we can make this dashboard very dynamic. So the first thing we're gonna do is using the charts and visualizations we built so far in this last few lessons, we're going to cut and paste those charts and just simply put them into our main dashboard area. So let's look at data bar is less than two. We're going to actually unfiltered this for now. Let's choose. All. Oops, this is not right. Let's look at Mel. I'm just going to basically just copy and paste this. Put into lesson seven and do a paste and maybe make this font a little less. This will weaken, keep everything nice and tidy. And there's a few ways of doing this. One way is just copy and pasting in the PivotTable. But then you might have to, you'll see these drop-downs, which is kind of annoying as well. So another way of doing this is what I like to do is actually just reference the data. So what I'll do is I'll select just these values that were referenced, the actual series. So now I just have the categories of my boldest little bit. Here I'm going to reference the actual data. Right here. I'm starting to get 13, so I don't get that GETPIVOTDATA. I'm going to change this to sell Dieter team. That's our actual revenue number. And I drag this down. They convert this into a currency format that I like 0. And we'll call this total revenue. This. Then now I can actually apply the data bars that we have in the original pivot table, which I'll just do as conditional formatting data bars. I'll do this kind of like green with a gradient that I like. The sparklines. Sparklines come from less than five. I'm just going to copy these states over for now, just like I did with the payment method over here. Let's move this over to here. He's special values. Let's actually insert a few more columns here and our dashboard here, I'm also going to the same thing that we did it with the sparklines and less than five, Let's make these all the same font size. You select. The cells, you want to insert the sparklines in. Go to Insert sparklines line. And we'll select the data from less than five, which was all data from C7 to I believe it was n 32. It okay. The June data got filtered out. Let's bring back June. There we go. Now we have a full from January to June. And now our spike lines will now automatically update based on that pivot, the pivot table, then the timeline slicer from less than three, if you recall, we had a slicer that lets us filter data. I'm actually just going to copy and paste this pivot table because I kinda like how the slicer can automatically change the data, the PivotTable in the dashboard. So let's paste this pivot table over here. Let's make this smaller. And I'm just going to insert brand new timeline slicer. They hit Okay. This, you saw this in less than three. I'm just gonna move this over here to the right, the header. And I have a nice little timeline where I can basically filter to specific months. Can use, as you can see here, that they'd automatically filters the pivot table based on what you select. And let's just select everything for now. We can see our data. Now, the map chart, let's actually move this level down a little bit because we have our state-level data over here. I'm just going to copy and paste this chart that we've created and less than four over into lesson seven. It's going to be a little scrunched, but it's okay. We'll just make this a little smaller. Maybe we can move this down as well. Alright, now starting to come together a little bit. And then we have a pie chart. Let's move this slicers thing down a little bit as well. We have our pie chart with dynamic legend from less than six. Again, I'm just going to copy and paste this chart from us and six, put it here below this label. And notice how the textbox that we originally were first remove these data labels. Notice how the textbox that we originally created, how it looks like they're removed somehow and let's actually go back and insert back that textbox. Insert textbox straggler here. Remember how we do this? We just say equals to that cell. And now we have our text box. I'm not sure I cut this textbox and put it into lesson seven. And there we go. I'm going to make this smaller as well, so we can fit this all within the chart. And this is really again, a nice way of looking at your data and looking at the actual values from your, your chart. If you want to create custom values that the default legend doesn't really show. Now, in step two, we're going to create one or two additional bar charts or line pivot charts based on the raw data and put them underneath this header right here. All I'm gonna do here is actually just take this, this I shouldn't get. It's going to create another bar chart off of less than two zeta, which is this thing right here. I'm just going to say woops go to someone the wrong file. Now. I'm going to say inserts, create a pivot chart. And it will automatically create as barter for me this, cut and paste this, and put this into our lesson seven. And that's going to be our bar chart. There's again, multiple ways that you can format this. Again, the most basic thing to do is put the legend at the top. And notice how these labels are pretty long. I'm actually reverse the the, the chart type to make it a horizontal bar chart. So let's see here. It's been a while since I've done this Change format. There's a way to change the chart type. There we go column. And then 2D pie chart. I like doing this because it's easier than here to read the actual long labels here. And then we can also fix these these axis labels as well. But I'm just going to leave this for now for the grid lines. And in the next step, align the charts by selecting shape, Shape, Format, align, or also using Alt and shifts. So what this is, what this means, when you have a bunch of charts stacked on top of each other like this, this one a little matter too much for us. Now, these two, when you have two charts, I want you want to align together. The easiest way to get them to align as just selecting, let's say this one is over here, this one over here. You can hold Shift, select the two charts, Shape Format, and then go to Align. Click on Align Left. And this automatically puts the two charts flush to the left. You'd also line right top wherever. This is the fastest way I like to move charts around to align them together so that they're kind of like all flushed, flushed together. Another way of doing this is by holding down the Alt key, which lets you move the chart around based on the actual cells in the spreadsheet. So if I hold on the Alt key and I start moving the chara, notice how they like. It snaps to the grid essentially. For some reason also creates a copy which is kind of strange. I'm not sure why. If I hold the Shift key now and I move my mouse left and right, it doesn't let me move the chart up and down very easily. So it's another way to kinda like align things along an axis. If I hold the Shift key down and up and up and down, it's hard for me to move it left and right unless I'm really move my mouse around. So it's another way to align things correctly. I usually just do this. Select the chart. I just do the Align thing here because it's the easiest way to move things along to one side. So that's how you can align things around as you is, your dashboard starts getting more and more charts. The alignment is really important to make it look nice and tidy. This one is not super well formatted, but you kind of get the gist. Let's make it smaller as well. And another thing I'd like to do, which I won't be doing in this dashboard is we're moving the legends from all of my, my chart. So if all of my charts have revenue here and it just says total revenue here. If this was also total revenue, Let's say I had a chart. Find other revenue chart that I had from previous lesson. I actually don't have one. But what I would do in this case, if I have a bunch of charts that look like this, if I had to turn like this, I'll move this over here, just making it up for now. If I have this same blue color for revenue, I would actually delete this legend up here and then literally just go up here and make my own custom legend. The reason is because you don't want to repeat this legend over and over again. Multiple charts we can do is just literally draw your own login, like go like this and but some border around this, like this. And there's better ways of formatting this. Well, you could say total revenue is equal to and just make this like the shade of blue that you like. So when people look at the legend, you'll see like, oh, like anything on this dashboard that has a blue in the chart label. That's going to be because their total revenue. So if I look down here, you'll obviously have the chart title renamed to revenue by contract type. But people will know that the blue corresponds to revenue by this custom legend that you have up here. The final way I might like, make this look a little more tidy. Again, this is still pretty rough around the edges, is to simply go to View and then remove the grid lines. We just have five. And now you just have this nice plain white background which you can then share on a Zoom call or a meeting. And it just looks a little nicer. And going through the data when you don't have those grid lines kind of distracting the, your teammate or your stakeholders from the main dashboard. Another way of making the, the grid lines disappear, which is another hack I used to do before I learned the grid lines was just selecting this data right here. Sometimes I like doing this because I don't want my whole spreadsheet to be removed. The grid lines, I'll just paint the background color of the cells to white. That's it. And it just makes everything look again, all nice and tidy. But then you'd say I lose my labels because these were actually black, white text on black labels. So don't wanna do that. Maybe this case I might just do remove gridlines. And that's how you can basically put all the stuff we've done so far in the last lessons into one dashboard. And this is why you should also consider when you're building out your own dashboards for your own company or organization. 9. Linking Interactive Slicers : We're going to create a few slicers and add them to our dashboard so that we can quickly filter and sort through our data and our pivot charts for our dashboard. Now, one thing that we want to do is be able to connect our slicers to all the pivot charts and pivot tables in our dashboard so that when either one of our teammates updates the slicers, it will automatically update the entire dashboard. And so everything is automated without you having to do any manual, copying and pasting or hitting drop-downs or even Mac. Let's take the dashboard that we created in less than seven and make it a little more interactive for our teammates to use so that they can slice and dice the data the way they want. Speaking of slicing and dicing data, we're going to actually add some slicers that make you didn't make your data more interactive to our dashboard. So in step one, we're gonna go back to the dashboard query and less than seven and add a view slicers underneath in the section that says slicers. So let's go back to lesson seven. And we're going to be down here and this little section right here. This is delete this chart for now. It's, it's a copy of this. And let's make a few slicers. So we have this pivot table here, which is again a copy of all the PivotTables in this workbook. I can just go to PivotTable, Analyze, Insert Slicer. And let's say I want to insert a slicer on gender. Hit, Okay? And then we get this lesser here. And if you've taken my other classes, you know, you can do a lot of customizations to these slicers, like resizing them, changing the colors. And all this can be done by going to right-click and then going to Size and Properties. And you can check out all these different things you can do to customize the slicer. Lets us make one more slides are for now, just so we can show the power of Slicers. Let's go to Insert PivotTable slicer. And let's do one this time on the contract type. Now we have this slicer. Let's drag it down to our slicer portion of the dashboard. This part of the dashboard is kind of like if you zoom out a little bit, when we share this dashboard with our teammates and this Excel file, they can use these slicers to control what actually shows up on the, on the dashboard in terms of the data. Now the issue is these slides, There's aren't necessarily connected to all of this data. So how do we actually do that? So let's go back to the lesson eight steps. We're going to right-click each slicer and select report connections and check off all the pivot tables and the list of connect the slicer to those Pivot Tables. Essentially what we wanna do is connect these slicers to all the data on this dashboard. So again, when people click on these options, it will automatically filter the data because right now it's not doing anything. As you can see. I can actually, if I want to be able to select multiple options here, I can click on this checkbox and now I can select both male, male and female. Okay, so let's follow this up. So if we right-click the slicer, go to report connections. Let's see what pops up here. You will see every single PivotTable that you can connect this specific slicer to in this file less than one pivot. Let's do data bars. You can see here these are all Lesson number by this number. And let's go back to lesson one, lesson to lesson three and take a look at what these represent really quickly. If I go to lesson one. If I click on this pivot table and then click on PivotTable Analyze. You notice here I rename this to pivot lesson one pivot. By default, it'll give you a like some random name like PivotTable one pivot table too. But by giving it a unique name like this one, less than two data bars, because this one represents the pivot table for data bars. This means that when I right-click this slicer, go report connections, these names are a little more descriptive as to what these pivot tables are present. You can figure out whether or not you want to connect these pivot tables to this slicer. So I'm just gonna go ahead and check up all these other tables so that this slicer will control all the PivotTables in all my lessons. And then, as you know, in our, in our dashboard, all of these charts, these visualizations are all connected to those original PivotTables in previous lessons. So now I'm going to zoom out to show you the power of this lesser. Show as much of the screen it's possible. If I now click on mail or female. See how all this data changes dynamically. Because we've connected this slicer to all the data, all PivotTables in our, our file. Let's do the same thing with this slicer as well. Report connections, expand this, this is check-up all the pivot table. Pivot tables. Alright. Now, if I uncheck some of these contract options, you'll see how this data all filters based on what I've sucked in this, in this slicer. Which is really neat because now when you share this dashboard with your teammates or colleagues, they can clear out the data in and filter and sort this data without having to actually go to the underlying pivot tables and select the filters and the dropdowns. This makes this dashboard much more interactive and everything is contained on one single page. And of course, when you actually organize your dashboard will hopefully make it a little more organized than this. But I just wanted to show you what the power of slicers can do for your dashboards. In step three, we already did this, but you can just click on a few of the options and the slicers to see how these can dynamically change the data on your cohesive dashboard. And that's less than eight. 10. Updating Your Dashboard : One of the biggest problems with pivot tables is that sometimes the data doesn't always update when you have new data coming. In. This final lesson, we're going to show you, I'm gonna show you how you can update your data pivot tables with one-click by converting your list of data to an Excel table rather than a regular list. So this will automate everything for you and your teammates and requires less manual work every day or every week or however long that you're updating this dashboard. In this last and final lesson of this class, we're going to talk about how you can update your dashboard data from us and seven, to make sure this data is always updated when you get new data into your Excel file. Essentially, what I'm trying to tell you is once new data comes into our raw data sheet, we want everything on pivot tables, all reports automatically update without us having to touch anything. And this is a really important lesson because this goes into the kind of like the back-end infrastructure around how your dashboard can stay up-to-date. Because typically speaking, you're going to be hitting all kinds of new data added to your source data right here, which is a file called raw data. New data will come to the bottom of this of this worksheet. And you want to make sure your pivot tables can pick up that new data. Right now it stops at around 6005987 to be exact. And in step one, we're going to pretend we have some new data, which can be this week's new data, this month new data, and it's in the new data tab. So we have this new data tab and this is zoom in a little bit so we can see some of the data. It has the same exact column headers as our raw data. And we have about a 100 new rows of data, which represents data for june. Our data are raw data only goes up through June 1st, and this contains all data for the month of June from June 1st to June 30th. So we have the first thing we wanna do is copy the data or new data from the new data worksheet into the raw data tab. And we're just going to pretend like we just got a new data dump from our database and we're going to add the data to the bottom of God brought it up here. So we're gonna go here to new data. I'm just going to copy everything from row two and down. So I've copied everything from row two to 100. Go over to the raw data tab, which is our source data type where all our pivot tables are built off of. Do regular paste right here. And you may think that your job is done. Let's try refreshing and PivotTable and see whether or not it picks up that new 100 rows of data that we just added to the bottom of the dataset. So it goes from now 5987 all the way down to 6,086. So if I go to our dashboard really quick and let's say I look at this table. If I click on PivotTable and if I just right-click, I shift from first-order refresh. Nothing happens. We should expect to see more data for the month of June, but nothing is happening. If I click on PivotTable, Analyze and click on changing the source, look at what happened. Look at nothing actually have. And if you look at the raw data, it still goes from A1 to Q 5987 in our raw data worksheet. And it didn't pick up these new rows of data from 5988 and below. So you may be wondering how do I keep this PivotTable, this pivot table, all these pivot tables up-to-date. If it can pick up that new 100 rows of data, it's a problem, right? Because I'll have to go into each pivot table and then change the data source and change this to be from K1 through Q 600 and whatever it is like an 86. Now there's a few hacky ways of doing this. You could make this a name range and use the count, a offset trick, which we kind of talked about, I think in lesson, lesson seven or five, trying to find, yeah, we use this offset function to dynamically create a range. There are some hacky ways of doing this, but an easier way, a more kind of native way to do this in Excel is creating a table. If you want to learn more about tables, I have another advanced Excel class all about Excel tables, which is really one of my favorite features in Excel. We're going to now go to step three. And this deleted new data from our main table and the rod Data tab we just added. So let's just go ahead and delete all this data for now. We went from 598598826086. This was our new data we added. Let's just delete this all for now. So we're back to square one of our original raw dataset. Step four, Let's turn the data in raw data into a table by selecting everything and pressing Control T, We're going to name the table raw underscore data as well. If you don't know what tables are an Excel really quickly, they are simply a way to add some formatting to your data, your list of data. But more importantly, it gives you a named kind of table object you can work with and other parts of your Excel file. So again, take my other Excel tables course. We'll learn more about Excel tables. But let's just follow the steps in this lesson for now. We're going to turn the raw data into a table that pressing Control T after selecting the whole table. So go to raw data, and I'm going to select everything from row one all the way down to 500988. I see everything selected. And now I'm just going to press Control T. And you notice how this you get this option saying where is the data for your table? A1 through Q 57. My table has headers. We have headers in row one. Hit, Okay? And let's see what Excel happens. Excel. Notice how our data looks pretty similar, but now it has these alternating row colors. It has these drop-downs which we can filter on. May look like you just did some basic formatting to our table, but it's important to know that this table can have a name. Now, we're going to call this table raw underscore data. So now I can actually use this table in formulas elsewhere in my Excel file. Let's go back to Lesson Nine. And now let's do, let's copy and paste the data from the new data tab again into the raw data worksheet that contains a raw data table. So we're gonna do the same thing we did in step one, which is copying everything from row two to row 100 in our new data. Again, this, this will represent new data coming from our database for this week's data or next week's data? Well, I guess this week because next week is in the future. Let's copy this new data. Go back to our raw data tab, and go to the very bottom of our table that we just created. And let's see what happens when I paste this data from 5988 and below regular command V to paste. And notice how Excel knows we're trying to add additional data to this table. So it adds the alternating row colors to everything below 26086. Now let's go to the final step and refresh the pivot table to see how it includes a new data that was added to the bottom and our raw data worksheet. So remember previously when we refresh this PivotTable, it didn't refresh the data. I didn't include the new rows of data. Let's see what happens now when I refresh this table. Right-click and then refresh. Notice how the June data kind of increased a little bit from thirty thousand, two hundred and eighty thousand. To verify that we pick up the new data. I can go to PivotTable, Analyze, change source data, and see how the references from A1 through Q 6086. So this means that picked up the new data because originally I converted my original table of raw data to a table. And so this now tells the PivotTable. Any new rows I add to this raw data table are automatically include that in the dataset. And this will feed into the pivot tables. So now if I go back to each lesson, I think these multiple already been refreshed by default. But if I refresh this, I probably already refresh because I think when you refresh one refreshes all. There's some setting for this. I can check the data source to make sure that that the source goes to the very end of my raw data. I can double check this with this table as well. And this is again a really neat trick because as you get new data coming into your Excel file, if you add the bottom of this table, your pivot tables will automatically pick up all those new rows of data without you manually having to go here, right-click and then change the data source and changing these rows, the row number to pick up all those new rows of data. This trick allows you to have a more dynamic way of updating your dashboard here with the goal of removing the need for you to have to do a lot of manual formula, editing or copy and pasting in your Excel file. And it just makes it easier to update your dashboard going forward when new data comes in. 11. Final Thoughts: Thank you so much for taking this advanced Excel class on pivot tables and building a dashboard. I hope you learned a lot about how you can automate a lot of the reporting and pivot charts and pivot tables in your current Excel files. The biggest takeaway again, as Automate, automated automate, how do I reduce the manual work on my pivot tables and dashboard. So I can spend more time analyzing trends, delivering insights, and ultimately delivering business impact to your company. I hope you get a chance to do the class project. Please ask questions in the class forum. Post your project. In the project section is supposed screenshots. And I'll be happy to provide feedback and answer any questions you have. I hope that you'll become an Excel grew on your team and impress all your teammates with all your new Excel PivotTable, and dashboarding skills.