Streamline Your HR Processes with Bills Template Google Sheets for HR

Bills template google sheets for HR

hello and welcome to this video in today's tutorial I'm going to show you how to create this mini income and expense tracker so the way this works is you're going to double click in this cell and you're going to select a month so I'm going to select any day from the month of March and this is going to update to the corresponding month and year and then you're going to come into this income section and you're gonna add all of your income sources so let's add a new one let's do other one and then you're gonna sign an expected amount to each of those income sources so let's do forty dollars as you can see I have nothing in my actual amount right here and that's where these income transactions table comes in so first you're going to set the day of the month in which this transaction occurred so let's say it happened on the 12th and as you can see this date is going to automatically appear so you don't have to type the entire date but you will still know what day of the week this was and then you can select one of your income sources so let's do other one and you can had the amount you received in that transaction so that could be ten dollars and that's what you're gonna see here and if I received another ten dollars in a second transaction I can just select that from the drop down and that will be added up here and then as you can see the difference between what you expected to receive and what you actually received will be shown here and then if you scroll to the right you're gonna find the expenses section that works pretty much the same way so you're going to enter your expense categories here then you're gonna enter your expected amount your budget so that can be maybe 300 and again you're gonna come in here you're gonna set a day and then you can select one of your categories from the drop down and these drop downs are created using all of the information that you entered here and then I assign money to that transaction so I'm going to show you how to create this from scratch in a new tab and as usual I'm going to try to show you as many features and as many formulas as I can and one very important thing to remember when you're following my tutorials is make sure that you follow along and do everything in the exact same cells that I'm doing it this way if you run into any issues if something is not working as intended you can come into my file and compare it with yours and everything will be in the exact same place so let's get into it so I'm going to start in this new blank sheet and I'm gonna name it April you can do this with any month that you want month but I suggest you follow along with April as well just to make sure everything looks exactly the same in the end so the very first thing I'm gonna do is I'm going to create this section right here so I'm going to start by selecting the group of cells starting on Cell B2 all the way to cell A3 and I'm gonna merge them together and then I'm going to go into Data while that group of cells is selected data validation and I'm going to click on ADD Rule and then I'm going to click on this drop down option and I'm going to select as criteria the is valid date option and then I'm gonna do reject and then I'm going to check this box that will show a help text in case you enter something that is not a valid date once I select all of these I'm going to click undone and when I double click on this group of cells this is what I should see this calendar will pop up so I'm going to select any day from the month of April and this is what I'm gonna see and what I'm going for is I want it to look like this so I'm gonna start by giving some formatting to the entire sheet so I'm going to click here on this little square right here to select the entire thing and as I always mentioned in my tutorials this is something that I like to do to make sure the formatting is nice so I select this Center option I select this align in the middle and then I select this wrap option so that way I make sure that everything that I input from now on will be centered so then I'm going to select this cell and I'm going to click on format number custom date and time and I'm gonna find the one that has just the month and the year and if you can't find it you can build it yourself by selecting these options using this arrow and then you can also delete things so I'm going to select this month and year option and I'm gonna click apply and then I'm just going to have it be a bigger font maybe let let's do 20 and then I'm gonna bold it and I'm going to make this a bit taller and then I'm just gonna wrap this in a black border so now that I have this I'm going to skip the summary part and I'm going to jump directly into creating this income summary section so I'm going to start on Cell B10 and I'm gonna select this group of cells all the way from B10 to E11 and I'm gonna group those and then I'm gonna write income summary and then this next row is going to be the total so for now I'm just gonna do total and I'm Gonna Leave This blank and then I'm going to write my labels on row 13 so this is going to be income sources and then I'm gonna have my expected amount right here I'm gonna have my actual amounts here and I'm gonna have my difference so I'm gonna make this column a bit wider just so all of my rows are the exact same size I'm gonna make this a bit wider and then I'm gonna start entering some income sources as an example so let's do paycheck one and then let's do paycheck two and then let's do side hustle so let's have those three just for our example and then I'm gonna give this expected amount so I'm gonna do 100 200 and 300 and then this one is going to be filled automatically from my transactions table that we haven't created yet so this is as far as we're gonna go in here and then I'm gonna make column f a bit smaller same with column A just so I can keep everything inside so now I'm gonna move on to creating this income transactions table so this has four columns so I'm going to select for columns starting on column G and then two rows and I'm gonna wrap this and I'm gonna name this income transactions so right now we're starting with the layout and getting it ready so we can start adding all of our automations and functionalities so again in here we're gonna have the totals and I'm gonna do the total label on cell i12 just because these income transactions table will only have my actual amount this is where my total will be I'm not gonna add that right now and then in here I'm gonna do my labels so we're going to start on Cell G13 with the label full date and then we're gonna do day income source and amount again this a bit wider so I want this to start looking like a currency so it looks nice from the beginning and this is a shortcut that I really love and makes my life so much easier so in order to select this entire area without having to scroll all the way down I'm going to select this three cells on this just this one row and then on my keyboard I'm going to press shift control down arrow a couple of times until it brings me all the way down so that's a really quick way of selecting an entire area without having to click and drag all the way down and then to format your currency you have several options you can just click on this format as currency button and the symbol you see here depends on the country that you have selected in your spreadsheet you can also go to format number and then select one of these options right here you can click on custom currency and select one of these options and you can also just come into this custom number format area and select one of these options so I'm going to select this one this is the one I like to use on my spreadsheets but it's entirely up to you the one you use so now that this is done I'm going to move on to creating this functionality in which you just added day and then the full date is created automatically so I'm gonna break this down in a few steps before we reach the final formula so the reason I created this the way I did is this is actually a full date so as you can see right here this is a full date and that means that we can grab the month and the year very easily so I'm gonna use these cells right here but this is just I'm gonna delete this afterwards so if I do month the month formula open parenthesis and then select this cell close parenthesis it's gonna give me the number four which is the month of April and then if I do the same thing but with the year so I do year I select this cell and then I close parentheses I get 2023. so as you can see I'm able to know the month of my current tab I can know the year and if the user inputs a day then I will also know the day so how do we transfer all of these into this formula so I'm gonna use the date formula so if I do date open parentheses you can see this explanation of the formula if you enter a year a month and a day you will get a full date so let's do it manually let's do 2023 the number four for the month of April and then I'm gonna select this they sell right here and I'm going to close parentheses so if I press enter I have a full date now let's try to do the same thing but this time instead of manually entering this 2023 and this 4 for the month of April I'm gonna pull this from this section right here so I'm going to start with the year I'm going to select this 2023 I'm gonna delete it and I'm gonna do year open parenthesis I'm going to select this cell right here and then I am going to press f4 on my keyboard which will fix this cell in my formula and all it does is it's adding these dollar signs right here so you can add them manually or you can set your cursor here and then press F4 and then I'm going to close parentheses and I'm still getting my valid date now let's do the same thing for the month so I'm gonna delete the number four and I'm gonna do month open parentheses I'm going to select this cell I'm gonna fix it using the f forward key on my keyboard then I'm gonna close parentheses and enter and as you can see this is now completely Dynamic so if I change this to maybe let's say day 20 then this is changing properly and if I were to change the month in here then this is also changing now I have may right here so that's how we make that completely Dynamic so now let's format it so it looks nicer it looks like this so I'm gonna use the same trick that I showed you before I'm going to select this cell and then I'm gonna press on my keyboard shift Ctrl down arrow a couple of times I'm going to scroll up just so you can see the changes and then I'm gonna click on format number and I'm gonna do custom date and time and I'm gonna find one that I like so I already know this is 2023 but I would like to know what day of the week that was so I'm going to select this one and then I'm going to click apply so now if this changes everything gets updated properly now what happens when this is empty when this is empty my formula is still trying to do something but I don't want it to do something so I'm going to come into my formula and I'm going to add a condition so I'm gonna do if open parenthesis and then my cell h14 which is the one holding the day is not empty then I'm gonna add a comma and then this will happen so if my cell h14 which is the one holding my day is not empty I want this to happen and if it's empty I want nothing to happen so I'm just gonna add a comma and then I'm gonna close parentheses so nothing's happening and if I add a day then something's happening now I want to replicate this behavior in the entire column and there are several options one of them is clicking and dragging the formula which it works but then it would take too long just clicking and dragging all the way down you could also copy it select the entire column and paste and that also replicates the formula so this one is great you could use this method but I love showing you different features and as many formulas as I can so I'm gonna delete everything I did so you're gonna come into the formula that we have right here here in this first cell and we're gonna add the array formula at the beginning then we're gonna open parentheses and we're gonna close parentheses in the end so we just wrapped our entire formula that we had within the array formula so now we're going to come into this h14 and we are going to extend that range all the way to the end of the column and we're gonna do the same thing here so you do a column right after age 14 and then H as you can see something happened here and this is extending the range from h14 all the way to the very end of the column I'm going to press enter and now this formula was applied to the entire column from this one cell so if I come down here Wherever I Go this is going to work all the way to the very end of my column and this whole functionality is being created from this one cell so that's one of my favorite formulas but as I mentioned before if you find it too complicated you can just copy and paste but what this does is it will extend these functionality this formula it will extend it and apply it to the range that you ask it to so every single row this will be checking if column H has a value and if it does then it's going to use this date formula to create a full date and as you can see the year is fixed so they sell the month is fixed to desell but the day is dynamic and it's going to be using the day that was entered on the corresponding row if there is one and what's funny looking about this is this has no formula so if you look at this you could think there's nothing here but if I enter a day then my date gets created properly again there's no formula here there's no formula here but if I enter a day then it gets created properly over here now if this is the first time that you see the array formula I understand if it's complicated I will continue using it throughout the tutorial I just want to make a pause and let you know that the first time I saw it I was super confused so if you're confused it's okay I'm gonna continue using it for now if you still can't wrap your head around it you can just pause the video copy the formula see that it works properly that you copied it properly and then I'm going to continue using it and if by the end of the tutorial you still want to know more about it please let me know in the comments down below so let's continue now that we have this I'm gonna delete this taste right here now that we have this I'm going to create this drop down functionality that we see right here so this drop down is created with the options that you entered in your income sources so I'm gonna come into this income Source area in my income transactions table so this is column I and I'm on Cell 14 so I'm gonna select the entire column by clicking on this cell then shift control and down arrow until I reach the very end I'm just gonna come back up so you can see and then I'm gonna click on data data validation and I'm going to add a rule I'm going to add a drop down from my range so I'm going to select this option from the criteria and then I'm gonna do Advanced options and this is something that recently launched in Google Sheets the option to see this as a chip but I actually prefer the older way which is just a narrow so I'm going to select that option in the advanced options I'm going to select this reject input and then I will select this show help option and to select my range I'm going to click on this button right here and then I'm just gonna select this one cell the very first option of my income sources so that cell b14 in and then I'm gonna do a column and I am going to input the letter B which will select the entire range from cell b14 all the way down to the very end whatever that cell is that's why I only do B without any number afterwards click ok so once you do that you're gonna click on done I'm going to close this and if you come into these drop downs right here you're gonna see the options that you entered here so if I do another one then that appears right here and since I selected my range to use everything in this column starting on cell b14 if I do new one then that's also going to show in my drop down so once we do that what we're looking for is we want this actual column to be updated depending on what you selected from the drop down and the amounts that you entered here so in this case if I enter two transactions for paycheck one each of ten dollars what I want here is I want to see twenty dollars right here so before I go into that I will give it a bit of formatting just so it's easier to look at so I'm gonna take this label so I'm gonna select them and I'm gonna bold them same here I'm also going to bold these titles and then I'm going to make this font bigger this is not looking like a currency so what I'm gonna do is since this one already looks the way I want to I'm gonna select the entire column this is another trick so you're gonna select the entire column you click on this cell shift control down arrow a couple of times I'm gonna come back up so just so you can see and then I'm gonna click on this paint format button right next to the printer and what this is going to do is it's going to sort of select the format of this entire area and wherever I click that same format is going to be applied it's green right now so it's sort of activated so if I immediately click on this cell that same formatting is going to be applied so this is another one of my favorite features now I'm gonna move on to this actual column so as you may recall whenever you add an income Source new source and you assign transactions to it so let's do 20 it's going to get added up automatically in this actual column right here so what I'm gonna do is something similar to what I did here I'm going to start by creating the formula for this one cell and then I'm going to use array formula to have that same functionality applied to the entire column without having to touch any of these cells so I'm going to do some if and then as you can see it's expecting a range a Criterion to compare it to and then the range that I want added up so I want this range inch i14 colon I to be compared to this one cell and if it matches I want these cells to be added up so as you can see in these two ranges I ended them with colon I and then colon J which is expanding the range all the way down to the very end of the spreadsheet without me having to enter an actual row so if I were to enter for example here cell j19 my range only reaches this row 19 but if I delete the 19 I leave just the J at the end it goes all the way down so then I'm going to close parentheses and I get 20 which is what I'm going for and if I add another 10 then I get 30. I'm gonna add 100 and this is going to keep on updating automatically but then nothing is happening down here so before I extend that all the way down I need to add something else I want to make sure that this formula will only be executed if there is an income Source here if the cell is blank then I don't really want anything happening so I'm gonna do if open parentheses and then I'm going to select this cell is not empty comma then this is happening and then if it's empty just a comma and close parenthesis meaning nothing is happening so once I click that it's all good but if I delete that income Source then nothing is happening so now I'm gonna extend this functionality all the way down so first I'm just going to assign some amounts to other options so let's do side hustle I'm gonna give that 300 so I assigned these amounts but there's nothing being added up in these other cells and the way I'm gonna expand this is I'm gonna do array formula at the beginning open parenthesis and close parenthesis in the end so I make sure I wrap this within the ra formula and then I'm gonna come into this b14 option and I'm gonna do colon B which means I will be expanding this all the way down and then in this sum if I also I'm going to expand column B so I'm going to do colon B and then I'm going to press enter and now the same functionality is working on every single one of these options if I did YouTube I have nothing here but if I add a YouTube transaction let's do 20 then it shows here and if I add another YouTube transaction 25 it's getting added up and this will work all the way down so if I were to do a new source and then I created a transaction right here new source I give it 100 then this is getting updated this is the array formula doing its magic from this one cell if you scroll down you get these values and it looks like you actually entered something but if you delete it I'm pressing my delete key in my keyboard it sort of comes back because it's not manually entered and as you can see this cell it has nothing in it it has no formula no value I really love this formula but it's really weird looking so again if you're confused just pause the video and copy these exact same formula and maybe try to look at it for a minute and see if it's starting to make sense to you and something very important to note is that when you do this if you then go on and manually edit the cells that are being used in that array formula range you're gonna get a narrower so I try to manually enter something here but I have the array formula applying this functionality to the entire column so if I enter something manually I'm gonna get this ref error and if I delete what I entered manually then it works again now I would like to check if everything is being added up properly so I'm going to add a sum formula right here I'm going to then click on Cell D14 and I'm gonna do a column and then D and then I'm going to close parentheses so I'm basically just adding up the entire column and then I'm just gonna copy this cell I'm gonna come into cell J12 and I'm gonna paste it so it's going to apply that same sum formula but to this range in column J I should be getting the same amounts I'm going to bold them which I am getting so everything is working properly and then I'm gonna use the array formula once more so if I were to do equals my actual amount minus my expected amount I'm gonna get this positive number because I earned more money than what I was expecting and again how do I use the ray formula so it gets applied to the entire column so so I come into the cell and I do array formula before what I already had open parenthesis and close parentheses so I can wrap it and if I leave it like that nothing is happening but if I come into this D14 section of the formula and I add a Colon D afterwards and then I come into this c14 section and I add a colon c and then I press enter it is now being applied to the entire thing and it works the same way if I manually do anything to it I mess it up so hopefully a formula is starting to make sense to you if it didn't from the beginning and now I'm just gonna copy this cell and I'm gonna paste it here and I'm gonna paste it here so it's just adding up its corresponding rows so now that we've reached this point I think it's a good idea that we start making it look prettier since the expensive section is going to be way easier because it works pretty much the same way so there's just a few tweaks that we're gonna have to make and then afterwards we're just gonna make this summary table and create this graph and then we will be done so I'm going to start just making this income section look pretty so the first thing I'm gonna do is I'm going to select this entire thing again you select one cell shift Ctrl down arrow all the way down I'm gonna wrap that with a black border so I'm going to click on this borders button and then I'm gonna select the black color and then I'm gonna click on outer borders so I'm gonna do the same thing here shape control down arrow and I'm going to give it some outer borders and then I'm going to get rid of the grid lines so as you can see in here I don't actually have any grid lines I just did this myself with a lighter color so I'm gonna get rid of the grid Lines by clicking on view show and unchecking these grid lines option so I'm gonna give this one a green background with some white font and then I'm gonna do the same for this section green background with a white font and then I want to make sure that I'm never editing things that I shouldn't be editing by accident so this column the actual column as you may we go we have this formula up here that handles the functionality for this entire column and if I type in something I'm gonna mess that up so I'm going to add a gray background to everything that is supposed to be automated and that I shouldn't be editing so that means these two columns shift Ctrl down arrow and then I'm going to give it this second gray background and I'm also going to give it some white inner borders so I'm going to come into the Border section I'm going to pick this white and then I'm gonna click on inner borders and then I'm gonna do the same thing for this section which I also shouldn't be editing I'm not going to add borders to that I'm going to bold this text and then this column is also automated so I'm gonna do the same thing shift control down arrow great background with white inner borders and then the transactions table I'm gonna have this be a darker gray this is going to be a darker gray these fourth gray right here and then this is going to be the lighter gray I'm also going to pull this and then I would like this to be closer to the text so I'm going to align it to the right and then I'm going to select both of these and I'm going to click on this italic option I'm gonna make this a bit taller and then I'm also going to add grid lines to this area that you are supposed to edit and that's going to remain with a white background so I'm going to select those two shift Ctrl down arrow and then I'm gonna give it some very light inner borders I'm going to click on the second gray inner borders and then the same thing here the second grade inner borders and then I'm gonna make the font for these two maybe a 13 and then this ones I will make those 12. I'm gonna delete this I'm not gonna use that anymore and then I'm going to alter the width of my columns so it looks a bit better and you can alter multiple columns at a time so if I select this three and I alter the width in one of them they all change I can also select columns that are not right next to each other by clicking command and clicking on the column that's command on Mac I'm not sure what that would be on Windows and then I'm going to make this smaller and they will all be the same width so I'm really liking how this looks and now I'm gonna move on to making the expenses section so I'm going to select both these tables I'm going to select everything including this column in between for this one row and then shift Ctrl down arrow a couple of times and then I'm gonna copy this area I'm gonna come back here and then I'm gonna go into the L10 cell and I'm gonna paste I'm gonna make this a bit smaller so right now this looks exactly the same but this drop down is not being tied to this section it's actually being tied to this section right here so if I add a new income Source here you're not gonna see it here but if I add a new new income Source here then that shows up that means that this one is actually being created using this which we don't want so first I'm gonna delete all of my income sources and expected amounts I'm gonna make this wider I'm gonna change the color just so we know that we are working on a different section so I'm going to change this to maybe a purple and then I'm gonna call this expense summary and this one I'm gonna call expense transactions now this I'm gonna call expense categories this I'm gonna call Budget and then again expense category right here I'm gonna make this wider and everything else I'm gonna leave the same so this formula right now I'm not gonna touch this because it actually works properly if I change the number right here everything is updated properly so this one works fine and as you can see in the formula if I press enter you're going to see that it's actually using this column instead of this one so that's exactly what we need and it's still using this cell to pull the year and the month and that's happening because we fixed it so no matter where I put the formula this will change depending on where you copied and paste the formula but they sell since I fixed it it's going to always be tied to this eight cell right here but this section of the formula since I fixed it using those dollar signs it's always going to be tied to this cell right here so that works fine no need to change that but this one we need to change so I'm going to select the entire column again I'm going to click on this one cell shaped control down arrow a couple of times I'm gonna come back up and I'm going to do data data validation and I'm going to click on ADD Rule and then I'm going to select the drop down from range criteria and I'm going to click on this select a range option and I'm going to come into this expense category section I'm going to select the first cell which is in column L row 14 and then I'm gonna do column and then l so that will select the entire range from l14 all the way down and I'm going to click OK and then I'm going to click on done and I'm gonna get asked this question and this is happening because since I copied and pasted those drop downs from the income section it's sort of treating them the same way but I actually don't want them to be connected in any way so I'm going to select the Justice instance option and this is really important if you don't click on the Justice instance option you are going to miss the income drop downs and we don't want that so you're going to click on just this instance and now it's going to give you these errors so I'm gonna come in here and I'm gonna delete these options and I actually forgot to make it look like like this instead of making it look like a chip and the reason I don't love the chip let's fill it up a bit so let's do rent and then let's say you select rent and that looks fine and if I delete then it works fine but if I delete Again by accident I actually got rid of the chip so I don't love it you can easily bring it back you just copy it and paste it from another one that is still working properly but I'm just still more into this look but you can absolutely leave it as a chip but to have it look like this I'm going to click on one of the drop downs and I'm going to click on this edit button and I'm gonna go into the advanced options and I'm going to select arrow and then I'm going to click on done and again I'm getting asked if I want to apply this just to this instance or applied to all in this case in the previous step I already untied these drop downs from the income part so now this is completely independent from that other part so I can use this apply to all option which will apply it to my entire column without applying it to the income part because those two are no longer connected they no longer have to do anything with each other because in the previous step I clicked this so now I can do apply tool and it gets applied here and wherever I click I see rent and if I come back to the income one it still works properly I didn't do anything that messed that up in this point just come back into the income transaction section and make sure this still has your income sources just click on a couple of drop downs and make sure you're still seeing your income sources and then you click here and make sure you are seeing your expense categories if it looked like this then everything's fine and you can move on so I'm going to add a couple more let's do groceries so I'm going to delete this amount and right now I don't have a budget for these so I'm gonna do 10 20 30 and then if I come here and I add rent and then I add an amount then the actual amount gets updated and the reason this is working right out of the box without having to change anything and just by copying and pasting the income section is if you look at this formula as you may recall this one cell is the one that's controlling the entire functionality so if you come here and you look at this formula you're gonna see that there are no dollar signs that means that I'm not fixing the formula to any specific column or any specific row so whenever I copy it and pasted those columns are going to automatically shift around so as you can see if I were to delete this and then I were to come into this income section and I click on this formula you can see that I have column B column I column B column J but if I copy that and I paste it here all of those columns were shifted around so if I were to fix this then it wouldn't work if I were to fix these values using the F4 technique that I showed you before so you just place your cursor on the cells and press f4 on your keyboard then this one works perfectly but then if I copy it and paste it then it's using all of these columns right here so if I press enter I can sort of highlight The Columns that this is using and since I use the dollar signs to fix them it's actually using the ones in the income section so if I undo this my dollar signs are no longer here so if I copy and paste it's gonna work perfectly and as you can see I just highlighted the cells that I'm using which are these columns which is exactly what I was looking for so in case you were wondering why this is working out of the box that's why the same goes for this one it's actually shifted so it would use these columns instead and then this one it shifted so this would use this column instead but it was fixed to this month and year sale so I hope I explained this in a way that makes sense to you if it doesn't please let me know in the comments below and maybe I can do an example that is just focused on this now I'm Gonna Fill This up a bit more before we move on to the summary section in and now something I like to do is I like to get rid of extra columns that I really don't need so I'm gonna delete those to leave just this one final column in the end and now I'm gonna create this summary table right here so I'm going to select a group of cells so I'm going to select everything from cell B5 to cell E8 and I'm gonna add a black outer border to that one and then I'm gonna maybe make it a bit taller and then I'm gonna add my label so I'm gonna do summary then I'm gonna do expected actual difference and then in here I'm gonna add income label this one's going to be expenses and then these will be amount left so then I'm just gonna very quickly give it some formatting so it looks pretty from the beginning so I'm gonna give this a blue background with some white text and then I'm gonna make it a bit bigger font size 11. I'm gonna bold this and then this one since this will be automated I'm gonna give it a light gray background so I remember not to touch it with some white inner borders and then this one I'm gonna give that maybe a darker gray background I'm gonna add one white border at the top and then also some inner white borders and I'm gonna select these three rows and I'm gonna make those a bit taller so as you can see they all changed at the same time because I selected them all at once and now what I'm looking for is I want my expected amount from my income section to show here so I'm just gonna do equals and then this total right here and then my actual amount from my income section I'm gonna pull that from here cell D12 and then the difference I can and either just select this one or I can do equals the actual amount minus the expected amount and that should give me this exact same number so I'm going to do the same for the expenses section equals and I'm going to select base expected amount the budget cell M12 and then the actual amount I'm gonna do equals and I'm going to select cell n12 and then I'm gonna do equals actual minus expected and I get this minus 10 and then finally for my amount left I want to know how much of my income I will have left after expenses so I'm gonna do equals minus expenses what you can do is either just do the exact same thing income minus expenses or you can copy this formula and then select these two cells right here right click base special formula only this way you will make sure that the formula will be pasted there it will adapt to their corresponding cells because I didn't fix anything but the formatting will be respected so I will still keep my border I'm going to highlight the actual one because I want it to stand out and now as you can see from my example I like this to change color depending on the situation so for the income part if I get a positive number that means I made more money than I expected and I had that turn green using conditional formatting and in the expenses section if I have a negative number I spent less money than I expected which is the case here and so I want that to turn green and if I spend more money than I expected then I want that to turn red and if I made less money than I expected I want that to turn red so I'm gonna do that with conditional formatting I'm gonna start here and then I'm just gonna very quickly do the same thing to these other sections so I'm gonna come into this difference for the income part I'm gonna click on format conditional formatting and I'm going to select the less than option first so if it's less than 0 Pro then I don't want anything to happen to the background but I want my font to turn red and then I also want it to become italic so in this case I earned less money than I expected so that's a negative number and it turns red so I'm gonna click on done and then I'm gonna add another rule since I still have this selected so I'm gonna add another row and I'm gonna do greater than zero no background and then I'm gonna pick a very dark green also italic and then I'm gonna click on done so if I come here and I pretend I made a lot more money than I expected then it's gonna turn green in this case I made 254 dollars over what I expected and it turns green and if I made less money it turns red so I want that to happen here as well so you could click here shift control down or a couple times select the whole thing and do that all over again or I'm gonna show you another trick so you can select this one cell and then you can click on format conditional formatting and you're seeing these two rules so I can actually add another range into these two rules manually so I can apply that in here as well so my range is cell e14 and all the way down to the end of my spreadsheet so if I select one of these rules and I do a comma and then I do e14 colon e and I press done then that also got applied to this range and now this row is being applied to cell E6 but it's also being applied to this entire range starting on cell e14 and going all the way down so let's do the same thing for this one I'm gonna do a comma in 14 colon B done and now that got applied in here as well now I actually want the same thing to happen here so the amount left if I get a negative number that means I have less money left than I expected so I'm gonna do the exact same thing I'm gonna add this cell to that range which is cell E8 so I'm just gonna do coma E8 done and that got applied I'm gonna do the same thing for the green one E8 done so now all of these have that same rule applied to them and I actually forgot about this one so I'm going to go back and do that as well comma tough done comma E12 which is holding this difference done and now my rule was applied to this entire thing and if I were to change this maybe I also want to bold it then Everything Will Change at the same time if I wanted to underline it then they are all changing at the same time so that's why I like to have them all wrapped together in the same rule instead of creating the rules individually so now I'm gonna close this and I'm gonna do the same thing for the expenses section but this time the logic is reversed if I get a negative number that's a good thing because I spent less money and if I get a positive number that's a bad thing because I spent more money so that turns red and another thing you can do is you can select multiple cells at a time and apply the formula to all of those at the same time so I use the command key on my Mac to be able to do that I'm not exactly sure how you can do this on Windows but what I do is I'm going to come into this different section and I'm going to select this column first so once you have that you can just go on selecting other individual cell while still keeping this range selected so I'm gonna click on the cell 014 shift Ctrl down arrow and then I'm gonna leave that selected and I'm going to press command on my keyboard if you're on Mac I'm going to try to figure out what that is on Windows and if I do I will leave it in the comments down below so I'm going to press command and then I'm going to click on the cell and that's selected that one cell without touching anything else and then I'm gonna press command on my keyboard again and I'm going to click here now I have this range selected and this cell and the cell selected as well so now I'm going to click on format conditional formatting and whatever I do will get applied to all of those cells that I have selected so I'm going to do greater than zero no background and greater means bad so that's going to be red I'm going to do italic and done and now I'm gonna click again add another Rule and then I'm gonna do less than zero no background I'm gonna have these become a green italic and as you can see that was applied to this entire thing to this total and then to this total as well I'm gonna click on done now I'm going to close this everything works properly everything is connected and now I'm just missing this chart right here so this one I'm gonna create by selecting everything from cell B5 to cell D7 without selecting the difference and without selecting the amount left and then I'm gonna click on insert chart and that inserted my chart automatically and then I can use this section to the right to edit the way my chart looks so I don't really need this title so I'm just going to click there and I'm going to press delete so I can have more room here and then I'm gonna make this font smaller again so I have more room so you select it and if it doesn't pop up automatically just go to customize chart and access titles and then I'm gonna make this font smaller let's do maybe 14 so yeah that's better and then I want to change these colors so I'm gonna make this expected ones I want to turn it yellow so you come into this fill color section and you select maybe a Jello and then I want the actual one to be maybe orange so you can either select it from this drop down or you can just select it directly on the chart and then click on fill color and then make that orange and we're done what you can do is you can just each month you can duplicate this tab and then double click and select a different month and make sure if you delete something make sure you only delete cells that have a white background so this way you can start over you can also just keep a blank tab so you can create copies from that one and you can rename this so that's it for this video I hope you liked it this did end up being a very long video so I hope you don't mind and I hope that if you followed along you learned something please let me know in the comments down below if this was too hard if this was too easy if you're still confused about some of the formulas and remember you can find the link to this file in the description down below so if you are confused if something is not working properly you can go into that file and see the formula directly and copy it and paste it into your own spreadsheet and if you followed along exactly in the same cells that I did that should work fine and of course this is not the only way you can do this this is just the way I did it for the tutorial so if you found a better formula or found a better way to do something please go ahead and use that in your own template and if you're stuck till the end please let me know in the comments and thank you so much for watching