[Music] thank you for joining me today I'm Inda Tracy and I'll be taking you through how to build the interactive Excel dashboard that you see behind me I'm ready to go with my isimply Excel t-shirt on now to quote Steven FW a dashboard is a visual display of the most important information needed to achieve one or more objectives consolidators and arranged on a single screen so the information can be monitored at a glance but with Excel we can take them one step further and make them interactive as well before we start a little background for those who don't know me I started working with Excel 22 years ago when I worked as an accountant in investment banks in London I now live in Australia with my husband Phil and our two boys Connor and Finn I gave up accounting eight years ago to pursue my passion for Excel and desire to help people get more out of it which I do through our website and blog at my onlinetraining hub.com I run an online EXL dashbo course and have trained thousands of people from all over the world and from a vast array of Industries the last four years I've received the Microsoft Excel most valuable professional award for my contribution to the Excel Community okay let's talk about what you can expect from the webinar well it's going to be at a fast pace you're not going to have time to follow along step by step but remember I'll be sending an email with the links to the video replay and files I'm going to teach you how to build a dashboard obviously right but I want to make the disting between building a dashboard versus dashboard design so in this webinar I'm going to focus on the mechanics of building a dashboard using the built-in tools there's no add-ins required and no VBA by the way VBA is the programming language for Excel I'll cover the ideal data and workbook structure and I'm going to build it fast and smart so I can update it in an instant I'll be using slices to create the interactivity and dynamic labels that respond to selections made in the slices so this is the data that we'll be using there's over 7,000 rows of sales data for two Australia wide fashion stores called Fashions direct and next look they're fictitious the data spans January 2016 through to July 2017 you can see the data is in a tabular format and it's classified by date Financial year the chain the location information so we've got suburbs State postcode country we've also got the manager the product category and product buyer and it's formatted in an Excel table and this is the first step in having a fast and smart build because any formulas charts or pivot tables that reference the table automatically or upon refresh will pick up any changes or new data so the ideal workbook layout has separate sheets for data analysis preferably one sheet for each pivot table if you're using pivot tables and a sheet for the dashboard I will be employing various techniques to ensure a fast and smart build namely Excel tables pivot tables pivot charts we be naming things like pivot tables Rangers and worksheets and when we name things it makes it quick to build and easier to understand when you come back to that file in three or six months uh I'm going to be using Excel 2016 to build this this dashboard but what you see will work in all versions of excel from 2007 onwards with the exception of slices which are available in xl20 onwards and the alternative to slices is to use combo boxes and some VBA code unfortunately I won't have time to cover this today but I teach them in my online Excel dashboard course we'll be using map charts and they're a new feature available to Office 365 XL 2016 users again I won't have time to cover any alternatives for those of you who don't have Office 365 but the map chart is a really small part of the dashboard and the reason I included it was to show you this new feature as opposed to it being mandatory for all dashboards okay let's get started so here is our data if I go to the end you'll see there's about 72 1 half th000 rows and we also have a sheet with some more data for August another 4 and 1 half thousand rows and we're going to use this so that I can show you how quickly and easy it is to update our dashboard once we built it so the first thing I need to do is format this data in an Excel table and the keyboard shortcut for that is contrl T and we need to decide whether our table has headers well mine has headers so I'm going to click okay and the first thing we need to do is give our data a name our table a name I'm going to call it data very imaginative I know and now we're ready to create our first pivot table so let me just have a look at the dashboard the first pivot table we want is for this line chart here for the sales by Chain so let's go back and we'll summarize with pivot table I'm going to put it on a new worksheet and it's correctly detected the table range name as data so click okay and let's give this sheet a name name let call it line pivot and we'll give our pivot table a name so for this pivot table we need to know the dates now I'm using Excel 2016 so it automatically groups my dates I can contrl Z to undo that or I can turn off that default I want to show you how to group them manually just in case you don't have Excel 2016 so just select any date right click group and then I want it grouped by months and years so click okay and now I have years and months in my row labels the next thing I want are the chains and I want the sales amount so I want to format these amounts because the formatting that I apply here will carry through to the pivot chart and I won't have to mess about with formatting them there so there's various ways to do this I tend to write right click and go into the value field settings number format and we'll apply those formats and I'll click okay and okay so that's all I need for my first chart let's insert a pivot chart for it let me bring that down into view so this will just be a line chart I click okay and the first thing you'll notice are all these filled buttons on the chart and we can use these actually to filter on the face of the chart but I'm going to slices for my filtering so I actually want to right click and remove all field buttons the other thing I'm going to do is just relocate my Legend to the top and we'll add a chart title so this chart is called sales by chain and we'll move it over to the left I'm going to hold down shift while I move the chart title and that just keeps it aligned and I'm just going to move the legend up here out of the way so we'll we just do a little bit of resizing here not a lot oops maybe that was too much okay I'm not going to mess about with the formatting too much at this point we'll come to that at the very end and um we'll do it all in one go so we're not wasting time the next thing I need let's go back to the dashboard is a chart for my or a pivot table for my sales by category so one way to ensure that all of your pivot tables share the same Source data and pivot cache is just a copy the sheet or the pivot table so I'm going to copy the sheet holding down control I'm going to left click you can see the little plus I get for the sheet that tells me I'm copying it I'll left click and drag let's rename that to category pivot and let's rename our pivot table and I'll delete the pivot chart cuz we're going to insert a different type of chart anyway so for this one I don't need my dates and I need the chain I want the category in my row labels and let's insert a pivot chart this one is going to be a bar chart so bar charts are really good when you have long data labels and that just ensures that they're horizontally formatted and you don't have to turn your head on the side to read them just makes it easier so again we'll right click and hide all the field buttons and in this one I'm going to actually delete the legend just click on it and delete press the delete key I don't need it because all of my charts are going to share a Legends so I'm just going to delete them as I go now we'll create a manual Legend towards the end the other thing I want to do is I want to see this data sorted in descending order so I'm going to click on the grand total cuz we're going to sort by the grand total right click sort and this one I actually want smallest to largest and that will sort the chart largest to smallest I know it's counterintuitive so one thing I want to point out is if you look at the Home Series in Orange for next look it is actually less than the kids series but home is positioned above kids and that's because this is sorted based on the total of these two columns remember we're sorting on the grand total so the total of home is greater than the total of kids irrespective of whether next look is bigger or smaller so that's the bar chart for the categories let's look at the next one we need this sales by manager chart so let's go and do that actually I'll should give this one a title first let's do that we'll call it oops we'll call it sales by category and I'll move that across there so we're consistent okay let's copy that one hold down control and left click and drag and we'll rename the sheet we'll rename our pivot table and let's change the data now so instead of category we want the manager and I also want them grouped by their state so let's pop that in now I want to change the Sorting so I actually want to sort based on the state in ascending order so that my chart is sorted in descending order so we can make this a bit bigger this one's going to be longer and we'll change that sales by manager okay the next chart we want to create is the pie chart so let's create the pivot table for that I'm just going to copy the line chart it doesn't really matter for this one now if you know me you'll know I don't really like pie charts I tend they tend to take up a lot of space um a little bang for the buck if you like there's not a lot to them so there's often better charts for the but let's go with it we'll create a pie chart cuz it's going to support my Dynamic labels that I'm going to show you so for this one we need to know the chain and then the sum of sales because I want to create a dynamic label I actually need the percentage of sales as well so we're going to pop the sales amount in again and this time I'm going to right click and show values as percentage of grand total so now I have my sales percent let's make sure my chart has a name my pivot table I mean and we've named the sheet so let's insert our pie chart and I'll get rid of all the field buttons I'll get rid of the legend and I'm actually going to get rid of the title as well see look how big they are the two pieces of information it's a quite a big chart and I'll just resize it slightly holding down control makes the pie smaller on all sides equally okay so that'll do for now for the pie the next thing we need to do is create our pivot tables for the spark lines so let's go and do that I'll copy the line sheet we'll call this spark line and I'm actually going to break my rule here and instead of having one pivot table per sheet I'm going to have all three pivot tables let me just show you the dashboard again I need a pivot table for the all chains or the total I need a pivot table for the next look data and another one for Fashions direct and I'm going to put them all on this sheet because I want to demonstrate and show you with them all on one sheet but if you were doing this yourself then you wouldn't have them on one sheet sheets cheap remember so I want to get rid of all of my subtotals and Grand totals and I want to make sure that the states here are first of all sorted oops that doesn't show on the screen so let's rightclick short sort A to Z which they are but I'm making sure they always stay that way and the other thing I want to do is some of the chains don't have stores in every state let's take a look so we can see here Fashions direct doesn't have any stores in South Australia or Tasmania but I need to allow for those States so what I want to do is ensure that those States appear even if they have no data so in the field settings on the layout and print tab I want to show items with no data okay so now let me make sure this has a name let's call it sparkline total pivot because this is the one for both chains and then I'm just going to copy it and paste it below this one will be the one for next look so let's give it a new name and we'll paste another one and this one will be for Fashions Direct let's give it a name Okay so we've named those three what's the next one we need okay so we need a pivot table for the map chart so let's go and do that we'll copy the which one should we copy doesn't really matter actually I'll copy the pi one and we'll call it the map pivot let's get rid of the pie chart and we don't need that we don't need the chains but we do need the states now for the map we need to make sure that Excel knows where these states are so we need the country information as well so let's pop that in now a lot of the time Excel will figure out where these states are based on the context but I prefer not to leave any ambiguity in my data just in case one day it gets it wrong so better to be safe than sorry is my motto so again let's get rid of the uh subtotals and Grand totals and the other thing I want to do is I need this data in a tabular format because map charts don't have a pivot chart equivalent they're a regular chart so I have to create my data in such a way that it will support the map chart and that needs to be tabular so for that I need to right click on Australia and go into the field settings and change the layout so that it shows items in a tabular format and I also need to repeat the item label so that Australia is on every row so let's do that okay so our data is in the correct format let me make sure my states are sorted and they are all right so this I can't insert a map chart on this pivot chart so what I need to do is copy the data paste it as values and then I'm going to to insert a map chart based on that so insert Maps filled map now that I've got my map I can point it back at the pivot table so let's select the data so at the moment it's picking up columns e to G let's just change that to a to c and we'll click okay so now you can see my map is picking up the pivot table data I can get rid of this data here and I'll get rid of the chart title and the other thing I want to do is because my map chart is showing the total data I want to differentiate the colors in here to distinguish them from all of my other charts so all of my other charts use blue and orange for the same Series so Fashions direct is blue and next look is orange so let's make sure that this doesn't use blue or orange because this isn't either of those Chains It's the the total so in the design we'll change the colors to a monochromatic scheme and we'll leave it at that now one thing I haven't done is renamed my pivot table to map pivot okay how are we getting along let's see we've got our data ready for our map and we're ready to assemble our dashboard so back in the file all I need now is a sheet to put my dashboard on so to prove to you that there's nothing special or magical about these dashboard sheets I'm just going to add a new sheet I'm going to drag it to the front I like my dashboard to be at the front of the workbook and later on I'm going I can hide all of these workings and my dashboard will be um separate from them if you like or at the front at least I won't mix it up in the middle of these so let's apply some formatting I'll make this row a little bit bigger and we'll put in some fill and this is called the big fashion group sales dashboard and let's change the font and we'll make it bigger and we'll make it white okay and let's Center it in that section so we've got our header now what we need to do is move our chart onto the dashboard so click on the outer edge of the chart crl X to cut and crl V to paste let's just rinse and repeat for each One crl X this one goes down here somewhere I'm just roughly placing them on my dashboard contrl V to paste it in and the pie chart come on Pi we'll go in here somewhere roughly and Spark lines I have to insert them manually so all I need now is my map and we'll paste the map in down here somewhere okay so don't worry too much about how uh everything is arranged so I need to insert my spark lines and they're going to go on row 12 so I'm going to cheat a little bit and I'm just going to come back to this dashboard and copy this text it's just text formatted it's nothing special it's just going to save me some time so I'm going to paste it in there and we'll re siiz those columns so that it fits all right so in order to build out this table I need to reference some pivot tables and first of all I need the state so I'm going to use the IF function to first of all test that that state hasn't been filtered out because if I've filtered it out with a slicer then I don't want the formula to return a zero if that pivot table has an empty state so first of all Let's test we'll go to our spark line pivot if this cell here equals blank let's go back here it's not showing me the formula just so you can see it let's close that so if the spark Line's pivot cell A6 equals blank then blank otherwise return I'll just copy and paste in there otherwise return the value in that cell and that gives me the first state we'll copy that down for all the states so the next thing we need to do is get the value of sales for act for all chains now I could create my own pivot table for this but I'm going to cheat a little bit and try and save some time by using one of the other pivot tables and the map one will do nicely cuz it has the total for all chains and I'll just show my formula bar that might help us see what's going on so let's reference the map pivot table you can see get pivot data is instantly inserted and I'll press enter now I want to do two things here I want to allow for errors because we don't want errors littering our dashboard so wrap get pivot data and if error so all it's saying is if get pivot data returns an error then just return me with a blank now the next thing I want to do is I want to be able to copy this form down let me show you what happens if I copy it down you can see I get the same result in every cell and that's because the state information is hard keyed and that's one of the downsides of get pivot data but we can automate that or make it more Dynamic by replacing that hard keyed information with a reference to this cell here and then as I copy it down it will pick up the correct state so let's do that and I'll copy it oops copy it down so now we get the correct data let's just format that in a currency with no decimal places and we need a little bit wider okay the other thing I want to put in here is a data bar just to help visually indicate the length of those bars or the size of those numbers and easily be able to compare them to one another because they're not sorted but by putting in the bars it will help us um identify which states are bigger than others and by how much it will give us some context so on the Home tab we'll go conditional formatting data bars let's choose a solid fill now I need to edit that so go in the manage rules and I need to change the color because remember this is all change so I need a shade of gray let's see if that one's okay uh probably a little bit dark let's try something slightly lighter we'll go with that one and let's see doesn't look a lot different all right we'll go with that for now normally I wouldn't put the bar and the value in the same field in the same cell because you get that effect where the bar can make the number difficult to read normally I'd have them separate but I haven't got a lot of room here so we're sticking them in one one column okay we're ready to insert our spark lines so first of all I'm going to select my range and then on the insert tab I'm going to use a line spark line line charts are really good for showing the trend of data over time so we're going to tell it where the range is so on the spark Cline pivot table I need all the data for the um months by state now there's a bit of a bug if you like with spark lines in that normally or ideally we'd put a dynamic named range in here so that as our pivot table grew as we added more data the spark line range would automatically grow the problem is you can't do that for a group of spark lines so let me just be clear this because I'm selecting all of these at once this will become a group of spark lines I can't create a dynamic name range for a group but I can create one for individual spark Lin so if I was to create an individual Dynamic name range for a C and then New South Wales and then Northern Territory and so on I could do that but that would be 8 * 3 so 24 Dynamic name ranges and I really don't think it's the best use of your time on mine to make you sit through me creating all of those so what I'm going to do instead is just insert the spark line and I'm going to allow for some growth by making the range extend past the end of the pivot table so we'll go with that and you can see there's some space at the end and you can tell that my spark lines are grouped because when I select one C I get this blue box around all of them now this is all chains so we need to make the spark line black let's do next look now again the same thing insert a line chart we'll go to the pivot find our pivot table for next look allow it to go past the end and we'll click okay now next look is orange so let's change that to Orange and Fashions direct rinse and repeat oops across to column Y and we'll click okay all right let's just make sure that's the correct shade of blue okay we are nearly nearly ready to make it fancy the next thing we want to insert are our slices so got some space over here for slices let's make this column a little narrower now there's a few ways to insert slices just like everything in Excel there's a few ways to do everything one way is to make sure you either have a pivot chart or a pivot table selected and then from the field list if you have Excel 20 13 onwards you can rightclick and add as slicer or you can go up to the insert Tab and choose slicer and this will give you a list of all the fields and you can actually insert multiple slices at the same time so I can select state and category cuz they're the other two that I need now I've got my three slices category State and financial year I can just move them over into place let's roughly resize them and if I hold down a alt it will snap them to the grid so that's another shortcut you can use you can see as I drag it across it's kind of snapping to the cells below I don't actually want it to do that so I won't hold down alt but that's another tip for you if you want to align things to the cells underneath and we'll resize that one a little bit and okay so our dashboard is coming together one thing that will really make it look better is if we turn off the grid lines so let's get rid of those and that makes it look a lot more polished and what's next ah let's make our manual Legend So instead of having this Legend up here let's put one up here and the reason we we can use a manual Legend is because all of our charts share the same coding color coding so we've created relationships between our charts by keeping that color coding consistent the last thing you want to do is use different colors for the same information and likewise you don't want to use the same colors for different information that's confusing as well so we can see in every chart Fashions direct is blue and next look is orange and the same here and here here so let's create a manual Legend I'm going to use some shapes just a rectangle we'll draw it in here and however big you want we also need a text box for the store name so this will be Fashions direct and let's format that so that it's got no fill and no outline and we'll make the font white so that it stands out better on the background all right uh let's just pin that for now okay actually that has a border so let's get rid of that and we'll just roughly align them hold down shift to select both hold down control and shift and copy them across and by holding down control you copy and by holding down shift you make sure they don't move off that horizontal line so this one is orange and this is called All right we need one more for our total stores or all chains so let's just copy this one again see how I at the moment I haven't got shift held down as soon as I hold shift it snaps back up to be in line with these ones so this is all chains and let's fix the color of this one so actually it's got a gradient fill so let's go and give it a gradient and now it's all washed out so let's fix that we'll go into more gradients and over here we can actually change this the gradient colors so the first one let's make it black this one will oops will make it slightly darker and and that's probably okay so that gives us a bit more of an indication so we've got our Legend now we can get rid of this one in the line chart and the other thing I might do is I'll set the axis so that the units are in thousands and that will just give us a bit more space and now all these task panes are taking up loads of room so have to bring them back down to sides we'll get rid of the lines that just clutters them um and this poor line chart doesn't quite have enough space does it there we go all right we need to repeat that formatting for these axis the axis on the other chart so that we're all consistent just double click to open the task Pane and also if you're using XL 201010 then double clicking will open up the dialog box that you have in your chart for your chart formatting in excel20 so let's squish you into there now normally I would be using a much bigger screen but I'm cramming it in so that those of you who are watching this on a tablet or a laptop can actually see what I'm doing it's not too small so I'm cramming it in when I normally wouldn't cram it in this much anyway so the next thing we need to do is create our Dynamic labels for the P chart so let's do that we'll do it on the pi sheet so this label let's take a look at it actually here it is here you can see here I've got the name of the chain the sales value and the percentage and the same again for the other chain and as I filter those labels update accordingly so let's go and take a look at how we do that so what we're doing here is really just joining text together concatenating text the first piece of information I need is the store name so we just simply select the cell and then we'll use ersan to join the next piece of text now this label wraps onto three lines so I'm going to use the character function and character 10 is a line break and that will wrap the text for me and the next piece of information I want is the sales amount and because I've clicked on the values area of a pivot table I get the get pivot data function and you'll see that the store name is hard keyed so I'm just going to select that and instead of having it hard keyed I'll enter the cell reference and then we want to join another line break and then we want the sales percent and again the store name is hard key so let's just make that Dynamic by selecting the cell instead now when I press enter you'll see all the pieces of information will be there but these two get pivot data functions are returning numbers and I haven't told Excel how to format those numbers it's converting this into text and I haven't told it how to format that text whenever you join text together whether it's a number or letters it becomes text and let's see what happens we get some ridiculously long number that means absolutely nothing so what I need to do is wrap the get pivot pivot data functions in the text function and that allows the get pivot data part to be the value argument for text and then I'm going to add a format so we'll tell it to format this in dollars and that will format the first value the sum of sales and then we need to repeat for the percentage so the format will be percents and now we have our numbers formatted although they're running onto one another and that's just because I haven't wrapped the tech the text yet so let's apply the wrapping and now it fits much better let's copy that down so we've got one for Fashions direct and one for next look now we can reference these labels in our pie chart labels and we're we're going to manually insert labels and the way we do that is by inserting a text box we'll do Fashions direct first so with the text box active we can see the pull handles are on the outside I'm going to click in the formula bar type an equal sign then click on my Pi pivot sheet select the cell containing my Dynamic label and press enter so you can see my labels in there I need to format it and let's change the font color to match the p and I'll align left and down let's make the font a bit smaller now I see that I need to make the P much bigger this is one of the reasons I don't really like pie charts is because they take up a huge amount of space and really return a very small amount of information for the space that they require now I need to format the text box so that there's no fill and no outline so no outline no fill and now it fits in there much better I'm going to copy this text box so I'm going to hold down control you can see my mouse cursor turns into a different symbol with a plus and I can left click and drag to copy so now with that's selected I can edit the formula to pick up cell E2 and press enter I'm unfortunately it loses all of the formatting which is rather helpful not so let's go ahead and repeat the formatting now one thing I like to do when I'm inserting labels that are colorcoded to match the chart segments is to maybe apply a shade darker so at the moment these are the same colors the segment in the pie and the next look font but if I make it a shade darker it makes it easier to read and it's actually really difficult to tell that that's a shade darker than the pi segment so it just makes it slightly easier to read for your users and um that's a little trick I use sometimes if I if I need to so now I've got my Dynamic labels but I haven't linked my slices to all of the charts and pivot tables at the moment they only control this sales by Chain chart so you can see as I select different items this update but nothing else does so let's go ahead and make those changes so first one is the financial Year I'll right click and go to report connections this brings up a list of all the pivot tables and you can see why it was so important to give them proper names because when you're trying to connect slices to Pivot tables and they just say pivot table one pivot table two it gets very confusing very quickly so I want this slicer to connect to all of the pivot tables now here's a tip if there is a pivot table missing from this list so you you're expecting a pivot table to be there and it's not listed there'll be two one of two reasons for that for your slicer to control multiple pivot tables all of those pivot tables must use the same Source data in this example we only have one set of source data so that's a definite sometimes though you can use the same Source data but inadvertently create a separate pivot cache so every time you insert a pivot table there is a copy of the data held in Excel memory called the pivot cache now if you're referencing the same data then there should only be one pivot cache but sometimes you can accidentally create more than one pivot cache from the same Source data and if that happens then you wouldn't see a pivot table in this list where it uses a different pivot cache so the solution is for example to copy one of the pivot tables that is in the list and recreate that pivot table that's missing so for this we want all of these pivot tables to be linked to the financial year slicer with the state slicer we want to connect everything but the map pivot and that's because the map chart isn't a pivot chart and the other reason is that normally the workaround that I would use is to link this map chart to a dynamic named range that picked up the pivot table however map charts are new in Excel 2016 and they don't yet retain Dynamic name ranges so unfortunately I've got to work within those limitations hopefully one day they will but for now we're going to just ignore the map pivot for the state slicer let's rinse and repeat for the category and for the category we can apply it to every pivot table so now we've connected our slices to all of the pivot tables and pivot charts when I select items in the slicer and let me zoom out a bit so you can see them in action you can see everything is updating so we're all connected we need to do a little bit of formatting to get it all looking good but otherwise we're nearly there so one of the benefits of building your dashboards the smart way that I've shown you where you link everything to a table that contains your Source data is that updating them is super easy and fast so let me show you down at the end of this file I've got some August data it's another 4 and a half thousand rows or so so let's just copy that data to the clipboard now normally I wouldn't use copy and paste for my data I'd use power query and go and get it dynamically and have it all automatically update but for the purpose of the webinar I'm just to make this process quicker I'm copying and we're going to paste it in so this is my data table this is what all of my Pivot tables are linked to and when you paste data on the very next row under a table the table range grows to incorporate that new data so at the moment we can see the end of my table is this icon here is indicating the end of the table when when I paste the new data in you'll see that shifts to the bottom of the new data so crl + V to paste it in you can see the table form matting has expanded if I go to the very last row you can see there's the end of my table so now I have 77,000 rows of data or thereabouts if I have any formulas linked to this table they already include that new data I just pasted in and for my Pivot tables and pivot charts all I have to do is click refresh all so let me show you back on my dashboard you can see I only have data up to July 2017 all I need to do is go to the data tab click refresh all keep an eye down here boom it's all up to date I can't tell you how exciting that is the first time you update a report with one click and then you go and get yourself a cup of tea or coffee and put your feet up cuz you deserve it I really hope that if there's one thing you take away today please take away how important it is to set up your file and your Source data correctly because when you do you can use these amazing built-in tools the way they're intended to make your life easy so that's updating your dashboards the next thing I want to do is fix the colors so they use my company branding and a quick way we can do this is using themes and with themes we can format our colors fonts and effects and we can even set up our own custom theme that picks up all of our company branding I can also choose from some built-in themes and as I hover over a theme you get a preview of what it might look like well that's a bit bright so the one I actually want to use is called parcel so I'll select that you can see the fonts have changed the colors and the layouts a little more spacious so now that I have everything looking the way I want in terms of colors and fonts let's spend a little bit of time formatting I'll just minimize the form uh the ribbon and let me just get rid of the formula bar so I have a bit more room okay so alignment is really important and aligning things helps you segment or segregate your data sometimes you don't even need borders and charts and tables it's simple alignment will do now one thing I should do is group these objects together so that when I want to move them I can move them as one so let's align the pie chart to the edge of the table here I've got a bit more room for my slices they're not aligned so let me hold down shift and select all of them and then we'll align them left and I've got some more room so oops let's move the state slicer up I'll hold down shift just to keep it vertically aligned let's move the category up and I can actually make these columns a bit narrower so that I've got more room over on the right okay the pie chart and the sales by Chain chart on aligned to the top and let's select the manager sales by manager and we'll align these three to the top so format Al line align top my pie chart height is different to my sales by Chain so one thing I'll do is go into format find out the height of the chart I want to keep so that's 5.54 select my pie chart and let's change that to 5.54 as well so now they're consistent and we can probably move this up a little bit now one way to move your charts using your arrow keys on your keyboard is to hold down control and select the outer edge you can see the pull handles of changed and now I can just nudge that up a little bit okay everything's looking pretty good I'm still zoomed out again don't develop your dashboards zoomed out always at 100% but I'm working with the smaller screen here for the purpose of the webinar so we'll just have to stick with that limitation all right that's looking pretty good I can make my category slicer longer okay let's edit the uh captions for the slices just to make them fit in a bit better so this can just be FY Financial year and this one can't do a lot with that maybe just call it category cat for short okay you know what I mean okay so one other thing we can do to make this look this report look more seamless and have less distraction is to get rid of the column and row labels and the the sheet tabs and the scroll bars so on The View tab we'll get rid of the headings and then on the file tab under options in advanced and then down in display display options we can deselect show horizontal scroll bar vertical scroll bar and Sheet tabs and click okay so now I have something that almost doesn't even look like Exel looks very clean there's not a lot to distract the user they can interact with the slicer you can see everything is updating all my labels all of my charts and we're ready to publish it and next month when you come into work and you've got the new data pop it into that Source data sheet refresh everything on the data tab refresh all and um you can get on with some more exciting work now one thing I have included in the file that you can download if I go to the end we've got uh D dashboard protection here so there's some information here on how to protect your dashboard when you start to share it with your colleagues some things you'll want to do with slices one of the annoying things with slices is when you click on them if you miss the button and it's easy to miss so if I miss the button I get these pull handles around the slicer and it will still work but it looks like it's in edit mode and I think it's not a really nice effect for your users so you can disable that you can also protect your worksheet and you need to make sure you protect it in such a way that pivot tables will still update when you interact with the slices this talks about how to hide worksheets and including how to really really hide them so that even when you right click this unhide doesn't give away that you've hidden sheets and some workbook protection and things you might want to consider here now we can also embed this dashboard on a web page so if you have an internet you could embed it on your internet and all of the functionality will work including the slices and I actually cover how to do that in my Excel dashboard course okay I know I ran through building that dashboard quite fast but my hope is that you've picked up a few tips as well as experienced the power of of Excel for building dashboards and that you're able to get started and have a go yourself and to help with that you'll find a link below this video to this page here you can download the workbook and it includes the complete Excel dashboard that I built here plus step-by-step instructions so you can rebuild it yourself it also contains links to tutorials on pivot tables slices spark lines Dynamic text labels and more so you can fill in any gaps in your knowledge and that way you can get up and running with building your own dashboards right away now further down this same page under the ebooks and PDF heading you'll find a link to my chart recipe ebook and this is designed to help you decide which chart to use for your data because if you put your data in the wrong type of chart then your message will be lost now I've only touched on the surface of what's possible with Excel dashboards in this video because I wanted to keep this under an hour but if you'd like to set yourself apart from the crowd then please take a moment to check out my full online Excel dashboard course where I teach you a whole range of different techniques including animations and you get five sample dashboards thanks for taking the time to watch this video tutorial I hope you enjoyed it please take a moment to like it and share it with your friends and colleagues and why not subscribe to my channel while you're here