Make an Amazing HR Dashboard in Excel - FREE Live Masterclass

hello everyone welcome to our excel live stream my name is chandu and i am super excited to have you all in this session before i jump into anything serious just tell me in the chat if you can hear me loud and clear and you know everything is good i could see that we already have more than 200 people on the stream quite a few of you are waiting for the last 10 15 minutes uh as you can see i'm excited too i got up early and uh yes [Music] so quran says yes you're audible thank you karam um it is it is super happy and you know really exciting to have you all in this live stream and i am i'm going to jump into the content uh in a minute but i just want to check in on with all of you how are you all doing i hope you're having a very good week and very good day so far and you know you're you're here to learn something have some fun interact with me and rest of the viewers and enjoy the session so let's just see where you are you're getting in from uh we got people from all over the world america's cpa from charlottesville virginia uh and joyce says hello from hitchtown texas philipp one of our regular says healthy hey that's good to hear uh and uh haima says no cake which simply means your awesome brother thank you thank you you are awesome as well and um uh kuram's shadow says from lahore pakistan you are my virtual guru thank you so much uh iwan says hi from netherlands hi everyone and oh wow uh miguel from chile uh hey miguel and uh canada so many people from india you know this is just like a a virtual gathering of everybody in the world just may learn um excel dashboards all right i'm gonna show you a sneak peek of what we are gonna build and then i'll come back and and kind of explain uh a little bit more about how the live stream works and then we'll jump into all the all the good things so let's just uh share my screen and i'm gonna hide this so yeah you see this this is the dashboard that um that we are gonna construct in this session um obviously something like this if you want to make in excel it's gonna take a little bit of time so this this particular live stream is built to be quite intense um but at the same time i don't want to kind of start from scratch like open a blank workbook and go from there because that would add another 40 minutes of extra content and stretch it out and so many of you might already be familiar with some of the fundamentals so what i have done is i have prepared a blank file that is kind of 20 done so it does have the data it has everything neatly set up so we can focus more on the charting and the visualization aspect of this dashboard so this dashboard is fully interactive and you could go and add more interactions here it's just that i was trying to think what is it that i can cover in about 90 minutes of time so this is where i got but in many real life situations you don't have to make a dashboard in 90 minutes or two hours or anything like that you would have more time so in such cases what you would do is you will actually build interactions and and you know messaging around what your audience truly needs rather than what would fit into a live stream so i'll do a quick demo here uh here i have got a slicer that lets me slice by any individual department so as the name suggests this is a human resources dashboard this is basically loosely modeled on a dashboard that i was building for a client here in new zealand and kind of i took the ideas and added some of my own and simplified the data set to come up with this so for example if i go go and click on alpha this is the first time i'm clicking on it on this file so the model needs to set up everything will update to show me what's happening with alpha likewise beta delta so some of the visuals will highlight the department that i picked while the others will update so for example this one here the salary versus tenure one here they will all update whereas these things here they just highlight and these kpi tiles here just show me at an overall organization level what is happening so that is the dashboard and this is the data for that like i said i have kind of cheated when i gave this blank file to you i gave you the blank file this is the blank file the link for this is in the video description below so expand the video download the blank file and then use that to follow along but if you don't want to follow you just want to stay and kind of watch me explain it and then later on go and do it for your own data that is also good so this blank file has the data it also has these assets these kind of tiles that you can use and customize some graphics that that can be you know rather than creating from scratch if they're already there we can just copy paste and use so that's the idea and then it also has a db layout neatly set up so that we don't waste any time uh fixing column widths or sizes and buttons and everything everything is already there so we just have to plug things into here but if you are feeling a little bit adventurous i'll put a blank sheet here called db full black go and create this there so that's what we're going to do um we will be building this dashboard hopefully by the end of the 90 minutes or two hours from now but if you don't get all the way here which is also the case with live streams because many times i explain and people might ask questions and you know we can get a detour and go somewhere else if i don't cover this whole full thing i'll try to wrap it up by coming back to this file and explaining the missing gaps at the end of the stream i'll also share this full workbook with you so you have both blank file and the completed file and you can use whatever you want for your learning purposes all right let's go back and see what's happening on the chat um so buddy forever what time what is the time right now in new zealand it's about uh 507 in the morning i got up around 4 20 uh made myself a coffee got my water uh took out the dog for her morning things and she's downstairs now she won't probably disturb us and yeah i am excited this this really brings me to the most favorite thing that i always do in my my streams which is tell me what you're drinking i'm having a coffee instant coffee it's a little too early for espresso for me but and this is what i'm having it's too hot actually and bellumi says it's 6 07 p.m here in nigeria i guess friday evening um yeah you could probably be having a much stronger drink than coffee uh youtube user is having iced coffee the one is having water uh pmd is having mountain dew and carol is having energy tea oh i wonder what that is uh is that some type of a special drink or is it just tea with something mixed in and uh philip is having kuawi koi coffee and becca is having the requisite coffee at daily must yes [Music] oh see mcbride is having chocolate coffee with mushroom extracts oh now that is something this is my go-to channel for atlantic data analytics thank you um and 7 pm here in germany so many uh i think brian says it's morning where i am so coffee oh wonder where you are brian are you also in new zealand or maybe a little early in australia and charles says every time i see you you different look um this is this actually brings us to the other thing uh we recently shifted our house so if you have been in any of my previous live streams you would notice that the room layout is slightly different i didn't have like cupboards in the back but now i do this is because we just moved to a house that is literally 200 meters away from the older house and we moved here about eight days ago so last friday we moved and this is saturday now yeah it doesn't feel like um eight days but it has been quite stressful just moving unpacking and on top of it the live stream means i need to have this room as tidy as possible so everything from camera towards this side is clean you can still see lots of boxes and useless piles of cables and everything down there so yeah that's probably why the room looks different all right i think we should get into the action i want to set a couple of ground rules normally i would have a presentation but i figured uh you know it's easier to just jump into things and and kind of take it from there so if you want to follow along you are more than welcome and in fact if you are somebody who is learning i highly encourage you to try to follow along um in in you know learn like that so download the sample workbook the link for that is in the in the video description below so check that one out and um and download that and then the final workbook the completed workbook i'll put it at the end of the live stream uh so that you have that as well so that's the thing if you have got any questions feel free to put them in the chat even if i am busy explaining something here um someone else might know the answer so they can give you an answer right away like oh for example i do something on a chart or i i write a formula there and you kind of miss that you can put that in the chat and somebody can say hey this is how that would work and if you are somebody who is a little bit more experienced with excel feel free to be a little generous and help others others out in the chat but any questions you are putting in the chat please start with q so that if i am scanning the chat i can quickly identify because we're getting hundreds of messages um and it's really hard for me to kind of identify questions uh without scrolling forever and so yeah put a queue at the start if you have a question but the question should be just related to the topic that we are discussing today which is how to make an excel dashboard while you are welcome to ask any questions i may not be able to answer more generic questions or unrelated questions for now so put a question if you have got a question there and then um from time to time i'll scan the chat box and try to address the questions but if you have got a question someone else can also guide you all right on that note let's just jump into the action uh welcome again officially to our excel dashboard session uh we'll go into excel i'll explain the blank workbook the data set and then we'll start building i will from time to time i'll refer to the full dashboard so that we can kind of see the progress this is because on the blank file we don't have any reference of what we are going for so it will be helpful like that um [Music] iwan says i cannot see the file do i miss something um you should be able to see the file i when i checked it is there it's in the video description um let someone know if the the file yeah the file is there i think uh and the full file is available at the end of the stream but the blank file is already uploaded there i'm gonna copy and paste the link here into the chat but it might be hard to click on the links in the chat simply because chat because yeah yeah so that's the link i i don't expect you to make a note of it or anything it's best to refer to the description because it's too long and you know yep okay so let's uh let's get into excel um i'm gonna hide my face that way you can just look at the thing and i'll show my face from time to time believe me i'm here so this is our um our completed dashboard as i explained we will be building everything in the blank workbook here so this is the blank file that i shared with you i'm gonna first step here save this as a blank stream this way i have got two copies one with the blank and the other with the version from the stream let's quickly understand the data the intent here is awesome chocolates has got a massive workforce and this is the employee data and the hr manager wants you to make a dashboard so that they can go and use this dashboard when they discuss workforce related issues or what's happening at the end of the month or with the executives of the company so it is your job to look at all of this data and prepare a dashboard normally because i i also work as a human resources analyst and i do a lot of dashboards in the hr space in my in my work life um what i can tell you is you would have a lot more data than this a lot more as in uh many more tables but in general uh you know i i what i wanted is i wanted to keep something a little bit more easier to handle for us so i kind of took the basic necessary data and put everything into one table but in general you may have more than one table you might have a separate table for how many leaves are taken a separate table for what what training programs employees have attended a separate table to keep track of recruitment and all of that uh here everything is kind of shrink into one table as much as possible just so we can kind of uh get going so this is a single table data uh set that we are talking about and here i have got these columns and then these two columns at the end these are the ones that i calculated in excel i could have done all of the calculations in a power query kind of thing again to keep things simple i did this here so let's just look at this data we have got employee id their gender fte stands for full-time equivalent so if somebody is one that means they are working full-time somebody is 0.53 that means they are working about half of the time and employee type so whether they are a permanent employee a fixed term employee that means they're only here for next 12 months or six months and other means they are some sort of a mixer type so it could be a casual employee or something the department in which they work the branch of the organization they work so each department has many branches how much salary we pay them what is their annual leave balance as of the time of data extraction when did they join when is their date of birth and then these things i calculated like i said tenure is basically this is simple formula today minus date of join divided by 365. so it will tell me how many days how many years an employee has been with us based on the tenure i have then kind of done a switch function here to figure out whether someone is a new joiny under one year under two years or more than two years with us so these are all the formulas that are there in the blank file you don't need to write them they're already there and the age calculation is similar to tenure uh today minus date of birth divided by 365. so that's the data the data is neatly structured into your table and the table name is mentioned as staff so staff table has my data and in the dashboard what we want is we want to show some key metrics how much is our head count what is the female percentage as a graphic how much is our fte and what is the full time percentage as a graphic how much is average salary as a number and then we also want to show here a graphic of what proportion of our staff are paid less than 100 more than hundred thousand dollars we call them as high earners and i want to see what proportion they are and then average annual leave balance as a number and then a breakdown of how many people have negative balances that means they don't have any leave they used up extra leave how many people have zero balances and how many have excess balance that means they're not taking leave so anybody having more than let's just say 25 days we can consider them as excess balance and i want to see that number so that is the left hand side and then on the right hand side we want to show some breakdowns and explanations of various numbers so this is going to be exciting the first thing that we want to do is go to insert and most of the calculations for this dashboard we will do through pivot tables this is my my recommendation if you are building lots of dashboard style work in excel to use pivot tables as much as possible to do most of your calculation work because that way you don't need to write formulas or you don't need to think about many complicated scenarios you just write pivot and that will do the calculation for you so we'll make a pivot table when you're making just make sure that you're adding this data to the data model this option what it will let you do is it will let you calculate more complicated things than possible with the simple table by using the dax measure so we will be using a little bit of power pivot during this this next 35 next 90 minutes to do our calculations again click ok and that will basically set you up into a pivot table thing and it will give you this familiar pivot table stuff i assume people who are attending the stream you have created a pivot table sometime in your other parts of life but if not you will find what's going to happen in the next 15 minutes more confusing than normal this is probably not going to be the right live stream for you if you have not never made a pivot table but if you have already made pivot tables you will find many things here quite interesting and useful so we will make our pivot tables here this worksheet is where all our calculations will live so i'm going to first name this as calc and yeah i'm gonna insert a couple of blank rows on the left so that we not uh normally i like to keep step spacing on the left hand as well so that everything looks easy and if we need to expand it on either direction things don't get little clumsy and then we'll kind of set a color here and then simply say calculations worksheet this is just to remind us that this is where the calculations are happening you know just in case you you're browsing through this workbook and you don't know where the calculations are you will know that that's where they are all right um so that's that and then we will make this pivot table here i'm just going to quickly check this 42 messages since i last saw the chat so let me just um get into a different layout and then [Music] so sharon says please consider office 2019 instead of 365. um this is a good suggestion the only problem is i don't have office 365 2019 i'm using 365 but i will highlight if i'm using a specific feature of excel that is not available in your other versions of excel um i feel like at this point at least two visuals will will be using office 365 features uh but you may be able to work around that using some other alternate techniques i will not get into the alternate techniques in this session because that just drags it out to six hour live stream and i don't have that kind of time nor you will be able to sit through that so we will just try to use 365 for some of the simpler things but if i'm using anything i will tell that you know this won't work in your version um philip says employees burn born in 2019 interesting company i don't think there is anyone born in 2019 here now the latest we have is 1999. i think you're referring to the date of join that's that's what it is um and ammo says 10 news just its hash name is there an error in the formula um i don't think there is should be an error i'm using a very simple excel formula there today minus date of join so um it could be that you maybe you're using a different version of excel like i mean i'm using english excel here if you're using italian or german or something else the formulas are different but they should automatically update if not use the relevant calculation um and use it all right so there are some other questions but i'll probably have to make a make a move there and then you know come back and talk to you uh this is one more common question that um [Music] that people say about the live stream which is okay the quality is not high enough for me i can't see things clearly unfortunately the maximum i can stream at is 720p whereas normally 1080p is your hd it doesn't really matter what you could do is you can go to youtube where you're watching this video click on the little gear icon or whatever at the bottom and then change the quality to the highest available that should be fairly crisp and clean that is how i have been live streaming all this time so um if you are watching it in a lower quality then what happens is it looks grainy but set it to the highest available and then it looks better so yeah the the problem is there are some very very old people in the data and this is one of the data cleansing problems initially i was hoping to address that as part of the live stream but i figured the you know some of these things we can ignore it's just that the company has a policy that if an employee is another type of an employee then we don't capture their date of birth we just use a default value in the system and that means when when i'm calculating the age of employees some people have ridiculously high ages because of the default the default is 1919 by the way so i hope that explains those things and adds a little bit of humor to what is already happening let's uh let's get into the pivot table and then let's build it out i'll keep my face on for now because i think that way you can see some of my explanation and reaction in real time but if it gets in the way of what i'm building here i'll i'll hide my face so this is the table and then we want to start off by calculating the head count first which is very simple right click on this and then say add a measure we will be using powerpivot to build all the calculations as much as possible so this is why we had to add this to data model so right click add a measure and and then that will open up the measure box from here you can generate any dax formulas to write the measures so we'll create our very first measure which is headcount and this is nothing but count rows of staff table so we take the staff table and we ask excel how many rows are there well technically power pivot and count rows will work these formulas are not like excel formulas so don't be confused with what you're writing here as excel formulas these are dax or power pivot formulas um and we use that to build calculations inside pivot tables and you can set a number formatting here in 2000 separator and click ok and that will come and sit nicely here as headcount and if i put that here i'll see that i have got 3890 people the next thing that we want is we want to know what our ft is so add another measure total fte and some of the fte column in the staff table so that's the thing and then this will be a number with one decimal point again using thousand separator and okay so that will also be there total fte comes up here like that so we have 3890 people but our total fte is 3762. what it simply means is some people work part time so our work capacity is really 3762 whereas we do have 3890 bumps on the seats the so that's uh that's the real difference so sometimes when you when you look at people you might think oh we have got 5000 employees but everybody is ha working half half the time so essentially you have the capacity of 2500 people so that's the total fte what else do we need we need average salary so we can calculate that right click add measure average salary and average of salary in the staff table and this will be a currency with one decimal point and click ok and we also need average leave balance so uh where range we'll call this as alb annual leave balance and average of leave balance these are all very very simple measures so i'm not really explaining too much about them but if you have got any questions feel free to put them in the chat someone will be able to guide you and otherwise i'll cut back to that but essentially we are doing a little bit of dax work early in the morning well early for me i don't know what time it is for you [Music] but it's not too hard i think this is uh something really fun and very useful and relevant so and then we can add these things there and you can see seventy six thousand thirteen point one uh these things are going like this my dashboard layout is going vertically so it's easy if i like to kind of think keep things in the same orientation as my output worksheets that way everything lines up nicely so i'll take this values here and then move it to my row labels area so click on this values thing in the pivot table put it in rows so the orientation changes and i'll get things going down here so our four numbers are calculated let's put these into the dashboard first and then start adding the remaining things later so we have got headcount number the number is just typed in so what i want to do is i want to select this style this is my tile and then click on the formula bar say equal to so this number should be equal to whatever the head count is so point to the head count cell and then enter so it'll show the number when you do this what excel will do is it will link up the box and the cell there so that whatever that cell has that's what this box will show unfortunately it's also gonna resize the font and all of that so the number is too small but this is a really simple matter of now selecting this and increasing the font so i'm going to make it 28.24 points and then also change the font to sego ui semi bold so that kind of shines so there is our head count we need to do the same for ft so ft is equal to and then point to this we'll do the same for average and leave balance as well so these numbers come up you again you can select all of these three set the font to 24 points and change the font to the bold so that they all look nice and crisp so yeah that's uh that bit we do need to know what our female percentage is and what our full-time percentages likewise we also need to know what percentage of our staff are paid more than 100 000 so those percentages we need to calculate um this is where uh we can write a little bit more dax i'm just gonna read through some of the chat messages to see if i missed out anything interesting or important um i missed half of this dang it oh is i guess you missed the half of the stream not the not the stuff that i'm doing and amber is posting some of these measures in the chat which is really awesome thank you amber for that and [Music] rohin asks how do you get staff to add measure so this is where it is super important when you insert the pivot table you need to click on the add data to the data model button if you don't do that you will not be able to add measures or do such calculations so this is important this is why i highlighted that bit earlier so if you've already gotten a pivot table and you didn't check that delete that come back and add a new pivot and add this to data model only then you will be able to and princess says i hope this is recorded so i can rewatch it this is being recorded on youtube and i'll try to keep it online for some time but occasionally what happens is if i am not happy with the the way or i am i feel like oh this explanation could do a little bit more i take it off and then i try to schedule it as a standalone video for some other time so if you if you have got time you're watching now please stay back and watch the whole thing that way you you will not miss out the live interaction part and you could also ask questions but if you've got things to do feel free to check back later and milan says can't we use format painter we can use format painter as well but it's only three things so it doesn't really matter we can select everything in format but if it's a long lot of tasks i need to do i would normally use format painter all right i see um this kind of a question quite often which is our table is not named i'll show you how this is happening if i go just insert pivot table without checking this and you click ok you will not be able to right click anywhere because there is no table name so this is what you most of you might be seeing you don't want this i'm going to delete this what you want is you want to right click sorry insert pivot table and then you want to add this to data model only when you do this you will have table name and only then you can add a measure okay so keep the questions going my coffee is getting cold so i'm gonna take a coffee sip and then we'll we'll get into doing the female percentage the female percentage is a very interesting problem and if you if you understand how that is being calculated then the other percentages are also kind of follow the same pattern so essentially what we are thinking about here is we already have the headcount we know 3890 people are our staff and we want to know what is the female head count so that means we want to take the head count measure that is already calculated and then we want to apply a filter on top of it such that we are only filtering down to female staff and then calculating the female head count so i'll calculate that let's just say that comes up to 1700 then we have got both numbers 1700 and 3890 then we can calculate that as a percentage so to do these kind of things dax offers a a versatile and a very special function called calculate what it does is it takes any existing calculation in this case headcount and then it applies a an extra filter on top of it so that it changes the value for that filtering again all of this might seem confusing if you have never done any power pivot or any anything little more than basic pivot tables but i'm hoping you know you learn now and then you can go back and learn some of the missing things come back and and you will be able to understand especially those of you but if you have already done some work you will kind of right away understand what is going on so to get the female head count right click on the staff add a measure and we will just say female will do it in two steps head count first and then head count percentage head count and this is equal to calculate head count so we want to calculate the head count and and here i can say gender of the staff table gender is equal to female so this is the filter criteria that we are specifying that i want to i want to calculate headcount by inserting this extra filtering so this is gonna just add that and then whatever that number is will come up so if i see this here female head count 2 409 female employees in this company so then i can calculate that as a percentage so this measure will be female percentage is equal to and we just want to take 2 4 0 9 divide it with 3 8 9 0 so you can do a direct division or you can use the divide function numerator is a female head count in the denominator is headcount and this will be a number of percentage format one decimal places like that so this is basically how the division will happen you you don't have to use divide function at a gross level it doesn't matter but normally it's a good idea to use divide function because it will avoid any zero by zero kind of errors and then that will come up uh if i just check that you can see that 61 percentage of our staff are female we can use the same idea to calculate any kinds of things so for example i can calculate what is the what proportion of our staff or permanent employees or sorry full-time employees that means their fte is equal to one so we can do that add a measure full time percentage this time we are going to do it in one go which is i want to calculate total fte i think i want to just calculate head count where ft of the staff is equal to one so this bit here will tell you how many staff are full-time that means their fte is equal to one they're working hundred percent of the time and then i want to just divide this with the actual headcount the full head count so in this case we are just using the divide operator instead of the divide function like i said normally i just use the divide function i don't do divisions like this but it doesn't really matter at an arithmetic level they both do the same thing and then again you can apply a percentage and then set that up there and then add this so you can see 92 percent of your staff full time you can kind of double check these things it's a very good idea especially if you're learning dax by for example you can set the filter to one and you can see that we have got 3590 people here my face is in the way now excuse me while i figure out this there you go so you can see that you know we have got 3590 people that are having fte as one so if you take that and divide with three eight nine zero you would get 92 percent which is what we were getting there so that that's pretty much how those things are calculated you can use the same idea to calculate uh for example how many people are paid more than 100 000 or how many people have negative balances or excess annual leave balance etc i'm just gonna do only the negative indexes but for others you can refer to the full file it's just that the process is same so we will be kind of doing the same measure again and again calculate this and that so there is no extra value in doing those things so to keep things moving i'll just show two more examples which is how many people have negative leave balances and how many people have more than 25 days of leave balance so add measure and then nega 2 alb we just want to count how many people are there like this we don't want to do a percentage here because in general very very few people have negative balances and very few people have excess balances so percentages will look very comical like 0.1 percent kind of thing so we'll just look by headcount so calculate uh headcount and then leave balance less than zero so less than zero will basically look at all the balances where there is negativity and then we'll just do a number formatting on that so 24 people have negative balances and then this is the last one um excess alb calculate head count leave balance greater than 25 so 25 is the benchmark there anyone with more than five weeks of leave accumulated we think they are saving up leave rather than taking time off and recharging and we just want to keep an eye on how many people are there and if this number gets too high that means people are either or work they're not taking time off which which is not a good thing to do so we would like to know that 51 people uh in a big scheme of 3890 these numbers are really small but if these numbers start to get high then you would want to worry about them so that's why those numbers are being reported anyhow that's a little bit of dax uh i hope uh i didn't freak you out or i didn't scare you by kind of pushed putting too much dax early in the morning but um yeah that's that let's just see [Music] if there is any questions i think vishaka is asking a follow-up question around manually inserting the values into the dashboard there is nothing manual about this other than the fact that we are writing formulas everything is automated so these numbers are not manually entered here this cell is linked through a formula to my calculation worksheet so whatever calculation says that's what this will be if my data changes and i refresh my head count goes up to 4200 this will automatically change um 3m box says in mac there is no option to add the add to date model option in pivot oh well unfortunately i don't have mac to kind of really know what is available or what is not available but i do believe mac excel is kind of couple of generations behind in some senses i don't know if power pivot is available there yet i know power query is available but power pivot uh i'm not even sure so if someone else is using mac feel free to help help the help them out um i feel like i'm going to miss a lot of questions because there's quite a few questions here um just because this name says lego bricks i'm going to read this one out i'm a big fan and consider you as my excel gurus i've missed first 15 minutes from malaysia hey thank you so much [Music] some of you might already know that i am i i really love lego so r1 says how many dax formulas have we added i think we have added about eight or nine but in the full file this is the full file here by the way i do have all of these about ten different formulas so uh we are we we do need to use dax if i don't use dax then what happens is you will end up writing a bunch of countifs and sumifs and all sorts of formulas that can get a bit clunky and they are not interactive so if i want to be able to calculate that number by selecting a department or something it's not going to happen whereas with dax it's it's a very simple thing so that's why we are using dax this is also the reason why i told early on that if you're making a dashboard you should think about learning a little bit more about pivot tables and dax which will simplify your life greatly it's a good thing to learn because once you know dax you can use this in excel you can use it in power bi so it's like one one stone two birds crazy for crickets is that to measure option available in excel 2016. it is available in 2016 2013 as well but if you're using like home and student version or something i don't think that is available but it should be there in other versions if you are not seeing that just search on google or whatever search engine you use your excel version and where is the add to date model and it should probably give you some guidance all right philip has a question dashboards can be built upon dax or dynamic array functions or formulas there are no refresh needed for di formula to make dashboard real time pivot tables for daf um it's kind of good but the reason why i don't bother with dynamic array formulas for dashboards as much anymore is the reality is your real world data doesn't change like every second you don't need formulas to update in real time most of the data updates are controllable like for example this monthly hr dashboard it it really gives away the clue right it is monthly so we don't need to think about updating the dashboard or worrying about data changes more than once a month and even when that update is happening in all likelihood the update would have happened through a power query connection so that means the data is not even here it has to come from somewhere through power query and we are already refreshing power query so there is no extra cost for me to refresh the pivot whereas if i am building something that is a little bit more real time then i would think about dynamic array formulas but dashboards in my opinion are not really changing like in instant live scenarios so yeah i don't know i mean i i feel like that would be a little over engineering we are going to use dynamic array formulas today anyway because there is one chart we can't easily reproduce with pivot table so it's easier to just do it with das but that's a very good question uh philip and i think this is the kind of thing that you should be thinking like what tools do i use to solve the job at the hand so you could have a hammer you could have a spanner you could have a screwdriver you could have a wrench or whatever all of these are different things so sometimes you may have to use one two or combine them to do what you need to do all right enough number crunching let's go and make some visuals and make this thing look pretty so we have done the main calculations and we have put that i'll show you how to put the female percentage as a percentage bar chart in the graph i'm not going to bother with the full time or or these other things because it's exactly same thing so i'll just leave that to your imagination or homework so here on the side somewhere here for kpi tiles and we're going to build some visuals uh female percentage this is equal to and then we'll just point to the pivot so 61.9 comes up and then rest is one minus that so both percentages are here i'm just going to apply percentage formatting on both cells and then i'll select these two insert a i want to have a stacked bar chart but when i try to do this you can see that somehow excel kind of keeps them as two separate series so female has one rest as one what i want is female first and then rest should go at the end adding up to 100 percent so while keeping this right click change chart type and then in excel 365 and 2019 you should have like a second chart type automatically figured out by excel but if you don't see this you'll need to tweak the way data is set up to do that so i'm going to point to that and that will give you this select this axis press ctrl 1 to open formats and set the minimum to 0 maximum to 1 so that it's always from 0 to 100 and then i'm gonna click on the plus button turn off access turn off title turn on grid lines so that this is just a big box next up select the any one of these and go to the formats and click on this little guy here and and then the gap width if i just set it to zero percent then what happens is the chart will take up the entire space in the thing so it will kind of fill up that whole box so now if i resize it it'll kind of resize in scale so we want this to be like that if i go too small what happens is this padding here there is a little bit of white space on the top and around that padding kind of takes preference and the chart becomes a simple line so the next thing is we want to select this entire box this is called plot area so you have got your chart area which is outside box and the inside boxes are that box is called plot area could use a little bit of drawing here so this outside box is ca chat area the inside box is pa so usually chart area and plot area have a little bit of padding this creates issues if you want to precisely size things so select the plot area and then resize it so that it kind of matches the chart area now if you resize your chart it won't have that kind of white spacing issues so we'll make it like this next up we'll select the [Music] the red portion corresponding to female and i am going to apply because our dashboard has got this dark backgrounds uh i want to use a color that will contrast well and work there a good test would be you can control x the chart here ctrl v and then put it here and then test it out if you think this works you can leave it there but if you feel like oh that doesn't really um work very well with my other colors there what you could then do is you can select these and you know start playing with it so for example the one that i used is white color and go to shadow and then add a a shadow effect and then select the rest and this one needs to have either no fill or i'm gonna [Music] yeah let's just go with that for now and then select this box and set no outline no fill for that okay something like that you know that will kind of give a sense that this is the portion that they are filling up and then the labeling here i think in the completed dashboard i used a different style yeah i used an outline as well you can see that the shadow kind of comes through unfortunately it's harder to format these things when you are also explaining them you will have to try out a few different things and do you might think maybe it could use a little data label as well and that is up to you i find that many people don't realize that when you point a value on the chart it will show a tool tip with the value there so you don't need to over explain things especially when you are building dashboards to executive audience who who who knows the business context and kind of are familiar with everything and smart enough to understand how stuff works so in that case you can just tell them that hey point here and it'll tell you what the value is and that that might be better option than cluttering things up with lots of labels but it's up to you whatever you want you can do but that's that okay so what are we doing with time oh we got about one hour um um cool i'm gonna go back to our thing and we will need to do a similar one here for the fte percentage and a similar one here for less than 100 000 or more than 100 000 people i leave that as your homework definitely do that if you have got troubles use the completed dashboard here and kind of connect the dots and figure it out all right let's go in and make this chart just to give you a recap of what we are building here we are building this visual here where if i pick a department i want to be able to highlight that but also show the head count breakdown by departments and then the female percentage on the top as a label so around 60 percent is our normal female head count but anywhere under 60 you can see different colored icons there i thought this is a fun chart to make it will kind of give you a chance to learn few more techniques and use them in other situations as well so that's that's really the intention there um so we'll go to the calculations worksheet here for a moment i'm just gonna maybe i will read through some questions and then i'll turn off my face uh yes he says uh sorry if this is a stupid question but why are there so many different languages excel formulas bags m etc yeah i mean i don't agonize about this every day but i do think that a little bit of unification of the languages within a single tool like excel or power bi could actually help lot more people adopt and use these programs in a more elegant and efficient manner for example imagine you you you you have to speak different styles of english like formal kings english or queen's english with your wife and in the informal millennial style english with your kids and and something else with another person in the house then you'll just go mad but that's really what we are up to here essentially all the dax m and excel formulas they all use english but the way you write formulas and the way you think about them is different particularly mental is the power query because it's case sensitive so if you write under small letters something it won't work whereas excel and powerpivot are happy to just take any case and work it out so yeah i don't know i mean this is for another day another discussion uh but yeah if you ever run into a microsoft product manager on the road probably you should hassle them about this is anyone out there who has seen a dax writing video on youtube please suggest and share me the link you like my videos you're watching this video you might as well see my dax video there is a detailed power pivot video on the channel with about an hour long there is also a live stream that i did on powerpivot and dax so you can see my past live streams and it will be there so do check them out that will go into more detail explanation of how dax is and how it works and how to build stuff with tags uh and you know take it slow this this is not supposed to be a dax video it's a dashboard video so that's why we are using dax as a tool but the end goal is to make a dashboard and i see that you have posted the same question several times i'm going to put you in timeout now but please don't spam the chat field because it gets a little clumsy for me to manage um all right i think that's good i may have missed many many questions up there but i hope you know you're all enjoying it i'm having fun my coffee is nearly out which means i can i don't have to take coffee breaks in the middle i can just get into the action so we are going to go back and build the department distribution graph so you can just um this is our main pivot table for summaries we'll insert one more pivot table generally just leave some space and go somewhere else and you can directly insert a pivot table from the data model so insert pivot table click on this from data model in other versions of excel these buttons will be somewhere else but essentially the functionality has been there in in various versions of excel since 2013 so nothing new with this and when you have this all your calculations will be available to you so you don't have to build them again you just do once and they're there so we will add a department and then by department i want to see headcount and then the female percentage so both values will come up here i'll right click on this sort the headcount by largest to smallest so my departments are in the descending order of headcount and then i'll also turn off any grand totals so we can just look at the basic values alone so this is my department pivot and somewhere here for the chart and what we need is we need a chart where i want to show all the departments as a column chart and then on the labels i want to show what percentage is their female percentage so that's really what we are going for uh all the data is here but if i make a chart from this directly it's not going to work because it will one is in thousands the other is in the percentage so we can't really see the second value so this is not going to cut it we do need to make a regular excel chart that is feeding off the pivot table but using that so here um we'll say department head count female percentage and then equal to and point to this and then just drag it down drag it sideways so we'll get a replica of the pivot table you can do it like this the problem with this approach is if and when awesome chocolates brings in a new department after a reorganization or whatever this pivot table is automatically gonna grow whereas this formula will stop here so this is something a little bit manual but i like i said this is a monthly dashboard so it only changes once a month and once a month if you think there is a new department it's highly unlikely that you know you keep adding departments every month but even if you add it's not too much of a hassle for you to select these formulas and drag them down so you can kind of build a checklist at the start of the dashboard and then go through those checks as part of your monthly maintenance work so that's an option for you but otherwise you can also think of smarter formulas i'm not bothering with that because they can get a little bit clumsy so yeah that's uh that we also got our first super sticker of the day smitha subash thank you subhita for the super sticker and i hope you are enjoying the stream so and i'm gonna then select these guys and insert a a column chart so we'll get this nice little thing we do need to do a little bit more formatting here for example what we want is if i pick a department i want that department to be highlighted in a different color so that's really what we are going for um this is um this is where i need an ability to pick a department as well so i'm gonna move this chart here for a while and then um i'm just trying to think what we okay we can go all the way top and for dpt selection so i'll select set up a section on the top here it's kind of hard to keep track of where everything is on this spreadsheet but for now just imagine it is somewhere there it doesn't really matter the position of it and here i will insert a pivot table from data model and in this pivot table we'll just put department and right click on the department and add it as a slicer so we'll get both of these a slicer and a listing of departments quickly turn off the totals and now if i click on a specific department like for example if i click on alpha you'll see that this cell here will tell me what department is picked let me fill up yellow color there so we can kind of see so this cell p6 is always going to tell you what department you have picked if you pick a single department of course if you multi select it will kind of show everything but if you select a single one it will give you that so this is really what we are going for this kind of a pivot is called a slicer harvester that means it will look at what slicer is saying and then it'll harvest that value i can select the p6 cell and then name this as cell dot dept selected department is short so now i know internally what department is being selected delta for example and then i can start using that as part of my chart so in the chart setup area here highlight and then if this department name is equal to cell dot department then i want their head count else i want nothing n a so it will be either n a or for the department you pick the value once this is there we can control c these values select the chart control v so that it will add that extra series to the chart and delta has two bars everything else has one bar or one column at this point i'll select these things ctrl 1 to format go here and set the overlap to 100 that means both should go and sit on one top of another so 100 so that this one is yellow color and that is red color next up we'll select these red ones and we will kind of fade them out a little bit like that so that this one will shine through and if you want you can select this guy here and add a little bit of shadow effect in fact i think what i have done here is yeah i used green and yellow color so i'm going to go with the same so that it's a little bit consistent like that and then the gap width also let's just set it to fifty percent and control x this oh we need we need to [Music] ah really this has been happening a couple of times since yesterday i hope it hasn't really crashed and we are able to recover the file uh let's just see we only lost one minutes worth of work so that's not bad cancel and then this one also we need all right let's just save this what else have you opened need this okay we didn't really lose anything um but i think excel seems to crash since couple of days now whenever i try to undo while i'm working with charts it could be a bug in the latest update they have done so this is my thing and if i go and bring up my slicer now [Music] i'll be mindful not to cut and undo stuff so the first time you open a file the model needs to set up so it takes a while but you can see that now it kind of highlights whatever department you pick so this is working fine uh the only thing that we need to do is now add a label on the top that tells me what the female head count is so we're gonna work on the label for the label you can again use many techniques um the technique that i used here is kind of using shapes so um we'll try that so [Music] it looks a little bit clumsy but it does work very well for for up to five values if you have got very long things then it's not gonna work so full half blank this is what really we are thinking about and we want to have a symbol for full what is half what is blank so a full would be you can select a cell press windows and the dot key together that opens up the emoji keypad and go to the symbols and go to the geometric symbols option and from here you can select some symbols that you think will work so for example the symbol that i used is uh this one for full and and here the symbol that i used is again the shortcut is windows and dot key um oh no that's one the the one with the two circles and then the last one is um an empty circle so this is pretty much how that looks like and then we can generate a label the label will be depending on this percentage here oh these persons look a little bit clown oh i we put the wrong measure here full time percentage not let's put female percentage yeah so depending on this percentage i want to either have a full half or blank so the way this should work is i'll do a sketch here so we want to print up to five circles each were 20 of the value and let's just say if it is 65 um now let's go with 79 so 79 has three full 20 so we want to three like this and then one half because it's not all the way up to 80 it's 79 so kind of like that and then that will be blank so that's really what we are thinking the first three are here and then this guy is here and then this guy is there so this is really what we are trying to generate you can use any other logic you want if you feel like this is too complicated chandu we should use something else feel free to do that i'll show you the method that i used which is using the wrapped formula take the full text and integer portion of this divided by 0.2 so 0.2 stands for 20 so this divided by 0.2 will give you that um and that will basically have oh my references are not locked so it'll it'll look like this we'll actually break this label into full blank so you can see there and then the for the half it needs to be the half will only be one of them it can't be more than that so if and then mod of what will be reminder mod function will tell you what is the reminder of two numbers so the number is here in the remainder divisor is 0.2 so if the mod is greater than 0.1 then i want this else i want blank keep forgetting to lock the references oh nothing is fixing interesting what is going on this is the one that i should be locking yeah so we get the the ones where it needs to be and then the blank will be just how many ever remaining things they should all be blank so for example i can do length of this plus length of this so this is three so that means i need two more blanks so i can say wrapped blank five minus like that again i hope this makes sense but if you think this is a little too confusing feel free to try some other technique it doesn't have to be this you can just even put that value as the label i wanted to try something fancy so my full label becomes concatenation of these three things so that that's the label finally everything is there let's go and add these labels so we'll select the main one and then add data labels so the labels will come on the top so while we are here uh it might be a good idea to just set the number formatting correctly so that the thousand separator will show up then select these labels ctrl 1 to open the format and check the value from sales option again this is available in 2013 and above so it's not new so value from cells and add this range as well so that that will come up on the top and then the separator will be comma so there is a little bit of comma between these two i don't want a comma so i want a new line and that's how that looks uh it looks good what happened is my sort order is out of whack this is because when i removed the full time percentage and put female [Music] pivot tables forgot which way to sort so i'm going to resort this so that we will get this visual finally ctrl c ctrl v on the dashboard and then we will put it here we are gonna adjust its size everything and then make sure that it kind of blends in with the container there so select this go to format shape fill no fill we also don't need the grid lines or eve or the vertical axis the labels are not visible so i'm going to select the labels and make them like that the department names also make them like that and select this box no outline so that it blends in like that so this is uh that visual it looks really fancy uh the correct selected department is highlighted once we move the slicer over here you know you can start interacting from here all right let's just see what is happening on the chat i hope we haven't lost everybody at this point but uh oh wow i'm thoroughly lost here but uh let's just start with this ravnith car error occurring where connecting tiles to number what error is happening uh there shouldn't be any errors really but yeah if you care to elaborate or maybe i am um elias says you have taken full-time person instead of female person for the department well i figured that out ibrah says honestly don't understand uh sorry about that that's not the intention to confuse or complicate i hope it's not the case with others do let me know if it's too complicated what we could do is we don't have to complete this dashboard we can just take it slow and learn and you know i i'll give you the full file anyway so you can figure out but i can slow it down but i feel the this is not the session for you if you are like a complete beginner or if you never done anything else in the excel this is for for those of you who are already doing stuff and want to know how to make dashboards so hope that's helpful um sirisha says it is showing missing range size suppose this is in relation with the tiles what i suggest is maybe you're not selecting the right things or you're selecting some big range instead of a single cell or you have done something else somewhere um i'll show you from scratch this is not really complicated stuff what we are doing with the tiles so if you go to shapes and draw a simple rounded rectangle you'll get this select the rectangle click on the formula bar say equal to go to the calculations page locate a number that you want to link up so for example 3890 so just select that cell and enter the number will come through here that's pretty much it there is nothing else i have never seen the missing ranges or any other error but if you are seeing that then probably i don't know i don't know i don't even know how to replicate that but let's just say if i pick this oh no it still works with multiple ranges also so i don't know exactly how that error is coming up but that's that is why are you conducting these free classes a little crazy i guess i don't know i just like talking to people and um yeah i find that this is a very good thing for both of us you get to learn something i get to have the interaction and enjoy the uh and enjoy the fun of doing things in life and yeah i don't know i've been doing it for many many years now so never really thought why i guess i just like talking and sharing um karthik says designing color and decoration to have good field dashboard how can i improve this design skill this is a very very good question and and something that most people who are doing data analysis work or customer facing roles where you have to prepare stuff but also present it to audience or share it with wider public you need to start thinking early on in your career so i'll tell you how i learned because i think that would have more wider implications i don't really know the only right way to do it like i know what i have done which is a lot of trial and error so i would make every time i would i get asked to make a report or graph or anything i'll try to do it differently just as a intellectual challenge for me a different look in different field uh and then that way what happens is you you get a lot of real world feedback let's say you are making the same column chart every week but i try to do a little bit of tweaking around the edges every week uh until i find the right combination of things that people are enjoying and that gives me oh this works this combination of doing these three things will will be good or this combination is not so good people are not enjoying it or they're finding it harder to read and understand so that's something that i even i today i do like um some of you may have attended my previous live streams every time the output or the ideas that are present are different this is not not accidental it is a deliberate choice i make i want to try to do something different test it out and see what works what doesn't work so that's number one the second technique that i used is i read a couple of books on designing things so i'll put a link for some of my book recommendations below because it was many years ago that i read these things so i think the book is called non-designers design book or something like that but i'll put a recommendation when i finish when i finish the stream in the video description check that one out and then i'll put some modern ones as well so that's that and then the third thing is i read or i watch a lot of other visualizations that people are doing and take inspiration from that or sometimes shamelessly copy them so for a classic example is i don't do this now anymore but in earlier days i would read a lot of new york times they have got a lot of visualizations and graphical stories and if i like something particularly then i'll try to recreate that in excel or power bi just as a challenge and learn different things so it's not something that you wake up one day and have you will have to build that intuition but whatever you do you will need to ask the basic questions first which is if you are creating this for someone else then you need to think from their perspective and start thinking what colors what messages what ideas are easy for them to digest and do their work better so don't think like what is it fun for me to make rather what is it that they are going to find helpful and start from that direction then it a lot of it becomes easier i hope that that was helpful if not that's fine as well um all right there are some other questions but we'll come back to that we will add a few more things to the dashboard so this is my dashboard and then we want to put a title here as well the titles can be anything so for example one title is whatever department you pick i can put a message around that like alpha department has 40 percent of our staff another thing that you could also try is a title that starts off as a question so question could be what does the distribution of employees by department look like and then the answer is the chart so these are all variations i'll show you one technique here and then the other technique after some time so we'll go here to our chart section and then we'll make a title i think i'm gonna move this down and then generate the title here so the title would be whatever department is highlighted i want to show what percentage of staff come from that department so it should be like 44 of our staff are from alpha department that's the message that i want to say so we need to calculate the percent and this is this divided by our overall headcount we got that number somewhere here so we'll just do that so this comes up as a percentage and then the message would be this percent of our people come from selected department so ampersand of our staff are from cell dot tapt so this should read 44 of our staff are from selected department unfortunately it would read like 0.44113 like that this is because even though it is formatted to look like 44 percent the internal value is that so we'll need to use the text formula here comma and then within double quotes 0 that will basically take that value apply that formatting turn that into text and you will get that message there so once this messaging is done you can go back to the chart and insert a we don't need to insert anything if i go to assets i've got a caption here readily available i'm going to copy this caption ctrl c paste it here and then put that on to the chart adjust its size and then right align it you need to tweak the sizing and everything but and then in the title bar so select this message click on the formula bar say equal to go to calc and then point to that same thing as earlier and then that will give you that and then this color will be this and font would be semi-bold and then yeah so it will say 44 percent of our staff are from alpha and as you interact and change the department so if i go here and um where is my slicer go to beta my title will also change oh it's always saying 44 percent i think our calculation is wrong oh yeah it's doing from here isn't it it needs to do that number all right we'll need to do a lookup here xlookup you can use vlookup as well um cell department in the list of departments and then the value the title keeps changing yeah you can put anything else any other stories or you can even type out manually as well whatever you do don't use the default titles like staff distribution or head count by department kind of titles try to use something a little bit more informative or provocative or insightful so that that space is used intelligently rather than just kind of like hear some information kind of thing so that's that um let's see what else is there and then so this is basically our head count over a period of time i don't think there is a lot of mystery around this chart so i'm not going to worry about this for now i'll show you how to do this this is a very interesting one salary distribution of a selected department and all departments so if i go to beta i'll see the distribution of those people like that so how do i make this so this might be very helpful we'll try to do this and maybe this and the rest i'll come back here and explain how those are done so we go to our blank file so whatever department i pick i want to be able to show their salary distributions so this is where i'm using the dynamic array formulas but if you don't want to use dynamic array formulas you can do something else it's just that there they are an easier option at this point for me so i will use that this will not work with excel 2019 16 13 whatever but you can still use it with 365 or excel online so you can always practice this it's just some of the versions that you're using at home you may not be able to work on that so selected department i'll just print the name here again this is equal to sell dept and for that department i want to know all their salaries salaries i also want to know their 10 years so this is where the filter function comes in very handy you can just say filter salary column where department is beta so we can go here filter staff table salary column staff department is equal to and then it will give you all the salaries like this it's a spill range so all the 800 or 900 people there will come up here we'll do another filter staff tenure department is equal to this so salaries comes here salary comes here 10 years come here and then the salary range who is okay so the salary range starts from this cell this is yes 34 in my spreadsheet okay and then this is tenure it starts from t34 so what we want is we want to make a chart that kind of reads all the data that begins from s34 and then kind of plots the distribution like that so we'll first make full salary distribution chart which is you select this entire salary column insert and from insert ribbon insert statistics chart click on the histogram this is available since 2016 so you should be able to make it in older versions as well and it will give you how the distribution looks like for our staff all the staff the default histogram will bucket the data by some default values so i'm going to select this axis go to format access options and instead of automatic we'll set the bin width to be ten thousand dollars and we will have an overflow and underflow as well overflow is anything 100 000 and underflow is 40 000. why does it look funny maybe i need to refer back to my original thing just to see what bucketing i used there oh fifty thousand two hundred thousand okay 50 yeah that looks alright so this is the distribution of the salaries at an overall level so we can cut this and for now we will put it in the dashboard we'll have to resize and massage this this is the overall thing if i want a similar distribution but for a selected department only so this is where the the filtered range comes in let's just see if there is any questions i think there's a couple of questions that are coming through ravnic says please address it urgently none of my tiles are connecting to numbers i have no idea i hope you're using excel uh and not something else uh and this is something that i can't really i can't see what what is happening on your screen nor you can share anything but you can ignore that you don't have to put them on the tiles just delete the tile uh and then put them on the cells there there is cells behind just put them there it doesn't really matter this is just a cosmetic thing so um yeah and then you can you can go and search up online oh my michael says what software do you use to zoom and draw i'm using a software called zoom it just search online and you'll find it might be helpful for presentations and things like that um but i think that was the question [Music] it could be that so this is probably if i go to my pivot table i don't have get pivot generate get pivot data option so i'm gonna for fun test this out generate get pivot data is enabled and then i go here and say equal to point oh yeah this is what is happening we cracked it guys so all right this is what you need to do go to pivot table analyze go to options and uncheck generate get pivot data i never have this on so it never occurred to me uh uncheck that and then now you're able to refer to the cells by themselves rather than when you point on a cell in pivot table you get this ugly generate get pivot data and that's why you get that error says this is particularly useful please let us rewatch the recording as well it will be available on youtube for some time for sure um and after that i don't really know normally at least for a week or two it will be there so feel free to bookmark or like this video and then go and and see it later um okay let's uh move on and then let's create the distribution graph as well which is i want that this data to be plotted as a distribution so this is basically a two-step approach it's a little complicated but very very handy for many things that you may want to do with the dynamic areas so what you need to do is go to formulas define a name and here this name would be you can give it a name like what's happening define name and then this name would be sel dot salaries okay you don't have to use sel you can use any other notation it doesn't really matter and then s34 is my starting cell for salaries and then just put a hash symbol next to it s34 hash so this is what you're really creating give it a memorable name and then point to the very first cell so s34 is this cell here and then the hash symbol there okay the pound symbol and then when you click ok what you have created is a list of all the salaries that are spilled from s34 cell that's pretty much it now you can go to insert statistics histogram right click select data and the data for this would be you can select the series edit it salaries and then here where it says p24 or whatever maybe the value just delete everything and then type sell dot salaries okay so whatever named range you have given that name range need to be there one bug with excel is if you just type the named range it won't work it expects some sort of a sheet reference so leave the calc exclamation or dashboard exclamation there only then it will accept if you just type sell salaries it's not gonna accept that and then when you click ok you'll get a distribution but this distribution is dynamic so whatever department you filtered that's what it will show we're gonna quickly change the access bin width to 10 000 overflow to 100 000 and underflow to 50 000. and that will come up you can control x this go here paste it and kind of position it nicely and i'm gonna take away my chart title and unfortunately some of the formatting for these new charts especially histogram box plot and waterfall we can't customize things so excel is showing this in a slanted fashion i'm not a huge fan of these kind of labels i want the labels to be horizontal but we can't adjust it no matter what i do so we'll have to live with that and then kind of take out the outlines and everything um [Music] grid lines also we can go select this and uh set the gap to be 25 so there's a little bit of white spacing and i need to oh yeah this is a green color for the salaries so select this format [Music] and then select this axis no line for that and then these text also we can kind of reduce their size in fact this axis is not needed and then we will add data labels select the labels and make them a little bit strong so this is my selected department salary distribution and then we will put the overall departments here all the departments so for this we don't need anything this is just for like a glance information kind of thing and no fill no outline 25 cap and then the bars need to be a little bit stronger so that's how they kind of look similar but i think if we go into specific departments you will start to see some oddities so for example if i go here where is my slicer i'm gonna control x the slicer ctrl v on the dashboard so that we have the slicer here and let's just see what happens now so if i go to alpha the distribution changes some of these you can see they're kind of like this but my overall distribution stays like that so it gives you a contrast of how things are and it works beautifully so that's that and you can put a title on the top that basically tells you what that that particular tiles are doing that's again you can go to assets and there is a caption here asset that you can copy and paste and customize them hey we got a super chat from mark as well hello from sunny minnesota usa thank you mark uh thanks for for that and uh yeah um so next we can fill up some of these things i'll show you some interesting ones the the ones that are obvious to make i'll go back here and explain for example here if you see [Music] this line graph is fairly easy to make once you have the necessary pivot setup these distributions are similar to this one here so it's not that exciting this butterfly graph might be interesting so we could try and make that but the salary versus tenure here is also very interesting so i'm gonna show you how to make this next and then we'll quickly understand how to customize the slicer uh and then rest of it i'll come back here and add that explanations within this file so we go to the calc and we have already both values this spill range is sell salaries so this one we will set up formulas define name cell dot tenures and then t34 hash will basically give you everything um from there and when you click ok that will give you another named range so now we have got salaries and 10 years at this point you can go to insert scatter plot and then add a blank scatter plot right click select data and add the data the data would be salary versus tenure and then on x axis we want to show tenure on y axis we want to show salary or whichever way i think x is salary y is tenure so on x axis we'll just point to a worksheet like calc and then type sell dot salaries for y again we point here cell dot tenure so that we will get both of these so we get nice little distribution of things we don't have anybody paid under 40 000 so just we'll set this axis from minimum start from 40 000 so that it looks like this in fact we could go all the way up to 45 and that might be a better option this is good uh i'll take out this guy and then there's a lot of density so i want to be able to tell these dots apart so select the dots go to format marker and the marker color will be green and then the marker outline will be dark green in the marker transparency you will just set it to 50 so that wherever there is more dots you can kind of see a little bit of clutter like that ctrl x this paste it here move it around and resize this and then we can go do some of the formatting which is take out all the uh things we don't need the grid lines or maybe we do but they need to be a little bit more subtle so i'm gonna select these grid lines and like that and then do the same for these things as well and this axis no outline all right so this the distribution of salaries it does look a little bit interesting than that and it's dynamic as well so if i pick a different department i'll see their distribution so i can see what is happening some of the formatting is a bit off so i'm gonna quickly adjust that select this axis go to number from general change this to currency and uh just set this to zero decimal points it's going all the way up to 190 000 we don't have anybody paid that high so i'm gonna go up to 120 000 so that it kind of spaces out these things a little bit probably tenure also we can stop at four start from zero and so you can start to see some nice little patterns as you play with this and this kind of thing is very helpful if you are trying to establish some ideas on you know who is getting paid more and you could do this by salary with 10 years salary with [Music] age salary with the relevant experience salary by creating all of those things to kind of really understand and you can get even more uh about kind of get a little bit more into these things for example uh the original work that i did for one of our clients here in new zealand is i wrote a blog post about this many years ago now salary so this is from 2017 so five-year-old post but essentially you know creating some jitter plots like this so that we can explain how things are changing by departments and you know kind of create this i originally made this an r for the client but later i thought you know it'd be fun to make it in excel so um i'll put a link to this in the video description later but otherwise you can always go search on internet jitter plot chandu and then that will kind of give you that um some extra ideas on because there's a lot of data points you don't want to kind of overlay one on top of another you may want to start thinking about how do i space these things out or how do i add a bit of random noise to it so that it looks a little bit more easy to spot things next up this slicer we'll need to fix this so select the slicer we have got seven departments so i'm gonna change this to four columns so select the slicer go to slicer columns four and that'll give you that right click on the slicer next and then go to slicer settings uncheck the display header option so that is also gone and then resize the slicer like that the problem with slicers is whatever you do they still have this kind of a white colored background and a box around it everything around in our dashboard is nice little rounded edges so it looks like apps on your phone but this one is looking a little bit sore so we need to adjust that before i do that i just want to acknowledge uh cynthia hey cynthia thank you so much for the super chat thank you for all the great helpful content it is very much appreciated thank you so much thanks for tuning in and watching the content and learning uh and philip says it always takes longer to build a dashboard than you estimate even if you have built it before oh well yes that's that's true um i try to um kind of reduce the scope of what i was going to cover because initially i had this grand vision in my mind and then i thought there is no way i could explain all of this in two hours so i kept kind of reducing the scope and i thought i had it but you know obviously there's lots of questions people will ask and there is a lot of things that that go when you're doing the stream so with the explanation i mean technically it's not possible to make something like this in in just an hour if you're starting from scratch so let's get that out uh it might look good from a marketing perspective that to say hey come and make this in an hour but uh to be honest it's not going to be like that it will take a while but i hope the ideas are good and you're able to take them because no one is working with this random data anyway most of us will be dealing with problems that are kind of similar to parts of what i covered so whatever it is that you're finding helpful that is always good in my book um pmd says that all jetta chart is awesome i adopted it to show performance of different securities for each portfolio manager so very versatile yeah thank you so much i'm glad i'm glad you found that interesting and useful [Music] all right so as i was talking about these slicers the slicers are good for interaction but they kind of look odd especially if you are going for some really polished look like this so this is where you can select the slicer go to slicer and you can create a duplicate style and then kind of customize it because our highlighting is yellow color the first thing that you may want to do is set up the style that is closer to yellow so this orange color so this one and then duplicate that so duplicate it in fact i have already done that and created the custom style for you so if i apply that that's how it looks but by default that is how it's looking so we'll work from here default default style right sorry right click here duplicate that and it will create a screen where you can customize the slicer so first up we'll go to whole slicer format fill and then the fill color needs to be same as your background color so that's the one and then border needs to be none and okay at this stage your custom slicer would look like this it kind of oh the background color is actually darker what color is that oh it's the one all right so i'm not able to select my slicer modify this and then format this color and while you're at it you can also adjust the fonts and everything i'm not going to bother with that and then the main things that you want to adjust are hole slicer and then selected item with data unselected item with data so selected item is the one that gets highlighted unselected item is anything that is not highlighted so those are the two main things but you may want to also adjust the hover things later um if you want to customize that effect so those are the things we're going to go with selected item with data format the fill color looks good uh we don't need any border around that and then i want to make the font bold so that it stands out and then unselected item with data format that and then this fill color needs to match background no no border and then the text needs to be like that so we'll get something like this it's kind of blended in now i can pick a different department otherwise it looks like that so that's the basic trick i you can go and tweak this even more but you know really um it's up to you and your audience like what the what you're really trying to go for and what the whole purpose of this is there is still these bits missing and this is narrative anyway so there's nothing there and then we need to add some extra graphs there so as we are already at the two hour mark i don't want to stretch this any further um i also have got things to do in the morning i need to go down and start making breakfast as well as my daughter has a net ball game today so we all are driving there so as we are running a little late on the time i'm gonna jump here to the completed file and cheat my way for the rest of the things this graph is a line chart it comes from this pivot table here so it just shows within 2022 by month how much is the head count and the head count is kind of show value as running total so it shows the running total of the headcount adds up and then i made a caption as well to show how many people we hired in the last three months and then made a line graph from that and then put that there messaging there and then added a label to the last point this one average annual leave balance by department is very very simple similar to what we have done here i think somewhere up there well it's here so each department what is their leave balance how many people have access how many have negative and then i extracted the data out here and made a bar graph from this and another bar graph from this just made sure that negative has a negative number in front of it so negative sign and positive has just those values uh and once you have negative and positive you can put that into the the bar chart and then that'll kind of give you a butterfly kind of a chart so that's really what i have done with that and then again added a title that says 54 people have access alb while 24 have negative balances there um and kind of printed the same numbers here as well 24 negative 13 people zero balance 51 negative x's so that that numbers show up so yeah that is uh how you can create this dashboard i will put a link to this completed workbook in the stream video description so download it the stream will be available for at least next couple of weeks i feel happy with the way the topic has been covered but i also feel that i have not properly explained some of these things so if time permits i might actually take out the stream and record the proper video on this dashboard but i'm not really sure when i would do that simply because between the house move and uh i i started a new consulting project for uh one of the government agencies here in wellington uh so things have been a little bit hectic and obviously making the main videos on the channel and everything so i don't want to over promise and under deliver but yeah the stream will be there so enjoy the content enjoy the files and if you want to kind of learn a little bit more like how these kind of dashboards are made what is the thinking behind it and what can we do with data with various types of examples do consider the excel school program i do cover many of these techniques in that course and it has more than 10 different dashboards different data sets different scenarios different types of techniques and ideas both for old excel and new excel so a wide spectrum of coverage there so check out the link for that is in the workbook as well in the video description too and sign up for the course if you are looking for one place to learn everything i want to say thanks to ruben for a super sticker as well thank you ruben for that and let's just see what questions you have yes rose sarvo sales says thanks for sharing you're awesome thank you so much uh servo you are also awesome as well uh and uh alexander says great teacher thank you alexander and [Music] sathya says data level concept you used is really interesting however for alpha and gamma we see same insights when we look at the label in fact how do we actually represent this in any other form so the label idea is just one of the many ideas that you can try um you could kind of get more creative around these things uh you don't have to use this like i said you could use some other things so for example what i would think about is maybe we can prepare some other symbols that will kind of give a better idea the two key concepts here are symbol as well as the power of the wrapped formula but because the labels on the screen have very limited real estate we don't want to print a whole whole bunch of 20 lines of things there that can make it clutter so you'll have to balance it with what we need to show what says what what we could do for example let's just say we have got a internal target to have at least 60 percent female in all our departments so in that case we don't need to present everything we can just highlight these three guys so we could have target mat and then here if um this less than 0.6 within double quotes windows dot key to open the emoji keypad and then if the target is not met i could for example show an emoji like that else i can print empty spaces so it will have the emoji here and use that as a thing i'll just show you that in a duplicates chart ctrl c ctrl v and here select these labels ctrl 1 select from range so instead of this range i'll point to that range and so we will get a different picture here these guys have all met the target whereas these three have not met so we'll get the emoji when it shows up in the cell it shows up in black and white but in the charts it will always show up in colorful thing so that's another cool thing that you could try there's other techniques as well i cover them in many different videos here and there uh so feel free to take ideas from from the wider world um deanna says thank you mike selker you're making learning fun thank you so much and uh yeah cheers uh all right um dave says this is really good you've covered an awful lot of information very interesting thank you thank you dave uh there is always a little bit of like at the back of my mind when i walk into the live stream like you know i haven't done enough or not but it looks like we have covered a good ground and there is a lot of powerful ideas at play here so yeah i'm happy i'm happy i got up early for this michael duffy uh sends in a super sticker as well thank you so much michael uh it is lovely to have you in the stream uh rich says question i'm on mac so missing some features do you recommend switching to excel online um it's up to you um i feel like mac excel has been saddled for a while now they have not adequately updated or kept up with the rest of the excel so but because the proportion of people who use mac over time is also slowly changing and increasing yeah it could change in future for example this is something that i would be very curious to know i'm gonna save this file and then i'll see if we can actually see this exact thing working and interacting in the online version i believe it's not possible because we're using data model uh but i could be wrong because they they always keep adding things there so a share i need to upload it to one drive i'm gonna do a quick upload to onedrive to just test it out but yeah that would be good okay we will just share it for that and paste it here so this should just open up the file in oh come on in the excel online i mean it does look this is a browser here so i'll hide your question and i'll hide my face as well so this is my chrome for those of you who are seeing and it kind of looks alright but i don't know if this interactions work so this workbook contains external data connections or bi features that are not supported so yeah some of the interactions don't work but the the look is there and i feel like you know these things would work because the filter and all are available online here so yeah you you would not be able to use it even with excel online and that's the thing pmd says thanks always with a super sticker thank you pmd for that and donald says sends a super chat as well thank you donald ian sends in a super sticker and so does james thank you thank you both of you uh it's it's thanks for taking time to share your knowledge i've used a lot of your examples in my spreadsheet projects thank you thank you so much david uh and philip says fantastic and awesome as always you are the chairman of the board oh well thank you awesome chocolates is a small company i am the chairman i am the janitor and everything there aka says this is my first session i'm enjoying it thank you akash uh james says thanks in the fantastic training as always you're all rich of excel is off the charts no pun intended oh well uh thank you and jeff says mac excel is an inferior product to put it kindly especially where power query and powerpivot are concerned you're better off running boot camp so there you go i he didn't mince the words whereas i have not used mac excel or even mac in in a long time so i couldn't really put my thoughts correctly in there s3m back says you're an mvp please request microsoft to make all the features in windows be available on mac it's not like that mac and excel windows have different internal architectures and stuff like that and they'll everything takes time and effort and and money and resources for microsoft or anybody else so why would microsoft invest their time in making mac excel better if they don't have enough users buying and paying for it i'm not talking on behalf of microsoft i'm just saying in general most of these decisions are not really made based on outsiders asking it's based on a lot of internal things as well so we don't really know what's going on but yeah you could make some feedback requests to microsoft especially if you're a mac excel user share the feedback you know in excel here if you go to help you have got a feedback button so click on that put the feedback they read that feedback and they'll hopefully respond to you ravi rich has a question as an interme alternative for mac users how about setting up virtual machine like aws and remoting i think that's what some people do they use virtual machine or parallels or something like that again i don't use mac so i'm not very much familiar with the actual specifics um salim says i see in the top of your excel sheet few buttons added can you show next time how to add them using macro you don't need to use macros to add this this is called quick access toolbar so any feature that you regularly use let's say you use format painter all the time you can right click and then add to quick access toolbar when you do that that will show up there so this is very helpful because you can access it any other as well when wherever you are you will always be able to access it so this is how i add the common things that i do to my quick access toolbar philip says any plans for updates to dashboards for excel by jordan and poornachandra the gorilla so this is the only printed official book that i have published and even that jordan kind of really carried the whole book and i only wrote a couple of chapters in the end but yeah i don't have any plans to write books because writing a book is is a very very time consuming exercise uh and yeah i mean it it is a i'm very glad and i'm very proud that i wrote that book but at the same time i'm also [Music] like you know is that the good use of my time kind of thing so yeah probably not i'll create videos i think this is more fun and interesting and challenging for me whereas writing a book means you're committing something on the paper and things are always changing so by the time someone buys and reads it most of the knowledge is out of date so all right so that's uh that i hope you all uh have thoroughly enjoyed the live stream i want to end this with a couple of big announcements i know you know it's already more than two hours so i don't want to take more of your time but just one quick update which is i am go i am going or i'm coming to london depending on where you live in this november so i will be visiting london and then afterwards bulgaria for a couple of online uh sorry live in-person events so i'm doing a two-day um physical in-person power bi workshop in london so if you live in london and you want to learn power bi from me um in a classroom setup we will be building for via reports dashboards understanding the whole thing from beginning to end in two days uh that that event is now on the tickets are on sale so if you're interested feel free to put a comment below not on the live chat but in the video comment below or um you know email me you know if you know how to get in touch with me just email me or visit my website or just search for um chendue london power bi class and you should probably be able to find that page so yeah so if you live somewhere around london or in other parts of europe from where you could easily reach feel free to check that one out and purchase a ticket and come and see me in person and if you live in bulgaria come and see me there later on i'm presenting at the bulgaria excel days so this is my real first international trip after covid in 2019-20 so i am excited i'm also slightly nervous but yay uh so yeah that's all for now i hope you all enjoyed this and i'll see you all again in another video i do the live stream at the end of the month every month so at the end of the september again we will do a live stream and we'll pick up another topic next month it will probably be around power bi so see you all then thank you so much have a rest of the fun weekend and bye we'll put that there paula says chen this excel is such an amazing resource thank you paula

Channel: Chandoo

Views: 190,154

Keywords: excel dashboard, excel dashboard design, interactive dashboard, interactive excel dashboard, excel dashboard tutorial, dynamic dashboard, dashboard excel, how to build interactive excel dashboard, interactive dashboard in excel, excel dashboards and reports, build excel dashboard, create excel dashboard, office 365, pivot tables, pivot table, hr dashboard, employee dashboard, human resources dashboard, hr dashboard excel, interactive excel hr dashboard

Id: ui657YnwLV8

Length: 138min 53sec (8333 seconds)

Published: Fri Aug 26 2022