Hi everyone, Kevin here. Today I want to show you how you can create an interactive dashboard in Microsoft Excel. With a dashboard, you can showcase the most important information to your organization, and best of all, it’s actually really easy to set up. You don’t have to know any VBA and you don’t have to install any add-ins. All you need is the base version of Microsoft Excel. Once you finish creating your dashboard, as new data comes in, it’ll automatically update to reflect the latest data. Also, once you’re ready to share it out with other people in your organization, you can very easily share it so they can get insights from your dashboard. If you want to follow along today, I’ve included a sample spreadsheet in the description. All right, let’s check this out. Here I am in Microsoft Excel, and I want to create a performance dashboard for the Kevin Cookie Company. Leadership is always asking me questions about things like our profit, unit sales, and that type of business stuff. Now I figured I could create a dashboard to answer all of those different questions. That way I can continue focusing on my YouTube videos. As you can see, I don’t have anything in this workbook yet, but luckily, I have data to build my dashboard. Down below, let’s click on the data worksheet. On the data sheet, here you’ll see sales data by market for the Kevin Cookie Company. Now keep in mind this is internal data, but I figured it would serve as a good example. So just between you and me, keep it on the down low that I shared this sheet with you. To build our dashboard, we’re going to create a few pivot tables. This table that you see here is currently in a tabular format and this works really well for creating pivot tables. If you’re new to pivot tables, I’ve included an overview video in the description. Also, even if you’re not new to pivot tables, I’ve also included a link to a video that covers some of the more advanced aspects of pivot tables, and if you think you know pivot tables, trust me, there’s always something new to learn. I’m learning new things about them all the time. As a first step, we need to make sure that our data here is in a table format. To turn it into a table, simply click anywhere in your data and then go up to the top ribbon and click on insert. Right here you have the option to turn it into a table. You can also press the shortcut key Control + T. Let’s click on this. In this prompt, it asks me for where my data is, and here it automatically identified all of the different data that I want to bring into this table. Here it also asks me if my table has headers, and this is my header right up here, it does, so I’ll make sure that I check this box. Next, let’s click on OK. All of my data is now included in a table, and it has this nice table formatting. Next, I want to insert a pivot table. So once again, I’ll click into my table and then up on the top ribbon, let’s click on insert and all the way over on the left-hand side, there’s the option to insert a pivot table. When you click on that, that opens up a prompt and it asks us what table or range do we want to use? I’ll simply select this Table 3. This is the table that I just created. I’ll place my pivot table on a new worksheet, and then I’ll click on OK. This now drops me into a new worksheet, sheet 1, and I can now start building my pivot table. For my dashboard, I want to have three different charts, so I’m going to need three different pivot tables and right now I only have one. If I go down below over to all of my different sheets, I can press the control key, click on sheet 1, and then drag it over. This will create another pivot table. It’s just a copy of sheet one. Here I’ll press the control key again, click on this sheet, drag it over, and now I have three different pivot tables and I’ll use these three different sheets to pull together all of my views for my dashboard. The first view I want to pull together is profit by country and cookie. Here I’ll right click on this tab, and I’ll select rename and let me call this just what it is, profit by country and cookie. Now that I’ve renamed the sheet, I’m ready to pull together my first pivot table, and once again, I want to know profit by country and cookie type. Over on the right-hand side, I have all of my different pivot table fields. First, I’ll select country and I’ll pull that down into rows. Here we can see all of the different countries that we operate in for the Kevin Cookie Company. Also, over on the right-hand side, I see a field called product. Here I’ll drag that down to columns, and here you can see all of the different cookies that we make here, and trust me, they’re all extremely delicious. Over here I’ll select profit, bring it down into values, and here I can see all of our different countries, all of the cookie types, and the associated profit. Now that I see all of my data here, I want to make a few formatting changes. Right now, you see that it doesn’t show up as a currency. Here I’ll select all of these different values. I’ll click up on home up above, and here, let me turn on the currency formatting type. Also, I don’t need all of these different decimals. I know, every penny counts, but for this analysis it’s not necessary. Here I’ll remove the decimals. Before I insert a chart to visually show this data, I want to make one more tweak to the pivot table. I want to make sure that I show all of the different markets in order of most profitable to least profitable, and I want to do the same for the different cookie types. Here I’ll zoom out just a little bit and I’ll click into the grand total column. Here I’ll right click, I’ll go over to sort, and I’ll sort from largest to smallest. So here you see that India is our most profitable market and the United States is the least profitable. Thank you to all of the Indian viewers of this channel, we’ve been able to achieve record profits. We can now do the same for the different cookie types. Over here I’ll right click, go down to sort, and I’ll sort this from largest to smallest as well. So now the markets are sorted from the most profitable to least, and same with the cookies. Now that my pivot table is all done, I’m ready to insert a chart and we’re going to use this chart on our dashboard. To insert a chart, let’s go to the top tabs and click on pivot table analyze. Over on the right-hand side, there’s the option to insert a pivot chart. Let’s click on that. This opens up the insert chart dialog, and right here I’m going to select the stacked column chart. Next, let’s click on OK. And here now you can see a pivot chart representing all of this data. Now when we did this sorting here, here you’ll see all of the different markets are sorted from the largest over on the left all the way down to the smallest on the right. Also, with the stacked bar chart, here you’ll notice that the largest items are at the bottom and the smallest items are at the top, so it makes it easier to consume the data. Overall this looks pretty good, but I think I need a title on this chart just so when people look at it, they can understand what it is. Let’s go up to the top tabs, click on design, and all the way over on the left-hand side, click on add chart elements. Right here we’ll go down to chart title and I’ll add it above the chart. For the chart title, I’ll simply describe what it is. It’s profit by market and cookie type. Before I bring this chart back to my dashboard, I want to clean up a few things. Here I have all of these different field items included on my chart. Here if I click on this, I can filter my product, but I don’t want this cluttering up my chart. Here I could right click on the item, and I’ll select to remove or hide all field buttons on this chart, and when I select that, it really helps clean it up. I’m now ready to move this chart to my dashboard. Here I’ll select the chart and I’ll press Control + C. Next, I’ll click over to my dashboard, and I’ll paste it onto this sheet. And check that out, I now have my first visual on my dashboard. Here I’ll zoom out just a little bit so I can see how it sits on my dashboard. Here I can select the chart item, and if I press the alt key at the same time, it’ll snap into different positions. So, this way I can organize my dashboard a little bit more easily. Here I’ll position this first item right here and I’ll extend it down just a little bit, maybe right down to this point. I think that looks pretty good. Next, I want to create two more pivot tables for my dashboard, and I’ll do these a little bit more quickly. Down below, I’ll click on the next sheet, and here I’ll rename this, and we’ll call this units sold each month. To track units sold each month, here I’ll select the date and I’ll make this my row, and right up here, I’ll select units sold as the values. So here I could see by month how many units we sold. To help with the formatting, here I’ll highlight all of the numbers, and I’ll insert the comma style, and I’ll also remove the decimal points. Just like we did before, I’ll click on pivot table analyze, and here I’ll insert a pivot chart. For unit sales over time, I think a line chart will work really well. I’ll select this one and then click on OK. This now inserts my chart and here I’ll update the title to say units sold each month. Here I’ll remove the legend, and I want to remove the fields just like I did before. Here I’ll right click, and I’ll hide all field buttons on the chart. This now looks really good, so I’ll copy it and I’ll bring it into my dashboard. In my dashboard, I’ll press control + V to paste and here I’ll position it right near the top, and here I’ll reduce the size just a little bit. That looks pretty good. Lastly, I want to create one more pivot table for my dashboard. I’ll go down to the bottom tabs and let me rename this one to profit by month. To create a pivot table with profit by month, it’s going to be very similar to the last pivot table that we created. Here I’ll select months and I’ll drag it down into rows. Next, I’ll select profit and I’ll pull it down into values. And here we see our profit by month. Like I’ve always said, there is money in the cookie business. Next I want to set it so it appears as a currency. I’ll highlight all of these values and here I’ll select the currency view. Also, I’ll remove the decimal places. Next I want to insert a chart, so once again, I’ll click on the tab titled Pivot Table analyze and right here I’ll select a pivot chart. To represent data over time, line charts work very well. Here I’ll select line and then I’ll click on OK. This now inserts my chart, and I don’t know why profit decreased in November and December. Those are the holidays, we should be reaching records. I’m sure management will be interested in this. Right up here, I’ll click on the title of the chart, and I’ll put in profit by month. I’ll click over on the legend that says total and here I’ll delete that, and just like we did before, I want to remove all of the different fields. I think that’ll make the dashboard look better. Once again, I’ll right click and select hide all field buttons on chart. Next, I want to bring this chart over to my dashboard, so once again, I’ll select the chart, press control + C to copy, and then I’ll click into my dashboard. Here within the dashboard, I’ll press control + V to paste and here all position it under units sold each month, and I’ll make my best attempt at trying to make it just about the same size. Now just like we did before, I can press the Alt key and then this item will snap to the cells on the page. Here I’ll do the same down here and now everything lines up nicely. To make sure everything aligns very nicely on this page, I have a few different alignment tools. Here I’ll select this chart, I’ll select this one, and I’ll go up to the top tabs called shape format. Over on the right-hand side, here I have my alignment tools and here I’ll make sure that I align them to the top and they were already aligned at the top, but if they weren’t, that would help me achieve that. Next, I’ll select these two different charts right down here. Once again, I’ll go to shape format and let me align them to the left, so these are also in alignment with one another. So, everything looks nice and organized. Along with setting alignment, I could also specify the exact dimensions of one of these charts. Here also like this chart and I’ll go up to format up on top, and over here I can see the precise height and width, so I could adjust these if I want different charts to be the exact same height and width. So far, we just have a static dashboard and I want to make sure that people can interact with this dashboard. To make it dynamic, let’s select one of the items, and then go up to pivot chart analyze. On this ribbon, there’s the option to insert slicers and also a timeline. First, let’s insert a timeline. Within the timeline prompt, I’ll select a date and then click on OK. This inserts a timeline slicer. Here I’ll select the item, and I’ll drag it over to the left-hand side, and here I’ll adjust the dimensions, so it sits right alongside different items. Next, I want to insert some additional slicers. Once again, I’ll select this pivot table, I’ll go up to pivot chart analyze, and over here, I’ll select insert slicer. Within this dialog, I’ll select both country and product. I want to quickly be able to filter my data by the country and the product. Next, I’ll click on OK. This now inserts two slicers, but before I bring it over to the left-hand side, I want to clean them up a little bit. When you look at this list, it’s pretty obvious that these are all countries, so I don’t need this header that says country. Here I’ll right click on the item, I’ll select slicer settings, and I’ll turn off the display header, then I’ll click on OK. I’ll do the same for product. Now that neither of these items have a header, here I’ll resize it, so it just fits the different slicer items. I’ll do the same over here. Now I can drag this slicer over onto the side. I’ll position it right here and I’ll do the same with the product type. With this slicers over on the left-hand side, you’ll notice that the width is not quite the same. Here I can select this first item, I’ll click on timeline and here I can see the width. I’ll set it to two and all select these other items, and all set the same width for them as well. Here now you can see that they all have the exact same width. With my slicers now in place, I can very quickly filter the data on my dashboard. Let’s say for example, I just want to see data for India. Here I could select India and you’ll notice that my profit only shows India, but my two other views didn’t update. How can I make sure that these slicers are connected to all of the different pivot tables? Here I’ll right click on the slicer, and let’s go down to report connections. Within report connections, I can specify what pivot tables this slicer controls, and currently it’s only set to the profit by market and cookie type. I want to make sure it updates all of my different pivot tables, so here I’ll check all of these boxes and then click on OK. I’ll do the same for the other two slicers as well. And check this out! When I click on my slicers now, it updates all of my different pivot tables over on this side, so this dashboard is definitely becoming a lot more interactive now. Let’s take a look at chocolate chip cookies in the US. Look at that, I can see how much profit for that cookie type. If I want to select multiple items with a slicer, here I can simply click on India and then drag my mouse down and that’ll select all of these different markets and here I could do the same for the product, so it’s very easy to look at my data how I want to view it. This dashboard is working really well, but what new data comes in and we want to make sure that our dashboard reflects it? Well, it’s very easy to take care of. Down below, let’s click into the new data tab and here you’ll see that we have additional data for 2020. It’s formatted in exactly the same format. Here let’s select all the data from this table and then press control + C. Once you copy all of the data, let’s click into the data sheet, and go all the way to the bottom of the table. This is the table that we used to construct all of our pivot tables, and here simply paste in all of the new data. Because we turned this into a table, it automatically incorporates this new data into that same table. Next, on the bottom, let’s click back into our dashboard, and we want to make sure that the dashboard now reflects all of the latest data. To do that, let’s click into one of these pivot charts and then go up to pivot chart analyze. Within the ribbon here, there’s the option to refresh. Let’s click on this and then select refresh all. And there you’ll see that your dashboard automatically takes all of the latest data into account. This dashboard now is starting to look pretty nice, but it still has the look and feel of Excel, and I want it to look more like a proper dashboard. To do that, let’s go up to the view tab up on top, and with the sheets selected right here, you have the option to turn off the gridlines, so there you don’t have those typical cells in Excel. Also, here I can turn off the headings, so now it’s really starting to feel more like a dashboard. Also, down below, we have all of these separate sheets for all the different pivot tables and the data. Here I could select all of those different sheets, I could right click, and then I could hide those different sheets. So now if you share this sheet out, people will only see the dashboard that you pulled together. This dashboard is now starting to look really nice, but maybe the color scheme doesn’t match your organization. Well, that’s no problem. We can also change that. Up on top with the top tabs, click on the one titled page layout and over on the left-hand side, you can choose from all of these different themes and that’ll apply it to your dashboard. Also, within themes, you can even browse for other themes, or you could even save a theme that you create, so you can customize this to look exactly how you want it to look. Now that we’ve created the dashboard and it looks exactly how you want it to look, you probably want to share it out with other people in your organization. Up in the top right-hand corner, you can click on the share icon. This opens up the share dialog, and here you could decide whether people can edit the sheet or if it’s only view only. Back here, you can select people you want to share it with, or you can simply copy a link and then share it out. All right, well, let me know down below in the comments, were you able to successfully create a dashboard? To see more videos like this, please consider subscribing, and I’ll see you next time.