Transcripts
1. Introduction: Hi there. In this course we're going to build an amazing looking dashboard that you can filter between different years, different products, different sales channels in different areas. We're going to start with the very basics with an ugly dataset like this. And then we're going to learn how to manipulate it with pivot tables. And you're going to work with me to create individual charts such as line charts, waterfall charts, pie charts, sells area maps. I'm going to show you how to create gradients, how to line everything up. And then I'll even explain to you what for the finishing touches to make it all look good and stand out. So if that sounds good to you, then I'll see you in the course.
2. Exercise File: Hello, and thank you for enrolling in this course. The goals of this course are to start from the very beginning, we have an ugly dataset and turn into a great looking dashboard. So if you're not confident with charts and graphs or very basic formula, then I strongly suggest that you go back and have a look at my Excel Basics course, creating a sales report. Because in that class we go through the basics of building charts and graphs, some very basic formula such as sum, and then we build it up to design a very basic sales report. However, if you have a greater understanding of Excel, then please go and download the file, which is called sales data in the project section. And we'll get started in the next video.
3. The Scenario and Data Sets: All right, so let's take a look at our first scenario. It says, Dear colleague, I have extracted the past five years of sales information from ourselves system. I need to analyze this data in preparation for an upcoming board meeting. Therefore, would you please send me an Excel spreadsheet that allows me to interpret the following areas, movies, the sales results, the percentage of products are 40 and have been returned. The individual products ratings, the results from each sales channel. And he says, I would also like to be able to select a breakdown for individual years, products, channels, and areas. And once you've completed it, please, as soon as possible. So what's happened is the director has extracted a lot of information from the past five years from the South system. And the director wants spilled to analyze the breakdown. And there's four key areas that needs to be broken down. The first area is the sales results. So they want to see how much has been sold through what periods such as each year. And then they want to know how many products have been faulty and have been returned. So we need to find out the percentage of products that are returned. They also want to know the individual products ratings. So how customers are violated them and the results from e to the sales channel. So that's where the products have been sold. They want to know which south channel we said in the best. So is it on the app, for example, or through the computer, on the telephone to people call up and buy the product. And on top of that, they'll have all the results. And they want to flip through each year and find out what the results are for that year so they can compare year-by-year. They also want to compare byproducts, channels, and areas. So we need to be able to create slices so that they can flick between each of these. So in summary, we've got four areas that we need to think about for this dashboard. We've gotta look at the sales results to percentage of products that returned their ratings and where the sounds are coming from. So we need to think about making this dashboard into four different sections. If we take a look at data that we've been given, which you should have downloaded now. And this data is made up of names, the cell's area, what year it was sold, the product that's being sold, how much it was sold for, how many of them were sold? In total lots of cells. So this times this the month it was sold in where it was sold from, was it 40 or not? And what did the customer right, the product. So we've got a lot of data that's just been extracted from a cell system. And we need to visualize this data because it's very hard to interpret looking at this data just as it is. You can't tell what was the best year, for example, if cells. So that's what we're going to do by building this dashboard.
4. Introduction to Pivot Tables: So in my opinion, from my experience of being given large datasets in the workplace and having to be able to provide them with a good visual analysis of that data. The best place to start is with pivot table. So let's take a look at pivot tables, how they work. I have a quick overview of that as well. So to get started with this dataset and creating a pivot table, we need to turn this into a table. So to create a table, all we need to do is click into the data, press Control and t. And you can see that highlights all of the data for us. And we need to make sure that we click, my table has headers, so it picks up the headers there and click OK. Now, if we want to change the format and the layer of this table, you might not like the colors and the blue and white lines will just go up here to table styles. Click the first one. That makes it light and removes the formatting of the table. We also want to give this table name. Let's call it sales data. And that way by given the table name, we can easily reference it later when we're creating pivot tables. Another important reason to have Add Dataset formatted as a table is if we go down to the bottom of the dataset, you can see this little arrow down here. And that shows that the data will expand if we were to insert a new row. So if we just typed some data in here, we can see that the table expands. Just like that, just down there. And if it wasn't a table, it wouldn't do that. It would not expand if it was just a normal dataset. And therefore, when we link the Pivot Table to the dataset, a pivot table also when I expand if new data is added to it. So it's quite important to have it as a table. Let's just undo that and let's go back up. So now let's have a look at inserting a pivot table. To insert a pivot table, we just need to click into the dataset. Click on Insert. Pivottable. We can always click recommended tables and it can give us a summary of some of the tables the XL recommends. For example, by area and the some of the sounds by area. So how much was sold in Arizona, California, for example, or it could be per product. But we don't want that. We're just going to have a quick look to normal pivot table. So we'll close that and we'll just click Insert PivotTable. Now we can see that it's picking up the data in our table called sales data, which is what we named it. And then it's asking, where do we want the pivot table to be placed? And we want to place them in a new worksheet we didn't want to inherit. We just wanna make a new worksheet for our new tables. So we'll click on New Worksheet and they will open a new one. Down the bottom, we'll create a new tab. So let's just click OK and do that. And here we go. This is the pivot table on a new tab down the bottom. So let's just rename that tub to Saul's line chart. So as we can see, it's created the pivot table in the sales line chart tab. And just a note for a pivot table to work properly, we need to make sure that all of the columns have headers. And because this data would be pulled through to the pivot table for each column. And then ETag headers then needs to be no total rows at the bottom. So nothing can be summed at the bottom or anywhere else. There can't be any totals. And you need to make sure that there's no empty rows. So we can't have a gap in the virus like this, for example, they all need to be joined together for the pivot table to pick them up properly. Flesh, it doesn't do that. All right, and let's take a quick look our PivotTable. Now when we click into this area, which is where we're going to work with the pivot table report will see this PivotTable Field section pop up over here. We can rearrange this pivot table section by clicking in the little cog. And we can, if we want, we can put the section areas side-by-side if that's how you prefer to work. I personally prefer working with it above and dragging it down. So as you can see up here, this is the column headers. And let's take an example. So let's track the area into the Rows column. And as you see, we have all of the areas come up in the rows. Now, maybe we want to know the total sales for each of these areas then all we would need to do is go to total sales. Drag that into values. And you'd see the sum of the total cells by value. And let us say we want to see the breakdown for a year, or we need to do is track the year into the columns and as you can see, then gets broken down with a years into the columns. So effectively all that happens is we can play around with these pivot tables and drag any section into any area in the boxes below, and then they will be reflected. It's quite simple really. There's also an area for filters. So let's say we want to filter by product. We would just track the product into the field to 0. And then you can see a new little bar appears at the top, which allows us to filter by product. So we can just click on the arrow and pick a product that we want to see the sum of the total sales by. So say we wanted to see the computer, will just click Computer, Click OK. And all of the numbers get filtered by computer. Now if we want to remove an item from the pivot table, we just click on it, four out and you'll see that the x appears and then it's removed. And as you can see, the little bar at the top and now disappeared. And we can also change the value field settings by right-clicking Value Field Settings. So company at the moment we can see this is set to sum of total values. So it's summing every value. But if we wanted the average, for example, we can click on average, click OK. And as you see, these numbers will now change to the average values. Okay, so that's the basics of a pivot table. You can see how easy it is to use and what you can use it for, how you can manipulate data very quickly. There's no need for formulas. So there's a really, really quick and efficient tool. I use it all the time in the workplaces, such a time-saver. And in the next video we'll start looking at creating the chance they're going to go in the dashboard.
5. Creating Charts for a Dashboard: So now let's start looking at creating the Charles for the dashboard to our requirement says that we need to show the sales results. So what we'll do for that is we'll insert a line chart. So we're just going to amend this one for the cells line chart. And to do that we want to drag the years and the mumps to the Rows column. So let's remove the area out of the fields and put the years down into the rows. And this also direct the mumps into the roads as well. And now you can see all the bumps go down the row inside the years. And we just want to make sure we change this from average cells to2, two sum of cells total. So right-click on that and click on value field settings as we did before, and its changes back to some that click. Okay, so now our pivot table is ready and we just need to insert a chart. So let's click on Insert Line Chart. And all we need to do now is delete the header and the lines. So let's remove that. Delete. So we just wanted to change the number format dollar. Double-click on the numbers had on overhead to format axis. And we make sure we click in the Axis Options. And we wanna go down the bottom two numbers. And let's change this from general to currency. And yet we're dollars. That looks perfect and let us remove the decimal places as well. So let's just delete the two and change that to 0. And then we go, that's the sales line chart completed. We also want to show another nice chart, which is the sales area map. And it shows a sort of heat map of where most of the cells have been made from which offices. So let's add that in. So we need to just head on over back to the table and then click in there again as we did before, insert pivot table and it's picked up the sales data. We also want that in a new worksheet again, perfect. Let's just drag that along to the end. And let's rename this to south area map. And we need to drag the area into the columns and then the total cells into the Values. And as we can see, we've got our total sales from each area. So that's just what we need. Now, the only issue with a sales area map it it doesn't, for some reason like to be inserted from a pivot chart. So when we click on insert and insert the map filled map, you can see it. We cannot create this chart type inside a pivot table. We need to copy the data outside of the pivot table to be able to insert this one. Plus non-issue, We can do that quite easily. So let's just copy the areas down, control and see, put them here, type in cells so we know what they are. And then press equals and click into the amount that we need to take down. And you can see this creates the GETPIVOTDATA function and this will automatically linked to the pivot table. So whenever the pivot table is updated, ourselves, area chart here will also update. So we just need to do this for free more areas. California, Nevada, and Utah. So now we can insert the Area map by highlighting this map filled map. And we just need to accept the date who's needed to create your map will be sent to being. So click I accept and it should appear. Alright, so let's just drag this over a little bit. And we just wanted to show this area with the data only. So we just click into the job, right-click Format Data Series and we come over to here, and we want the map area to be only regions with data. And that way it just shows areas with data and that rather than the rest of the country. One of the point just to note is that this maps facility is only available in the latest versions of Excel. So if you do have an older version of Excel, this may not work for you. Alright, so we went to the next stage of our scenario. And that is where we need to find out the percentage of products that are faulty and have been returned. So let's head on over to the sales data area. In the cell's data area, we want to obviously insert another pivot table again. And we also want it to go into a new worksheet as well. And let's track this worksheet to the end. And let's call it the full T chart. And for this one, we want to put faulty in the rows. And we want to filter byproducts. So we'll put that in the filter. And that's as per usual, put total sales into the values. So now we can see the sum of the total sales that have been returned and haven't been returned. But we don't want the sum of that. We want to count. We want to know how many individual sales returns have been. So as we saw earlier, that's just quickly right-click Value Field Settings, and let's change that to count. So this will not show the total sales value that's being returned, but it will show how many times a product has been returned at the moment is currently set for all products, but we want to do it for each individual product, for the computer, laptop screen, and the phone. So let's change this one to computer. And we want to create for separate pivot tables for four separate charts. So highlight this all control and C. And let's paste it four times. And we're going to filter them through each different product. So the laptop. And we're going to change this one to the phone. And finally, we're going to change this one to the screen. But we need to find the percentage of products returned. And to do that, we're going to divide how many of them returned. So this amount by the total amount of cells. So this create the basic formula which is equals, and we'll click in the 13, will divide that by the total amount of cells and then press Enter. Now we want this to show as a percentage. So we'll just change that to a percentage. And there we go. That's 30% of products had been returned folder Computer. And let's just do this for each of the other pivot tables. So equals nine divided by 33. Hit the enter and equals and nine divided by the 22. And finally the nine here divided by the 24. And we'll just highlight these free and format them as a percentage as well. Now for Add Dashboard. We're going to want to show the percentages. And to do that, we need to just insert a text box. So let's just draw it here. And we want to link this textbox to this percentage because then we can play around with the formatting and we can move the percentage around because it's in a textbox. So inside the text box and we click up to the formula bar, press equals, and we're going to link it exactly to that percentage. Okay, we can format that in a minute. But let's just do this four times quickly. So I'm gonna copy that and paste it three more times. And that's just move them down and we'll read link them each product. So this one here, we're just delete that and we're going to link that to the twenty-seven percent. This one here we will link to the 41%. And same for the last one. That's just linked that to the 38% falls green. So we need to make the font bigger for all of these as well. So it stands out in a dashboard. So let's highlight them all and change the font to 30. And that center. Alright, we'll reformat them a little bit further later on when we're, when we're finalizing the dashboard. Okay, so now we want to put in the full donor Charles for each product. So we just click into the computer pivot chart and go to Insert Pivot Chart. And let's pick a pie and we want the doughnut one on the end. And click OK. Just move that one up to the top so we know which one it belongs to. We'll edit this to look good later. I'm going to quickly do that for the EBIT for each other. So I will just skip ahead for that. Alright, so that's all of the four charts and they don't look so great at the moment. But when we come later on to finishing off, the dashboard will make them look really good. Ok, so we have free more charts to create. And the next one is the individual products ratings chop. So again, let's quickly pop over to ourselves data. And we want to insert another pivot table. So we'll click on pivot table, has picked up sales data as per usual, and extract the sheet to the end. And we're going to call this one the satisfaction chart. So we're going to put the ratings into the Rows column. And then the revenue total cells into the values and the products into the columns. Now just one thing to point out is quite important for this chart to have numbers. So one star, two star free stuff forced on five-star. The reason for that. So the child can then be numerically ordered. So we can have it going along the axis from one star all the way to 5-star in order. But we'll see that when we look at the chart later. Ok, so let's insert the chart for this one. So we go up and click on Insert Pivot Chart. Pivot chart. It's going to be a 100% stacked bar chart, which is this one on the end of the bar charts that are on their side. And let's click OK. And as you can see, this chart shows the breakdown of all the stars ratings. So how many products have been rated 5-stars? What percentage have been rated 5-stars? So it gives you a good break down. And we're still going to format this chart later on to make it look a lot better than that and easier to read. And now we're on the second from last chart, which is the results from each sales channel. So how many cells have been made through which channel? Through the app for the web site, so on, so forth. So we'll, we're going to create a chart. And that chart is my favorite type of chart. I always love to use this chart in the workplace. I think you can get really good results with this child really does clearly explain the flow from the beginning to the end and how that split apart. And we will see that in this chart, which is called a waterfall chart. So let's head on over to ourselves data again and insert a pivot table. Click OK. Let's drag it to the end. And we're going to call this one the waterfall channel chart. So we need to put the sales channel in the rows and the sales revenue in the values. Alright, so let's insert the Pivot Chart, which is going to be the waterfall chart, go down toward full. And the problem with this one again is that we can't create this type of chart and sort of pivot table. It's a little bit frustrating and it's my favorite type of chart. So it's a bit of a shame, but we can always get work around that. So what we need to do is just copy all the data back outside of the pivot table like we did before. So control C, control V. And then let's use the GETPIVOTDATA function to pick up the amounts, so equals, and let's click on the app. And the same for the computer, telephone and the total. All right, and now we should be able to insert the waterfall chart. So let's highlight the data. Go over to insert. And the waterfall chart is this one here, will fall. Let's bring it over. And let's delete all the unnecessary items in this chart such as the title as well, the grid lines we don't need. And the amount. One last thing for multiple chart to work properly is we need to set a total for the full chart. So there's right-click on the total and set its total. Now we can see that it makes complete sense how the app takes up this percentage of the total. Computer takes up this percentage, and a telephone takes up this percentage and you get a clear breakdown in terms of amount and visually how much cells have come from which channel now. And that is why I like that old full chart. Okay, so we're nearly there now. We're just going to add one more little section in which is the top salesperson. I think that will look good as well. Just to include this, even though it hasn't been asked for, having good steel may be quite beneficial. So one last time, let's go to the sales data. Insert pivot table, click OK. Drag it to the end. And let's call this top salesperson. So we're going to put the first name in the rows and the values, the values area. And we're going to sort this table in descending order so that it shows the highest sales person at the top. So to do that, we click in the drop-down arrow, More Sort Options, and descending by sum of total sales. And click OK. And now we always have the highest person at the top. So to get our top salesperson information, we're just going to click in the cell next, do the pivot chart for S equals and we want to always pick up the top name. So press enter. And then in the cell next to it, we're going to press equals and try to pick up the top amount. However, we just want to change it because we don't always want to pick up Nicholas. So we're going to delete this area in the formula bar there and just type in D4. D4 will do is highlight Nicholas. So we're always searching manually for that because sometimes it does for up a few errors. If we tried to use GETPIVOTDATA in this scenario and then press Enter. And one last thing I want to do is just use an if error formula. So if we just typed in if error and go down and press tab, come to the end comma and then close the bracket and press enter. So if there's no data there, it would just be blank and it won't show up or an error message in the dashboard. And by doing this, we'll be able to show this data whenever it's filtered to the front page. So if this was to change, this data will also change on the dashboard. So there's just changed this to get rid of the decimal places. And again, we need to create the text boxes because we need to do that when we are putting data on the front of the dashboard, that's text. So we'll insert two text boxes. And in the textbooks we will link it to the name which is Nicholas on women. And let's just copy and paste that. And for this one, we're going to link it to the amount. Alright, so now we've created everything we need in preparation for the dashboard. So in the next video, we're going to look at designing the dashboard, the background, and the tiles, and just generally making it look good, ready for the data to go onto him.
6. Designing the Dashboard: Alright, then let's have a look now at the fun part, which is designing the dashboard. So the first thing we need to do is create a tab for the dashboard. So let's click on the plus type in dashboard. And we're probably best to put this one at the beginning of the Excel workbook because it'll be the first tablet they see when they open the workbook. And the first thing that we want to think about when designing this dashboard is we want to get rid of these grid lines. There are no use to us for the dashboard, so we just go over to page layout of the gridlines, remove them. A. My favorite type of background is an office building background. I think it looks really professional and smart. And I'm gonna show you how to get one of these the easy way if you've got the latest Office 365. So let's go over to insert pictures, stock images. And now these images are quite a low resolution. But it seems to work fine for me for the pictures that we're going to use. For the background, you can obviously go and find high resolution images on Google and use those. It's entirely up to you. So I'm going to type in offices and let's see if we can find a nice image that we suitable for background. And this one looks okay. I think I like the look of this one though. So we click on it. Click, Insert. Okay, and this is our image. Now, I think, in my opinion, dashboards that best when the image is a bit darker because we don't want the background to stand out, we just want it to fade into the back. So if we right-click on this Format Picture, and let's go over to picture, picture corrections. And let's make the brightness about negative 70. So let's type in minus 17. Got that looks good. If we were to just insert this image now is, is the current pixel resolution that it has. You would just see lots of multiple squares of this image taken up the whole screen. We want it to just be one full sized image. And the image size that works perfectly, in my opinion, for that at 100% zoom is around about 2010 pixels. And I'll show you that when we edit this. So to edit the size of this picture, what we need to do is right-click. Let's save the picture. Let's save it as darker. Dashboard. Background. As a JPEG is fine for now. Okay. You can save it anyway you want I just saved in my documents. It's entirely up to you and you save it. So just open the folder now where you save the picture. And then we're going to edit the pixels of it. As you can see, the current dimensions of this picture is 8.54416, a little bit small. So we're gonna make that a bit bigger or quite a bit bigger. So right click on it. Click on edit. Now this will open paint. And we're gonna go up to the top and click resize. We're gonna click on pixels. Change that size to 2010 and the vertical size will automatically update as well. Click OK. We can see that it gets a little bit blurry, which is probably why you'd be best going to get a high resolution image off Google. But when we put this in the background, it will be fine. And then just save the image and close it. Okay, so we're back in Excel now. Can delete this picture. And we're gonna go to Page Layout background. And we're going to insert from a file and then just go and find your image in the folder where you saved it. And double-click on it. And there we go. We've got the image as a background. And that looks pretty good to me so far. All the blurriness will be covered up by the dashboard anyway. But again, as I said, if you'd go high resolution picture, it shouldn't really be an issue. You may need to change the pixels of your image, keep adjusting and saving it. And you may need to adjust the zoom in and out until there's only one image on the worksheet. Alright, so let's create the background tiles now for our dashboard, it's actually quite simple. So let's click on Insert Shapes, a rectangle. And the rectangle we're going to do is one for the title bar at the top. So we're just going to drag that all the way along. Move it up to the top. And let's make sure we can see the whole screen. Yep. And make sure it's in the top left corner. So I'm pressing the left enough on the keyboard just to ensure it's right up at the top. And now we're going to insert another rectangle or the bottom. This is going to hold our slices. So approximately let us take a guess because we've gotten, we've got to work out the exact dimensions somewhere in the center about that. Looks about okay for now, and we'll just make that one black for the moment. It looks quite good. Maybe let's make it a bit bigger. Something like that. Yeah, seems about right for now. Bring it down just a little bit. And the next part is we want another rectangle. And this is going to be our dashboard. We need to break this up, but I'm first off, we're going to just get the outline of the size of the dashboard, how we want it to look. So think about that. So far seems OK. You can use these columns as references to see how far in we are. So I can see 123456 columns in this side. 1-2-3, 4-5-6. That's about right. So for now we'll leave it there and we probably just need to adjust this part on the bottom, but we'll do the final touches later. So let's just get it roughly right for now. Let's make this a little bit bigger. Up to the top. I think it's really important with dashboards as well. In my opinion that we make sure the distance between each of the boxes as him REF is roughly the same. So we'll see we'll we'll play around with these in a minute. But let's just get them so they look about the same that'll do for now, right? So we've got the outline of our dashboard and now we need to break these boxes up because we have different errors that a dashboard that we need to see and we want to separate all of these. So the first thing we're going to do is insert some more rectangles. Now, let's go for a rectangle that is about nearly just about third of the width. This rectangle somewhere around here like this. And let's just change the color quick clip the top two grey so we know that it's just an outline. Move it over. It'll be I'm using the right hammer on the keyboard to move it along. Kate, that'll do for now. And let's insert some more rectangles. We want one going across the top width ways. So let's just put that in and leave a gap as well. So we can show that the separate wanted just about halfway. So we can see that's probably using this as a measure. We can see that's a little bit below halfway. We don't want that, so let's just move it up a little bit more about there. Yeah, that was good. And to more rectangles. Finally, after we change this one to gray, insert the shape rectangle. We want it just before the halfway line here when we draw it in. So let's just put it around about here and change it gray Control, C Control, and V. So we get another one. And then this line, these up here. Like that. What I want to do is I just wanted to make these a little bit wider just to remove this because capital a bit smaller. We just need to adjust the width slightly because the gap pair is just a little bit big. So we'll come up to the top. And let's make this one for 0.15 inches wide presenter. And the same for this one. We want them to be the same size and symmetrical. So also for 0.15. Now, we want to move this one over just a little bit to line up with this box at the top. And we pretty much done. All we want to do is let's zoom in a little bit and align the bottom boxes just down here. So I can see it nice to go down one. And this one also needs to go down one to there. These two can also, if we hold both of them, just I'm just going to drag it over. And that's pretty much okay. I want to just make this a slightly bit taller. This one's I've got gap there. All right. I think that's looking good for now. Obviously, you're going to have different gaps if you worked along with me and done this yourself. So you need to adjust them just the way that I did. Just move them ever so slightly. And once you've finished doing that, we need to do is remove the background outline. So click in there in the blue area and just hit the delete button. And now we have an outline. Let's also zoom back out to 100%. If you zoomed in, now, we want to group these together. So when we move them and adjust them in a minute, they will come together as one. So click on one, hold Control or Command, click on the rest of them, and then right-click and group, and group together. Now that all is one, which is perfect. And I just want to do a couple of finishing touches and then we should be okay, I'm gonna make this black box just a little bit taller. So now obviously is we look at this currently this gray is a horrible color for this dashboard. It doesn't blend in at all. Even this blue at the top is not very good. So we want to obviously adjust these two colors that make the dashboard stand out because that's so important. I think when we create a dashboard must have colors that blend in and really make the chart standout adds real credibility to the dashboard. So I really think you should think about your design when you're creating dashboard. Again, nice color palette, which I'm going to explain to you in a second. Some websites on the internet have some really, really good color palate designs. And you can take some inspiration from that. But for this one, I think what we're going to go with is a nice dark blue gradient mix in with a nice red, pinkish color. And I think that colors should match really well. So I'm going to jump into PowerPoint now and I'm going to show you a quick way that you can get some colors from an image that you like and use them in your dashboard. Alright, so we're here in PowerPoint and don't worry if you haven't got power point because you'll still be able to complete this dashboard without it. I'm just showing you a little tip that you can use if you do have it. If not, don't worry, I'll show you how to put them in the dashboard so you can also follow along with exactly the same colors I use. So I've got here a stock image again, and I just went to show you how we can extract these two colored quite easily just by using the eyedropper tool. And all we need to do for that is it's just insert. It can be anything into shape. And let's just put shape here. And there's Control C and V. This will get two of them. Now, let's try and make this top shape the same color as this lemon and it's Bolton shape, the same color as the background. Click in top shape. Go to shape, fill, pick the eyedropper, and let's pick the lemon. And then you go to simple as that. And we'll do the same for the other one. So I drop and this nice blue color, and that's how you can easily get the colors you want for your dashboard. You could, for example, copy in an image from a website that you like and just do what I've done and you've got the colors there. All you need to do then once you've got that is making the hex, I'll show you what I mean. Click on the Shape, go up to shape format. Click on the shape fill, go Morefield colors. And you can even take these free numbers for RGB, which is the red, green, and blue numbers, and make a note of them. Or you can just take the hex code and it'll give you the same color. So what I'm going to do just to show you is take a screenshot of this. And I'm gonna go back over to Excel, and I'm gonna show you how we get this blue color into Excel. Okay, so now we're in the dashboard again. I'm gonna paste in that picture that I took. And as an example, I just want to show you how to get this color as these background of these Dashboard tiles. So we will right-click on the dashboard tiles. Format, Shape. Go to feel. Let's just pick solid feel for now. Click on the color, go to More Colors. And all we need to do is type that hex code in there. So this hex code, we're just going to copy it over three d, c, three d, and this is 00. Can hit enter. Here. We've got the same color. And as I said, I'll show you all of the colors that we're going to use in this dashboard. So then you've got the hex codes for them and you can make sure you've got the same colors me that way. Now once you do pick a color like that, what you'll notice is that it comes up in your recent colors just down here. And that's what you'll keep referring to. When you design your dashboard, now, I'm going to go and put all of my colleagues I want to use for this dashboard in. Okay, so I've copied a couple of colors and I'm going to be using in this dashboard. But here, now, all you need to do to get these colors saved into a recent colors is click on one of them. Dropped the hour down in the fill color area, got to more colors. And then click OK. And to do the same for all of them, more colors, click OK. And now if you haven't look, once you've done that for all of them, you'll see them all in there. So I'm going to leave these colors for you guys in the cell's data. So I'll just leave them there for you. And obviously you'll be downloading this sales data tab anyway. So if you want to use the same colors there though, some of the other colors that we use will be standard colors. But I'm going to explain that as we go along. So now let's finally start finishing off the designer this dashboard. Because we're nearly there. Let's right-click on this dashboard format shape fill. We're gonna go gradient fill because I think a gradient field looks really good. Let's remove these two balls in the middle. So you click on one and you click on the red x, and click on the next one in the middle, click on the red X. This one, we're going to use this dark blue color I showed you a minute ago. So dropdown the feel and that's fine. That is this one here for me. And then the colonia, and we're gonna make it a standard but bit light-blue, which for me is just here. And that's looking good. All right, so let's quickly adjust the angle. The angle companies going at 90 degrees, we will get 0, which makes it look like it's going from left to right. Doc is on the left, no angle straight to the right. It's like a perfect normal looking gradient and will adjust the transparency about 20% that we go from this time to look good. That's a lot better. Now we just need to do the same for the bar to top. Let's just change the colors to a gradient fill. So make sure you've clicked on the part of the top gradient field. No angle, same color. Okay, that looks quite good to me. So let's close the Format Shape. Then there's one last thing we need to do to these. And let's just remove the boulder outside. So Shape, Format, Shape, outline, no outline. And the same for these. Click on their dashboard tiles, shape outline and let's remove the outline. Okay, we can start seeing that coming together. We want to put a title in this part of the top. So it's quite easy to do that. Just insert text box to try and center it. Okay, so we'll call it sells dashboard in capitals. Go it center the text and highlight it and change the size to 26. Now, let's make the text a white color. And we're going to then remove the outline. So no outline and no fill. Just align this a little bit more centrally. I'd say somewhere around here, that's quite good. And the final stage for this dashboard is to add the labels and the titles for each one of these boxes. So what we're gonna do is insert some rounded rectangles. So Insert Shape and we're looking for this rectangle that slightly rounded, which is this one. We have a rectangle with rounded corners. And we want to put them in here. And we want to drag this little yellow dot all the way in so they round. We're going to remove the outline as well. So you can just click on that save time. And we're going to put the feel to white, but we're going to make it transparent. So let's right-click Format Shape. So let's move the transparency to around 80% and get that looks good. Okay, now let's just move it down a little bit in line. Okay, looking good so far, make it just a little bit tighter. Gonna copy and paste this four times and move these into each box. We may need to adjust the size for these later. And finally, we're just going to add some shapes and titles into these labels. So let's just put the titles in now. So we go to insert a text box. And we'll make the font size 12. Let's remove the shape, outline and the shape field to know feel. Complete this box a little bit bigger. And we're going to call this top one cells results. And let's just change the text color to white, white font. Again, I'm just going to copy these four times so we can put a title in each one of these boxes. Alright, so we've got these four copied and pasted. Now, I'm just going to move them into the tiles. Because we know we've got four different sections. This is going to be the sales results section. And we'll have a chart that shows the sales results. Then we're gonna have this outer channel down here. So let's just change the name of this 12 cells channel. And this one we're going to have four product returns, percentage. So we'll change this one to product returns. And finally, this one will be the product ratings. And the very, very last thing we need to do to finish off this design is to get some nice little logos, some icons that fit inbetween here seem to make a bit of sense, so easy on the eye. And we're gonna do that by Insert icon. And let's click on the business results that we want to find. Which one looks good? And this one looks quite good. So let's select that one. And for South channel, let's type in something like maybe shopping. See what comes up for that. I like the look on this one thing that makes sense. What channel it could be on the mobile. Shopping, on the mobile side looks good. And product returns, let's try and type in returns maybe. But it looks quite good. We'll take that one. And finally, we need product ratings. So why don't we type in ratings? Okay, I think we'll go with this one. And then let's insert them. We want to take the graphics feel and change that to white. And says Will four highlighted together? We can just make them smaller, all in one, go like this. So let's distract the sales results up into this one. That looks good. Cell channel down into the cell terminal one. Perhaps make that one little smaller and a second product returns into here over just a little bit. And the star ratings up in product ratings.
7. Adding Charts to a Dashboard: Okay, so in this lesson we're going to copy and paste over the charts that we've made now and stop finishing off the dashboard. So we'll start with the cells line chart. So let's click on that control and X to cut that. And let's bring it into our dashboard. We have control V. And we're going to place this here. And what we want to do is we want to change the design of the chart. One of my favorite charts to use is this blue one here. So you can click on that and it will change automatically to that. And it's just close as pivot chart field. We want to now right-click and format the chart area. Going to remove the field because we want it to be a clear background and we're going to remove the border as well. So no border line around the chart. So let's just resize this chart quickly and let's just delete out the total. And finally, we want to get rid of these filled buttons. So we just go to Pivot Chart, analyze, and click on the field buttons that removes them. Alright, so we just want to adjust the line in this chart now has had some colours to it. And the way we do that is we click on the line and then come over to our format data series. So if this doesn't appear, just make sure that you've clicked into the data series of the line and right-click and then Format Data Series. And we want to go with gradient line. And we're going to remove the middle two like we did earlier up. And we're going to pick red for our fall right color. And we're going to pick a nice green color for left color. And we're going to move the green position to about 50% of the way along. Alright, so now we need to bring in ourselves area map that's controlled and x and cut this, paste it into the dashboard with Control V. We want to delete this legend. We don't need that. And then let's just delete the title. All right, then let's just remove the fill and the boulder. Okay, so let's just resize it now so it fits along nicely. Next to our self-talk. Maybe we'll make the south charges to Louis molar. That's good. And now let's bring in our top salespeople. Nicholas, bring that into the dashboard. Put it there. And let's get the other one for the amount that's cut that and paste it into. All right. Okay, now let's just link to the formula from the top sales person tab at the bottom. And when we click in there, we can see that it picks up the right cheat to take it from. And then just press Enter and do the same again for this box. Clicking just between the equals and a dollar sign, top salesperson, press enter, and now they're both linked. Now let's hold control and click the name and format both of them. So head over to Format, Shape, know feel no line. And it's changed the text to white and the size to 18. Okay, so now our textbooks is formatted. Let's just make them a little bit smaller. And we can bring them a little bit closer together. Now we want a background like we have for the other tiles. So I'll just take one of these backgrounds. Let's take this sales channel one. Let's copy and paste that. Bring it up here. Make it a little bit bigger. And let's move these names inside the box. And let's change the color of the box to read just stands out a little bit more, maybe this second red here. And to finish off, let's just get an icon like we have for the other titles as well. So we'll go on, insert icons. And let's say something like Champion maybe. Well, we got there. Okay, and I like the look of this field trophy, So let's insert that, make it a bit smaller. Change it to white, fill. And there we go. That looks good. Now I'm just going to spend a little bit of time realigning this. You may want to make the space a little bit wider because there may be a big name, for example, or a large number. So I'm just going to realign everything and we'll move on to the next area. So I'm just going to copy and paste all of these donut charts into the dashboard. Okay, so we just want to format each chart quickly. Let's get rid of the fill, the outline. And let's remove the field buttons, the title on the legend. And we'll do that for each one. Alright, so once you've removed the feel, the outline, the buttons, the headers, and the legend, then we need to put these in an order that we're going to remember. So let's just remind ourselves if coming in this order. Now the faulty donut chart is the computer, that laptop, the phone, and the screen. So we're going to put them in that order. So computer can go number one, the laptop, the phone screen, then we'll remember which ones they are. So I'm going to put this one in the first position. And this one in position number two. Just make it a bit smaller for a second. And number three. And the last one. Now we know from top to bottom it's 1234. Alright, let's resize these charts to 1.45.45 width. Now you may need to pick your own height depending on your settings. So just play around and see what fits the best. Ok. We're going to make one more change to the. Pie charts very quickly. We're just going to go into the faulty donor tab and we just wanna change these to descending for each one very quickly. And the reason we want to do this is because we want to see how many had been returned. And we're going to use this blue bar to shares how many been returned before the orange balls on this side. And that wouldn't give us a true reflection. So we're going to make this second half of the donor with no fill for each one. And we're going to change this area to a nice red color. And to make them look a little bit better, we're just going to increase the doughnut hole size. Double-click in here. Probably about 85% is good. Let's do that for all of them. And that way we can now fit out text in the middle. Alright, now we need to go and get the percentage. Let's copy all of these over control X, control V to paste. Now they've gone our site. So let's just bring them back up. And let's click back into the box. And same as before. We just very quickly need to go into the faulty donut chart and press Enter for each one. So now Excel knows where to pick up these percentages from which tab. And we just need to adjust the sizes of all of them. Let's make them 028 size font. Change the text to white. And we're going to get rid of the feel. Okay, let's just decrease the size of these boxes. And one last thing, let's just get rid of the shape outlines. Now this is number one and it needs to go into the first 27% goes into the second, 141% goes into the third one. And finally, 38 goes into this one. And let me just quickly a line these up. Okay, and once you've finished aligning them, we just need to insert the text boxes that will tell us which product is. So let's copy and paste a textbox. And the first one is the computer. Just type this in Caps Lock. And let's copy and paste this framework times. Let us a laptop. Let us define, and then the screen. And that's just going to realign everything and make it look a little bit better. And let's bring in now the waterfall chart for the sales channel. Cut and paste this one in. Let's form that the data labels and put them in the center. Let's increase the font to 12. And that's just change them back to what text, including this one down here. And we just want to change the currency. So right-click on them, format data, labels go onto number, changes a category to currency, and it's removed the decimal places to 0. Click Back in the chart. And we want to format the chart so we didn't want any fill. Let's move into the area now and just resize it a little bit. Removed the folder. Let's now format the colors of the chart. We want to make them all a gradient field and this bar on the left should be white. And let's change the angle to 120 degrees, the transparency to 50%. And for the blue one also to 50%. Alright, let's just change the total bar. So click into that one. Change the left color to red. And the 220 the same as the rest. Transparency also needs to be 50 as well, at a solid border around all of the bars. Let's click into all of them. And at the solid border, let's make it white. Let's increase it to 11. And there we go. That's how waterfall chart. Alright, let's copy and paste in the final chart, which is the satisfaction shop. So we'll click Control and x and paste this in. Let's move it over and resize it. Again. Let's get rid of the field buttons. And we wanted to move this legend to the bottom right, and it's just, I think we've probably best to switch chart so we can go switch row and column. Now we have the percentages, stars and on the bottom, and each product up the top. One of the things I do want to make this channel not Friday. So let's change the chart type and changes to this one, which is not freely. Okay? Yeah, that, that definitely looks better. Now, let's get rid of the field. And let's get rid of the border. Let's change the text to white. And a little bit difficult to read. That looks a lot better. I think maybe we can change the grid lines as well to maybe a nice gray color of transparency that's not so bright. Somewhere around 45% looks good. Alright, now we need to change the individual colors for one-star. So let's clicking to the ones die area and select gradient fill. Let's make this our normal red color, the one we picked. And standard red color for this one on the right. And change the angle to 90 degrees. Okay, and for the next one, which is two stars, commonly orange gradient fill. But let's make this a little bit lighter. Co2, more colors. And that's just track. This BOP would be somewhere here. Let's see how this looks. Yeah, that looks quite good. And the gray one, we will change this to gradient field as well. And we'll change to try white on the left. And for the right. Let's pick this gray color here and drag it down a little bit. Maybe about 84% for the yellow. Let's make it a green color off we pick gradient, maybe this green. And for this one, something like this. And for the last one, let's change that to gradient fill. 90 degrees angle. Start with a darker green and let's end on a lighter green. And finally, let's just give each of them a border in white. Okay, so now we've finished adding in all of the charts into the dashboard. In the next video, we're going to look at finishing it off with the slicers and going over any final touches.
8. Creating Custom Slicers: All right, so we're now on to the final part of our dashboard creation. And that's to for in the slicers. Now the slices are use to manipulate the data. As we know in a scenario, it says that they'd like to be able to select a breakdown for individual years, products, channel and areas. So these slices will enable us to do that. So to do that, and let's click on a chart. We can use this char here, click on slicer, and now we need to insert slicers, as we just mentioned for the years, the area. So let's click on those. And then we also want to insert slicers for the product and the sales channel. And then click, okay. So these slices are currently linked to the chart at the top. Now, for example, we can take these slices and click on a year for it. Let's pick 2015 for example. And you'll see that the chart changes for 2015. So it's filtering the PivotTable for year 2015 in the South chart. And you also noticed that none of the other charts change because these slices of not got connections with any of the other charts yet. So as we flip through the years, we can see that they change automatically. And what's actually happening if we look at the sales line chart, because it's been filtered in the pivot table here. So we're on 2017 here, and we've chosen that for the slicer. So to remove the filter in the slicer, just click Clear Filter button. But what we want to do is we want this slicer for year to interact with all of these charts at the same time. And the way we do that is right-click on the slicer report connections. Let's make this a little bit bigger. And we just want to connect to all of these. So let's tick all the boxes. These are all the charts. So the 440 donut charts, the sales area map, and the multichannel Chart. And let's just click them all. So they all now linked to this pivot slicer. And click OK. And now hopefully when we pick an individual year, all of the charts should change. They do, that's perfect. We can see them all changing. Alright, brilliant. And we just need to do that with all of these pivot slices very quickly. So just right-click on each of them, report connections and very quickly make them bigger and click all the boxes. So for products, we're going to leave out the full TDD donut charts. And we'll just pick all of the upper ones. For South channel again. Just all of them is fine. All right. Now let's just make them fit into the black box at the bottom. And that's quite easy to do. Let's just line them up first. So I think we'll have area at the end. Sounds channel their product next and probably a year at the beginning. Now we can see these boxes don't fit too well. If we were to shrink these boxes, it just we don't want to be scrolling down through the years. And the way we can make that better is by adjusting the columns. So if we just go up the top here to columns and for year, we probably want to change that to free columns. So we can see how this changes. Now. You see we've got 123 columns. So we can shrink this box up. So we get rid of the scrollbar. Perfect. No, let's put that down in the black box. And for product we probably only need two columns. And the same for sales channel. Two columns and area that let's just resize them more quickly and put them in the black bar at the bottom. Okay, and now that we've done that, you probably notice that they don't look too good. I mean, it really ruins the dashboard. What we're aiming to make them look like now is this with a nice blue highlight every time we hover over one. And then when we select, They all turned to black, the selected year will stay white. So that's how I am. And you can see I've done that by creating a custom dashboard slicer. Put it back to the normal one. Now, I'll show you how to make that. So what we do is we take this standard slicer, which will probably be here on your screen and right-click duplicate. So we'll make another one. And we can just call it Dashboard slicer and then press OK. So now that will create a duplicate one, forests here. And we're going to modify that. So we click right click, click on modify. And here is our area that we modify our slicer with. And the first thing we want to do is change the slicer to black, the background to black, and get rid of the border. So let's do that. So we click in format, remove the boulder. So clicking None. And let's feel the bat grand to black. Then we click OK. And if we click OK on this multiply slicer style screen, we'll see that it changes and updates there. Now the issue we've got, the text is disappeared. So again, let's quickly edit that. So if I click modify and we need to change the header. So we'll click format will again remove the border and will change the font to a white color. And then click OK again. And we can see that year was back. Now, that's looking a lot better already. And then we just want to edit the format of how when we select items with data. So for that, we will just change the fuel to white. And then click OK. And now we can say that the tabs have now changed to white. In my opinion, that blends really well now with this dashboard. And it seems to fit the stylist dashboard a lot better than these horrible white ones. However, we're not quite finished yet because when we click onto a year, we can't really tell that we've selected that year because the unselected items, which will be the remaining years that we haven't selected still remain white when we change the years. So it's very hard to tell which ones are selected. So again, nice and easy to change that. Go back up to your slice off the top and modify. And we want to click on unselected items with data format, we're going to change the text to white and the background. We're gonna change that to black and click OK. And as you can see now, they changed to black when we select a year. Now we're nearly done with the whole design of this dashboard. Or we need to think about now, is this color when we hover over is not very nice color. I think it's better if we match the color above in the tiles, which is a nice blue color. So to do that, let's right-click on our slicer again, modify, click on hovered selected items with data format. Change this background to blue. Let's have a nice gray border. So select a gray border outline. And we're gonna change the font to white. And click OK. And we're gonna do the same again for the unselected items with data font. We want it to be white and feel to be this blue color. Click OK. Click OK. There we go. That's looking a lot better now it fits in really nice with the tiles above and finish off, it's nice and quick or we have to do is click on all of the other slices. Most holding Control and changed it into our new custom slicer format. And that is done. So as you can see, we've now finished our dashboard. I think all we need to do now is just the very final touches. And I think it's really important once you've finished all designing, you've got all your charts in all of your slices and now in the age to take a step back, have a look at it and see if you can find any little things that don't look so great. So in my opinion, taking a look at this dashboard, everything that's quite good so far, I think there's nice spacing between everything. However, I think maybe we could make this text a little bit bigger. So I'll change this to 28, maybe 30. I think that seems to look that looks a little better. Let's leave it as that. One other thing. I think maybe this could be better if it was a red color to be in line with the dashboard. I think maybe the blue bins in a little bit too much. So I'm going to quickly change that. And to do that, we're going to double-click into the chart, open up series color, change the lowest value to white, and the highest value to this nice red color that we've been using, which I think it's called Rose. You click that. And yeah, you can tell that looks a lot better. Let's close this off. And as you can see, if we click through the years, all of the charge change independently. And it looks great. And yeah, I'm really happy with this dashboard. We can also then filter by product. For example, we can notice that there's a trend in 2020 with computes the doom really well, so that stands out now. That's great. And we can even check the area. So overall, hopefully now that you've created a dashboard with me or if you've just watched, you can see the power of a dashboard and how we can really interpret all of our financial results, prove visual analysis. Now the only thing to bear in mind we have this dashboard is is that we've used pivot tables. Now, pivot tables do not update automatically when you add new data to the sales data at the bottom. So if we go down to the bottom and you return to new data. So let's show you that for example, if I put a new line, tie into a large number like this. So it very clearly stands out. When we go back to the dashboard, nothing's changed and nothing will change until we click into a chart, click on pivot chart, analyze and refresh. And there you go. We can see these charts are completely skewed now. But what that means is we need to make sure that we're clicking refresh as soon as some new data goes into the chart. So let's just remove that entry and put it back to normal. And go back and refresh the charts again. And everything goes back to normal. And there is another way that we can solve this problem. And I'm going to show that with a little bit of VBA coding in the next video.
9. Automatically Refreshing Pivot Tables with VBA: Okay, so let's add the VBA formulas spreadsheet so that if any data is added, it will automatically update without having to click refresh every single time. And now I'm not going to go into too much details on this because it's out the scope of designing this dashboard. So I'm just going to very quickly walk you through it. So first things first, we need to go to file and save this as a x LMS Macro Enabled Workbook. So we need to bring in the Developer tab. And to do that, we go to File Options, Customize Ribbon, and we need to find the Developer tab down here, just ticket. Click OK. And now you can see the Developer tab shows up here. So let's move into the Developer tab, opened up the Visual Basic, and we need to find the source data sheet, which is going to be the Sales Data tab. So Sheet two. So first of all, we need to create a subroutine. So just type in what I'm typing, which is Private Sub worksheet. Underscore, change, open the brackets by val target as range, and close the bracket and hit enter. So that's created out. Now what this basically means is that every time there's a change in the worksheet, so a cell is changed, whatever we're about to type underneath. That's what it's going to be performed in this routine. So what we wanted to do is refresh our pivot tables every time it changes made. And to do that, we just need to type in apostrophe. Only, refresh all pivot tables. And then hit enter for each, and we want it for each pivot cash. So for each PC in this workbook, dot pivot caches, this one here. Then hit enter again and a type MPC, refresh and type in for this one. Next p, c. And I should have done it. Let's just check if it's working. So we will close this. And the dashboard seems okay for now. It's quite normal and it's changed amount in this line to something really high. So will spot the change, go over to the dashboard. And there we go, we can see that it's gone a little bit crazy. So let's just go and change this back to 1275 and go back to the dashboard and perfect, it's updated.
10. Congratulations! Your Assignment!: Okay, thank you so much for getting this far in the course. I hope you enjoyed it. Please check out your assignment in the assignment section and make sure you have a go at creating your own dashboard. There'll be a downloadable Excel file with the sales data in the project section. So see if you can have a go at creating a dashboard like that. I'd like to see what's all backgrounds and designs and color schemes you can come up with. And once you're done, please upload it to the project section and share it with everyone else.