Speaker 1: In this video, we'll create this awesome interactive Excel dashboard in just 15 minutes. First, we'll structure the dashboard and make it dynamic. Then, we'll create all of the charts and visuals. And finally, we'll customize them to fit our look. So let's get into it. So here's the Excel file that we'll be working with, which you can download for free in the video description. Let's suppose that we're working at McDonald's and we're tasked with creating a sales dashboard for the South America region. And for this, we're given three different tabs. The dashboard, where we'll have all our charts and visuals. Then, the inputs tab, where we have all of the data to create the visuals. And finally, we've got the contacts tab over here, just to have the contacts of all the managers. So let's go back to the dashboard. And the first thing here is to make this dynamic. So you can see that we've got all of these icons here to the side. We can actually hyperlink them, such that they navigate to different tabs. So we'll go to right click on this one, say. And from here, towards the bottom, you'll find the link. And we want to link it to a place in this document. And more specifically, to the inputs tab. We'll hit OK. The third one, we can link as well. We'll go to link again. This time, for the contacts. And the final one here, you can see the question mark. Let's say this is a support, so it actually links to an email. So let's suppose this is to an email address. And you can specifically type, say, info at support dot com. That's just a made up email, but we'll hit OK there. And then this first one should go to this dashboard tab. So we'll go to link again. And link it to a place in this document, the dashboard. And we'll hit OK. The idea here is that when you click on one, it's going to take you to that different tab. We need to add the same thing across all of the different sheets. We'll go to this first one. Just select the column by clicking on the A here. Control C. Go to Control Page Down. Control V. Control Page Down again. And Control V. And now we can dynamically navigate across the different sheets. Now that we have the navigation in place, let's work on the general structure. So we're going to add different shapes here. We're just going to go to Insert. Then under Shapes. Let's go ahead and create a rounded rectangle. And we're just going to drag it more or less from here, from column B all the way to column M. And here you can decide how rounded you want it to be. We don't want it too rounded. So I'm just going to hover over there. The shape fill, we're going to want it in a white color with no outline. So we're going to go to no outline down over here. One final touch here is actually adding a shadow on the background of the shape. Kind of like so. Once we have the shape, we can go ahead and add a title here. So let's call this a Sales Dashboard for now. Control A. In case you don't see it, it's probably because the text is currently in white. So we'll change that to say a dark blue. Control B to bolden. To make it bigger, you can just click on this bigger A here. Once we have this first one, you can just hit the Control Shift and then just drag it. That's going to allow you to drag it down over here. So the idea is to have three different shapes here such that they're going to be the three KPIs. So we'll go ahead and reformat this. Let me fast forward how I do that. Then right below that, we're going to have one large chart over here and then one smaller one down below here. So let's do the shapes for that as well. So Control Shift and just drag down. Let me reformat that as well. And finally, we need to add one more shape over here which is going to represent the map of South America. Nice. Now that we have all of the shape structures, let me fast forward how I rename them. Here's what the structure with the titles is going to be looking like. So we've got the title and what the figures are in. Right below that, we have the three main KPIs. Then we're going to have some kind of a line chart over here alongside the customer satisfaction scores. And finally, a breakdown using a map chart to see the sales by country. Now that we finished this first step, the second step is to work on the different charts and visuals. So let's head over to the Inputs tab for this. So Control Page down here. The first thing we're going to do is go ahead and select all of the customer satisfaction scores here. So just hit on Control A for that. And then we're going to go over to Insert and we're going to add what's known as a radar chart. So we'll go to this drop down over here. Under All Charts, we're going to go towards the bottom where it says the radar one and we'll hit on OK there. And we don't really need this legend here as we kind of have that already in the labels. So we'll delete. We'll also delete the title here. Now you can more or less see how we're doing on each of the factors and speed clearly isn't a very good one and nor is service. We'll just go ahead and move this over to the side and make it smaller and we'll work on reformatting it later. Right next to that, we've got the map chart over here. So the country breakdown. So Control A again. This time under Insert, we're going to head over to Maps. Go under Filled Map and we can just remove the title there. You can see that's looking fairly good already. So we'll just make this a bit smaller and leave it here to the side. Then for these sales figures over here, to compare them easily, we can use something like a line chart. So we'll go Control A, Insert and we're going to go under Recommended. Hopefully it shows up there and you can see that we've got the line chart. So we'll hit OK on that. But you'll notice that this axis over here has way too many labels. So you actually can't see what's going on within this range. So from more or less 180 to 250 is what we care about. So first we'll delete this title here. We'll change this axis by right clicking on it. Going to Format Axis. That minimum amount is the one we want to change and we'll add something like 180 here. And hit Enter. And now you can see that discrepancy a lot better. And again, we'll just move that to the side for the time being. Now let's work on these up over here. Basically here we're going to get the different proportions. So the percentage of complete, if we look at the formula, is simply going to be the actual over the target. So we just want to get these two here. So the complete and the remainder. That's going to be enough for us to create a donut chart. So we'll go under Insert. Under the pie over here, we're going to select the donut down below. Great. Let's remove the title. We don't need the legend either. And one more thing that we can do here is actually add a text box inside, which is going to give us the percentage that's completed. So in this case, it would be 85. For this, let's go to the Insert tab. Under Shapes here, we're going to select the first one, which is the text box. And then we're just going to drag it over here. But you can't simply put the 85% as that's not going to be dynamic. So we're actually going to go up over here to the formula bar, go to Equals, and we'll select that 85% ourselves and hit Enter. We can then reformat it. So maybe put it in the middle and let's also center it like this. Ctrl-V to bolden. We can make it a bit bigger to something like size 16. And then we'll change the color to white. Something like that dark blue. Great. From here, let me just go ahead and resize it. I'm going to fast forward how I do the same thing for all the other ones. But one final thing is making the donut the width here a bit thicker so we can see it a bit better. So we'll go to right click there, go under Format Data Series. You can see that we have the donut hole size. So this is what we want to change to something like a 65. And now you can see how it looks a lot thicker. Let me fast forward how I do the same thing for these other ones. You can see here as I'm working on the second donut chart, I just need to go ahead inside the text box and change that from a D to a G. I'll hit Enter there. But you'll notice how the formatting all of a sudden changes. If you want to bring it back to this original formatting, you just got to select that text box, go under the Format Painter, and then just drop it over here. So click there. You can see how it's reformatted it the same way. Let me fast forward how I do the last one. Nice. Now we have all of the key visuals that we're going to need. If you like what you're seeing and you want to learn more, you can also check out our range of courses, which include Excel for Business and Finance, PowerPoint, Finance and Valuation, as well as a range of bundled packages. And what makes our courses different is that they're all applied to the real world. So aside from teaching the theory, our lessons also offer case studies that simulate the type of work you might be assigned in your day to day, ranging from creating a financial model from scratch on Excel to creating a professional slide deck presentation on PowerPoint. And if you get stuck along the way, you can easily ask us questions in the course discussion forum. So if you're interested in checking it out, you can go to a link in the description below. All right, back to the video. Awesome. Now that we have the dashboard structure and the visuals, we just need to bring it all together. So for this, let's go back to the inputs tab. First, we'll select the map chart and just hit on control X. From here, let's go back to the dashboard and control V. And we're just going to drag it over to the side. And let me just reformat this one. You can clearly tell it doesn't look too great because the map is so small. If you want to change that, you just need to go to right click under the legend. First, click on the legend, right click, go under format legend. And we want to click deselect this button here. So show the legend one. And now if we look at how that looks, you can see that it looks a lot better because it's removed that spacing. Then let's also remove the fill here for the chart. So we'll go no fill and we don't want a border either. So no border. Now it should all look a lot better. Nice. So that's the map chart done. Now let's focus on the other visuals starting with this trend line over here. So we'll go to the inputs tab, select this trend line, control X. Let's go back to the dashboard here and control V. And for this one, we're going to want to change both the colors and we're also going to change kind of how the markers are currently labeled. First, let's select the entire chart under chart design. Let's go ahead and select the third option here, which looks a bit more elegant. Then we'll click on that specific line, the orange one for now. Go to right click, format data series. And here is where we want to change both the color and the markers. So we're going to select a line that's going to be in red color. And then we're going to select the marker right next to it. Select marker options. And from here, we want a built-in marker that's going to be a circle. So we'll select the circle from here. We're fine with a size five, but we want that fill color to be a solid fill. That's going to be in white. And we also want a border for it. That's going to be a solid line in red. Let's also increase the width of that to something like one point. And now we can kind of see what that looks like, which hopefully looks a lot better. Same thing here with the blue one. Let me fast forward that. Great. So now you can see that we've got one in red and the other one in dark blue. Let's also remove the fill here. So we'll go for no fill and no borderline. Great. Now that's looking a lot more like it. Now we'll work on the radar chart here for the customer satisfaction. So go back to the inputs. Let's Ctrl X again. Let's go back to the dashboard and Ctrl V. And again, we'll just drag it over. Let's try to get the same format as this one over here. So with this dots as markers. So we'll go to right click. Go under format chart area. We want no fill, no borderline. And for these actual points, we're just going to select them. And we're going to go for a solid line that's a dark blue color. And we're going to go for a marker that's going to be rounded again. So built-in. We'll go for a rounded one. And let's go for a dark blue color. Solid fill, white. And we're going to go for a solid line in dark blue. And increase the thickness of that to a one as well. And there you go. Great. Now we just have the three KPIs left over here. And for these, it would be nice to not just show the donut chart, but also have the actual number here on the side. So for this, we're just going to insert a text box. So under shapes here, we'll go for text box. And we're just going to select it over here. And again, we want to make this one dynamic. So we'll go equals. And we're going to link it over to the inputs tab under the actual amount for sales. And same thing with profit customers later. So once we have that, we need to reformat it like we've done before. And then do the same thing for these. Let me fast forward that. Now that I have this first one, I just got to go control shift. And just drag it across like so. And then control shift again. And drag this one as well. Now I just need to change the labels by changing the link. And if you get this issue again where it's not formatted properly, just got to select on the first one. Go under format painter. Just click on that second. Same thing over with this last one like so. Great. Now we're ready to go under the inputs and select the three donut charts here. So just hitting the control key, you can select all three. Control X. Let's go back to the dashboard and control V. Now we just need to reformat all of these. So let me show you how I do it for the first one. Let's select it. Then go to right click under format chart area. Let's go for no fill. And we'll go for no line as well. Then we'll select the donut. Just select this blue part only. Let's say we go for a dark blue color here. So like the dark blue we've currently been using. And we'll select the remainder. So this area that we've still not fulfilled in orange right now. So something like a lighter blue. Like so. Great. Now let me do these other ones as well. Awesome. Now we have a really nicely designed dashboard. Which is also interactive using the different hyperlinks over here. And you can see that it tells us pretty much all of the key information that we will need. In just one sheet. If you want to learn to make dashboards like this even faster using Power BI. Make sure you hit that subscribe button as those videos are coming soon. For more on dashboards check out this link over here to make them with pivot tables. Or this link over here to check out our course. Hit that like and that subscribe and I'll catch you in the next one.