How to create a fully *INTERACTIVE* and AWESOME Dashboard in Excel (tutorial + sample files)

this is an excel dashboard like no other making this dashboard has pushed me to my absolute creative limit and brought me so much joy that i couldn't help but share it with you all [Music] in this epic tutorial we are going to create a fully interactive dynamic and just drop dead gorgeous dashboard using microsoft excel let's go here is the blank data file for our awesome dashboard it already has all my sales data product data location data and further here my people data these are the data tables that we will use when generating the dashboard apart from the data sets it also has a bunch of assets these are predefined kpi tiles some standard icons and color schemes logos that are used in the dashboard further down on this assets page i have put images for our maps these images are i have downloaded them from this github url but everything else on this page is locally sourced from excel you can also add your own icons from the insert ribbon icons button in excel 365. the dashboard itself is broken into four individual components overall business summary geographical performance team and people performance and finally the product performance during the course of video you will also learn many powerful excel concepts that can be reused or reapplied in other works of life we will be talking about how to use power pivot data modeling and dax within excel to calculate lots of complex numbers from your data sets we will also understand how to use custom number formats and various formulas in excel to calculate the output values that will go into our dashboard at the dashboard layer we will understand how to use conditional formatting sparklines various types of excel charts and visual design and formatting features of excel so that our information that we are presenting is coming across in a nice and beautiful manner we will also understand how to use advanced formulas such as filter sort sort by and spill ranges to facilitate a dynamic sorting of the data on the dashboard layer we will also use data validation and slicers to make our dashboard fully interactive this is going to be a epic journey which will cover multiple aspects of excel and bring all of them together to create something that is fun useful and really valuable let's go our first step is to create a new worksheet where our dashboard will live apart from the dashboard worksheet there is also going to be a calculation worksheet where all our calculations live this is a very good practice whenever you are building complex excel workbooks to think of one place for outputs and one place for calculations that way you can keep these two separate and make changes if needed easily so let's add a dashboard worksheet and here in this worksheet we'll start by setting the background color on the entire worksheet so i'll select all the cells go to home and then select my background color which is this one here i have put the background color there so we can see that you know that is the color from that as far as the color scheme of the dashboard is concerned i'm just using the default excel theme colors along with some of the standard colors next we are going to select columns a and b and make them narrow this way there is a little bit of space on the left and that makes things look nice and clean our dashboard will actually go from column c onwards and it'll go as long as it needs to be so the very first thing that we'll put is our dashboard title which is chocolate sales so we can insert a text box you can use this button or shapes text box and then just draw a text box when you are moving around these things if you hold down the alt key it will quickly align to the cell borders and within this text box i'm gonna type my chocolate cells next we will apply our header formatting style to this the header formatting is barlow condensed extra bold so i'll select that and we'll make it nice and big and remove any fill colors any outlines so this is my dashboard title next up we're gonna make it stand out and we will follow the same pattern for most of the text boxes in the video so i will not re-explain the process for them but for this we will do so i want this text color to stand out if you notice in the assets headings is this color which is my yellow color on the top so the easy thing to do would be just select and set this to yellow if you want to go a little bit more deep you can also go to shape format text fill gradient and pick a gradient that suits your needs so when you add a gradient it kind of looks a little bit more fancy and add maybe a little bit of shadow effect so that it looks uh kind of coming out from the screen next i'm gonna place our awesome chocolates logo here so this is my logo ctrl c ctrl v there is the header for the dashboard we will have a couple of buttons on the top to access the data and calculations worksheets but since none of the i mean data is there but the calculations is still not done so we will build those things at the end of the dashboard now before we create anything else on this page we need to do a whole bunch of calculations so let's go ahead and start building that calculation worksheet we'll keep switching between calculation worksheet and output worksheet so that we make some calculations come back and build those calculations here the first step is to make sure that all these tables are connected so we can go to the data ribbon before even that let's just quickly make a note of the table names sales data is in the sales table product data is in the product table locations data is in the locations table and people data is in the people table as you can see products locations and people are all connected with my sales data table because they all share the same kind of columns so we want to connect these tables together so that we can build one pivot table when we are analyzing the data this is where i can use the data ribbon relationships button and set up the relationships the file that i provided you doesn't have any relationships so this is the very first thing that you want to do before you start making the calculations so we'll make a new relationship sales table product column is linked up to products table product column we'll repeat this process for the locations as well as the people tables and once all the relationships are set up you can close this and your tables are all now linked up at this time i'm gonna add our fourth and final worksheet to the dashboard which is the calculations worksheet all the calculations for the dashboard shall live in this page again i like to keep my columns a and b narrow and blank and build everything on the left right hand side from there on make this nice and big apply some background color and then type the words calculation worksheet here set the header formatting you might notice that when i go to the font selection i already have barlow and ariel here but if you open a default excel workbook this is not how things are they kind of look as calibri or whatever is the default font so how to change these two this is a one time step all you have to do is go to the page layout and from here you can customize the default colors and fonts excel will use within that workbook so here in the fonts i have defined a custom font set that has barlow condensed as my header and ariel as my body font you can do the customization through this customize and then pick the fonts that you want this way you don't have to make individual choices if i change something here then it changes across the dashboard if you notice the finalizer dashboard while there are a lot of different calculations and interactive abilities the whole conversation really starts with the tiles on the left hand side so here i'm telling you what was our total sales value 21.7 million and how much is it for the selected category so bars is what i picked that is 10.74 and then i'm also telling what is the month on month change for the latest month versus the previous month how much has our sales gone up so in order to build these tiles we need to calculate what is the total sales boxes shipments etc at a gross level as well as how much are these numbers for a specific category that is selected by the user likewise we also need to know what was the number for month-on-month every month so that we can then pick up what is the monthly changes so we do need to calculate a whole bunch of things before a single tile can be constructed so let's go ahead and build the necessary pivots to do this bit i'm going to leave the first few rows on my calculation worksheet blank because we do need to calculate certain things at an overall level we don't know what those are going to be yet but we will discover them later on so we'll build our first pivot table here so we'll select a cell and then say insert pivot table from the data model because we have already connected the tables we can just use all the tables and the relationships together to construct the pivot table here and we'll click ok let's start by calculating the overall numbers what is my total sales what is my total boxes etc so most of that information can be generated from the sales table for example we have got an amount column and if i just drag and drop it here i will see sum of amount as 2170 so 21.7 likewise i can put boxes i can put customers as you keep adding them you can see that the values go across the screen here if i move this here i'll see them down here so these are my overall summary figures we do need to count how many shipments are there likewise we also need to figure out what is our total cost and what is our profit as well as profit percentage so to count the number of shipments you can go to the data and each row is a single shipment so if i just count how many rows are there within the sales table that will tell me how many shipments are there because our data is already nicely linked up i can use the power pivot to build such measures so right click on sales table add a measure and this will be total shipments and here we can use the count rows function on the sales table to sus figure out how many shipments we have done we format this in the number and this will show up here i can add that shipments as there is no filtering on this pivot table whatever numbers we are seeing is at a gross total level next up we need to figure out what our total cost is if i go to my data table you will see that we only have the amount we don't know what is the cost of the individual transaction to figure out the cost we need to do a two-step process we need to know what product we have sold in that particular shipment go to the product table figure out the cost for raspberry choco so here it is it costs four dollars nine cents per box for raspberry chocolate so given that in this particular shipment we have sold 495 boxes of raspberry chocolates the total cost for us would have been 495 times four dollars nine cents so we need to kind of generate a imaginary column of individual costs and then add them up to figure out what the total cost would be you can of course add a column here but we can kind of do all of that with the dax directly so that's what i will do if you're not comfortable with using power pivot and dax you can also introduce a total cost column and use that to add up the total costs so here right click add measure total cost and we can use the sum x function within dax to do this kind of a table operation where for each row in the table we want to do certain thing so we want to do sales go to the sales table and for each row within the sales table i want to figure out what is the sales boxes times that with the related product cost per box so that is the syntax we go to the table for each row within the table we then want to do this multiplication number of boxes times the relevant product cost and once the multiplication is done then sum x will add all the multiplications together to come up with one final total cost and then we'll format this in currency we can then go ahead and add the total cost to the pivot table and then you'll see that the total cost is around 6.6 million let's just quickly apply some of the formatting to these values as well so that it's easy to read so 21 million is my total revenue 6 million is our cost for getting these chocolates onto the ships and given the total cost and total amount we can calculate what our profit is using another power pivot measure so total profit this is nothing but total amount minus total cost so we can use the square bracket to access the predefined calculations so sum of amount is already there minus total cost and add that to the pivot table so 15 million is your total profit we can also calculate profit as a percentage add measure profit percentage is equal to divide total profit with sum of amount and this would be a percentage with one decimal point and we will introduce that to the table so we make about 69.2 percent profit at an overall level we do need to know how these numbers look good for the last two months in order to calculate month on month changes so we will have to copy this and paste the same pivot here again so our original pivot is for giving me the summary or overall figures and this pivot will tell me what's happening at a monthly level and then here in the sales table we do have a date so i will introduce the date into my columns and that's gonna aggregate this at individual year level by default but when you expand you will see this at a monthly level instead of expanding one at a time i'm gonna use my column hierarchy here and delete the quarter and date component so we are only looking at year and month and now i'll just expand everything this formatting where things are going sideways is a little tricky to work with so what i'll do is i'll move these things here in the values there so that we'll get into more of a traditional format for the table this is all good but there is some extra blank rows in the middle which kind of will make it hard for us to do any formulas so i'll go to the design ribbon report layout and switch to tabular layout repeat all labels and turn off any grand or subtotals for the first couple of pivot tables that i'm making i will explain all of the process in a greater detail that way you know what is happening but afterwards we will be making many more pivot tables and i will only explain the intent of the pivot table and then show you the final outcome you can figure out the rest because we will not be making any extra measures we will be just reusing the values and just arranging the pivot tables let's go back to this page here so we can quickly see what is what else is required so you can see that we can calculate the 21.70 and we are able to calculate that 64 percent given that we have the individual monthly numbers but that 64 percent needs to be for a selected category of product so if i pick bars it is 64 but i go to bytes it is 60 like that so even though the monthly values are there they need to be linked to a slicer so at this point it is ideal for us to introduce the category slicer because this slicer kind of connects to many many different parts of the diagram so we need to build this up front and then start linking it up so let's do that while keeping this monthly pivot table active you can right click on the category field from the products table and add a slicer so this is going to add the slicer for us and now you can quickly test out that the monthly values will change if i go to bytes or other or bars this is the slicer that we will put it on the final dashboard after formatting it in a neat way we'll do the formatting bits later for now let's just figure out how to do the calculations next to calculate the month on month changes what we need to do is we need to know what is the amount in the latest month which will be the very last value in the pivot table and then what is the value in the previous month and then do the percentage analysis you could of course write a dax measure to do all of this and give you one percentage but i'm gonna use excel formulas because they are a little more flexible and easier to work with but because every month we will add some data this pivot table will be growing in this direction so we don't know exactly where the last cell would be and where the other cell would be so a better option would be if i can sort this pivot table in the reverse order on the dates then every time i need to do month-on-month changes i just have to do the first two cells irrespective of how big my pivot table is so this is where we're gonna go and apply a z to a sort order on the year and then we'll do the same for month as well so this will basically flip my pivot table uh putting the latest month in the row number one and then the previous month always will be in row number two so at this point our calculations will be just limited to that particular two cells so given all of this we can now go ahead and do the calculations before we do that i just want to remind you that in the final dashboard we are not using the customer numbers anywhere so we don't need the sum of customers in the pivot tables here as well i just added it when we were building it so let's just quickly delete those next let's calculate the monthly changes so this is where next to this pivot i'm gonna build a calculation area we can call this as summary for dashboard this month and we're going to use simple formula so say equal to and then point to the current month's amount value here if you do not see a cell reference and instead you are seeing a get pivot data function here you can use the pivot table options to uncheck the generate get pivot data thing so that it points to the cell address instead do the previous month like this the orientation of this table and our pivot table are sideways so it's going to look a bit clunky but we'll just have to repeat this process if you are following along and your numbers do not match make sure that you have set your category to bars that's the category against which we will test everything and then when we move it to the dashboard we can just change the category and the numbers change like if i click on bytes here you can see that these numbers change we'll use bars so we have got this month and previous month and i can calculate month on month change here mom percentage which would be this month divided by previous month minus one so compared to previous month our total amount went up by 0.63 or when you apply percentage formatting 64 and then when you drag this down you will see the percentages for every kpi value the next thing that we need is we also need to know what is the overall number because that that needs to go on to the big kpi tile so we will have a overall figure here and then this one would be equal to and get the value from here this pivot table is in the correct order as per our table here so i can just drag these things down and i will see the values there and we'll just have to do the formatting correctly we also need a similar set of values for what is the total overall value for bars category alone what is the total amount what is the total boxes etc there are a couple of ways to do this one would be you can add the grand totals back here and then use the grand totals but because the grand total will be at the bottom it's going to create a different problem i'm going to construct a new pivot table instead that will be easier because that pivot table needs to also have the connections to the slicer we will copy this pivot table and i'll paste it here and from this pivot table we'll just restructure it so there is no date thing going on so this pivot table looks exactly like that it will only have six rows but the numbers here are different from that because this one doesn't have any slicer filtering going on whereas this one is connected to my slicer on the category and again we can control c these values right click and paste them as reference to get a selected category value so we can call this as selected apply some formats so that they look consistently so now we have all the numbers required to produce the dashboard file we can go to the assets and you can see that there are some sample tiles i have put here so i'm gonna show you how to construct the tile so we can select all of these control c paste it on the on the calculations worksheet and here we have got three things we have got the label sales you can just type anything here this is a text box and the icon is you can use the insert icons that's how i have done i already have the icons in my assets tab that you can readily use and the number is really what matters so the number should be equal to this value one way to do this is you can just select the kpi tile go to the formula bar say equal to and then point to that cell and that's gonna give you the value like this the one problem with excel is every time you change the text box value like this you will lose the formatting so you'll have to apply the formatting again i find this a little annoying but it's fine so this font is barlow condensed semi bold and then apply the color so the total amount goes into this color and that's how that tile will look the problem with this is it has too much precision twenty one million seven hundred and one thousand seven hundred and twenty two dollars you don't really want to show at that level of detail when you're making a executive dashboard so a good option would be to print this as 21.70 million or something like that there are a couple of ways to do this number one option is you can select the cell ctrl 1 to format it and from here instead of currency change to custom and use a custom formatting code to change the way this is displayed so right now it is showing everything if i put two commas i'll now get it in millions and then a decimal point and a zero i'll get it in a million and after decimal point in hundreds of thousands so 21.7 to give an indication that this is actually in millions we can then space double quotes and then print mn here to kind of say that that is 21.7 million dollars so this is how you can do a custom formatting and when you change this this one will change because the custom formatting can be a little clumsy and kind of tricky to work with i have come up with a different way of doing this in a more manageable way that way you don't have to go to the self format place and do it so i'm gonna undo all of this and instead of using this cell to link up we are going to have these cells for calculation and then here we will set up a separate area for display we will follow the same pattern many times in the dashboard so you will see that there is a calculation section and then there is a display section for the display we need to have a format code and these are the format codes for each value it follows the same idea that i showed you there it removes some of the extra fluff from the code so given this format code i want to take this number and turn that into that format this is where the text function comes in you can use the text formula select a value in the format the format will always be in column y so i'll just change this reference like that and you will get this nice little formatting appropriately applied the beauty of this approach is if i change for example my data is no longer in millions but it is in billions or hundreds of thousands i can go and customize this in a more friendly manner without touching multiple formats for my dashboard so now that these values are there this is my overall we will have a similar section for selected and i can select this and instead of v22 we can point to this one z22 and again it kind of resets the formatting and we will get the nice little tile here we want to do the same for different things so first up once this is generated i will ctrl c this and paste it in my dashboard these two work this thing is not working this is because it is saying z22 so it's referring to the z22 on this worksheet we'll have to redo this by mapping it with the worksheet specifier so now it becomes calculations c22 and we'll get the value here we will have to do the formatting again and our sale style is now ready we'll just repeat this process so that we can get the rest of the tiles so there are our six tiles all of them say sales now we simply have to point them to different cells so this becomes z23 of course we do lose the formatting but now that we have got one tile properly formatted we don't have to individually do them you can select this style double click on the format painter and then just apply the formatting consistently for all of them we still need to change the wording and icons but these numbers are working so we'll keep the sales boxes and shipments in blue color costs we will change to the this color and then profit will be always in green color so every time you see the green color on the dashboard you will know that that refers to the profit in the final dashboard instead of using a single color i have used a gradient mechanism so if i go here you can see that it is actually using a gradient um so you can also do that i'm leaving that to your imagination let's just type these words here boxes and we will also change the icons to change the icons it's easier to go to the assets pick the icon that you want so for boxes we will use this icon ctrl c and then go back to the dashboard right click on the boxes icon change graphic from clipboard repeat the process for shipments and other stuff as well so there are our six styles just showing the total amounts the next step is to add another text box that will show me what that value would be for a selected category so again we can use the text boxes so i'm gonna quickly add those text boxes here put the text box underneath select the formula bar say equal to go to calculations and point to this number it will come up there and this text box will be in in the orange color once one of these is set up you can control c control v and then quickly apply the same for rest of them and also make all of them the yellow color with the italicized and make sure that they're not too big so i'm going to make them 10 points so that they're there but they're not getting the same amount of attention as my original values okay our tiles are now ready we need to show month-on-month changes as an indicator right next to them and for that we are going to use conditional formatting as we have already calculated the month on one percentage changes building the conditional formatting is really simple and we can use this column column e to build the formats first up let's just copy the month on month percentage changes ctrl c go to the dashboard select an empty location and paste them as links so that we can see the values here now i will move one value at a time to the relevant position and then the cell underneath we will make sure that the value is also duplicated so we will get two cells with the values you'll quickly understand why i'm doing that next we will use this cell to show the percentage and that cell to show the icon so from home conditional formatting icon sets and apply a icon rule i picked this style of an icon and then we need to edit the rule so i go to the manage rule edit first up we'll make sure that we are not showing the value so just icon alone and the icon needs to be green up arrow when it is a number greater than 0 that means the change is positive from last month and it needs to be a downwards red color arrow when the change is negative you can leave the middle one as it is and when you click ok you will get the icon now when there is no number just the icon you can apply the cell alignment to bring that to the middle middle align this one as well and then select these two double click on the format painter so that we will get the same sort of conditional formatting and alignment all the way through now i can adjust the size of this column however we do need to make some adjustments to the colors for example all of these need to be in the orange color or the yellow color when i pick a category and if i don't pick any category then they need to have nothing likewise this one needs to fade back to the lighter color until i pick something so let's make those adjustments to start off we do need to know if the user has selected a category or not so we'll do a couple of adjustments first up we'll pick our category slicer ctrl x that and place it on the dashboard for now i'm just gonna position it all the way here later on when we finish constructing the dashboard we'll make sure that the formatting and the sizing and alignment is all consistent so right now through this i can see visually if i can if i have selected something or not and i can also change my selection so i can go to bytes or bars but irrespective of what i do while the values change the coloring is not changing so the next thing that we need to know is we need to know internally in our calculations worksheet if a category has been selected if so what category is selected so we'll go to our calculations worksheet remember the blank area that we left on the top this is what we will use to build a slicer harvesting pivot so we can copy one of these pivots this pivot is already linked to the slicer so ctrl c ctrl v and in this pivot we will list the category in the row label area so that it will show the categories and then we'll get out of all the values so that whatever category that you picked on the slicer will be listed here so if i picked bars this is what that will be but if i go to my dashboard and for example pick all the three or clear my slicer it's gonna show me everything we can use these cells to figure out what categories are selected we will set up an area outside the pivot and here we can just check how many categories are selected at awesome chocolates we only have three categories so i can use count a to basically point to that range to see how many are picked so right now three are picked what i want to do is based on this number i want to print a word that says all multiple or the name of the category that is picked so this is where we can use the choose function choose of that number if it is 1 then the category will be in my c5 cell if it is 2 then we want to just say within double quotes multiple and if it is three then within double quotes all so this is going to say all but if i go to my dashboard and for example pick bars and bytes this will say multiple and if i pick just bars this will save bars so category selected the number will tell me how many i have picked and this will tell me what is the name of that category if only select single one is selected using these values we can update the dashboard so the first thing that we will do is if i pick all three then on the dashboard i don't need to show this extra number so we can go to the calculations this is where the selected values are coming and here i can add a if condition if my f5 is equal to 3 then i want blank else i want the text formatting to be applied then we can drag this down see what happens when i clear the filter as i have picked all three this is gone these things are not changing this is because our reference is changing from f5 to f6 here so we need to lock this to f4 and then drag this down so that they are gone and the boxes have now magically disappeared the box is still there they are just printing empty spaces and when you select something that value will show up the next thing that we will do is we will change the color of these values to the orange color when you select a specific category so i'm going to first select everything and make them the default color which is this one and then select all of these holding the control go to conditional formatting add a new rule the new rule would be using a formula i want to check is equal to go to calculations point to my category selection and then just say is it less than 3 so anything under 3 means we have picked a category if it is 3 that means we have not picked a category so if so we can apply a format font color needs to be that and okay and okay so when you select something this is the color that it'll show up but if i don't select then the color will be different a quick note here for example you can see that costs went up by 95 percent compared to last month and that does show up as green apparel normally in a business situation if your costs are increasing that's not really a good thing whereas a green color generally means it is a good thing so you may want to think about the semantics of this and figure out a different way to present or at least sensitize your audience that this is what is happening but for now this is all good let's say just some of these column bits our tiles are looking pretty good now let's just add a spacer line in the middle so that we can tell them apart you can use the insert shape line and then draw a line in the middle if you hold down the shift key you will draw a straight line and select the color outline that looks good let's just add it across that looks good now let's go ahead and add the country information here referring back to the completed dashboard you can see that we are showing all the countries the order in which these countries is shown is based on the profit percentage but you can see that here there is a sort by option so i can show them by profit or by sales whatever metric that i choose based on that the countries will be arranged in the descending order and then the biggest country will get highlighted in blue color and all other countries are shown in the regular dull color so this is uh this requires creating different side types of calculations and then working with the images and shapes so let's go ahead and build that part in our calculations worksheet we will go a little bit down and build an area for maps here we will construct two pivot tables one for telling me what is the value of total sales and profit percentage for each of our countries and one for telling me what those values are for the selected category alone so that we can show everything together on the report so we will insert a pivot table from the data model and go to locations and put the geography on the row label area so these are the six countries in which we operate and then go to the sales and we are interested in the total amount so amount goes there as well as profit percentage if you want you can also add some of these other measures there and include them in the sort options i'm gonna turn off the grand totals from this this is my overall level detail i need another pivot like this so i'll control c paste it here and then this pivot we will link it to our slicer so when you start creating multiple pivots like this sometimes it can become hard to see which pivot is what this is where excel offers a feature called pivot table name the names of pivot tables are by default auto generated by excel but you can type your own names i normally don't bother naming them but if i'm making a complex dashboard like this i create the names so that it's easy for me to figure out what is what later on so let's quickly name these pivots this is my country pivot all and then this is my country pivot selected so that we can tell them apart when we are trying to establish the connections next we will select this particular pivot the country pivot selected and then click on filter connections option from pivot table analyze and link this to the category slicer so this way this is the only one that responds to my slicer values whereas this one is always set to my overall values all good now both of these are there and in the dashboard now i need a mechanism to pick and display one of these columns as well as arrange them in a sort order so we need couple more things to make this happen number one is sort options there are two sort options available for this area of the dashboard so they are sales and profit percentage and then we can select these two ideally you may want to give these names so that later on it becomes easy to work with them so using the name box i'll give this a name sort options dot country list you can give this as long a name as you want this way it's easy for you to spot it later on all right so we have these two options and then we can selected option for now i'm just going to assume we picked select sales as the selected option later on we will automate this bit so i'll fill some yellow color here to tell us that this needs some work later on so we picked sales and sales is x match whatever i picked in the sort options country list so we picked the first option if i change this to profit percentage that number will be 2. next up we need to get the corresponding values and then list them here so pivot values and here country sales profit percentage and i'm just going to control c right click and paste it as a link so these are actually linked to my pivot table and this is my original pivot table data so this is one way to do it another way is you can also just say equal to and select the whole thing this way excel will automatically spill the values from the range that you have pointed either way is fine and once the values are here we now need to sort them based on either the sales column or the profit column so this is my pivot values and here is my sorted values to sort we can use the sort function in xl365 sort of this array because i have pointed to the pivot table everything is now as a big range sort this array on the index which is already specified by the user here now they say one or two so sales or profit but the first column is the country name so this needs to be plus one and the sort order needs to be in the descending order so that our most sales country goes under the hop and the lowest sales country comes to the bottom so these are my sorted values and then we also need to show the selected values so we need to add those values as well now the problem here is these are two separate pivot tables and we took the original and then sorted it so new zealand goes on to the top so here i need to figure out what is the corresponding value for new zealand we could for example use a lookup or something like that alternatively you can get a little bit smart and do that in a different way let's get the pivot table values here we will have country sales profit percentage and then here this is equal to and then we'll just point to that range so that we'll get a copy of the pivot values here i'm using xl365 so this kind of a behavior and spilling automatically happens and then the selected portion will be equal to and then these values you need to make sure that the ordering of the countries and the values are exactly same otherwise this can get into some sort of weird combination issues but once all the values are here we can then sort them sorted list so sorted list would be and here i can use the sort function select this big range and then the sort index is specified by this number here it says 1 but we know that the first column is country name so it needs to be plus 1 and the sort order is descending so that it will sort on this column if i pick profit percentage then it will be sorted on that column in our sorted data the first two columns represent my all values and the second two columns represent my selected values now that the countries are sorted we now we have to figure out how to show their images on the dashboard so in the assets tab i have put all the countries images there so each image is listed in one cell if you notice they go from b44 in the assets tab up to b49 the country images and the names our corresponding names are in c44 to c49 so let's set up some names i'll select the c44 to c49 and then name this as country dot names we'll do the same for column b country dot maps so we got two named ranges one named range points to my names and the other named range points to the cells where the images are located now we are going to use one of the most powerful and beautiful tricks in excel to get the images dynamically onto the dashboard i'll show you the technique at a high level first and then we'll go and implement it later so given these images let's say i want to get the third image and then show that in my dashboard we can for example use the index function so index of country maps that's the name and then if i say three i should hypothetically get the third image unfortunately this will only say zero because index is trying to get me the value of the cell but it is also pointing to the cell where the image is so we can use this kind of a logic to do the job for us all we have to do is first make a image in excel so if i select the third image control c go to calculations right click and paste special linked picture i will get a picture of the cell notice that this is a picture that is linked to assets b46 so whatever is in b46 that's what i will see for example instead of india map if i put let's just insert a icon here of this map of africa so that's what it is there and when i go to calculations that's what i will see so whatever is in that cell that's what we will see here now if i can dynamically change this formula and point it to maybe b 45 i will see the map of canada instead of india so this is the core logic we are using a picture link to dynamically fetch and change the source or the link where to which the picture is pointing on the fly based on what is being sorted let's just restore this i'm gonna delete that and move it back here so to make it happen we need to have six countries the maps of the countries are as per the names that are shown here so we will have a map id number and this is nothing but x match this name in the country names list so this will tell me i need to get the fourth image like that we need to have the same values all the way down because this is a dynamic range i can we can just drag this down and it will show me what the map ids for each of the countries is and if i change my sort order so from sales if i go to profit percentage i'll see different listing of countries and different map ids here based on each map id number if i can generate a dynamic name that gets me that particular map then we can use that logic so let's create a named formula here to do that go to formulas define name and then we will just say map1 the map1 would be index of country.maps this number so that's the first map that i want it doesn't like the map one because map one is a cell reference so i'm gonna use map.1 and we will repeat that process for other names [Music] so i now have created all the six maps map one map two map three map four map five map six and each of them pointing to a different map id as per my numbers here this might seem a bit counterintuitive but i encourage you to study this a little bit more if you have trouble understanding the logic once all the map names are generated we can test out the logic so for that i will go to the assets again i'll copy one of these cells we'll copy new zealand this time and paste it here as a linked picture so we'll get the new zealand map as it is notice that the formula says assets b47 where it says assets b47 if i say get p map.one it's gonna get me the usa map because notice that usa is my map one if i change my sort order to sales now i'll get new zealand here because new zealand is the first country so this is the dynamic map we need six of these like that so i can control c ctrl v and then let's just test out the logic here and then we can go to the dashboard and make those adjustments all the six of them are pointing to map one will say this needs to be mapped to map three map four map five and finally map six so all the six countries as per the order of sales is shown now if i change this to profit percentage i will see them listed as per their profits looking pretty good now for whichever country is number one we want that to be highlighted so i'll select the map 1 go to picture format and recolor it from color we'll select the blue color unfortunately we are not able to use more variations because whenever i try this the color doesn't change it seems like a bug in excel so instead i'm gonna go with this blue color and go to corrections picture correction options and from here adjust the brightness until we get the kind of blue color that is consistent with our dashboard style so that seems pretty good this takes a little bit of trial and error i think 35 percent brightness and 60 contrast is what will give you that color which is closely matching with our highlight color as per the color scheme next we'll select this go to picture format and picture effects and we will add a nice glow effect around it we'll use this blue color but more glow options and then from here i can customize this so i'm going to go with this color and adjust the sizing and transparency so that it stands out a little bit we'll have to test out a few different combinations again unfortunately we can't test this one here we will have to place this on the dashboard to see it so i'm gonna once all of this is working you can hold down control and select all these images ctrl x them paste them on the dashboard that looks super bright so we'll need to adjust that and position these in relevant places right now these maps are all too big for my dashboard so i'm gonna go and size them we'll adjust all of their sizes one go with the picture format height option and then just set it to 0.8 which seems to be sufficient i was just checking my other workbook to see what colors i have actually used what effect i have used it does not even glow so it's a shadow effect and let's just adjust some of these alignment and everything and it looks pretty good now we'll also have to adjust these colors to make them a little bit dull and the black color doesn't really go well with our background so again you can use the same colors technique here and then tone them down to a different shade like that probably they'll do and now the countries are showing up here nicely we need to show the name of the country how much is the sales value for the overall value and what is the selected value and if profit is chosen then we need to show those values as well so let's add that extra bit of calculation here all the numbers are there we just have to figure out what are the right numbers and bring them to the screen we'll set up for dashboard area here and we need to get either these two or these two depending on what is being selected so country and then country is nothing but this six and then value value selected [Music] and we can use some if formula here if this thing is equal to 1 then i want this value else i want this value so we will get that [Music] and then we can just do it like this make sure that the references are correctly adjusted so we will have all the values here now we just need to take this to the dashboard one quick trick is ctrl c here go to the dashboard in up empty place paste as links and then just move everything to the relevant position i'm gonna also adjust the column here so that it can fit all my countries while the values are here we are not really sure how to format them if i apply percentage formatting what happens when i change to sales then this number needs to be in dollars so the good way to format this is again to use our format technique we can define the formattings right next to the filter options so sales formatting would be dollar hash comma comma 0 0 m and then this is 0.0 percentage so we'll make sure that these two are set to text so that they don't really change when i type something and now that these are there we can use this number to figure out which formatting to do so here you can change our if calculations all of that and then text so now this will always be as per the format of the value that is being sorted on so if i pick profit i'll get percentages if i pick currency then i'll see the dollar figures here and those values will trickle down here nicely all we have to do is highlight the usa values in a different color and then bring these in in the different color so for usa we will apply our header formatting in this color so there are our countries and their information it all looks good let's just test this out by using the slicer if i pick bytes other i will see the values change if i clear then i'm seeing all the values when i clear the category slicer these values are duplicated and that's not necessary so we will need to turn these off whenever the value is all again we can use the if condition on this bit here if my category selection is 3 then i don't want anything else i want this bit so this will kind of turn that bit off so when i pick everything i won't even see the other option but if i select an individual category only then that'll pop up pretty cool we can also add a label on the top that says country sales this is where on the assets i've got a nice little globe icon so i'll control see this paste it on the dashboard move it there and then right next to it insert a text box again we are typing the words here but if i pick profit percentage then i want this to say profit percentage so initially we'll set it like this and then in the dashboard calculations worksheet will generate the title the title will be it'll always have the word country there and then if in fact we don't even need if we can just say ampersand and then get this bit so it'll be country profit percentage or country sales we can just use upper on that to turn that into uppercase so that will be that and now we can go to the dashboard select this text box say equal to and point to that so that'll be that and change the color and adjust the font size until you can see it pretty beautiful we'll also need to bring in the sort option here so that user can directly change the value from the dashboard so for that we will use the row number 38 for doing all of those things and i'll make it nice and big so that we can quickly spot it in the big dashboard and here for these kind of things i am setting a different background color which is slightly duller so that it kind of stands out and here we will just say sort by and in this cell go to data data validation list and then this list is equal to you can use f3 to bring the name list and then select sort options country list click ok so my options will show up here i can pick sales or profit percentage notice that even when i pick this nothing happens on the screen but the ability to select is now there next up we'll select this cell go to calculations and where we have put the yellow color equal to and then point to that cell so now whatever i pick on the dashboard that's what this yellow cell will say so now it is linked so we can turn this blue and i can select sales i can select profit when i go to sales i'm getting errors so let's go ahead and fix this problem i think we may have made a mistake somewhere whoa all these calculations work i think this is where the problem is happening i suspect it's the text formula that is creating the headache okay notice what the problem is it's because we just typed m here directly it needs to be in double quotes then it will work so now these values are coming up that is good but the values are wrong it is actually it should be 3.7 but it is saying 3.77676 so it's going into greater detail than needed so again um i think it's better to just copy this rather than type it again all good now the values are perfectly coming up and i can see what is the new zealand value at an overall level and what is it for bars and i can go to bytes if i want to see it by profit i can change this to profit and then that's what it'll show up we'll adjust the column bits to make sure that there is enough space to display everything and now our best country goes to the top other countries are listed here the way we are highlighting the best country is using the picture format options but it is always just the plain country color and then the picture format is changing the fill color there for me our next job is to create the team performance and individual overview of the performance this part of the dashboard has many powerful and dynamic ideas so let me first do a quick recap of the things that we need to achieve and then we will go and build them the top one is a graph that shows me how much is the total sales and profits by individual teams within awesome chocolates we have four teams yummy's delish tempo and juices and they are arranged in this graph here the blue colored one is the highlighted one and i can change this to a different team using this so for example if i pick to emis that one becomes blue color the star indicates the best performing team yamiche is both the highlighted one as well as the best performing team but if i change the highlighted to juice then the star will still be with yummies because it's the number one team whichever team i pick the people of that team are listed here along with their sales profit profit percentage data and a trend of their shipments so within this table here you can arrange the data in any order right now the data is arranged by the profit percentage and that is also indicated by the header with a down arrow next to the profit percentage i can change this to anything so for example if i change this to sales you will see that these values are now in the descending order the profit is also having a icon next to it to show us how good the profit is we have a profit kpi so all of these people are meeting the kpi and they're all green i can turn off the profit icons uniformly using this button here which will remove the icons from both of the profit columns in one go so if i off this the icons are gone if i on it i will see them this particular area here shows me how many shipments they have done or whether they have done any shipments in the last 28 days so this is basically like a win loss chart that tells me most of these people have done shipments for example madeleine hasn't done any shipments in the last few days i can change this to sales and then see the day sales trend number of boxes or number of shipments or change it back to the shipped question mark to see whether they have done a shipment or not on each of the last 28 days we will create this in two steps we will figure out the logic and necessary calculations for the graph here and generate the graph along we will uncover some interesting techniques and then we will figure out the logic for this table because the structure and the formatting of the table here is similar to this table once you know how to make this table you just need to know what else need to change to generate this table let's go in the calculation table we'll go further down to an empty area and then set up a people section for our calculations and here we'll set up our very first pivot table which will tell me how much is happening by individual teams so we will insert a pivot table from the data model this particular area of the dashboard both my team's chart as well as people chart is not really connected to the category slicer so here this pivot table can just come from the full data all the time our first pivot table will make a pivot table with the team on the row level area and go to the sales and we will list two things one is the total amount and the next one is the total profit we'll turn off any grand totals from this and in our final output we would like to see these four teams arranged as per the total sales so that the biggest selling team is on the top or it has the biggest circle so to do this we can right click on the sum of amount sort this largest to smallest when you do this every time there is a updated data through data refresh pivot will automatically reapply the sort order right next to the pivot table we will set up an area for generating the graph the specific graph that i am using here is a x y graph and this sort of a graph x y and bubble graphs cannot be generated from the pivot table directly so we'll have to kind of capture the pivot values outside and make the graph we need the team column sales profit and we also need x and y values the team is equal to whatever is the pivot table value and then we can just drag this down we are operating under the assumption that there is only four teams if your data has more teams than that you may want to drag this down a few more rows for our best performing team which is the number one team as per the pivot table data we also want to append a star symbol next to it so in this cell here i'm gonna use windows and dot key to insert an emoji i'll search for star and add the star emoji here and then in this cell instead of directly getting the pivot value we will just say ampersand this value and drag it like that because these cells are empty this will be just as it is and yummies will have the star the star looks in black and white color here but when you put it as a label on the chart it will look in colors we'll do the same for amount and profit and now we need to figure out what the x and y values should be let's just do a quick screen drawing so that we can understand what values would work best so imagine a graph like this this is my x-axis and this is my y-axis so this is 0 on both sides and then we can just think of this as 1 and 2 and 1 and 2. so our best team we would like to place that value here with a big circle equivalent to their amount of sales the second best team i want to put it here third one we can put it here and then the fourth one can go here okay so one two three four for the best team the coordinates are thus going to be x is 1 y is 2. so this is 1 and 2. likewise we can put the coordinates here as 2 comma 2 like that so those are what the values of x and y will be we can just manually type these or you could use some sort of a clever formula to come up with x and y values if we have more dynamic list of teams as at awesome chocolates we only have four teams it's easy for me to code them like this so i'm just going to type the values now and as far as size is concerned size is equal to your total sales anyway so we'll bring the size values here this way it's easier for us to generate the graph once you have x y and size you can select all of this go to insert click on this scatter chart area and then pick a bubble chart this doesn't look anything like the way we have pictured so i'm just gonna right click go to select data and then see what's happening series one edit make sure that x values are here y values are here so as you can see it is reading the header row as well so i will adjust the reference and we will get the correct output here number one team second team third team and fourth team now we need to turn this into a dynamic and interactive graph that always shows the selected team in a different color so we will need to figure out what team is selected here on the top of the calculation area we'll set up a selected team for now we will assume that this is juices but later on once we finish setting up the dashboard we will link this up so again we will turn this into yellow color to remind us that this needs more work so selected team is juices and then here we can just say size 2 and this one is simply going to be if the team name matches whatever is selected here then only the size will come up otherwise it will just stay not applicable so we can use a if function for that if my team is equal to whatever is selected here then i want the size else i want not applicable this doesn't get me any results this is because i've mistyped the value name here it is juicy's and now i'll get the answer here so now that we have got a size 2 let's add that to the chart right click select data add and then this name is selected team and its x values are here y values are here and then the bubble sizes are these four values so you can see that the selected team gets a different color in reality the selected team is also showing up as another series and that series is overlapping this series so now all we have to do is make sure that the colors are adjusted so that the selected team gets highlighted in blue color and everything else is in the gray color so we'll select the original series of the bubbles and then using the format apply a formatting so i'm going to go with the gray color and then change it to a gradient like that and then take the outline so that's how they look and then select this one and then apply the the blue color and then try the gradient on that one as well so that that will get that and then add a shape effect of shadow around it and show options and then adjust the shadow settings so that it gets a little bit strong shadow it's hard to test out this thing right here simply because here everything is white color but we can see how this would look like when we put it on the dashboard so we will fine-tune the formatting later on once we move it to the dashboard but for now we still need to add some labels and make sure that it is readable and looking nice and good the first thing that we also want to do is we don't need to have all these axes and everything so i'll select the axis go to access options make sure that it starts from 0.5 and goes only up to 2.5 we'll do the same for this side as well and then take away the axis you need to frost state just access settings before you remove the axis next up i'm just gonna take out the grid lines as well as chart title so that this is how it looks like we can also select the chart go to format and then make sure that it doesn't have any fill color at an overall chart level and outline too so that this is how that'll look next we need labels the label needs to be team name new line total sales new line total profit and then that's how the label will be for all the four bubbles so we need the label here we'll make a new column called labels and then this one is equal to text join delimiter is newline character this is denoted by car code number 10 and then the values that we want to get are this value sales value and profit value we are going to get that but unfortunately they will show up as numbers but if i apply wrap text you will see that this is how they look team name looks good the sales and profit are not formatted correctly so we also need to adjust the formatting so before making the label we need to first apply some correct formatting so i'll move the labels here and then sales formatted profit formatted and here we will use the format code to generate the correct way of formatting this using the text formula value is here and the format code is there and then update the formula so that it reads these values so that we will get a nice little label here correctly adjusted remember that i have word wrap on we don't need to have the word wrap thing here because it's not even relevant for this so i'll just turn that off now we'll select the chat we want the label to appear in the middle which is equal to that value so we'll select the chart add labels and the labels will show up in the middle the default values are x values i think go to label options and where it says x or y value we'll use the value from cells and point to this range click ok and the labels nicely show up in the middle we no longer need the y value so i'll turn that off and it all looks good but some of the labels are overflowing so i'm gonna select the bubbles and make sure that i'll adjust the bubble sizes so scale bubble size to 100 let's test with 200 and then see that looks nice and big and the label is also readable let's move this to the dashboard so that we can test out if the colors are nice and good so we can just ctrl x this and go to the dashboard paste it here ctrl v as you can see the colors are not perfectly working out so let's just adjust them all right that looks better we'll also need to adjust some of the spacing between things so for example between each section of the dashboard we are keeping a column blank and the column width is set to 36 pixels so we'll make this one 36 pixels wide we'll also need one extra column here to show something else on the table so i'll make this one also narrow and then we will move the table here and make sure that this is nice and big like that we want to show a label in the middle that says sales and profits by teams and then draw some connecting lines this is where again you could use the charts or you can kind of cheat so this is what i've done because we only have four teams it's easy to make that up right here using some drawing shapes so let's add some text boxes and then using the shape line we'll just connect this like this so that there is two lines going on next up we'll select our text box place it in the center and we also don't want to see the lines where the text box is so i'm gonna insert a circle shape in the middle and match this with our background color now outline so that that kind of blends in there now we will just push this back and then select these lines push them back so that we'll get a view like that we'll also change the line colors and our teams chart is looking nice and pretty you can test the highlighting right now it is on juicy's let's test it to delish and the color changes to this one here now we just need to link it up to a cell on the dashboard we will do that once we set up our team table so underneath this here i would like to show my team values again just as a reminder you can see that we need these values for each person in the team sales person sales profit profit percentage and then a dynamic graph that tells me what happened in the last 28 days to make this we are going to use a pivot table for this portion of the data and a calculation cells for this portion of the data this is going to be interesting and that's where we are gonna conclude this part of the video in the part two of this awesome dashboard tutorial we are gonna learn how to construct the remaining parts of the dashboard along with many advanced pixel tricks feel free to check the channel after a few days for that video alternatively subscribe to the channel and enable notifications so that you get elected whenever that video is live i'll catch you in the second part bye [Music]

Channel: Chandoo

Views: 185,211

Keywords: chandoo, chandoo.org, Excel, spreadsheets, how to build interactive excel dashboard, how to create an excel dashboard, dashboards in excel, excel dashboard, excel dashboard design, interactive dashboard, create excel dashboard, excel dashboard tutorial, excel dashboards and reports, interactive dashboard in excel, build excel dashboard, dynamic dashboard, kpi dashboard, Sales dashboard, beautiful excel dashboard

Id: bG1Qha-Ii7A

Length: 80min 52sec (4852 seconds)

Published: Tue Jul 12 2022