The amazing ONE formula Excel dashboard with LET function

do you want to create stunning and professional looking dashboards in Excel do you want to learn how to use excel's powerful features to visualize and analyze your data do you want to impress your boss colleagues or clients with your amazing Excel skills if you answered yes to any of these questions then this video is for you in this video you will learn how to create an app big beautiful and Powerful Excel dashboard that will take your data to the next level and here is the best part this entire dashboard is going to be made with a single Excel formula that's right if I remove the formula the dashboard is not there and the moment I put the formula the dashboard is there let's take a look at this [Music] let's start with the basics this particular one formula report approach relies on many of the newer functions available in Excel 365 the biggest ingredient is of course the let function but as you can scan from this entire Goble de of the formula there is a whole bunch of new formulas in there so let's take it one step at a time first let's understand what our data looks like the data comes in three buckets we have got the shipment data of awesome chocolates each row is one shipment and we know who the person is what country they ship it to what is the product what is the date amount units and a shipment identification number and then we also have two other dimension tables one is a products table that tells me for each product what is the category and how much is the cost per box using this cost per box information along with the revenue or the amount information I can figure out what our total costs are and profitability is and on the people's side for each salesperson we know what team they operate in what is their profile picture URL as well as the picture itself for this picture I have used the image function to extract the picture from the URL so now that we know the data let's take a closer look at our one formula report as I said this entire report is generated with this single massive formula we'll go into the specifics of that formula in a minute but I first want to demonstrate some of the more powerful aspects of this one formula this report not only tells the whole story but it is dynamic so for example I can choose to sort this report right now it is sorted on Revenue by some other column maybe cost and I'll see the entire thing updated with the person that has highest cost up in the first row along with the indicator for cost cost shown here in my headings and then I can also change the ordering of the report from high to low to low to high likewise I can also select how many people I want to see in the report right now I'm seeing all the 25 people but if I want I can see just the first 15 people or 10 people you can see that everything is dynamic as I change my formula output automatically shrinks and right now it only prints up to 10 rows with this information as this is a very big formula I find it very hard to write that formula using the cell or the formula bar here so for that reason I recommend using Excel lamps which is a free adding from Microsoft if you ever want to write bigger formulas or use python in Excel or even the AI features of excel so when you click on the AI Exel Labs thing it's going to automatically show me here because this is what I've set it up but you will be able to switch between different Labs features I don't want to explain this formula because this is going to take forever to explain this kind of a very big formula as you can see here this formula is 188 lines long with many many things going on instead what we will do is we will rebuild this from scratch so that you can understand the concept before we get into the one formula dashboard a word from our sponsors well there is no sponsor I am the sponsor of this video this video is part of my Excel School dashboards program apart from this video there are more than 50 lessons in the Excel School dashboards program that cover the entire spectrum of Excel for data analysis work right from Power query all the way up to creating dashboards everything is covered in that program so if you're looking for a comprehensive and clearly designed course to help you elevate your data analysis skills consider enrolling in the Excel school program using the link in the video description when you go there you're also going to get a fully unlocked one formula dashboard file as well I hope to see you in the Excel school program thanks I'll add a new worksheet and here we'll kind of construct most of the skeleton of that formula from scratch I'll select a cell and click on my Excel labs and I can type the formula here I like to keep the Excel Labs thing floating on the screen so that I can type the formulas better so what we will do is we'll start by saying let and hit enter and inside this let we are going to define various steps of the formula to do all the operations now if you go back and look at this you can see that even though this is a single report essentially it is broken into multiple columns we have got the picture of the person name of the person the revenue revenue bar chart units like that so let's figure out how to get each of this information from the raw data remember this data is at a row by row level whereas all I care about is individual salesperson level information so let's go back here and bring up our Excel lamps and first up let's construct the report data variable which will be we can start this with the shipments table itself so report data is right now shipments table but as you can see in the shipments data we don't have any cost information so we do need a way to bring the cost information and add it on top here so let's go back here and so we're going to start by saying hit stack hit stack is a new function in Excel that Stacks data one next to another in a horizontal fashion so I can combine two or three ranges together so I want to get all of my shipments data and then for each shipment I want to find out how much is the cost so the cost can be found with an xlookup function xlookup shipments product so for each products in the shipment stable I want to go and look up in the product table product name and then products table cost per box at this point if I close my H stack formula and comma and return just the data variable and when you hit enter you'll see that it will give you the main shipments table along with cost per box for each of these items that we shipped we don't want the cost per box we want to find out what is the total cost so let's go back here and update this formula and then say that xup should be multiplied with the shipments table number of units so when you update this you'll see that each row now has a total amount and the total cost information here so now that we have calculated that let's go ahead and add one more to bring up the picture of the employee and put it in the cells so here in the HED stack we we have one X lookup that gets me all the costs and multiplies that with the units then one more X lookup xlup shipments salesperson look them up in the people table salesperson column and then return the people table pick column now we can see that when you hit enter on this you're going to get for each row so for example here is Mal Weber their picture looked up from the picture column of the employee table and whenever the name repeats you'll get the same picture CA we have that picture in my sample data of the pictures I have got some duplicate so don't worry about it but essentially all the pictures are coming in dynamically so now that all the data is here let's go ahead and calculate the totals at salesperson level so we have created a variable that tells me the data the next variable is sales people you're just going to call this as SP and point it to people table salesperson column and let's just see what this looks like if I return SP I'll see that all the names of the people so now for each of them I want to get what is the total sales what is the total units what is the total costs Etc to do that first let's get some of the columns out of the data itself so I'm going to Define some variables revenue is my choose Columns of the data variable so that is the entire big table that we saw earlier and we want to pick the revenue column now I don't remember exactly which column it is so I'm just going to start by saying to and return the data itself so we can kind of make a note of what it looks like so you can see this is how the data looks like column one is the name two is Country Three is product four is the date five is my Revenue six is units and seven 8 is the costs so we looking for five six and eight columns really so let's go back here and then let's get the fifth column as my revenue and then units is choose calls of data sixth value cost is e8th column and then now that all of these are there we can actually create the profit variable profit is revenue Minus cost and profit PCT percentage is profit divided by revenue and if you want you can for example just kind of scan one of these things so for example let's just return profit and and you'll see how the profit is for each row we don't want to see this information at a row level we want to see this aggregated at salesperson level now so let's start calculating how these values look like at a salesperson level so now let's define second set of variables called Revenue by SP you might be tempted to use the new group by or pivot by functions unfortunately we are not able to do such complex reporting with those functions so that's why I end up writing slightly longer formulas and what we want to do is for each salesperson SP we want to calculate what their total to revenue is this is where the by function is really helpful it can scan through all the items in a list and apply same logic on all of them so by row of SP so for each salesperson what we want to do is apply a Lambda function the Lambda is going to take the a a is the current sales person and then apply that logic on them so the logic is really a simple function it just want to sum up all the revenue for that person so SU the revenue but we don't want to summ it up for everybody we want to sum it up only if the person is the current person that is a so Revenue times and here we want to multiply this with the shipments table person column salesperson column is equal to a so that's the logic and this kind of like a Boolean logic we apply this kind of things in the filter function also and then let's return Revenue by SP to see how that looks like so this is how much is the revenue for each of the people in our team in the order they appear in the people table uh it can be a little bit tricky to debug these things but uh you should be able to cross check this with the pivot table now we need we do need to do the same kind of thing but Calculate cost byp units byp Etc and we can see that this particular column will be referred multiple times so I'm going to create a variable for that um I'm going to call this as all SP and put that into there so this is one of the beautiful things with the let function it lets you kind of build up these variables and reuse them and then change this to all SP is equal to a so that my logic looks a little bit crisp here and we're going to copy this few more times and change the wordings so the second one becomes units by SP and for each of them internally the logic will change so units will be units column multiplied with that and for profit percentage we can't do this uh kind of a logic we'll have to do it at an aggregate level so this is going to be profit by divided by Revenue by SP and then we can kind of cross check whether we are still tracking well so for this I'm going to use a hit stack hit stack and then I want to get the SP all the salese and then see what their revenue looks like what their cost looks like what their units looks like so this is basically kind of like a pivot table that we created although a little bit too long winded um but that is the core of the logic that I am using to generate this one cell report now comes the fun part we want to make it more like a report rather than a dump of values so this is where some of the nicer things I had to think about out so I want to be able to show the picture of the person then their name then the revenue and the revenue I also want to represent with a bar graph initially I thought of using the conditional formatting data bar and just duplicate the number so I'll show you how that would look in first and then I'll tell you why I went with a different approach so for example when I'm hit stacking I can take one of these values and repeat it what that does is it will give you a duplicate so here I have got two values like this and while we are there let's also bring the picture so we have got SP and uh eore pick as a variable which is basically people table picture let's add that in the front as so we'll get the picture and the values and as you can see these pictures are really small so I wanted to make these rows a little bit big so that we can actually see the people so maybe 32 pixels or something like that and then let's just uh fix these columns and now these values I want to represent as a bar so I can select that and then go to home conditional formatting data bars and apply a data bar as the number is already here I don't want to see the number there so I'm going to select this again manage rules and just say show the bar only so now the bars come up when I initi made it like that I was not really happy with how thick These Bars were plus eventually when the final report came to be I want to make the first row a little bit bigger so that we can emphasize that person and you see what happens this bar becomes even chunkier and I didn't really like that look so I wanted to go with something a little bit different to get this kind of a thing so this is where Excel offers another way to draw bars in the cells it's called inell charting it's a very old technique and I haven't used it in many years but I found that for this particular scenario it works beautifully let me first demonstrate that to you in a Cell here so if you go to your cell and then type the pipe symbol let's just type it six times like that this is how it looks like if in a different cell I type it a little bit more that's how it looks like so if you kind of squint your eyes they do look like a bar chart but but you do have to squint really hard so this is where if you go to the font select these cells and change the font to play bill it's a built and font in Windows so you don't have to install it or anything you'll see that the font reduces the distance between the pipe symbols and lets you create a kind of like a mini version of a bar chart in the cell so this is the basic technique that I used now if you look at the number itself 749 492 we can take that number and normalize it so that the maximum value whatever is the highest number in this column so for example right now it is 850,000 that's going to become 100 pipe symbols and everything else is scaled down you might be thinking okay this is all good but how are you going to draw that many pipe symbols so this is where the rep function comes in we'll say rep within the double codes pipe symbol and then specify how many times I want to type so for example if I simp say type this 35 times so we're going to use this approach go back to the formula again look at the Excel apps where we can easily type these formulas and let's generate a bar so now I'm going to make one more variable I'm going to call this as Revenue bar and this bar is going to be a number of pipe symbols we can't directly generate this so I'm going to first make a variable called Revenue Max and calculate what is the maximum Revenue by SP value is revenue by SP is an array so max will tell you what is that maximum value which would be 850,000 then Revenue Norm which is a normalized value of the revenue is revenue by SP divided by Revenue by Max time 100 so this is how you can normalize a value to 100 and what this will do is for the most most highest value which is 850,000 this number and that number will be same so that will be basically 100 because 1 divided by 1 is 1 and everybody else is scaled down in relation to that person now we can generate the revenue bar which would be repped off pipe symbol and the number of times is revenue Norm it's going to generate a number of bar values for us let's see this here instead of Revenue by SP I'm going to return Revenue bar and and you'll get the bar chart here I'm going to first delete the conditional formatting rule from that and then select this column change this to label and if you want you can reduce the font size and once you make the column width Ade equate enough you can see the bars appearing nicely and these are basically font values so you can align them you can change the color so maybe let's just tone them down a bit and you'll get a nice little bar chart in the cell that is not too big or chunky irrespective of How High the row is so this works beautifully with what we are trying to go for the next bit of puzzle is if you look at this again so all of these things are explained to you now and then I want to figure out if the profit has met a kpi the kpi is you should make at least 55% profit if so you'll get a tick mark else you'll get an x mark So for this let's calculate the profit indicator so we will add one more and then say profit indicator and this is basically if profit percentage by SP is greater than 0.55 then one else zero and then let's add that to our pile of things profit indicator so you'll get zeros and ones and I can select this column apply a icon rule of that let's go ahead and manage this rule edit it first up I don't want to see the number I just want to see the icon so I'm going to say show icon only and then adjust this number rules to say that green tick when the value is greater than or equal to 1 and x mark when it is less than 1 greater than or equal to zero so it's going to basically print green tick or x mark and we don't have a third condition so I'm just going to take that out and when you apply you'll get these the icons might initially look a bit like 8 bit but once you fix the alignment and reduce the font size to 10 points or something maybe 11 points they're going to look nice and crisp and we can now see who has met the targets and who haven't met the targets we'll also need to apply some formatting on these things so I'll apply formatting here and for these columns you can apply number formatting directly so initially I applied a currency formatting like this but I wasn't really happy because I see that the numbers have too much of a precision and I wanted to see this in a thousands value like round it to, so 749k is what I want to see something like this so that they look a bit nice and easy on the eye to do this you have two options one is you can format them right here you can select this column go to format instead of currency change to custom and use the code dollar hashash and then an next comma do0 within double code SC so that's going to change the value like that but I was already doing all of that in the formula and I set myself a challenge that even that should happen through the formula so I'm going to do do that by calculating few more variables here uh we're going to call that as Revenue disp so that is the revenue value that is going to be finally displayed on the screen and this is basically we're going to use the text function and then the value that we want to use is revenue by SP apply the format code directly here so now wherever I'm using Revenue I can in the final report I can just use the revenue desp and you'll see that that's what it comes up here as directly the beauty of this is I can then apply the same logic for my cost disp so that that kind of consistency can be achieved everywhere let's do that for units and other things as well and when you're doing this for units it becomes no dollar symbol and for profit percentage it's just zero percentage there you go a bit tidier representation of the data already I was very happy with this report and I wanted to add h on the top wherein I can pick how to sort this report so right now it is not sorted at all we can apply sort order so this htag is my final report and I wanted to apply the Sorting of course then I went a little bit more and I calculated the 13 we Trend as well using this grid here all of that is still part of this I'll explain this logic of this in the formula here directly rather than redoing it but let me just show you how to apply the sort order for the Sorting I do need some variables so these are the variables which column I want to sort it low to high or high to low and how many people I want to show after sorting so three variables all of them go and sit into my settings worksheet here these are the three variables that you have selected on the screen cost is the current sorting option low to high and then I want to see the 10 people after sorting so we can use these in conjunction with some simple lookups to basically figure out which column we sorting on and all of that these are trivial calculations I mean if you have come this far and you able to keep up I assume you know how to do this so I'm going to show you how to apply that logic into that one formula now so let's come back here and let's uh create one more variable called report data and then use the htag for that so all my report data is now in the hstack formula and and then now I need to apply the Sorting logic before we apply we'll just return the report data so it's as is and now we don't want to report the data as it is we want to First apply a sorting on it so in order to sort we need to know what the sort criteria is so we'll say sort by column and point to the number here so this is C5 here that's the address so I'm going to unfortunately when you're editing this in the Excel apps you're not able to click on the cell because the moment I click it changes the formula writing for that cell so I'll need to make a named range or something or no remember the address so it's the settings worksheet C5 is the cell so let's go back here and then write that settings sort order that is ascending or descending sending would be minus one ascending would be one again that is settings worksheet C6 and then show would be settings C7 so that tells me how many values to show so now that those variables are part of our big let function let's apply the sort order in order to sort what we will need to do is we'll need to take the the report data and then sort it by one of these columns so sort by column number is this I'm going to call this as column number and then we'll calculate the column itself sort by column so the column would be based on the number so the number one is sales number two Revenue number two is units Etc So based on that we'll need to pick one of these variables so we can use choose function for that choose sort by column number and then if it is the first value then I want the revenue by SP we can't use the display values for sorting it has to be the raw values if it is two then it is units by SP if it is three then it is cost by SP and if it is four it is profit byp and then if it is five then it is profit percentage by you can see that you know it's a very straightforward idea we can use the sort by function sort by function report data it needs to be sorted by sort by column in the order order is defined by sort order so that's the final output that we are going to get I'm going to save this and then you'll see that it would have sorted on something I'm not really sure what it is sorting on so it's cost low to high so obviously cost is this 297 is the lowest cost and 399 is the highest so you can see this is the sort order I'm going to change this from cost to revenue and then set it to high to low so we should get bran BOS 850 808 807 789 all the way down to 674 Works beautifully now let's implement the the number of rows as well so once you sorted we can store that into a variable sorted undor report and then finally final output is final output take the sorted report and then show that many rows so show tells me how many rows to show so I just want to show take function just tells gives me the top n rows where n is defined by that something is wrong oh sorry I think I need to return the final output so there you go now we are seeing just the 10 rows of that final report if I change this from 10 to 20 you'll see that it will give you all the 20 values and if I change to all we only have 25 people that's why and it is completely Dynamic I can sort it I can flip it in any which way and all of that on top of this beautiful formula I have added some bells and visz through conditional formatting so that icons you have seen that they come through conditional formatting and for this grid here I have calculated the previous 13 week values and then applied a color scale on top of it and then fed these 13 values to a spark line in Excel you can insert a spark line from here and then point to this grid here whatever those values are that spark line will show so if there is no formula for example if I delete that formula as there is no data the spark line is blank but the moment that formula is there the spark line will also show up you might be thinking okay so how do you calculate these guys here so let me show that to you in the final formula here directly you'll notice that this formula is slightly different than the one that I explained but essentially the logic is all same so here what I did is I calculated few more things so first I calculated all the dates into a variable called dates then figured out what the start date is start date would be whatever is the maximum date minus 13 weeks so that will take me to the starting date of the report that I want to generate and then I calculate units by date using a make array function which has the same number of rows as how many sales people that we have so SP count is also a variable that counts how many salese are there and then 13 columns so essentially it'll make a 25 by3 grid and the grid uses this Lambda function to populate the values so for make array it takes row and column numbers as two variables and internally it's just a giant sum or some product formula here that that grid is always going to show units so I'm taking units multiplying that with the current sales person which is captured through that logic and all the dates that happened in the week of the context so and the end of this units by date you will get all the values all the 25 by 13 values and then eventually we bring that units by date into the report data hit stack and when the entire report is done I selected the first row and heightened it so that we can see that clearly and change it the font settings for this row by making it all nice blue color with bold and increased font size so do you like this one formula report I know that this is a very unique case and not many people would actually need this it's kind of like an Overkill if you ask me definitely Overkill but I thought you know this shows you an interesting use case of how we can combine various things that exist in Excel using one beautiful formula rather than dozens of different formulas and get the result of course it is a lot of work and not for everybody but if you are the kind of person who enjoys this thank you for watching this video up to this point feel free to grab the data and give it a go yourself let me know how it goes in the comments too

Channel: Chandoo

Views: 28,701

Keywords: chandoo, chandoo.org, Excel, spreadsheets, excel dashboard tutorial, how to make an excel dashboard, one formula excel dashboard, excel dashboard advanced, interactive dashboard in excel, dynamic dashboard in excel

Id: taL-a8qIHtk

Length: 34min 13sec (2053 seconds)

Published: Tue Jun 25 2024