Streamline Your Client Payment Tracker Excel for Research and Development

Client payment tracker excel for Research and Development

are you wondering how to create a project tracker in Excel perhaps something like this well if that's the case then you've come to the right place because today I'm going to be showing you exactly how to build this step by step now if you want this template without having to go through the Audi process of following what I do then you can click the link in the description below and you can download this tracker uh for a small fee it will be under the price of a cup of coffee and that does help support me in my channel so I would really appreciate it and it will save you a lot of time as well but if you do have a little bit of time available then I'm going to show you how we can set all of this up including all of the important functionalities which will help make this more automated and Visually appealing so with that said let's delve into a brand new Excel sheet so the first thing that I've done is I've saved this file of course you don't need to be reminded of that but I would recommend that before you begin you save a new copy just so that you don't lose any of your work from there it's always a good idea to choose a font that you may like I think calabri is very good it's very professional so I'm going to be using that for this tutorial so one thing you can do at the start is name your tab at the bottom we could put project tracker here uh and this can be particularly effective if we want to create project trackers for multiple projects and have them kind of simultaneously aligned at the bottom here in separate sheets so we can choose between them that is an option bear in mind this is a Tracker that has worked for me in my organization there's various different templates out there but this is the one that I personally I use and personally believe has everything that you're going to want to include so the first thing I recommend that you do is you simply put at the top a reference to exactly what this is all about so I like to bold this and I like to make this very very large so what I'm going to be doing here is I'm going to be doing this just to show you uh how to quickly expand the size so this is in calibri we could change the font color to a gray which or you know an in between a gray and a black and that just kind of helps us to uh yeah it just makes it look a little bit neater if you like so here are the recommended columns and I'm going to be showing you the um the formatting that I'm going to suggest as we go along now here is a quick tip that you may not know already and this is how to insert checkboxes in Excel and we're going to use this for the first recommended column which is at risk we want to be able to specify any projects or tasks on the task level that are at risk so to do that what you may need to do you may not have done this already I have but you may need to alter your ribbon on your Excel so to do that if you right click at the top on the ribbon I should say uh you will see the options that's right click on my mouse customize the ribbon we want to click this and what you want to select is develop veler so if you don't have it on it won't appear at the top so let's go through that again right click customize the ribbon find developer and have that checked press okay and you'll notice this has now popped up and the reason why we need to do this is It's because the only way we can insert checkboxes is with this functionality so what we want to do here is we want to put insert so in the developer ribbon insert check box and then we simply draw it in here okay now what you might what we'll need to do here is we want to edit the text so we don't want any text at all so I've just deleted that out and now this is now checkable okay if you right click it you get this kind of uh area and then we can kind of move it into the center of the cell control C and contrl V will enable us to copy it we just need to do that uh for every single you know copy a little extra tip you hold control and then select them then you can do two at a time until we get about five so let's just do the first five I'm going to do one more right click contrl + V so yeah this may take a little bit of time to set up initially but bear in mind you can use this kind of functionality for all the other templates that you set up going forward so I want to get that aligned it's slightly off that's going to really annoy me so bear with me two seconds I think that's it so now we've got five here I can if I hold control on my keyboard and just select all of these then if I press contrl C and contrl V those shortcuts we're going to get five at a time and then oh sorry I'm being a little bit pedantic with where I put these but you get the idea there may be a cook way to do this but this is what works for me now that actually a little bit off I'll just to put this in for now you get the idea you get what you need to do here okay this is why buying my template might be easier CU you Sab this faf anyway the other thing I'd like to kind of suggest you do is make this uh the column headings one size bigger I find that to be a little bit useful in kind of differentiating what the column headers are and the content that's below the other thing that we can do is we can put um so I'm using this on the ribbon in the home area we're using a fill color and we can use that to again differentiate the column headers I think this is a particularly uh useful color for this column now what I'm going to do is I'm going to build out the rest of the template for you and then we'll delve into the content areas which will be important for you so the first one is going to be status and that can be again on the uh task level or project level we're going to have priority and again I'm going to show you a Nifty trick in a minute to add the formatting that we've just set up here to the whole column area we're then going to have deadline we are then going to put task okay we are then going to put description so the description of that task on the task level assign to so an an individual I SP that incorrect so who's responsible for it we are then going to put um a new air well this is going to form into a new area we'll see this shortly this is going to be deliverable so what is being delivered um percentage done we are then going to have fixed cost because we want to have an area where we can track costs as well again on the task or project level so we're going to have fixed cost here estimated hours again for the completion of the task and we're going to have actual hours so actual versus completion that will enable some tracking further down the line to see exactly whether we met our objectives so I formatted this earlier what we can now do is if we select this cell and press format painter you'll get this little kind of Paintbrush if I now left click on my mouse and drag all across it's going to apply that formatting to the rest of the columns another thing we can do is if we click this at the top it selects every single cell and column in the workbook if I now double left click on here it's going to expand the um the columns to the appropriate width that match how kind of big these are so as as an example estimated hours requires more of a column width than and say then percentage done does okay so we're going to do that and I'm actually going to drag this one back because I want this to start a little bit earlier so now another thing I'd recommend that you do we're going to separate some different areas of this tracker so the first one is above task and we're going to call this pro project and I'll put a little s because it could be one project or it could be multiple and I'm going to merge and center up to here and you'll see why we'll do this momentarily I'm going to expand this up so it's a little bit bigger I'm going to do 18 for now always change it and I'm going to put this in um a lighter gray okay now the next one we're going to do is deliverable I'm going to merge this to percentage done format painter done and then the last set is going to be cost and hours so in here we're going to put cost hours I'm going to merge these and I'm going to hit this and I'm sorry to done that format painter done so now we've got some different kind of areas and what we can do at this stage is we could put some uh borders around it so we're going to go all borders and what you can then do here is put some borders around this as you as you would have seen at the start this video the formatted template that you can purchase is all this is all done for you and it's all in my opinion it's all kind of perfected I spent a lot of time working on that template so um just bear that in mind this is not going to look exactly the same um but this is just I want to show you how you can create this template as quickly and as efficiently as possible so that's the first thing we need to do okay now what I'm going to recommend that we do we're nearly getting there actually there's not much believe it or not there's not much more that we can really do onto this template there's a few other things but not too much the first thing we're going to do is add an area to add projects and tasks okay so this is going to be um so this what I'm what I'm essentially getting at here is by doing it like this we can track kind of a whole project or we can track track it by task and if I'd fill this out it will make more sense so task one task two task three task four and task five of course we could keep adding tasks projects can have multiple T you know many many tasks you can even break this up by Milestone and task so you could put something like Milestone one task one and you could actually then start using the indenting to kind of you know have that kind of parent child relationship if you wanted to but I'm not going to do that I'm just going to do task for now uh I just wanted to give you you know some some context as to how you can kind of adapt this to suit your own needs I will indent that just so they're kind of aligned underneath and what we're going to do here is select all of these cells contrl + c contrl v and I'll do it one more time and I actually just need to uh get the borders all the way at the bottom now because I've expanded the content area and this would be you could put your project name so if this was say project uh web launch web site launch you could do that here whatever the project is called you could put that in but I'm just going to call it project a of course we'd need to update that to Project B and project C and we need a couple more of these don't we so contrl V pop it down there and the alignment's all off but as I as I kind of mentioned I'm not going to spend too much time I'm actually going to leave them kind of a little bit wonky so I do I'm fully aware that this is wonky um but as I say want some some time save you a bit of time there uh I'm going to put this in a no we don't want that one sorry we want a fill and again I'm going to suggest a kind of lighter gray so we can then use the format painter and do that against each one and this is just splitting out the projects it's just making a little bit more um you know we can yeah we can see between the projects and actually I'd probably recommend Bolding these as well uh just so we can kind of differentiate between them now we're nearly there really there's not actually that much left but there's two more things well there's a couple more things I want to show you the first is I'm going to set up what's called as a freeze Paine so that when we scroll down certain um you know this content area remains so to to do that select row four we then want to go to the uh where is it I think it's in review no view sorry it's in view select the view and then we're going to put freeze pane so I've selected row four remember and then I press freeze Pane and what this is going to now do is when I scroll down so I'm in row 108 now sorry 100 row 100 onwards it's kept all of that and I just think that's really really useful for working in this template so that's what i' recommend that you do you set up that freeze pain again you don't necessarily have to again you can turn that off by going to view freeze pan and just click unfreeze pain so that's the first thing I'd recommend you do just to make this template more applicable and useful for you the second thing I'm going to set up what's known as some data validation and what that Ena you to to have is almost like dropdowns so instead of having to type in the priority every time or the status every time they're kind of available uh for for for select ction for anyone who's using this project tracker and I'm going to be doing this by building out a key and I'm going to do that on the right hand side so the first thing we're going to have is we are going to have the status and then we are going to have priority so the statuses that I recommend that are useful for projects again could be different depending on your needs we're going to have not started we're going to have in progress and again this can be applicable to not only the project level but the task level so this is why this is really really useful we're going to have on hold we're going to have uh complete and overd these are quite useful statuses to have going to move this across I'm going to bold this and this I'm going to put a little bit of a this around it just to show you excuse me that that is a separate key you could even put something like key here if you wanted to and you could move these down just what you want you want to make this user friendly so it may make sense to you but anyone who maybe opens this up somebody else may not be aware of how it all kind of work so if you can kind of you know you could actually put this in a separate tab if you wanted but that's what I'm trying to get at so in terms of priority classic low medium and high and I'm going to put border around that as well let's do that now we need to set up the data validation so what we want to do here is um this is a quick trick to get it applied to every single cell in the column and then we can just remove it from those at the top because we won't want the drop down to appear in these four I found this to be the most quickest way I'm sure there's you know you can do it on a cell level you can select you know a cell range but this is the most effective so let's do status first so what I recommend you do select column C we then want to click data at the top on the ribbon we then want to click data validation and then the First Option here in the settings we want to put select click this drop down and put list so the source this is the status remember is all of these here so from not started down to overdue I left click and dragged you'll see it's kind of created this range you could actually enter that manually but you know It's tricky to remember and know how to do so it's best to just drag press okay and what it's going to now do is if I click on this every single cell in this column including these top ones is going to have the ability to have not started in progress you know Etc now if I double click C it's going to expand the column to to to to meet the width of the data that we've now got in because we now need to do that because as you'll notice it was too kind of narrow before so that's what we can do now we don't want the drop down to be here or here but we do want it to be you know all the way down here just in case we add more projects and tasks so to remove it from these cells simply collect uh select all the cells you don't want that drop down so it's C1 down to C3 data data validation click on data validation and then just put any value and then what that's going to do is it's going to have it's remove that drop down from these cells we don't need it in but it's applicable for every other cell in that column so now you just need to do the same for priority so let's do that again select column D let's now click data data validation data validation list Source this time it's the priority so it's these three here I left clicked on my mouse and dragged from low down to high it's created the range in the source era press okay and now we have the priorities which is brilliant so and again we can do this on the uh we could do this uh on the project level let's just put this as that what I'd actually recommend that you do is actually make make this whole row bold okay and I'll do that for all of them as well just so it as you can see it differentiates the project level from the um task level and you could even select all of this and you could indent it as well so you could do this home indent if that makes life easier for you it might make it look visually more visually appealing it might not but I'm again I'm presenting options to you okay so we've got the priority in we just need to remove it from the C cuz again we don't want you know that in here we could I just but I just think it looks a bit messy so to do that select those cells data data validation data validation and we're going to put any value okay getting there we're nearly nearly there of course the rest is just for d u manual data entry unless you wanted to put you know you could have a list of certain assignes you could do that as another area in your key and you could do the data validation there description we probably would want bit more room in here we're describing the task or the project here so this could be project to create the website this could be buy the domain name this could be build the website you get the idea uh percentage done you could change this to a um you could CH well let's actually start with this one first you could change this to right click on the column and you could change click format cells and you could change this to a date and you could change the type you want again that's probably just more for kind of visual but then if I click on here and I put in say the 25th I'm using any date this brings to mine here of nov oh I didn't I I can't have done that properly uh I didn't put it in the right format sorry uh let's just click on this then it's it should update it no it doesn't recognize nov if I put November okay I've messed that up ignore the last couple of things I've said if I put 2511 yeah it's updated it to that format sorry it didn't recognize what I put in there so if I quickly just change that to the top one then you you get the idea it's in date format so if I put 101 it will you see what I mean it kind of puts that in there so I would recommend changing the deadline to the date format you could do on percentage done you could change the format cells to number uh you could do you could put decimal places in if you wanted uh percentage is a good one uh for this actually sorry obviously now if I put that it's going to add the percentage every single time so I've just typed in seven and it's added the percentage just saves a little bit of time so change column J to the percentage fixed cost you could put this in a currency so you could right click and go format sales and there's a currency option you could change your currency you might tell by my accent I'm British so we got pounds but you could put that in anything you wanted probably dollars um and you could do other things here with estimated natural you could put a couple of decimal places in so you've got those options now the final thing I'm going to quickly show you is conditional formatting and that's really really useful for just bringing bits of data out as you use your project tracker and making it uh yeah it draws your eyes to the right places so as an example anything that's overdue or high priority you could put in red as an example so that it stands out and you can quickly see any project or task that needs attention so to create uh some conditional formatting I won't put all the conditional formatting rules in because that'll just take too much time uh and I'm sure your needs differ but I'll show you how to create them which is the important thing so to do that you go on uh home on the home Riv and you click conditional formatting then what you need to do is set up some rules so if I click manage rules I don't have any in here at the moment so what I want to do if I go back to conditional formatting new rule now what you need to do is I would well you don't need to I would suggest that you use format only cells that contain so any cell that uh is a equal to so now anything that's equal to overdue we want to now you click the format button we want to appear in a red so make sure Phill is selected so all these options at the top make sure the last one Phil selected I'm going to put red and I'm going press okay I'm press okay again and let's go on manage rules so we need this to be this worksheet so okay no sorry the reason why that didn't work manage rules we need this to be this worksheet and applies to that needs to be the entire worksheet so if I select this and then put that press apply every time overdue is seen it will go in a red so if I put overdue in here also has gone to red brilliant excellent so you could do that for every single status so anything that's complete so again let's go through conditional formatting uh if I go manage rules and if I put this worksheet and then go new rule format only cells that contain so we want to be complete so cell value equal to complete this has got obviously be exactly the same here so if it's completed then it won't work if complete you see what I mean it's got to match essentially it's got to be able to find the exact text in the in the sheet so we're going to put this in a green press okay press okay again now we want this to be the entire so at the moment what this is essentially saying applies to is it only looking it'll only it'll only go green if cells within this range has that text but we want this to be the entire worksheet so just change this so now I've done at one already we can select all that and press control and c and go back in here and press contrl V or alternatively you can press this button and then then select that left click uh left click on your mouse the far left button here with this kind of triangle uh and then press enter and that that's just basically every cell in the in the workbook Press app apply press okay so here we go so at the moment this is you know you could actually change the text to be a white you could have this bolded as well if you wanted to so just thought I'd mention that so you can now work through all of these we can have on hold in a yellow say in progress might make sense to be a blue so think about the statuses and think about what colors make sense that's what I suggest that you do as I say the templates can have all this in place um and maybe just go to the start of the video and you can see the colors that I've used and then you can do the same for priority as well so you I'd probably suggest having different colors to the the status just to make sure there's not you know confusing you don't want say um Two Reds or your eyes could just be a little bit you know it doesn't really make sense so make sure they're kind of different colors but yeah that's how to create a project track in Exel I hope this video has been useful as I say Link in the description below if you wanted to pick up this template it's going to be less than a cup of coffee uh and you're going to get all this without having to build it but if you're still here with this video chances are you've already got this built um but as I say hopefully this has been useful if not go back to the start of the video and you can kind of copy it from that screen pause the video and and kind of copy it from there hope you hopefully you picked up some XL tricks as well along the way but with that said um thank you for watching please like the like this video if it was useful that helps to tell me I should produce more videos like this and do consider subscribing to my channel if you haven't already I've got so much project management content on there especially around building project artifacts like this project plans those kind of things project road maps so I'm I'm hoping that that they're useful for you but with all of that said I hope you have an excellent day