How to build Interactive Excel Dashboards

[Music] thank you for joining me today I'm into 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 I simply Excel t-shirt on and to quote Steven whew 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 online training hub comm I run an online excel desperate course and have trained thousands of people from all over the world and from a vast array of industries in 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 distinction 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 slicers to create the interactivity and dynamic labels that respond to selections made in the slices so this is the data that be using there's over 70,000 rows of sales data for two australia-wide fashion stores called fashions direct and next look they're fictitious a 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 well so we've got suburbs state postcode country we've also got the manager the product category and product buyer and it's more matted 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 and we'll be employing various techniques to ensure a fast and smart bill namely Excel tables pivot tables pivot charts we'll be naming things like pivot tables ranges and worksheets 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 I'm going to be using Excel 2016 to build this dashboard but what you see will work in all versions of Excel from 2007 onwards with the exception of slicers which are available in Excel 2010 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 Excel 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 seventy two and a half thousand rows and we also have a sheet with some more data for August another four and a 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've 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 ctrl T and we need to decide where that our table has headers well mine has headers so I'm going to click OK and the first thing we need to do is give our data a name I'll 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 OK and let's give this sheet a name let's call it line pivot and we'll give our pivot table the 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 control 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 OK and now I have years and months in my row labels the next thing I want other 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 charts and I won't have to mess about the formatting them there so there's various ways to do this I tend to right click and go into the value field settings number format and will apply those formats and I'll click OK and ok so that's all I need for my first chart let's insert a pivot chart for it let me bring that down interview so this will just be a line chart I'll click OK now 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 choose slicers for my filtering so I actually want to right-click and remove all filled 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 tea 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 just do a little bit of resizing here not a lot look 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 and 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 to copy the sheet or the pivot table so I'm going to copy the sheet holding down control I'm gonna left click you can see the little plus I get for the sheet that tells me I'm copying it a left-click and drag lets rename that to category pivot and let's rename a pivot table and I'll delete the pivotcharts 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 this 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 legend 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 because we're going to sort by the grand total right click thought and this one I actually want smallest to largest and that would 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 sort of 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 should give this one not a title first let's do that I'll call it we'll call it sales by category and moon for that across there so we're consistent okay let's copy that one hold down ctrl and left click and drag and we'll rename the sheet we'll rename our pivot table and let's change the data now 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 attend they tend to take up a lot of space and a little bang for the buck if you like there's not a lot to them so there's often better charts for this but let's go with it we'll create a pie chart because it's going to support my dynamic labels then 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 the 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 filled buttons I'll get rid of the legend and I'm actually gonna get rid of the title as well so look how big they are but two pieces of information it's a quite a big chart and I'll just resize it slightly holding down ctrl makes the pie smaller on all sides equally okay so that will do for now for the pie the next thing we need to do is create our pivot tables for the sparklines so let's go and do that I'll copy the line sheet will call this sparkline and I'm actually going to break my rule here and instead of having one pivot table per sheet I am going to have all three pivot tables let me just show you the dashboard again I need a pivot table for the all chains of the total and either 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 that doesn't show on the screen so let's right-click short sort A to Z which they are but I'm making sure they always stay that way and the other thing I wanted 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 sessions 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 well copy the which one shall 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 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 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 eg let's just change that to a to C and click OK 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 to use blue and our ends but 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 that's 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 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 dash broadsheets 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 separate from them if you like her 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 head up now all we need to do is move our charts on to the dashboard so click on the outer edge of the chart control X to cut and ctrl V to paste let's just rinse and repeat so each one control X this one goes down here somewhere I'm just roughly placing them on my dashboard ctrl V to paste it in and the pie charts come and pie will go in here somewhere roughly and sparklines 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 everything is arranged so I need to insert my spark lines and they're going to go on row 12 so I'm gonna 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 gonna save me some time so I'm gonna paste it in there and we'll re-size 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 - 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 0 if that pivot table has an empty state so first of all let's test we'll go to our sparkline 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 sparklines pivot cell a6 equals blank then blank otherwise return I'll just copy and paste it in there otherwise return the value in that cell and that gives me the first state or copy that down for all the states so the next thing we need to do is get the value of sales for a CT for all chains now I could create my own pivot table for this but I'm gonna cheat a little bit and try and save some time by using one of the other pivot tables and the map worm will do nicely because 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 that'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 formula 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 wiser okay the other thing I want to put in here is a databar 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 that will help us identify which states are bigger than others and by how much it'll 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 it a shade of gray let's see if that one's ok probably a little bit dark let's try something slightly lighter we'll go with that one and let's see it doesn't look a lot different alright 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 on one column ok 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 sparkline pivot table I need all the data for the 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 line 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 named range for a group but I can create one for individual spark lines so if I was to create an individual dynamic named range for a CT and then you South Wales and then Northern Territory and so on I could do that but that would be 8 times 3 so 20 for dynamic named ranges and I really don't think it's the best use of your time or 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 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 cell I get this blue box around all of them now this is all changed so we need to make the spark line black that's doing next looked now again the same thing insert a line chart or go to the pivot find our pivot table for next look allow it to go past the end and we'll click OK now next look is orange so let's change that to orange and freshens direct rinse and repeat Custer column Y and we'll click OK 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 I've 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 2013 onwards you can right click and add a 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 a financial year I can just move them over into place let's roughly resize them and if I hold down alt it will snap them to the grid so 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 it 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 okay so our dashboard is coming together one thing that will really make it look better is if we turn off the grid line so let's get rid of those and that makes it look a lot more polished and what's next let's make our manual legends so instead of having this legend up here let's put one up here and the reason 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 you can see in every chart fashions directors blue and next look is orange and the same here and 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 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 ctrl and shift and copy them across and by holding down ctrl 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 on again then see how at the moment I haven't got shift held down as soon as I hold shift it snaps back up to being line with these ons 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 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 also make it slightly darker 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 planes are taking up loads of room so I have to bring them back down to size we'll get rid of the lines that just clutters them and this poor line chart doesn't quite have enough space does it there we go alright we need to repeat that formatting for these axis the axis on the other charts so that we're all consistent let's double click to open the task pane and also if you're using Excel 2010 then double clicking will open up the dialog box that you have in your shop for your chart formatting in Excel 2010 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 pie chart so let's do that we'll do it on the pie 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 and the first piece of information I need is the store name so just simply select the cell and then we'll use ampersand to join the next piece of text now this label wraps on two three lines so I'm going to use the character function and character ten 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 a hard keyed all into 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 keyed 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 to 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 have told you 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 it absolutely nothing so what I need to do is wrap the get 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 percent so the format will be percents and now we have our numbers formatted although they're running on to one another and that's just because I haven't wrapped the tip 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 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 PI and our line left and down let's make the font a bit smaller now I see that I need to make the PI much bigger it's 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 outlines and 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 ctrl 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 its selected I can edit the formula to pick up cell e2 and press Enter 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 color-coded to match the chart segments is to maybe apply a shade darker so at the moment these are the same colors the segments 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 pie segment so it just makes it slightly easier to read for your users and as 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 change art so you can see as I select different items this updates that 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 slicers 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 to 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 service 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 to 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 so 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 dynamic named range that picked up the PivotTable however map charts on you in Excel 2016 and they don't yet retain dynamic named 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 pivottables and pivotcharts when i select items in the slicer let me zoom out a bit so you can see them in action you can see everything is updating so it all connected you 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 four 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 and 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 I paste the new data in you'll see that shifts to the bottom of the new data so ctrl V to paste it in you can see the table formatting 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 thousand rows of data or thereabout if I have any formulas linked to this table they already include that new data I just paste it in and for my pivottables and pivotcharts 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 I'll 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 because 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 were 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 I 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 cool 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 of 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 simple at alignment will do now one thing 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 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 aren't aligned to the top and let's select the manager sales by manager and we'll align these three to the top say format align 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 five point five four select my pie chart and let's change that to five point five four 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 that pull handles have 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 a hundred percent 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 alright that's looking pretty good I can make my category slicer longer okay let's edit the 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 cats three cats for sure 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 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 Excel it looks very clean there's not a lot to distract the user they can interact with the slicer you can see everything is updating on 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 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 date 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 the 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 the 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 workbook protection things you might want to consider here now we can also embed this dashboard on a web page so if you have an intranet you could embed it on your intranet 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 experience the power 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 slicers sparklines 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

Channel: MyOnlineTrainingHub

Views: 5,036,805

Rating: 4.9463301 out of 5

Keywords: excel, excel dashboard, dashboard excel, dashboard in excel, interactive excel dashboard, interactive dashboard

Id: K74_FNnlIF8

Length: 52min 25sec (3145 seconds)

Published: Mon Dec 18 2017