Beginner's Guide to Excel Dashboards

this is the beginner's guide to creating dashboards for your Excel workbooks and what is a dashboard I can give you a couple of examples here's an example of a dashboard now this is a beautiful complex very complicated dashboard that's not what I'm gonna show in this particular video we're gonna start at the beginning with the basics and how to get started creating dashboards but you can see that there's a summary for the year here and this is data that's being shown on a dashboard but the data actually comes from another spreadsheet or in some cases another workbook and here is a chart a pie chart here some graphs and things and again all of the data that's being shown here in this dashboard comes from a spreadsheet generally the spreadsheet looks much more complicated it's cluttered full of data but dashboards lets you focus in on specifically the kind of data and the metrics that you want to look at here's another example this one's showing sales total and trend by state now of course in Excel you can create stuff like this just right on a regular spreadsheet but with a dashboard it makes it look a little nicer to pull the charts the graphs the numbers that matter most to pull those out from the regular spreadsheet that you normally use and separate them and put them on their own spreadsheet here's a third example so again those are very advanced complicated examples but now let's look at the basics of getting started creating your own excel dashboards so here I have a complicated large financial data spreadsheet there are hundreds and hundreds of rows of data hundreds of records and just lots and lots of information here also notice that past years are listed to the right so we have 2018 2019 and 2020 so this is just a lot of information how do I know what I'm looking at here how do I know what's important well if I know how to create a spreadsheet know how to read a spreadsheet I certainly get that information just by paying attention to what I'm seeing but sometimes we need to simplify things so here in the lower left corner I want to set up in this case a fourth spreadsheet a new sheet so I'll click the plus sign and it automatically called it sheet 4 but I'm going to double click on that name and change it and call it dashboard now you could call it something else if you prefer I'll tap enter on the keyboard and there's my fourth spreadsheet I'm going to move it to the front and that's gonna be my dashboard now it's a good idea whenever you're creating a dashboard to kind of map out what exactly you're going to show on the dashboard maybe here in the upper left I want a list of total sales for the year maybe over here I want average price or something like that to be shown so it's a good idea to map this out sometimes it helps to do it on paper or you could cut some corners and just do it right in Excel and that's what I'm gonna do here in this tutorial so what I want to do is go up here to insert and one of the simplest ways to start setting up a dashboard is just to use some simple shapes and for me shapes are hidden here under illustrations so for you just look for your illustrations group and you should see it and I'll just go in here to shapes and there are a whole bunch of shapes that I could use I think I'll use rectangle but maybe one that's got curved edges or something like that so I'll just click and drag to set up a shape here alright and I would like my current year let's say 2020 gross sales to appear here I want it to appear here in this blue box now I can click on the box and I can type I can type in text I can type in numbers and I could just figure out what the gross sales number is before 2020 and I could just type it in there but there's nothing special about that right it wouldn't get updated as more items sell so let's look at how to make it automatically update and sync with the actual data so I'll just delete the numbers that I put in there and the trick for this is to click on the shape so that the shape is here and then to link the shade to the data in question the way you do that is once the shape is selected just go up here to the formula bar and type in equals so this shape equals and then just click on 2020 and I want gross sales so I'm gonna go down all the way to the bottom of this data and it's quite a big spreadsheet so let me just quickly show you a trick if I click here on gross sales even on the title I can hold ctrl and then the down arrow and that should take me down to the bottom of the spreadsheet okay look there's totals right here so I can click on the exact cell that I want to use and then up here look my cursor is flashing that's what I'm working on right now so I tap enter on the keyboard and look it has linked this shape with the data on this spreadsheet alright so you can see it ends in five nine eight point five five nine eight point five now back here on the dashboard I can make this look better by clicking on it and I could work on let's say the font size make that bigger maybe that's too big there we go I can decide if I want to Center it which I do I could Center it horizontally and vertically I think that looks a lot better I can also go here to the font color and I could select something other than black I could go with white I think that looks pretty good could go with yellow just about any of these colors here to make that look a little nicer make it pop so that people like to look at it you can also change the shape background color or fill color so play with the font and shape fill colors to find what looks best to you now you might be thinking that that's just all cosmetic it doesn't matter that much and you might be right but really that's the whole point of dashboards is to make it look nice to make it look attractive and easy for people to access and read the information that they're looking for alright so now I've got this shape and it's linked to the data on this spreadsheet now let's take a look at what happens if the data on the underlying sheet changes so here we have 5061 what if that becomes twenty five thousand and sixty five when I enter that data it changes the total here does it update the dashboard yes it does the dashboard should always match the data that it's linked to now I do have a minor problem with this and that is I'm not exactly sure what this number means and there's a couple of ways I could fix that I could go here to insert and add a text box so I can click here text box and just click and type total gross sales I could click and drag and put that inside the same box and then make it bold make the font size bigger perhaps and again when it comes to dashboards a lot of what we're doing is making things look nice so I think that looks really pretty good now what if I decide later to move this somewhere else I could click and drag to move it but look these two items the text box and the shape with the number they're not really connected they're not really linked together so that might be a problem what you could do to try to fix that is you can click on the shape hold control and click on the text box so now they're both selected and then go here to the page Layout tab in the arrange group there's an option to group objects and so I'll just click that group and now they act as if they were one object so now I can really easily move elements of my dashboard and put them where I want them to be now that's just one way to create the beginnings of a dashboard there's many other options that you could do and instead of putting the title of what it is here inside the box I could insert another shape and have it appear here and so maybe I have another rectangle above and that's where I put the text and then I could group them together but for now I'm gonna stick with this this is just a basic Excel dashboard now for the next element of my dashboard let's say I want to track the total number of items sold but this time I want to save myself a little bit of time so what am I going to do I'm gonna right click on the dashboard element that I've already created and I'll choose copy and then right-click so now I've got two of them it didn't copy the title unfortunately total gross sales so I still do want that I'll just select it control-c control-v to copy paste and then I'll drag it over here so now I can double click on it to highlight the whole bit of text and I can type total number of sales so this is dollars here at the left and this is just total number of items sold now obviously the data that I have here is linked improperly it doesn't match the text that I just typed so I have to change what this shape is linked to but that's not hard I just click on it and then I go up here to the formula bar I'll delete what's there and then I'll go in to the 20/20 spreadsheet and I'll find units sold so here it is hold ctrl and tap the down arrow it takes me down to the lowermost part of the spreadsheet and you'll notice that I did cheat a little bit I added averages underneath totals but right now I'm still dealing with totals so I'll click there on the total number of items sold and you can see up here it's entered it into the formula bar I tapped enter to take me back here to this screen and you'll see it is linked up properly 806 806 now unfortunately it did change the formatting but that's okay I can just click away then click back to it and I can just go up here and make those same adjustments that I made before once again if I want these two things to be linked I should click on them holding the ctrl key while I do it and then go to page layout and group give me a minute to add one more element to this dashboard and that's going to be the average price of the item sold so I'll need to get one of these averages numbers I think it's this one here and that's one of the things about dashboards you have to be really careful you have to make sure that you're getting the proper source data if you don't it can be a huge mistake and cause a lot of problems for people so give me a minute to set up that third dashboard element it's very similar to what I've just done twice and then I'll resume the video so this is a great beginning to the dashboard for this workbook and I'm pretty much gonna leave it here for this beginners guide but there are a couple of little housekeeping things that I need to do before I end this video number one this is money this should have a dollar sign attached to it so I understand what I'm looking at this one's fine as is this one here again should have a dollar sign also this doesn't make a lot of sense to have $118 and all of those numbers for the cents I want to change these up so they are easier to read and understand but watch what happens if I click here and then go to the Home tab home ribbon typically I could use these tools here to change how those numbers appear and it looks like they're grayed out I can't do that so here's the trick what I have to do is go to the source data so here on the 20/20 spreadsheet here's the first piece of information so I need to click on it and then change the formatting here so I'll change it to currency and notice that that took care of the cents problem as well so now if I go back to the dashboard look it shows up as dollars and cents now that number is so big that I'll have to make some changes to make it fit in fact I might have to just stretch out the rectangle I think that looks pretty good the way it is there but at least that's readable and Wow take a look at that I wish this was my company now let's try the same thing with average sale price so I'll go in here here's the average sale price notice that I could just go to the Home tab home ribbon and use these tools here to increase the decimals or decrease the decimals so I could get it down to just two digits in the decimal area and if you go back to dashboard look it solved that part of the problem but in fact this number is currency so all I really have to do is switch it to currency and that'll solve the problem too there we go so that's pretty much what you need to know to get started creating your own excel dashboard there are some other intermediate skills that you might want to learn and some advanced skills so if there's a lot of interest in this video and in this top I'll consider creating an intermediate guide to excel dashboards and then maybe an advanced guide to excel dashboards in the meantime though I hope you found this tutorial to be helpful if you did please click the like button below and consider connecting with me on my social media accounts like Facebook Pinterest and Twitter and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe please click the bell next to the subscribe button if you do that you'll be notified whenever I post another video and watch for another video from me at least every Monday if you'd like to support my youtube channel consider becoming a supporter of mine through my patreon account and you'll see a link to that in the description below

Channel: Technology for Teachers and Students

Views: 491,198

Rating: 4.9152923 out of 5

Keywords: excel dashboards, dashboards, dashboards in excel, using excel dashboards, creating excel dashboards, creating dashboards in excel, create excel dashboards, create dashboard, create excel dashboard, how to create an excel dashboard, how to create excel dashboard, make excel dashboard, gather excel information, beginner guide to excel dashboard, excel dashboards for dummies, dashboards for dummies, advanced excel

Id: s0E2pmXQcPA

Length: 14min 2sec (842 seconds)

Published: Mon Sep 02 2019