From the course: Access 2019: Building Dashboards for Excel

Build sunburst charts and treemaps

From the course: Access 2019: Building Dashboards for Excel

Start my 1-month free trial

Build sunburst charts and treemaps

“

- [Instructor] Now that we have data in our file and it's linked so that it'll stay in realtime, let's build a few charts. I want to build some charts that will stay static in my dashboard. Let's go ahead and create a new sheet. And I'm going to go ahead and move that to the end. I'm just going to drag and drop it there. And then from our queries and connections, you see the Sunburst Department and Manager. I'm going to actually just hover over that and from the peak, I'll hit the three little dots, I'm going to load to, I'll do a table, it'll be in this existing sheet I just created, I'll go ahead and click okay. This data is perfect for our hierarchy chart because it has the departments and then the managers and then the sum of the minutes that they watched. It's in a table and it's already selected. So what I'll do is I'll go to my Insert tab, and in my Chart section, I'm going to choose those hierarchy charts. I'll go ahead and hit the drop down and choose a sunburst. Okay, great, I'm going to go ahead and move it over, just a little bit. And then I'll close my queries and connections for now. All right, I'll go ahead and size this. And I have some formatting to do. But let's go ahead and give it a chart title. Let's call this Department and Team Minutes by Manager. Again, I'll have some formatting to do. I'm going to change the size. And I might change the size a few times as I build my information. I'm going to go ahead and click one time on the Labels, go to my Home tab, and I'm going to decrease that font. Just make it a little bit smaller. All right, perfect. Go ahead and name this sheet below Department and Team Minutes. All right, I'll go ahead and add another new sheet. I'll go back to my Data tab, I go to my queries and connections, scroll down. Okay, for this one, I'm going to ahead and grab my departments. So I'll hover over my Departments, and again from the peak, I'm going to go to load to, I'm going to load it to a table in the existing sheet, and click okay. And this shows me the total of the minutes watched for each of the departments. So I'm going to go here to A16. And I'll go ahead and load my data for my videos as well. So I'll hover over Tree Map Videos, go to my ellipse there, I'll load it to a table, and I'll put it on A16. It's perfect. All right, great. Now again, this shows me the total of the minutes watched, but for this particular tree map, I really want to just see my top five videos. So I'm going to go ahead and hit my filter there, go to my number filters, and choose top 10, and them I'm going to adjust that to five. And remember, today's top five may be different three months from now, so filtering by top five and connecting it to the live data will make this adjust. All right, I'll go ahead and click okay there. All right, now these are ready for tree maps. So I'll go ahead and click in that top one, I'll go to insert, back to my hierarchy charts, and I'll choose my tree map. Okay. Go to my bottom one here, go to insert, and go and add another tree map. Okay, great. I'm going to go ahead and close my query and connections there and make a few adjustments. So I'm going to delete this legend. Definitely better. Go to my one below here, with my top five. Delete my legend there. And I'll go ahead and call this Top Five Videos. And I'll call this one Minutes Watched by Department. Perfect, and then I can make any formatting and label adjustments that I would like here. I kind of like these like they are, just the defaults. But again, I can make any of those adjustments. Typically, I will save all of my formatting options, like my colors, when I get it onto my dashboard sheet. Right now I'm just constructing all my dashboard options. All right, let's go ahead and call this one Video and Department Minutes. Remember Excel is a universal tool for all different types of scenarios and projects. These charts will update in realtime, but for our dashboard, they will consistently stay the same. They won't update or change based on filters, but they will update based on new data.

Contents