Building Interactive Dashboards Using Microsoft Excel

1. Introduction: Hi, my name is Ruben. And in this class I'll be teaching you how you can make your own interactive dashboard in Excel. I've gained my experience from working from different large corporations or accelerates, mainly used to provide the information and insights. This class is specifically designed for managers and larger corporations that want to know what's going on within their team, OID in their market. Whether you are in finance, Logistics, sales or marketing. And a department that has data. This course will help you to convert that into information. The structure of this class has been built up in three parts. The first part, we'll look into what is a dashboard. Why do you need a dashboard? And what do you want to get out of it? Second, will look more into graphs and charge on will learn which chart or graph to use for which kind of information will make our hands dirty. We'll make our own interactive dashboard along the classes. Excel can seem a little dull. It's just adding some numbers in some cells are subtracting some numbers. But once you get into Excel, and once you learn more about the possibilities, you'll see that you can make your data come alive in great visualizations and that it can tell you the information you need to run your team or market. After this class, you'll be able to impress your colleagues with your Excel skills. And you'll be, you'll be able to make better decisions for you and your team. 2. Class Outline: Welcome to the class overview of building an interactive dashboard in Excel. In this video, I'm going to quickly give you an overview of what we will be doing in the coming lessons. So the first one we see right here is a classic project overview. That's what we are talking about right now. Then in lesson one, we're not gonna make her hands dirty yet, because we will be starting by explaining what a dashboard means and what's important for creating a good dashboard. Then, as at lesson two, we will start opening up Excel. And we're gonna follow through all the way to lesson seven to make our, our dashboard. And we do it in steps that we first focus on our data. This we will do in less than two and less than three. Then we are going to make our PivotTables and PivotCharts. This is less important, is, is going to be a field. It's a little longer than the rest. And then as it implies, we are going to focus on the dashboard. Throughout. Listen to seven, the project is also running. So basically what I'm going to ask from you during this class is to make your own. Before you start watching the videos. I figure it will be good that before you listen to starts you have a datasheet. You'd have some sort of idea of what you want to make a dashboard for. If you don't have it. No problem. I will leave my Excel sheet and the description. I will upload it. So you can download the Excel sheet used a data I use, and then just fold along. All right, this is the Class Overview and get ready for it at the first lesson or recommend talk about breaking down an interactive dashboard. 3. Lesson 1: Breaking Down an Interactive Dashboard: Hi, welcome to our first lesson of this class. For a ribbon, a breakdown what it means to make a dashboard. We're going to look into what makes a dashboard grade. I'm not going to look at a good example of this, which is a best board over car. At last, we're going to review our method into building our dashboard, which is a three-step method. For this lesson. You don't have to make your hands dirty. You can keep Excel closed for now. Since we're just going to talk about what makes a great dashboard. And after this lesson, we will be starting to open up Excel and make our own dashboard. Jonathan breaks study in 2013, find an interactive dashboard that it should give KPIs at a glance, should be relevant, it should be constantly updated, and it should have a graphic interface. All four of these are done and possible in Excel. However, having it constantly updated can be a little bit more tricky, but that's something we will go into in the next lesson. He also compares an interactive dashboard to dashboard on her car. If you're driving and you're watching The wrote, you don't have time to carefully look at your dashboard and how many kilometers per hour exactly am I driving? You don't have time to dig into the details, so it must be information at a glance. It's also irrelevant if your engine breaks down or if you have something read your engine, a light pops up. It's also constantly updated because it gives you the speed threat. And it obviously is a Graphic interface which helps you seeing those KPI set a glass. In Excel, we're going to try to create the same thing. The goal is that you don't have to make a study out of your excel sheet for two hours to know what's going on and should be a quick overview. And it should give you the information that you need at a glass. That's what we're aiming at in this class. However, we have to be super careful in making this. Because is well-known in news and an articles that statistics can be very tricky. We have to carefully inspect how are we going to visualize and how are we going to set this up in order to not lie to ourselves what statistics? I'm giving you this first example. So first-year dropouts of college, XYZ has increased by 100% in 2019. As a parent, you'd say, wow, I really have to take my kids to another college. If this is the amount of college dropouts. At a glance, this gives you the idea that this cool is performing very badly in 2019 compared to 2018. But if you look into it, it only has to be one person extra added dropout to make this statistic 100%. So in this case, in 20181 person dropped out and in 20192 person stepped up. It's still nothing. It's one person extra. But it gives you the statistic of 100%. The message here is being that when are we going to make our dashboard? We have to be super, super careful that we get the right information to ourselves. Therefore, we're going to build it up in three steps. So we're first going to list what is the information that we need? What do we want to know? For example, we want to know who the best performing salesman is in America and the United States. Then how do we visualize this information? We have a graph, a decision tree, and witchcraft to use. But you're also going to have to think about what data goes into. Telling me who the best salesman is. Is this the profit that degenerate a siesta revenue they generate is just the amount of clients stay close, assist the satisfaction of their clients. It can be pretty complex. And step three, what data do I need for to make this visualization? We discovered it, but also in order to make the graph you sometimes you need some extra data like the bait or the month or to region. So in those three steps, we're going to work throughout this class in order to make our interactive dashboard. So in our next class, we're going to look at how we can source data. We're going to start making our hands dirty. So I would also recommend you to open up your own EMT excel sheet. Or if you already have data, make sure you open up an Excel sheet with the data. And we're going to start adding sources to your data. This is going to help us to automatically update the source, but if not possible, no problem, we can find a work around for it at two. But Excel already offers great opportunities to connect your Excel sheet to forums or any other source. So buckle up. And for the next class, we will be starting to make our own interactive dashboard. 4. Lesson 2: Data Sourcing: Hi, welcome to lesson two of this class. In this lesson we're going to look at the sourcing of our data. And we will see that this we can do in two different ways. We can do it online and we can do it offline. Online gives you an endless amount of possibilities of connections you can make to your sheet. And if you're using the offline Excel, then I'm going to show you a very hidden secret feature which allows people to fill out forms to putting your data. At last, we're going to look at how we can make sure that everyone that collaborates in your sheep puts in data that's formatted the right way. So we prevent spelling mistakes or wrongly formatted data. Alright, so I'm now on the website of power automate disease, a tool provided by Microsoft. And it helps you connect different applications under which Excel. Here you just see a couple of icons of what they have. We can go to all connectors here. And we can already see outlook. There is SharePoint. We also have, I saw Microsoft Forms. Those tools can really help you to keep a constant flow of data and a life flow theta also and a tool like Microsoft forums also helps you to give you cleaned up data, validated data before it's kind into your sheet. For example, if you have a 50 colleagues entering data on a daily basis, forums help you to prevent them from making mistakes. For, for example, putting in the right bait input or using certain codes or giving them drop-down menus, instead of them just entering the cells. You see Google calendar, there's Gmail, you can even add Facebook. There's also other tools, various Xavier. And there's possibly if you go to long enough for it at 1000 of possible connections you can make. However, this really depends on how you use Excel, how your company and uses, and storage Excel, for example, if it's locally stored on a server, then is going to be more difficult to connect a gmail or a Dropbox. If you use also the offline Excel, but you can also use the online Excel, which you can access through your browser. And then the endless possibilities become more endless. In our case, we're just going to keep it simple. The reason for this is because the connections you can make this kind of a complete class on its own and how you have to do this. So therefore, I just wanted to briefly show you the options you have. And we're gonna use one that's internally built in Excel. Alright, so we are in our example data Excel sheets right now. Let's assume that the sheet is being locally stored, locally safe, where we cannot use the online Excel. Then we want to still use a method that makes sure that all the data we put in is put in nicely structured and without any typos. To do this, we can use forums. Should click on this arrow and click on More Commands. You click on all commands right here. And you type form. There, you have it right here and you add it to your list. I've already added it. And you'd click on okay. And we can find it right here. And here we can add the data. This is a great tool that you can use if you have other colleagues using the sheet. And you don't want to have them mess with the excel sheet. That you can just make them. Use this form to add the data. Before we going to use it, we wanna make sure that there is data validation going on in this form. So that prevents us from making typos or using the wrong date format. So we're going to make a list, the beta validation sheet. We're gonna make a list of all the categories that we want to have validated. So in our example, we have regions. There's a pretty limited amount of regions near six, I'm sorry, seven regions. Since this is an endless amount of countries, it can be pretty hard to put all of them in. Assuming that this, this company is not going to expand anytime soon. We're going to copy all of the countries. We're gonna put them in their foundation sheet as well. We click on remove duplicates. And then we have 185 unique come countries. And this is also how you can put it in other categories. So countries skip the columns, I'm gonna let right here. Then we have the items. So this is the item type. We have the fruits beverages and we just want to make sure that there's not gonna be any typos or abbreviations or for example, offers is good. Just going to read, often supplies is going to be written as supplies. Zilch channel and the order priority. So all we can do is we're gonna add data for innovation for regions. We go to sheet, we select the column, we click on Data Validation list. None. The source is going to be the seven regions. Now we click and enter, make sure that those dollar marks are in there. So whenever you go down a cell in our data sheet, the range doesn't go down with it. Then for countries, we're gonna do the same thing. So list source. And we're going to select all the countries that we have for the item types. Same thing. And again, make sure those dollar marks are selected. Then for those who may seem like for only two categories. While we do have data validation going on. And the reason for this is just to make sure it isn't going to be any typos. It's only two channels. But let's say one person puts an offline with one f. The system, the Excel dashboard is gonna think there's three channels which really messes with your, with your desk dashboard later on. Then order priority. It's the last one. There we have it. From now on when people want to add data here, you have to go to the dropdown and you have to select your region, country, item, channel, and priority. Alright, so those have been done then for ordered date and ship date, we want to make sure that everyone's going to put in the right format phrase. So let's say right here, someone put in the 13th of January 2010. What happens is Excel is not going to recognize these as big. It's really gonna recognize it as a text. That's also why you see it on the left side. This only holds when there's no formatting going on in this column. Text goes to the left, numbers go to the right. And here you clearly see it doesn't pick up as a date, which also means that whatever you do, Excel is not gonna be able to have line charts, for example, because they don't know that date. Or do you wanna do is you want to click on the cell. You want to click on text to columns are not going to actually split two columns, but we're just going to change the format. We click on delimited. We're not gonna select anything right here. Make sure if that click next and then you click on B8, D M, Y. And I, you know, I quit trick on how to correct the beta. Now we wanna make sure this doesn't happen again. So we're gonna select ordered date and we're gonna select shifted. We're going to click on data validation. And we wanna make sure that whenever someone puts something in right here, it's a we're just going to put in the first of January 1900 just to make sure it's being registered and recognize those dates. So whenever someone puts into European notation, it's not going to recognize. Alright, so now we can go back to our forum. And the data validation we just put in also applies to the form. So we're just gonna put in a record and see what happens. When we click on new. We're going to add a region's Europe, countries. We're gonna add Ukraine item type. Let's make a typo here. Fruits would is it seals channel of line order priority is going to be e, which doesn't exist. The date we're gonna put in where we just talked about the European notation to see if it recognizes DID with order idea with data of elevation. I haven't done it right now, but I would like to know that you can add it the same way with data validation, how many digits it should be shifted. We're gonna put in the right notation. Let's make it 32. Then the unit sold, let's say ten prizes, ten, cost is five. So our profit is 50. So let's see what happens. We press enter and now we get our first error message. So we're going to see what's happening here. It's fruits, it picks it up, it means it's working. Next one. It's the order priority. Let's change it back to D. And then we have our date, which we have to send back to us notation. Alright, so now we have everything set to make the form. We can fill out the form now. And every time you make a mistake that's not being recognized in a sheet, it will also not go through the form eater. You'll get an error message to make sure that we won't make any spelling mistakes. So that was it for this lesson. And the quick summary of what we've done, we have at first looked at the different possible online connections that we can make with our Excel sheet. For this, we would have to use online Excel. Then we've also looked at applying a form that's internally built in Excel that we could use. And we have protected unstructured data. So what I would recommend you to do before we move on to the next lesson is to look at your own data, see where things could go wrong, and then think about the way betas written down different abbreviations that can be used or just make sure that all the data that's being put into your data sheet is put in nicely and structured the same way. So it's, it can be easily picked up by our dashboard. Alright, so in the next lesson we're going to start building our charts. And we're going to use our three-step model to go from the information that we need to data we need to provide to get this information. Alright, so see you in the next lesson. 5. Lesson 3: Structuring Data: Hi, welcome to lesson three of this class. In this lesson, we're going to plan the building of our dashboard. So in R3 step method that has been mentioned before, we're gonna put in the information that we need. We're going to look at the charts that we want to build to present this information. And we're going to look at the data that's needed to build those structures. Alright, so we're back in our datasheet. Before we move on, I would like to mention that always when you add new tests, make sure. Did you give it a name, this one In a previous lesson, we'll still call sheet one. But let's just make sure that we get these in name and keep things organized. Sheets will be having a lot of taps later on in this class. And it will be nice if we can always easily find Beck the things we're looking for. All right, with that being said, I have created a new tab right here, built plan. And this one is the one we're going to use for this lesson. Built down as basically a three-step method into a planning. Our Dashboard. What we do here in the first step is we're going to write down, which I've already done all the information that we want to get out of dashboard in case you're making a desperate for colleagues or you have to make reports to people, ask and consult with them with what they want to see, what did they want to know. And be very, very specific, but also very critical about this. It's very easy to do. Make a chart that doesn't represent what you actually want to know. So make sure to what you write down here is the thing you need to know in order to make your team or yourself perform better. So we're just gonna do one together. So I wrote down here, offline versus the online sales performance trends. Performance being revenue. In our case, I'm offline and online are channels that we're going to compare. And Trent means we want to see over time if something's changing. So in order to decide which chart we're going to use, which is step two, we are going to use a decision tree, which I'll share with you in the leak. We start in the middle. So what would you like to show? And this is clearly a comparison. So we want to know the difference between online versus offline. And we want to notice over time, since we are looking for a trend and we have many periods, it's, we're going to look at this for years and the last years and every year, also the 12 months that it has. And it's non-cyclical data. So the conclusion is we are going to use a line chart, which are, we are going to write down right here, the light truck. And then for the beta, we're going to write out all the data that we need in order to make this line chart. To make sure you're not gonna write anything down that you don't have your data sheet. Make sure you're going to add theta foundation. You click on list, you select arrange CO2 data sheet, and select the first column. And make sure you have to double and marks. It doesn't go down when you go down with the cell. So we lock the reach. So if we go back to our built blend, we can select a year and a month or a year and order month. We already set performance being revenue, so we're gonna select Total Revenue And we are comparing online versus offline which artists sales, Janice So this one's ready. The next step would be, which we will do in a following lesson, is making a pivot chart. And we can basically just click on these four Beta datasets and we will have our pivot table. So I just wanted to highlight a couple of other charts that we want to make. The first one is to sales per country. If we would use our decision tree, we would end up at a bar chart. However, something that decision tree doesn't offer us is some of the fish realizations that Excel offers, which are pretty cool. So for this one, I had in mind already using this one. So we can have an actual roadmap where we highlight the countries that have the most seals. And if we hover over it, we can see for individual country how many sales they're making. And this is a great visualization for a seeing at a glance at which what this class is all about which countries are performing well. So we're going to write down roadmap. We're not going to use a decision tree for this one. And in order to know this, we need two things. Its country. And we need to tell the revenue and just copy this one. Alright, then for profit margins, I would like to highlight, we need to add a couple of profit margins actually. That's here. This is profit margin. Profit margin here. And we have five different charts. Are we going to use a profit margin, but in our data sheets there is no profit margin. So what we're gonna do is we're going to add a column profit margin. And we're going to divide the profit by the total revenue. We're going to so lots a whole column. And we will make sure everything as a percentage. And sort of these are the profit margins. And then we had lead time is also one that's not there yet. So we're gonna add lead time. And this one is very simple, since we already have a ship date. Araf, the Order Date, delete time is basically the difference between the two. So here we have a lead time of seven days. And here we have profit margin anytime. And then according to our data validation, don't shoot automatically pop up here now at Lead Time, profit margin. And that's for the next step is just to fill out the whole sheet. I skipped step two for these two, but it was just to show that we now also have these drop-down menu. I would like to ask you to do the same thing. So think about the information that you want. Then look for a chart that you need. And for the beta. If there's no data yet that you'd need at its first year data sheet. And I would like to emphasis editor datasheet and not any other sheet. Because in the dashboard this will cause some errors and confusion if we have data coming from different sources. So make sure you use one data sheet. And then we can add it into step three and we have our plan ready for the next class. For the next lesson, I have to say we are going to make pivot tables. And those tables we will make in separate apps, where we then can also easily generate charts for you. Before you move on to the next lesson, I'm going to ask you to fill out your own plan, make sure that you have everything set that the data's correct. What we discussed in the previous lesson, and then in the next lesson, we're going to move on into making our first tables, charts. 6. Lesson 4: Pivots & Charts: Hi, welcome to lesson four of this class. In this lesson, we're going to pick up where we left off in a previous lesson, where we have been planning how we're gonna make our dashboard in a previous lesson. In this lesson, we're going to make our pivot tables and charts, and we're going to put eventually in our dashboard, we're going to cover five different types of charts. The first one's going to be a bar chart. The second one we're gonna make a column chart. Third is a line chart. We're going to make a map chart or treemap chart. And five, we're gonna make it a roadmap. Alright, so in this lesson we are going to make the different charts that we have. I'm not going to go over all of them. I believe it's 16 of them. 1715, sorry, these are 15 charts. I'm not gonna make all 15 in this lesson. I am going to do is purgatory. There's hilbert type of chart that we have. I'm going to make one. So just to see and you have to pay attention to and how to do them. So we're gonna start off with the bar chart where we want to see the top five highest profit margin countries. And the data that we are going to need is profit margin and country. So we go to Insert PivotTable. We want to use data source right here. So disease from a, I'm just going to select everything to make sure again that those dollar signs are in there and we locked arrange or you want to create a new working? Alright, first of all, let's make sure that we give our sheet and new men profit margins countries. And here we just have to strike country, which immediately goes to ROS. It's what we want and we want to select a profit margins right here. And he's already go to write categories here. Now, we see that those profit margins are large numbers, but we need them to be less than one Xunzi, it's percentages. So what's happening here is that we have the sum of all the profit margins, which means that for all the fields are the roads that are in our data sheet that half Afghanistan, it adds up all those percentages to get it. However, we want to know the average. So what we do is we click on Value Field Settings and we click on average. And I was correct. And now click on the red formatting that percentage. And we want to have one decimal since there'll pretty close to each other. We want to know the exact difference. And what also, we don't need all the countries. We just want to know the top five profit margin countries. So the best-performing WAS, So all we do is we go to jail, you filter, you click on Top Ten. And then we're going to make this five items by average profit margin. That's correct. We should have the top five lines. Blank, shouldn't be in there. So let's make Let's double-check our data range. I think I already know why this is happening. We have one entry without any further a country. So we're just gonna get rid of case one. And now we should. If we refresh this one, we shouldn't have that anymore. Slogging are very hurt. So these are the top five profit margins. It's not sorted, but we also want to have it sorted. It looks nicer later if we're gonna make our chart. And we want to sort it based on our average profit margin. So now we have it sorted, and now we're gonna make our chart. So we click on the pivot table again. We click on insert. Here under charts, we click on 2D bar chart. There we go, there we have it. So what we wanna do now is edit it, want to give it a title. So we're going to be so five, perfect margin. Country to styling is something we will do in another lesson. So let's not worry about that. And also it's pretty hard to know exactly what the profit margin is. So what we also want to do is we want to get rid of this one. And we're going to add data labels. So we click on design. We click on Add Chart Element data labels. And we want to put them on the outside end. And I would call this one we don't need and we also don't need field buttons. And we're going to hide all field buttons on the chart. And there we go. Every habit. You can change this around like how you prefer to see it for me, it's just nicer and we can see percentage right next to the bar. And for that reason, since we don't have an x is below, we can also get rid of the lines to make it as clean as possible and as minimum, minimal as possible is gonna give us a better information at a glance, which still is spin up the main aim. Right? So that's it for bar charts. And now the idea is to also do it for the other four. I will do after a day's lesson and it will make sure I had done before the next lesson, but I'm not going to bother you with it, right in this video. So the next one is going to be a column chart. We're going to skip those for. And here we want to know the profit margin, margins per channel. So again, we click on Insert PivotTable. We again need to submit the data sheet right here. Make sure you have everything we do below are assigned and arranges in it. And we want to create a new working worksheet. And this one is to profit margins per channel. So perfect. Margins for channel, it's actually perfect margin. And the data that we need, we're going to quickly check in here. It's the profit margins and the sales channel. Those two we need to select Sales channels right here. Profit margins should be at the end. We again have the rows right here and the value is right here. And it's the same issue as we had with that previous chart. We don't need some, we need the average. And this is filter out this blank. We don't need this one. And here we have it. Let's also give it a percentage. One decimal. There we go. Now we are going to add the chart. This is a column chart, so 2D column click right here. And it's basically the same process as we followed before rhythm bar chart. We don't need the total. This, we only have one variable. We are not going to use the lines were not going to use the axis. We're gonna give it a name, profit margin per channel. And we're gonna add data labels. We're gonna put it on the outside. And then lastly, we don't need those. So we're going to hide all Field Buttons chart. And this one's ready to. Alright, so the next step is to make the line chart, which is offline presence online sales performance strength. So we're gonna look at the sales channels offline and online, and we want to see a trend over time. I add a trend. To be more specific, we wanna see movement between the performance of the two sales channels. And so rather than just comparing both channels or, or years using bar charts, we actually want to see the movement. And let's say the company is implying a strategy where they want to focus more on line over the years, we want to be able to see that this is actually working out and at, online is going up and offline is going down. So in that context, we are going to use a line chart and we want to need sales channel or the year or month and total revenue. And so what we're gonna do is we go to the data sheet, we select all the data are already selected before we go to Insert, click on PivotTable, check if the range is correct and we create a new worksheet. And we're going to rename it to sales channel for men. And here we want to select the sales channel. You asked let the order date. And we want to select Total Revenue. The automatically to order date is going to be put into categories. In this case, we have the years and quarters and we want to put seals channeling columns. But let's say we don't want to have quarters, but we want to have months instead. So what we do is we go to analyze, we click Ungroup selection. And here we can de-select quarters. We don't need those, but we want the months and years. And this is automatically pulled from the date that we had in the data sheet. So the Order Date. And now it should give us that months. So that's correct. We are going to have this blank Let's fill filtered out, went up real quick. And this is our sheet. Let's use accounting. We don't need the sense, so let's remove those decimals. And here we have our table. And with this we can make our line chart. So we go to Insert, we'd go to chart and we click here on 2D line. It's basically preference which one you want to use. But since there's gonna be a lot of different columns, which are two months, let's not use a one would have thought. It's kinda look very, as you can see here already. It's got to be very hard to, to see the line. So we're just going to use a line. It's a little bit more cleaned. And for me are we basically are going to follow the same steps as we did before. So we're going to remove the buttons, Hide All Field button on chart. This one is handy. We want to keep this one. I read things here, alright. But we're going to put it in a chart title, which is offline versus online sales. And this is the reason you were using the line chart because we can very clearly see the movement in the sales online and offline. What might be interesting to is to add a total to see if this switch and strategy from offline to online over time is actually affecting the total. But we already have another chart that's going to include that. So we're gonna leave it as clean as possible, as minimum, minimal as possible. As said in another chart, we want to have our information at a glance so we don't want to put in too much information. Right here. The numbers are a little large. So what we are going to do is we're going to display units as we can do millions right here. So again, contains less and less as minimal as possible. All right, so this one is done. And what's going to be great about this chart is that later on, when we are going to implement the data slicers, which is for another lesson. And this one is going to be very handy. As you can see this chart based on certain countries, you can select the specific years, you can make it more specific, which will make everything more clean as well. Maybe what we can still do to make it a little more cleanest, do make the line a little bit more thin. Because right here in this area is very difficult to see the movement. And if it's thinner, we can more clearly see. You want to click on this one. Here. We want to decrease too with a little bit. That's made kid, see, 1.5 should do. And we're gonna do the same for the other one. And I make it 1.5. It just helps a little bit. There's no need to have very, very thick lines, especially not with a lot of movement. Alright, so we have our line chart. Let's go back to our bill plan. So our next item, Our next chart is going to be in that chart where we want to see the profit performance of the items. We basically want to compare the items amongst each other to see which one is the most profitable. For this, we're gonna need a mat chart. It's not in the tree that I have shown before, but it's one of the options Excel offers. And I think too quickly at a glance again, see which prof, reached product is most profitable, brings in the most profit for the company. It gives a very clear and quick information. Just to note, this is not about the item profitability itself. So we don't want to know the highest profit margins per item, but we want to know out of the whole profit that the company makes, what products contribute the most. So for this we're going to be total profit. And I didn't type, and let's just start creating our table right away. So we're going to select all our data. We're going to insert a pivot table, check our range, and make the new sheet item. Just to make it very clear, let's call it profits country of fusion. And for this we're gonna need item type. And we're going to need to approve it. Alright, there we go. First of all, let's remove the blank. We're not going to need that one. And and this map chart, it's if we want to show labels, showing the dollar is not going to give us a very easy information on how much it contributes. So what we're gonna do is we're going to click here on field settings and we're going to make them percentages. So show value as percentage of the grand total. So now we know how much percentage the product adds to the profit. So for example, baby food contributes for almost 10% of the profit of the company. Cosmetics. The biggest one, it's 18.7%. All right, so with this, we're gonna make the chart. So I'll go to Insert, click here on treemap, and then we get this message. For some reason. This keeps showing up with certain charts. This is also appearing with maps. And in this case then a mat chart. What we wanna do is we want to copy the data. This is just a little work-around. Paste it as values. And then we're going to select our data, insert the treemap. And now we're going to change back our data to disk column. So we're going to select A3 until B6. And now we have the PivotTable selected. We can now get rid of this one. And just quickly to form at this one, we wanted to be at a glance as possible. So we're going to delete all the legends and we wanna make labels and values in here. So we're gonna do is design and we click an ad chart elements all the way on the left. And we want to add a chart title, but it's already there. So we want to add data labels. And, but not only the labels, we also want to see the percentage. And then we click on more data Label Options. And then we can select value right here. And then we can see the percentage do. And now we have this grand total, but we don't need this. This is irrelevant. So we click on the table and we click on Design grand totals, and we clicked off for rows and columns. And now we have only our products. There's a lot of blue going on. As already mentioned before, we're going to do the, the name and formatting later on. But just to quickly show you if you want to change individual colors in a chart, you double-click on the chart. And you can above here select the college. And but as I said, this is part of the theming that you wanna do it individually. You double-click on one. And then we should get the option to change, to feel to a certain colour. Alright, so we have a metro aren't the only thing left for us to do is to give it a name. Item, profit, contribution at, and that's it. That's our chart. The next step for us is to make the world map. And this is done with the sales per country. So what we wanna do is we again go through our data sheet, select everything, Insert PivotTable, click, create a new worksheet. And this is revenue. Per country. We select a pivot table, select country. And we can select the total revenue. It's format the number here right away or move the decimals. And now what we wanna do is we want to insert the world map. And we get the same issue as we got with the map chart. So we're gonna do the same thing. We're going to select everything, paste it as values, and then create our map. Once we've done that, we go to select data and we're gonna change it back to column a and column B. So this becomes a and this becomes B. And we have changed our data. See my chart, and it's right here, but we want to move it up. And now we can also delete these two columns. And the moment, it's not showing much. And this is because we have, I think our grand total included. So all the other countries are relatively low and that's why all the colors are the same. Yes. So again, what we wanna do is first of all, let's take out the blank and do like we did with the med chart. We go to analyze, sorry, didn't go to design. We click on parental roles and we turn it off. And now this is how we want to see it. So now all the countries get a color. And and we get this very cool overview of the world of where our sales is coming from. This is one of the features that Excel offers that I really personally really like. It's super-advanced. The other medically picks up the countries in the, in the column. And this is just a super, super cool and advanced overview of your sales. I don't think we're going to need this right here to save us some space. So something we haven't done yet, and is usually something you want to do while you're making charts, is naming both your tables and charts later when we're going to use this slicer. And possibly if you want to use other features too, it's always handy to have them named just like the tabs at something we haven't done along the way, but it's something that we're going to do now afterwards. So what you wanna do, you wanna select the tab, you click on the table, and then here on analyze, she'd give it the name. And in this case, it's sales channel for four months. And just going to copy the name. Because we're going to click on the chart, we're going to do the same thing. So sales channel performance. So there was all of them. And the meantime, before the next lesson, I'm going to finish up all those charges that are left. And then in the next, next class, we're going to start pulling, putting them together, and creating our dashboard. 7. Lesson 5: Putting the dashboard together: Hi, welcome to lesson five of this class. In this lesson, we're going to start putting together all the charts and pivot tables that we made in less than four. Again, that there's lesson, we'll have our first rough version of it that's called. Alright, so we'll start off with a blank dashboard tab. And in here we're going to put all the charts. And so what we're gonna do is we're just going to copy and paste the charts. After we've done that, that's hyper sheets. So we create a better and cleaner overview of our attempts. Since we won't be needing those anymore. I'm going to be the same for this one and hide it. And we're going to keep doing that for all the charts that we have. Alright, so it looks pretty messy. We're going to clean this up before we do, just in case you are not, I change something in a Pivot Table or for whatever reason you want to go back to the tab that you just hit. Click right mouse on any tab, and then click on unhide. And here you can select the sheet that you're looking for. And this is by the way, also the reason why it's very important in to name it. Because you can imagine if this says chart, sorry, xi one, xi two, xi three, and it goes until 15, You never know which chart or which table is in what I Sheet. Alright, let's type there's again. So now we're in a fish board. Let's make sure that we have as much space as possible. And just for the sake of organizing, I'm going to zoom out a little bit and solve for our dashboard. We're going to divide it into three categories. Before we do a wire for whatever dashboard you're making, try to make it as easy to read as possible. I think we'd categories does is you make it easier. And what our goal is to make information available at a glance using colors and using a nicely designed dashboard helps. What we are going to do is we're gonna make three categories where we had one Profit. Category two is going to be revenue, and category three is going to be lead time. So in case we want to know anything about profitability of the items we know. Ok, we have to go to this section and it makes it more accessible. Alright, so let's start sorting our, our tables. So we're going to put lead time over here, and we're going to put everything that's lead time over here. And profit margin. Who's gonna go for the middle? Let's just scroll down a bit. Let's just put a year. This one too. So we identify. All right, and let's give it a title and we're gonna do it by making the cell. It'll beer. Is rho, i mean. And let's just give it a color. And we're going to call this dashboard exerting its call it sales dashboard company, XYZ. Remember we are on a 70% zoom. So if we're going to zoom in because he's gonna look much bigger, we have to take that into account. I see him also missing columns. Let's put this one over here. And what also helps for it a dashboard, since we are not going to use the cell stuff much, We just want to remove the grid lines right here and already starts looking more clean. And let's stay on 100% just so we're not going to make the church too big or too small. So we can size it properly. And we're just going to form our dashboard. Leave some space on the left. That's where we're gonna put our slices layer. So I'm going to move this one a little bit over here. Hold Alt. You can actually use magnet, I think it's called magnets. That the chart nicely aligns with this column, as you can see. Or at least any line in this sheet, maybe we should turn on the grid lines, so it's more easy to see. Let's turn them on real quick. You'll see that if you hold ALT, though, we automatically stay within, within those lines. And these were helps us putting it all together more easily. So let's just do that. First of all, before I get, let's add titles. This use this space for everything that comes from a revenue right here, merge and center, and that's called this revenue copy it come here. There's one, there's going to be profit. And this one right here is kinda be lead time. Now we can make them a little bigger. Cells don't have to be that high. And let's make sure it in Texas centered prudently. And we can do the formatting later. It's just so now we have our sections where we can stay within and we're going to increase one smaller. And it's, remember to keep holding the ALT button to make sure that we align it nicely. Extra space. Let's move things up a little bit. All right? So I think we still have enough space and let's do one more. And I'm now I'm thinking this is a truly one of the most important charts and sort of profit and sales. So let's make this one also big enough. So we can see it in, in good detail. Alright, so that's our profit and sales. Let's put our sales per region. And here, again, I'm holding all this one up a little bit because he's being, so let's just make the text a little smaller so it fits in very goal. Alright, let's do the same thing for this one right away. This one. The legend is pretty big, so we're going to need some space for that. Let's not make it as big as the a21. And let's also decrease the size of this text. And we're going to call them a little bit more smaller. And it looks very cluttered. And however, when we're going to use this slicers and we're going to select specific data. It's more easy to compare. I think it will also help treat very well. We make it a little bit less high. So this will be when we're going to use the slicers. This one is going to be more accessible in terms of getting the information out. Offline versions, online sales. I think this one fits right under this one. This is a pretty obvious one. We don't need a lot of space for this. So to keep it like this. And we can put the seals per country. This is a big chart and let's see if it fits right here. I don't think this is kind of work. We have to find a way to make this one a little bit bigger. Let's also decrease the size of this one. Just gonna, I'm just gonna change it around. And I'm thinking this is the one where we should have more detail because it contains more lines. And this is actually one that gives us the trend very easily without having much space. And we can even make it a little bit more small. Move it smaller. There we go. Now we have more space for discharge. Split like this. Now we can put this one right here. All right, and then we have one chart left for a revenue. And we're gonna put it right here. And see if this thing, this is a little bit too small. So let's make this bigger. There we go. So we have our revenue part done. Then we go to profit. I think I showed you now clearly that we, we basically want to setup the format of our dashboard and then we're going to just fill it in with the charts. I think that's a key point of this class. The other remark is hold Alt when you're moving around, it's going to align things more nicely. As you can see, this fits perfectly. It just looks much more professional when it's aligned nicely and glued together. So for now, before the next lesson, I will make sure I have my dashboard ready. I'll put everything together. I recommend you to do the same. Again, think in terms of how can I access my information quickest. And in my case, is by subdividing into Lead Time, profit and revenue. But thinking your room, chase and how are you, are you want to do this? So I'll see you in the next class. Okay? Okay. 8. Lesson 6: Making the dashboard interactive: Hi, welcome to lesson six of this class. In this lesson, we're going to make our dashboard indirect. And we're gonna do this by adding slicers. Adding slicers means that we're gonna make buttons. We click them are all dashboard is going to take. All right, so we're back in our dashboard and we're going to add the slicers today. And we're going to put the slices here on the left side. So what we want to put it a little bit space and events, and what Slicers aren't going to do is you're going to manipulate the data and you basically select a category or one of the options that are slicer gives you and all the data you see will be filtered based on this slicer. And so to do that, we are going to create a slicer. You do this by selecting one of the charts. And it doesn't really matter which one, but make sure you select one. Otherwise, it's not going to connect to the graphs. We click an insert when we click here on slicer. And then we're going to choose the slides just that we want. We want slicers based on region, item type, sales channel and years. And as you can see now we get region or we get the options of all the regions. It's just nicely Linus. We get item type. We have the years. We'd also created one column with the months. I'm not going to use it because it's a little bit too specific for Dish dashboard, but you have basically the option to use anything from this data sheet that we created. One node makes sure that all the data that you use comes from one sheet like redid, although we have like 20 different sheets. Everything is linked to this one. And if you don't, then it's going to be a little more complicated to install the slicers. So I really recommend you to make sure that all the data's coming from the same place. Alright, so now we have installed the slicers, but as you can see, it only manipulates this specific graph. And this one sentence, one. That's better. How we can change this as we're going to right click Slicer report connections and we want to select all of them. So now BS1 is connected. Rather slices we gotta do is same. So we do report connections. We're going to select all of the checkboxes. Now we have connected all of the slices through all of the data. Let's zoom out a little bit. So we can see that, let's say we remove all the filters and we can see that everything changes. We did. Which is I think a super cool feature that makes it super dynamic. And it's how you basically add this license. And so for example, I mentioned before Regents graphs. So it is just a sales for region and the profit per region. They look, let's just take the seals per region as an example. When all the lines, it's super hard to see any patterns or its information at a glance doesn't really apply for these. I have to say it's also because over the years, all continents are pre constants. So there's not really any trends going on at first glance. But the slice has really helped to clear up this data show. Let's say we're going to change the years and we just want to know 2011, discharge becomes much, much more easy to, to, to see and get information out off the Earth if we use channels. It makes everything look much nicer. Alright, that's how you add the slicers. This is how we have made our dashboard interacted. I ever convince you to do the same. There could be some troubleshooting. I want to don't want to go too much into depth into troubleshooting. One of the things you could try is to allow connections. Some sheets don't allow for connections, which means that the tables to PivotTables and everything have connections. Make sure that's turned on, and then it should work. And yet that's how you make your dashboard interact. For the next lesson, we are going to make the dashboard pretty. So we're going to apply some colors. We're going to use the brand book of Amazon can show you and to illustrate how we can apply those colors quickly and consistently. And we will apply the font. Alright, I'll see you in the next lesson. 9. Lesson 7: Branding & Formatting: Hi, and welcome to the last lesson of this class. In less than seven, we're going to cover how you can brand your dashboard. Excel offers some great features. How you can very easily completely brand how your dashboard is gonna look like. So we're gonna change some colors and rigid gonna change some fonts. And we're going to customize it a little bit with the colors. Sodium information is more easy to read. We're going to highlight our categories so we can get our information at a glass. Alright, so we are in our dashboard right now. I zoomed out a little bit more now, 60% right now. And what we are going to do in this lesson is we're gonna make it look more professional. And Brendan, obviously formatting, changing to farm colors or whatever. It's super personal. There is no right way to do it. But I'm just going to show you some tips and tricks on how you can make it look more like your company's brand and overall more clean, um, or professional. So we're gonna start off with the removing grid lines. You can see them here. We're not using the cells, are just putting in some graphs. And if we want to add text, we can always use textboxes. Was granted I was better to shape our dashboard. Blank stare, distracting, and we really need them. So just select a new cell and go to View and click on the box to remove the gridlines. There we got already looked a lot more clean. We're also gonna remove burst or we have around a regress, which we do by clicking on a graph. Unfortunately, you cannot click all of them and remove them all at the same time. We have to do with one-by-one or click on the outline. I'll fast forward a little bit. So you wouldn't be bothered by me removing all those other outlets? Alright, so we've removed all the vowed lines and I just saw we still have one of the slicers turned on, please turn it off. And so we have no more outdoor outline. So what we wanna do now is let's add a color to go, titled. The purpose of this video. I want to show you how we can use branding. So let's say this is an Amazon dashboard. Obviously it's not. But let's use the branding and the college about Amazon uses. So what do we wanna do is we want to, we can change all the colors one by one, but obviously that's gonna take a lot of work. So does a good feature in Excel that's going to help you. And brand fish sheet. So you go to page layout. And we can make our own new team. And we can go to customize colors. And here you can customize all the colors that there are. For Amazon, I quickly Googled Amazon's color schemes. So we have the Amazon orange and Amazon blech. And the coach right here. So I'm going to put those in to 55, 153 right here. And you score was 0. And then we have the Amazon orange. And for this one we can put black. And you can see everything changes right away. So that's a quick way to do it. We can insert the logo. So we click on, click an illustration and we click on pictures. And I already save this image. Right here, the Amazon logo. This one isn't PNG. That would have helped. But anyway, is used for the purpose of showing how it's done. So I'm not going to change it. It would have been nice to have this background would've been transparent so it blends better in, but well, for the purpose of the video, it's not necessary. Let's crop the image a little bit. Right here. We don't need all these whitespace. Here we go. So now we have the Amazon colors. We have Amazon logo in it. I see that this one is still blue. Blurts, change that and see what's happening with our font. Why that's not happening. He's probably this blue that we want to change it. See what happens if we make it orange. There we go. Now that's amazon too. And you can see earlier in this class we have individually changed one of the colors of the map charge. Every color you change individually to a color which hasn't been wasn't in the color scheme. Do you have then changed? Will stay the same color. So in this case it's a red, but we're just gonna leave it. Once you have made your custom colors, you can save it. And let's say Amazon branding. And there we go. And sort of fonts. We are already Google it. Amazon uses REL. It's one very popular font for it's not their logo, but it's everything on the website is without email. So let's use that 12. And here we go. And we're gonna do the same for this one. And we're gonna call it Amazon rending, resave it. And there we go. Do move around a little bit. So let's just make sure that everything, nothing overlaps. And vigor does good for those three headers we highlight in a little bit. So we're going to select them. Holding control, we can select multiple cells and we wanna put in a fill. This one doesn't give me two columns you're looking for. And we went obviously use the Amazon color again from, I believe Fishkin was already updated on those colours. So humans use this one. And anti black and white. It's not the prettiest. But again, just for the purpose of showing you how it's done, that's made them black. So it contrasts with the first row. And then we can make the texts written Cupid white. All right, there we go. So now we have subdivided R3 categories, but maybe we can make it a little bit more clear by adding a color to the background of those charts. Let's use very light colors. We don't need very bright colors. And if students lucky right here. So we go to Format replete on film. And it's add very light orange. And again, I'm going to speed this process up little bit. I know it's not the prettiest dashboard, but again, for the purpose of showing it doesn't have to be the prettiest reuse some of the Amazon colors. Although this is not really a color hours looking for half the real Amazon color array here. And we can put it in ourselves, was 255153. And now we have the real Amazon color. And we can make this one a little bit bigger. And in terms of bromine, make it black. All right, so there we go. We have formatted our dashboard. Our categories are nicely highlighted. Using the button will move in around. Everything we've got right here. Really helps to make it look more type. If you don't, you'll see there are some white lines in-between the graphs because they're not perfectly aligned. And now it just looks very tight, very professional. And this is our dashboard. Alright, so that's it. That's the end of this class. Thank you for following it all the way to the end. I will be uploading the file that we have created. And good luck with your own interactive dashboard.