Proforma Invoice in Excel for Healthcare Simplifies Your Billing Process

Proforma invoice in excel for Healthcare

okay just make sure this is working okay so if you're in the audience right now I know I started a little bit early um if you're in the audience uh mind just giving like a uh a thumbs up or something because I'm trying out a different uh stream platform um so I've never used this one before so if you're able to hear me okay and stuff like that uh might just like throw in like a thumbs up or something like that in the chat just so I could see it just so I could uh make sure I am uh doing everything okay I'm gonna make sure I get everything else working all right okay okay so no comments so far so let me let me get this figured out here so we're all good okay oh here now they're coming okay cool now okay it's a little little delayed Okay cool so we're good so like I said this is my first time doing any kind of like a live Workshop or anything like that um I have one planned for Saturday I planned that about I'd say like two weeks ago which is one I am to be honest a little bit more uh used to the I've been working I've making my own budget dashboard for a while uh but I wanted to test this out I've never done a YouTube live never done a workshop so I'm like you know what I got a little bit of time now I'm GNA put together a nice little Excel dashboard using some healthcare analytics and the whole point of this is the project itself it's not that complicated however it's really up to interpretation so the way I put together is just how I want to put together if there's things in here that you want to do different even like a color or a type of chart that's all fine I kind of won't go through this and kind of talk about how I would process this data how I would put together how would how I would design the dashboard and then answer any questions that come in because I know we have this up on YouTube and with this uh streaming platform I'm using streamyard I'm using uh I get all the comments right here I'm not sure they seem to come a little bunched up but any questions or anything like that I'll see it on one screen and on my other screen as you see right here is everything I'll be working on so I'll be able to look at both monitors with no issue um but of course like I said if you want to access this data set and the materials that are with it it's in the description of the video it's a GitHub link so once you once you click on it there should be option to download you download that and it should be this document right here if there's any issues with that you could definitely let me know I'm G take a little bit of time to kind of talk over this data set and kind of like discuss what we're doing with it and what the end result should look like so with this data set it's basically looking at an emergency room now there's many different types of departments there's general practice Orthopedics and so forth this is all based of the Year 2022 and 2023 we're not going to work with all this data I try to make this also a little bit realistic where if you're doing a project you're most likely not going to analyze every single thing in it you're gon have there's more of a specific reason to what you're doing so the whole point of this is we're only be focusing on Orthopedics now I'm go a little more in depth onto what we're going to do with it but in here we have the date that the patient checked in their unique ID their gender their age the uh what it says SAT score what that means patient satisfaction score so scale 110 one being not good 10 being excellent their last name their race admin flag now what that means is if it's a true that means it's a little bit more of an emergency false no problem wait time that's how long they waited to actually uh be checked in and the department referral is depending on if there's a specific department they were uh referred to um so so a little bit of everything here we're not going to be using most of this data though okay so the whole point of everything we have here and kind of almost get like a uh should I say a little um teaser with some of these uh different sheets down here is what we're focused on is we are trying to only focus on the year 2023 in Orthopedics and we want to do in there several uh insights we want to pull out of it we want to know how many patience we want to know the average customer score we want to know the average weight time we want to see if there's a correlation between the weight time and the time of the day and we want to focus maybe even the week day there's many different insights we want to pull together because we almost put we almost want to assume that I mean I don't about you but sometimes when people ask for these requests sometimes they don't really know they have an idea what they're looking for but at the same time they don't so I think the whole point of this is to like okay we want to provide um how do I find this uh I found it on um realworld data I forget the exact I could I could link it um in a second but I did make some changes to it I um I changeed up the dates because they were kind of based in like it was like 2019 and 2020 so I updated them to be a little a little bit more current I took out some columns that weren't needed uh but I could find that link at the very end if you mind me again I could go uh on the I could look back at my history and see exactly where I found it so I give you look The Source in case you want to uh put on your project yeah I found it from this website so I can definitely do that no problem um but I found it was a real world data I forget the exact website name but it was one of them but I like said then I'll make sure I get that to you um so yeah like I said most of the things we put together especially for me on the job is I'm given a request but usually there's not too much information behind it so what I want to try to do is like okay I want to see what is the most important how and how do I highlight that is it with a table is it with a chart is it with KP kpis like what is the whole purpose of it so before we get started is there any uh like I said this is my first time doing a workshop and I could move a little QuickTime so I'm going to try my best to take it nice and easy answer any questions you have on side is beautiful thing with these two monitors as I can look here and there you know so if there's any questions come up I'll be able to look and see um and also what's good about this being YouTube live is that you're able to look back in case you come in late or you have to go somewhere for 10 minutes or something like that you could always look back and jump back and forth um so if there's any if there's no questions we could definitely hop into it and so like I said we have this whole entire data set I'm not do anything fancy with it I just want to filter for what I need and like I said what we need is anything in in the year 2023 and that was a department referral with Orthopedics that's it so I'm going to do is I'm just going to create a uh new sheet I just go right here new sheet now I'm going to call this uh Orthopedics just because that's what we're be working with I want to make sure that I like to keep the whole data on one sheet I don't want to mess with that at all I want to treat that like almost like treat this like if you're uh if you're big with SQL if you're pulling from a table right you're not going to mess with table you're just trying to pull from it it's basically we're doing except we're just doing a couple filters to get exactly what we want so we have Orthopedics here uh like I said I want to create a filter so a quick keyboard shortcut with that is control shift L and the whole entire top row becomes filters I only want 2023 so let me drop down there I don't know why but only when I do this date filter on this sheet it gets really slow there we go I don't know why every other thing is just fine okay get rid of that don't know why it's so slow Okay cool so we're here right now with 2023 so anything in here is only the year 2023 you want to make sure it is with that because in this day set there's 2022 and 2023 we only want 2023 so now we go over to column J which is the department referral and we want to make sure because as you see right here if I click column J we see there's a count of about 4,800 uh people that checked in so about 4,800 but like we only want Orthopedics that number should go significantly down so get rid of all this and we only click on Orthopedics click okay how we got now yep 532 so like I said you should be having in a day colge be only the year 2023 and it should only be Orthopedics for the uh referral once we get what we need we're just going to copy all this and paste it into a new sheet we're not do anything fancy with it we're just going to take what we have put it in a new sheet and then we could take all these filters off and put it the back the way it was so this you click into any cell contrl a to select your data contrl C back over to Orthopedics and contrl +v to pcee into A1 and when you do this it gets this kind of format right where it doesn't really get fit into cells properly all you got to do is just click on the entire sheet here double click and we're all good um so that that's a quick little shortcut with that and then I like to make sure um it is so if you go to the GitHub link let me see if I could uh let me pull over a screen here so you get to this link right here this is exactly the link that went if you click there's a couple options you download raw file or view raw whatever that is if you do one of those two you'll be able to um to download zoom in a bit oh was a little tough yeah Grace I'll zoom in when when I get back no problem um but yeah to get this data set because I uploaded it as a file you just got to click the download button here or click View to get the uh file to download that is how you access it look at that look at using two screens this is awesome zoom in it's a little tough for you guys to see with this because it's G this might get a little hard later on but let me see is one is this a little bit better for anyone this a little bit better see okay I'm going to assume I'm you know what here let me go here let me make let me make everything a little bit larger I'll just do that so cool better awesome cool no problem yeah CU I know it's tough because like on my end this might get a little tough because when I have to do the dashboard I like to zoom out a little bit when I do my dashboard so I could it's a little bit different of a design than looking at a sheet but it's weird like on M I feel like it's so huge and I look over here to the right right which is the the feed and I'm like oh my gosh it's actually way different so like I said I appreciate you mentioning that so I want to go back to my data and if I want to just get rid of the filter I just go to data and just clear filter to put it back the way it was um I just like to make sure that the place where I got my data from is exactly the way it should be um it's not filtered in any way just in case I have to go back and get what I want so that's all good there and also another little thing uh I am not the biggest fan of like auto save cuz sometimes it saves my work when I don't want it to but I like to definitely hit that save button every few minutes or anytime I do a big uh let's say a certain lookup or some kind of visualization I always save my work with Excel so um we're all good here so there's a couple things we have to do here there's a couple things I wanted to extract I want to pull it from this date right here so I want a few things I want to know when I look at this date I want the time because you see right here it's set in the 24 hours I want to be you know 10:00 a.m. 1 p.m not just at 22 hours 15 hours I want it set like that and I also want to know the weekday that it was on and you know what I want to pull the month as well but I want the month to be the text I don't want to be one two three four I want to be January February March and then the weekday I want know was it a Monday Tuesday Wednesday and then the time I want that set as a 12h hour time not a 24h hour so we're gon to add in a few columns here and I'm just going to call this one month call this one the day of the week and we'll call this one now I'm GNA put it as time frame because I'm GNA explain to it when I H get to that part because there's gonna be something we're gonna be doing with this time because at because when you look at this you'll see some them be like oh like it could be 125 or 115 or 145 it gets really hard to track that so what we're going to do eventually is put everything into more of a time frame so first things first we're going to do the month and we see this currently set up as uh as time so I'm going to do here is do equals text you see right here that text it converts a value to a text in a specific number format we'll open that up and for Value I'm going to click into a two and how do I want this and I want this as a month so all I'm going to do for this one is open up my quotes and just do I'm do four M's one two three four close it up we're going to take a look when I click enter and we're all good so I just want to show again that's with when I do four Ms now if I do let's say three you're going to see a little bit of a difference here it abbreviates it right it just leaves that as Just J an now I I want the full month because when I do my uh slicer later I want to be the full month so that's how we set up for the month and doing the day to week it's going to be much very similar as well but I want to make sure I apply this to the rest of the uh cells swamped and done double click right here apply said rest now when you're doing this right you want to make sure you since you have everything you need here you don't want this leave this as a formula you want to turn this into values you don't want to leave the way it is because if you mess with the cell or mess or anything else it could ruin what's going on in column B so you have to pce that values um before I do it because since I'm a I am a big uh keyboard shortcut person what is if anyone because I know it's not as uh many people like would be in like the Discord webinars or whatnot but does anyone know the keyboard shortcut to uh pasting something as a value not just not just pasting but pasting a cell or column as a value do they all know what that keyboard shortcut is three two one okay should move on so in order to do this I want to paste out a value so each of these cells becomes basically a text or value I don't want to be set up as a formula so I'm going to do is just copy it contrl C I'm going to do controll alt V and it pulls up my pce pce special uh table click V again because I want to paste out the value click enter and we're all done so in order to do that really quick to take anything that's in a formula and turn it into a text or or a value it's Control Alt VV that's how that's done and you see right now when I click into each cell it's a text or should I say value so now it's set up so anytime I do anything with this it will not be affected it's right there it is its own cell it is good to go so now for da of the week it's very similar so once again I'm doing text again clicking into A2 putting in a comma and for the day of the week we're going to do the letter D four times close it up enter and we get a DA the week same thing apply to the rest contrl C Control Alt v v enter so it's very similar both ways the month which is M and you want the day which is a letter D yeah so yeah with this being on the reason why I'm do this on YouTube live is it automatically records that's why I love that's why I want I want to give this a try because it automatically saves and I could download it as well and for example in the beginning when I was talking about everything about the data set stuff like that I could trim part of this video out to make it a little bit easier to watch back so that's why I'm doing on YouTube live just so people could watch this at a later time to double check some things or whatnot um it automatically saves which is why I wanted to give this a try so we have everything said with da of the week now for the time frame what I basically I'm trying to say is like okay if it if there's anything from 1 to 1:9 p.m. I want to just call it 1 if it's anywhere from 2 to 259 I want to justj to be two so there's a few ways you can do it um you could do a huge if statement but that would take forever all I'm going to do is I'm going to just extract the hour from the the uh dat colum that's it I just want the hour and then if it's AM or PM so there's a couple things I'm going to extract it it's gonna be a text but I don't want to be text it has to become a Time Value so I'm going to do a nested text formula using time value so start us off I'm going to be doing equals time value and what does going to do is anything I put in here whatever the result I put in it will turn that into a Time Value automatically I don't have to go up here and do anything crazy with it it automatically becomes a Time Value so I'm going to be clicking then into I'm going doing this as Now text going into A2 and we got to think okay what do we want to pull from this so we want to pull the hour so we're only going to be typing the letter H one time and I want to also know what that hour is it AM or PM so I'm going to do am slash PM oh not om pm and we going to close that up but because it's nested we have to close it up again to complete the whole entire formula so that's what that looks like um I'm going to click enter and I'm going to show you what that looks like and then I will discuss how we will approach that enter let me apply it to the rest now you might be looking at this and you're like what the heck's going on why why is it all mess up like that well I'll I'll explain it a second but we can see right here all we have to do now is go back up to here and I want I want to do the time but I want to do a little bit different and you see right here that it recognizes it as a time if you look right here in the time column and there we go we get everything we're we're looking for right we extracted it for only the hour so for example this say 1725 so that would be 5:25 pm it only says five because we're only looking at the time frame here so we're all set there and once again to make sure everything I do in here does not get affected just contrl C Control Alt v v enter and now we have our time frame okay so we're all good there so we have because we're using some of these things like these two will eventually be uh you know what here let me show you kind of like what I this is what I made and this is kind of me just kind of throwing stuff on a sheet didn't really organize it too much I just kind of threw together on sheet but for example like these slicers here are what we're using to filter for some of these results so I want to make sure that I extracted that from the date so we could um does it mean we'll write four oh so the reason why I'm not doing four you know what um Let me let me let me show you what happens let me just uh I'll clear this out I'll show what happens when you I'll show you when you do like two hes um do you know the long cut way long cut way to past okay microsof oh yeah I got you yeah I I have um a Macbook I I hate working with it I I'm so used to working with my like PC my windows stuff when I get the Mac it's it's terrible but yeah I'll I'll do that in a second when I do the the uh Pace vales so I'm going to do that again with the time value text A2 and then let's say I do two h's close that up let me get so oh so now it's set up as time let me see okay so right now it's just is I'm not going to erase all too much my progress here but basically with the H it affects it because I only want this to be in one spot right here if the H would affect it as it would say for example if it was two it would say 01 it would say for 1 pm it would be 01 dot dot z0 PM it would make it too many uh letters and you see what here with anytime you do day of the week or month by you putting in it four times it wants the full value not just it abbreviated so all it means is how many times you put in that letter H for hours if you put it in additional times it extracts a certain part of the hour it wants but because I only want to be like the lack of zeros in the beginning I only put it in as one H to uh Pull It in like that because I don't want anything extra in front of it I just want what's the appropriate number uh because like I said you're you're extracting as a text so Excel doesn't understand what you're using it for it just knows the letter h means our they don't really know what you're doing with it so you have to make sure like you know with you're doing the days of week or month as I did before the month I put an M three times that means we're only going to bring back three uh letters if you do two brings back two and so forth um so let's say look uh so if you just want to just copy and paste here using your uh Mouse you just click into the data you want which is all set up right here just rightclick you just click copy and then when you get to here there should be a option right here where it's the pce options and it's like these numbers right here it should say uh values or if you go into pay special it'll be more options there but by doing it that way that's just how you use it for the uh copy and paste as values that's you just see you should see something right here with the numbers right there um so that's how that's done okay yeah I could do do so much D I kept it I only tried to do a little bit of data cleaning with this one cuz I said I'm trying to keep this one pretty simple but in the future as the more experience I get doing these workshops I have way more stuff planned in the future for you guys like I said good thing about YouTube live if I'm not mistaken you could go back if you came late or you're unsure about something you could go back and then kind of you know uh jump around a little bit should I say with what you're learning and where you're at so we're all set I'm GNA stop talking because we are all set here we got everything we need we got our month day to week our time frame we are set uh what I want to do now I think this always advisable and also you might think okay like we uh this datcom is not as needed since we extracted everything I still want to keep it there I don't want to mess with that I like to have it for reference I don't want to mess with that one bit I want turn into a table you should always turn uh your data into tables never just leave them like this because when they're in tables much more you could uh work with much more organized and you can name it way easier for the future when we do our dashboards so you want to turn something into a table click into any cell here and just do control a and the reason why I do that some people might think okay let me just select the entire sheet well that's not your Excel will load to crash if you try to turn this into a table you're have all these cells become part of the table and it's going to crash you only want to select what you want so clicking anything here crl a to select your data and contrl T to make it a table now make sure it should automatically do it but it says my table has headers it does if this is unchecked it'll it'll just say column one two three four it's going to look pretty bad just make sure that set SI of headers and we're all good and you can make this whatever you could put a color in here all these different colors you want uh I I I don't know it doesn't really matter too much I like to have for tables depending on what they're about I like to keep the colors associated with it since this is just an overall table I'm just going to leave a blue because it doesn't really doesn't really matter that much but with this now we could use we create pivot tables um any cell yep any cell control a and then control T yep and what you want to do with this this is huge for when you want to start connecting your slicers later and anything like that or just in general if you're working with a bunch of data name your tables so over here in the in the top left where it says table name call this whatever you want I'm just call this uh Orthopedics since we're we're working with that and it's GNA help so much because later they're gonna be like okay where do you want this slicer assocated with with instead of being table two three four five six and not knowing where it goes which happened to me yesterday yesterday when I was working I was like oh I forgot to name my tables when you name them so much easier to know what's going on where things should be going so we are all set with this now I I I'll leave this up to you guys it's up to you um I kind of put this in order on how I'd want to do something the dashboard part is going to be I like to use it I like to sketch what I want my dashboard to look like first uh and how I do that is a couple ways and how I do this right like there's many times I put this together and I end up making it a little bit larger at the end or I get rid of a certain area but it's also it's always good to kind of sketch things out a little bit so you kind have an idea where you want things to go I will say that once you create one and you start some more designs AKA templates save them and just reuse them or or or change up a little bit here and there it's GNA save you so much time so much time but for now it's kind of just we're going to talk about how to just like how I just look at a if I'm doing something really quick right we're treating this like a quick assignment this is not something where we're spending a week on this is something that your manager comes to you at 9:00 a. like hey can you get this to me by the end of the day this kind of how it gets put together so what I'm going to do is I I kind of want to design it just as reference this is kind of what it looks like right I don't really have I don't really have borders on or on anything here um but I want to do something so I make sure I am focused on the right areas and not all over the place so I'm going to do with that is um also if you saw too there's no grid lines on this there's you know the cells and everything but there's no gri lines which is you know a lot easier to look at but I do that at the very last second because what I do now is I basically create a couple boxes and I honestly just kind of look right now I'm doing this with you guys I did this yesterday justtin messed around with but I'm kind of like doing this too with you all um I'm just going to go from like E2 to E10 and just maybe go take up about let's say yeah about that much uh and what I'm going to do is I'm going to go to borders which is right here I'm going to do thick outside borders what that's going to do is basically like okay I want my for example in this one my title to go up here right if you saw right here that's kind of what that looked like was my title uh eror to the left and my kpi guys to the right so it lets me know I want eventually to do some things with this box now this could change this could get smaller could get larger can maybe get nods wide but it's good just to sketch it out and this might get a little tough to see but I typically go out to 80% with this just so I could have a little bit more room um and then so that's what that looks like I'm going to put my uh let's go [Music] um let's go right here I'm GNA put one right there like I said this doesn't have to be perfect because at the end when we start moving things over here these grid all the stuff will go away it's just kind of for reference or you know if for example you do this and it's four cells wide instead of three it's not that big of a deal because you'll see when you put it in there how wide do you want your slicer to be how wide do you want your chart to be this is for you to just kind of see right away and once you start putting some things in there you'll see what you want to keep and what you don't want to um so we have here also so we got I'm have my title here my kpis here slicers AK filters here and I want I'm have a table here a chart here and a uh pretty long line chart combo chart down there uh what I'm going to do with this is should kind of go here with this create one there yeah I'm me get rid of that that's a little too wide a little too much and I'll go one right here and then we'll just go right here like I said I'm kind of just sketching right now this will definitely not stay the same and you can see what I did before my my dashboard and the other one did not look like this it was you know this one chart was not this wide this one wasn't that short it's just for reference so when you start moving things over uh we're not like I said just throwing things for no reason but this is like just a quick little like uh should I say you know let me go like right here in the middle because I'll be like you know what this will be for my kpis cool that's nice split so I have my little design up and like I said however you want to do it you could do a little bit different too like I said there's more data in here that we could work with uh this is really just for a quick reference for so at the end we're not going in too blind with this um does anyone have any questions about the dashboard design or anything like that or any questions about dashboard designing as I drink this really cold coffee no questions cool like said if any questions come up just uh feel free to let me know so let's get to our first one I labeled all these sheets because we're going doing for each of these sheets will be a pivot table so this one's be kpi so the whole point for this one is going to be we're looking for how many patients there what was the average customer score and what was the average weight time so in order to do that go here to Orthopedics you click it you click into any cell here because this is a table Excel will recognize it and select all of the data that's why we like to set up as a table uh so we go into insert we go pivot table and okay it says say you see right there picks up Orthopedics table which is that's why we like to do that instead of saying table one table two you just know exactly where it's coming from you want to make sure we're doing existing worksheet because we already set one up existing worksheet go back to your kpis and click into anywhere I like to go to like C5 like right there and it's you to start your first pivot table now once again just like the others we want to make sure we name this so I'm just to call this kpi guys just so we know which one is associated with so once again I'm trying to find out the total patience the average customer score and the average weight time so we have to think let me go back to Orthopedics which of these columns would be the best choice for to find how many patients there were defining the count of it like which of these columns makes the most sense which one would ideally make the most sense for us here there we go thanks thank you Meredith patient ID yep so like I said with the patient I always like to make sure in anything I work with that there are some kind of unique ID because it gets significantly easier if you're trying to join data or try and find a certain count because it's Unique it's way easier to work with and to make sure you're not doing like count of Department referral or count of something else like that's just not the best uh technique or practice so I'm g go back to kpis and I'm GNA drag this into values so we're good right there so we're good there with that that um so we have the count of patient ID and let's go now to so we said the oh my brain's messing up oh yeah it's average score it's patient score drag that in here and I'm G to drag the we time in here too now when you're looking at this yeah make sure especially with Excel they like to take numbers and they always want to sum them they never just count them right because they see numbers like okay we have to add add add add so make sure when you're doing this you got to make sure you're not just throwing it out there because like I said the average score is 784 like that makes no sense right you just got to take a second to look at that uh so make sure you go into the value section the dropdown with the we'll start off with the patient score go to Value field settings we're going instead that instead of that being a sum we're going to average no that Max yep there we go click okay and then same thing for the wait time field settings average good okay so we have this setup right here we have the amount of patience and the score and the weight time however these two numbers will that's not going to work right so to me I want the score just one one digit I don't want there being a million so let me just go up here where it say this right arrow decrease decimal decrease that to just one and then the weight time this is in minutes I don't want to say 35.8 minutes or I'm just going to make that a whole number so just going to take away all this around it and we're good so we have everything we need right here we have our patient ID the average score and the average weight time so we're all set with that one pretty simple right to the point uh now let's go to the next one we're going to be looking at the customer score by each month this is why we got that month column because without it it would been extremely difficult to put that together in a pivot table so same thing I'm going to my Orthopedics data clicking into anything here go to insert pivot table again existing worksheet score by month and same thing it doesn't really matter where it goes and click okay so now this one and for same thing here you call this a name I'm just going to call this uh SAT score because it's all about the satisfaction score with that so like I said I want to know the satisfaction score by each month I wanted to average one but I also want to know how many patients were there in that month because there could be a situation where there was one month that was a low score for example like a 3.2 but maybe only 15 patients were there in June meanwhile one month had a 7.2 and had 80 patients show up so you want to make sure that when you're showing off uh average ratings especially customer data you want back it up with how many people actually uh gave in their opinions for it because you don't want to have sit where oh customers gave a low rating but there was only two people that did it compared to other other months so this one a little bit different with the setup I want the months to be in the uh like the rows and in the columns I want the uh average score and account so I'm G take month put that in rows so we're good there then for the columns we're going to be putting let's go actually and then in the columns for this because we're putting a value in there all we got to do with this is take the how do want set this up I want to do the average score first and then the ID yep so patient score right here we'll change it in a second and ID right there so like said once again just like another one to count patient ID it counts because it's counting so the csat score that is the uh patient satisfaction score so if they give for example a one a scale of one out of 10 one means mean they had an awful experience 10 being they had a great experience so we want to make sure we understand uh how the patients gave the score based on let's say the month now we have to we have to also know this we don't know yet if there is a correlation between when they came in or something like that depending on the month but we want to make sure we're backing up any assumptions we have before we send us off to anybody so I like to make sure we're looking at anything we can to make really see if there is a reason why we're getting a certain score is it by the month is it by the time of the day or whatnot um but the satisfaction score is extremely important if you're in anything to do with customer data you're always rely on feedback from customers because that's how you always improve that's how you know what's working and you know it's not working um but like I said in this one the satisfaction score is a number so it all automatically adds it up it's going down here to some of uh SAT score field settings average we're done same thing here I just want to make sure this is all we go that one kpis now how do I go about the kpis and like I said this is kind of like using my imagination with this right I'm just imagining a manager coming to me and be like hey Chris uh I feel like we need to understand what's going more in going on with the orthopedic Department I feel like there's lots of customers calling up with issues we have a lot a huge turnover in employment we got to know what's going on so for me I'm thinking okay I want to understand how many people are even coming in what's things are like the average score kpis are kind of a good thing that affects everything that's going on so kind of like the main points right how many people are coming in what's the average score what's the average weight time and depending on the filters we put in those numbers get affected right so that'll also get affect it as well it's a better way of doing that instead of just throwing a huge uh table to people making them do the work by some of these kpis These are numbers that should be looked at that can be affected based on the month the day of the week and to me those were the most important ones but like I said with everything else especially with doing these projects as I recommend anyone who does these you can kind of dictate how you want to go I always recomend people watch them to see okay how's his way of thinking what do I see that's different what do I see things I agree with and from there you adjust and then you have your own project that's how I did mine and that's how I think people should do theirs too um so to me that's what I saw but if someone sees something differently you go do that it's up to you but to me that's just how I'm putting this together that that's a very very good question um so we're all set here with that one get the average score which all these numbers matched up with our kpis over here and we have our average score by the month you see right here some are sign significantly less than others one that's 4.1 in June 6.4 in January so maybe there's a reason for that we don't know yet so we're all set there and then now we got age bracket I like this one this this this is a oh gosh I got speak right that's G be a fun one so going back to Orthopedics same thing we're inserting another pivot table go pivot table existing H bracket C5 again doesn't really matter where but I like to go C5 so I'm call this one age bracket and what I mean by that is we also want to understand like with the people giving these reviews are these people are these adult patients are these adults who have kids who are coming in like because you understand too that most people who as a person who's worked in an industry that is like I worked in the sports training industry and everyone I worked with was anyone from the ages five to 20 and most of the time you're dealing with parents who are doing anything with booking the lessons running the teams and stuff like that and when something happens to their kid that they don't like they'll really make sure you they leave the worst review possible right so we want to make sure that all are the reviews getting are they mainly you know parents giving reviews based on their kids experiences or are they mainly uh older uh you know just adults giving about their experiences uh yeah customer satisfaction yep yep customer satisfaction or in this case patient satisfaction score uh my brain just I don't know why it typed in because I'm so used to working with customer satisfaction scores in the past um but this could be we'll call it P it's a more patient satisfaction score more accurate um so this one's a little bit different this one's it's going to look a little bit messy but let me uh go from there let me go age so I want to do first is I want to put in the patient age in Rose this is about all the ages that came in so you can see the youngest patient was one and there was a now was 79 that's the uh range we have now I want to know how many of those um patients came in so I'm going to drag patient ID into values so we see right here there were five patients who were one eight that were two six to were three and so forth um but how I want to do this is because I'm using a number this could get a little rough this this could get a little messy here so I want to group them together and I didn't spend too much time if you want to group this in a different way you can but all I'm going to do here is group it by every 10 so 1 to 10 11 to 20 21 to 30 and so forth so I'm going do with this I'm going to click into any cell go to rightclick and there's G be a group option right here so then you click group and then right here it says okay it's going to start at 1 ending at 79 which is yep that's exactly what it is and by how many it to be by every 10 so every 10 it's going to change so let's just start at one then next group will be at 11 then 21 31 41 and so forth so let me click okay and see what we get here and there we go now you get to see how many people were in each group so there were 73 people who came in who were ages one to 10 there were 62 who were 11 to 20 70 people who were 21 to 30 and so forth so that's just something to understand and then kind of looking at this right you kind get an IDE idea of looking at it that it's pretty consistent throughout which might prove something it might not but that's something that they want us to look at and we put together in this way it gets really difficult if you want to group it in for example inconsistent numbers but if you find like you want to do a certain group with this do not buy a consistent number like this works out pretty well on a pivot table so we're all good here with that one we have our age bracket for what we want and then we got one more pivot table to do one more and we're all done with the good old pivot tables so go back to Orthopedic go to insert pivot table existing go to patience and wait time I should have made that a little bit better with that name there it's a little confusing click okay and this one I'm should to call this one uh I'm just call wait time so this one's going to be a little bit different we're going to be trying to comparing two metrics in one because it's going to come in handy when we do one of our charts so with this one I want to know by the time okay this want to be based off the time I want to know like okay if if it for example was uh 800 a.m. I wanted I wanted the average weight time at 800 a.m. and how many patients came in I want to see if there's a correlation because for example if a lot of patients came in and it's a really high weight time that makes sense if there's not a lot of patients but it's a huge waight time that that doesn't make any sense at all so we want to start getting an idea on where that comes in is there a reason for that and so forth so let me pull in time frame into row and there we go that's good and let me get rid of this hours here move that perfect good um let me see I'm also gonna do something really quick let me go here let me select my time I don't want the seconds on there go into number here yeah I just want to do this I'm just going to have a set like this I don't want because there's no seconds involved with it I just want to have the hours and minutes so I'm going to just keep it right here that's much better that's much better work with so we're all set here and the good thing the reason why we also turn this into a time before is because Excel recognizes it as a time as see it goes from 12:00 a.m all the way down to 11: p.m all in the correct order that's why we do that much easier to work with so like I said we want to know how many patients came in at this time and also what was the average weight time want there's a correlation especially when we start working with our filters later so let's start off with uh let's go the average weight time we'll change that in a second and then the amount of patience so I said we got the and like said the more the more you start working with Excel especially like pivot tables there's so much you can do with it I could talk about pivot tables all day long but the more you just start working with Excel and the more you start understanding where things should be going especially right here in pivot tables how you work in powerbi and things like Tableau it's going to be so much better just way you're able to know where things should be going and how you how data should be working helps a ton so the counts fine but like said with this we want the average time so go into values field settings average done let's change this all into uh just full number there we go the reason why we do full number is because thing about because because this is in minutes uh if you do like 31.2 you have to start thinking okay what is 02 of a minute which is 12 seconds but like you don't want to be doing you don't want to force people to do that you don't need to be too precise with that I think this is fine if your manager comes back and like hey we really want to make sure we know what the minutes are or the seconds then yeah sure we could do that but it's always best to keep it with this because you don't want to get to a point where you're getting too many uh numbers all over the place is way easier to work with so this is just a quick glance at just looking at the time now of course when we put our our filters later we're gonna really drive this down a little more because we can see here that for example uh glance at it quickly 40 seems to be the highest and there's only 21 people there and 409 like that makes no sense so at 5:00 a.m. and 1: p.m. or to highest or actually 11: p.m. uh the highest but we got to understand is because of a certain day was there a certain day that was bad a weekday or something like that um that's where the filter that's where the uh slicers will be coming in to make sure we understand that a little bit better so we're all good here now we could do kind of some more fun things and kind of put together a uh some visualizations so let me go back to with this will'll be no visualization we're going to be creating uh some kpi areas r or dashboard so we're not do anything with that yet but let me go into this now when I see this I'm trying to think is there a way I could show this off besides a table is there a way I could show off the uh average score because I want to make sure I know the average score and the patient how many patients came in is there way showed off better at a table and if there is what could that way be to me in this situation I just think a table is the most straightforward way to do this I don't think a graph or anything like that is needed for this because sometimes you just want the numbers sometimes you don't need a chart to really show you this um but what we could do uh this is something if you want to do I'm not going to do it because I think it could take away from the data but you could for example put in a uh conditional formatting to be oh if it's between if it's less than four well I want to be a red if it's between 4.1 and 5.5 I want to be a gold and if it's above 5.5 make it green that kind of symbolize if it's a low score middle score high score but honestly I don't know if I want to do that because I think it might take away from it um if anyone wants me to do I can do it like at the end but for right now I'm going to leave the way it is because I think a table in this situation is just all that's really needed so going to the age bracket here with this I'm going to do a a bar chart I think doing a bar chart here is uh the most useful I'm GNA go to insert and I'm going go to Pivot chart right here and then usually when you click that it right away gives you what they recommend Excel will look at your data quickly and recommend you okay we think you should be doing this column chart or you should doing this bar chart and I'm a huge uh bar column chart person because I think it's most straightforward way to show off your data now sometimes you don't need it sometimes you do in this situation I think it's best because we're using a grouped amount of data so that's what the bar chart looks like that column chart and the bar chart looks like this now I don't know about you I kind of want to do the column one I think this one makes most sense because I think it we read left to right and we see right here the numbers uh first one 1 to 10 11 to 20 21 to 30 we read left to right with this well first of all we'd have to switch up the bars and make sure they're going up and down or keeping it that way it just could get a little bit confusing a little bit so I'm going to leave it as a column chart I'm not going to get too crazy with it leave right that click okay and I'm GNA leave it right there for now I'm not making any changes in the chart I'm going to do that when I pull it into the dashboard but for right now I'm leaving it right here the way it is and then I'm G move on to the patience and wait time and with this one there are are two metrics we're looking at this one we definitely could do a visualization because when the numbers are pretty simil when they're they're different meaning but when the numbers are close to each other there's most likely a way you can incorporate both into a visual and it doesn't bring too much away from it so what I have thinking this one is okay what is the best way for me to show this off with it making sense I love doing combo charts using a bar chart and a line chart I think that it's very useful I think you could really see quickly correlations and that's what I'm GNA do with this one and I'm go to insert pivot chart and see th it recommends a clustered column but like I said that's going get hectic if there was only let's say uh four things I was looking at then maybe but because there's 20 four different type of rows we're working with here it's G to look horrific so what I'm GNA do here is go down to combo and that's exactly how I want to be um uh it's G give you an option of this so the option of the uh patient ID do you want the patient ID to be a line or a column I want to leave it the way it is I want the patient ID to be a line I want the wait time to be a Col I want that because the weight time is usually going to be higher it's always going be higher than the patient ID I like that the line not be on top of the bar because it doesn't really then make too much sense I want the line to be almost in the bar so that's how we're going to do it um that looks okay to me click okay and like I said there's gonna be there's G be some changes we do here from the color changes and everything like that um especially down here as well but we're all good let me see here I don't want because I need to zoom out a little bit have to do something with this uh these times here I don't want the seconds here but we we'll make that change a little bit so before I continue does anyone have any question so far before I start putting together the dashboard part which could get a little hectic does anyone have any questions or anything like that about putting together anything I've done so far with the age bracket the weight time or anything like that is there any questions before I jump into the dashboard part now we're all good cool okay so to the dashboard here we go this is where things are GNA start get a little bit hectic okay I like to like I said in here ORS me a handful of kpis be working with and here is be kind of like our title and some text so what want to do first is I want to create where I want my title to go and what I did so far in this one there's one two three four five six seven 8 9 10 11 okay so I'm going to take the first six here I'll zoom in a little bit so everyone can see take the first six one two three four five six trag across hopefully the people who use uh the keyboard for everything don't yell at me but I like to use my mouse sometimes um what I'm going to do with this is I'm going to merge and center it so I'm going to go right in here merge and center tap basically creates one huge cell so anything I put in here will stay in here and I want that to be that way I don't want this going anywhere else I want this just being one huge cell for my um title and then right here with this this about five so you just covered rest merge and center that's going to be where some like the uh text goes where in there I'm gonna be putting things like make sure you use the filters below to find a data you're looking for kind of like a little description area should I say um but that's for the data that's the data sorry the title and what I'm going to put in for the title I'm just put in let's go Orthopedic uh analytics dashboard 2023 close it up and because it's all merg and sent into one we could do a ton with this what I'm going to do is I I like to keep things I said left to right I'm going to just go over here align all the way to the left I'm GNA buff it out like one or two buff it out a little bit put more in the let's go here in the middle yep and now I'm going to make this a little larger see how much it gets okay let me go 32 perfect 32 too so we're good there um I'll bold that so we're good with that and then for I'm not gonna do anything yet with this I'm going put in here uh description just so I know um actually let me that description put summary filter info just so I know what goes in there so I'm not messing with things too much or anything like that um so we're all good with that and now I want to step our kpis right here so I gotta think with this one we got one two three four five six seven8 nine 10 11 12 13 13 okay so I could do here so there's 13 how do I want to do this want this take up a few okay so let's go here I'm g go here to R3 go down to R4 I'm going to cover three cells across so it should look something like this like a 3X two same thing I'm going to merge and center that one go here to vwx merge Center that one perfect see right here with this what I did was I did a merge Center here I like to have a little space between uh so I put a space between obviously like I said these lines will not always be here but I put a space between this space between that space between this and then ideally I could almost basically get rid of this right here it's that stop right here at some point actually let me put it back I'll leave it there and then here's going to be the titles of our kpi so like said before we had things like our how many patience what was our average score and what was the average weight time and we can put that all in right now um what I want to do with this is I'll just put in uh number of patients and then for this one will be average uh we'll call it pat score PTI score I like to abbreviate maybe I don't want to write everything out because like I said I don't want to get to a point where certain certain these cells are much larger than others and for this one I'll just put in average oh boy click click some uh wrong things there average weight time and I want to make sure everyone knows it's in minutes okay so we're all good here let me just put these right in the middle I want both these to be line in the middle let me buff that out a bit let's go 14 put that in the middle 14 middle 14 and like I said it might look like it's kind of crammed in there right now but like said once these uh grid lines are out you're not going to notice at all you're not going to notice them at all so that's why it's okay to um buff these out a little bit you know let me make these even a little bit larger trust me you're not going to notice a Thing Once the grid lines are out actually no yeah me I'm leave my 14 now so now let me put in the numbers I want now I want these numbers to be fairly large right I want these to be the big part of this so let me think here so we one two three four five six and I'll just go across so it's got six by that do six or was that five one six yeah merge Center good so just one big box there and if you want to just do this really quickly and you know exactly where it's going to go you just contrl C copy it and let's say I want to start here crl V and control be done and you're box it just like that um like I said it's not it's not best practice to be always doing a bunch of merge and censors but because we're sending this off as just like a report it's okay because they're not going to they're not going to see these grid lines they're not going to see these boxes if they really wanted something a quick easy report within like 30 minutes you wouldn't be doing all this but just practice understand how merg Center works and its capabilities one thing you know about merg and Center though is the biggest thing is obviously when we're doing a dashboard like this it doesn't really matter too much but if you want to uh if you put this on a regular sheet right let's say we go since we don't need this right now let me let's say I merg these two together you know I'm not do basically all I'm saying is you can't filter through cells that are merge and centered there's a way around that uh I'm not going to get into that now because I doesn't really matter too much but like I said merch Center could ruin spreadsheets so it's not good practice to use it very often unless you know it's for a certain reason but with this it's not being filtered up and down so it doesn't really matter too much all I'm G to do with this is type equals in here and all I'm going to do is go back to kpis and click into here and automatically gets that pivot information click enter and just do the same thing for the rest go back enter equals enter cool so all good there and actually something I did forget to do is I want to apply going back to kpis because we're doing it by things by the month and a day of the week even though it's not affecting it I just want in there so when we connect them at the end we could use these filters for everything involved so we're all good there and and same thing here we're going have to reapply what we had before so just changing the that to a one decimal change it to a full number and same thing here so we have plenty of room for this so let's see when we put them right in the middle I'm G bold it how much room can we go okay that's maybe a little too much 48 okay I'm gonna go with 48 here let's see bold that 48 enter yep put that in the middle 48 enter good now you might be thinking okay why don't we paste this at specials and we pulled it from a pivot table like no we want to do that for a reason because we're taking it from a pivot table this will always these numbers up here will always change depending on the filter you choose so we want to keep that the way it is um so we're all good with that we are all set with our kpi so now we have to bring over our uh three charts so let's start off with our uh table so we go into here and I'm going to just contrl a select everything contrl C let's go back to our dashboard I'm GNA click into now this is not going to take up that much room that's the thing it's not going to take up too much room but it's okay so let's go here I'm GNA go into was it j16 once again doesn't really matter too much I'm going to be taking away all of these lines at the end contrl V and let's make this a little bit larger we go don't need that okay we're good here and I'm gonna zoom out because now I get to the dashboard you'll notice that things could get a little bit a little bit different here I like to zoom out to 80 just so I can see everything through uh want to do here's