Create Efficient Workflows with the Google Sheets Billing Template for Management

Google sheets billing template for Management

okay so in this video we'll be creating an invoice template from an invoice template now that's a sentence i never thought i would say so let's go under file and i'm gonna go new and from template and this should give us some pre-made templates that come with google sheets right so if i scroll down a little bit we have this work section and one of those is invoice so i'm going to go ahead and click on this zoom out a little bit so this is the template we have so we're going to take this use whatever the designer did here to make this look really nice and make it better from our perspective there are some things i'm going to change here so i don't want this invoice over here i would like to move that here on top i'll go ahead and do that so i'm going to take both of these cut which is ctrl x or command x you can probably also do right-click cut and then go over here someplace and do command v to paste it and apparently that had a lot of merged cells with it so it created those extra columns with it but i'll deal with that in just a little bit i would like to move this invoice numbers and all that stuff here someplace so i'm gonna take this and again cut it and place it over here maybe i'll do it right here on the left just like this so let me unmerge these so i'm going to select these go under this and on merge i'm also going to unmerge these i don't really need this submitted on delete that so invoice number i'm gonna move this invoice number here and that seems to be merged too so i'm gonna unmerge these and then i'm gonna move a copy of this down so one of these will be the date and the other one will be the due date so that should be okay and if you wanted more of this you can obviously just repeat this again to have some extra things here maybe i'll move this one up so i'm gonna take this again control or command x go on top here and paste and here i don't need a due date anymore as a matter of fact i'm gonna unmerge all of this stuff now if you need this payable to etc you can keep it you can even move it a little bit here if you wanted to here i don't need this much space so maybe i'll delete one or more rows a couple of other things i'm gonna do here here where we have the total i don't want this color for this so let's just change it to something like that and make it a little smaller and i'll use this design for total price i'll just copy that and paste it oh apparently this is merge 2. let's get rid of that and this is in the wrong cell then so we're going to cut and paste it here again and we're gonna move this thing a copy of that in here so we can have some text total i'm gonna add a line here to push this down a little bit so that's our total i have this extra columns now because of those merged cells i had so i'm going to delete that get this back to this the same voice probably too large let's bring it down a little bit now we have subtotal here we have adjustments we could add something like tax percentage and tax assuming you might have lines like that too so i'm basically just gonna add a couple of lines below this and you don't have to do it if you don't have them obviously and i'm simply just gonna copy and paste this this one will be the tax rate and this one will be the tax amount and i'll just type some percentage here i will add one more line here and we'll have something like adjusted total and let's make this a little bigger to match this so this 14 let's make this 14 too so now let's take a look at this to see what this looks like so if i go under file and do print and we're going to change this from landscape to portrait so it fits on one page looks pretty good so i think they did a pretty good design on this i'm going to cancel that again you can change this colors and stuff if you wanted to now the main thing we're going to work on here is the formulas first of all let me just take care of this part really quickly because that's going to be pretty easy so we have the subtotal for now we'll keep what it is which currently is just the sum of this column adjustments it's just a number that's entered that's fine adjusted total is going to be our subtotal plus our adjustments that plus that 500 the tax rate is going to be whatever the tax rate is and this is going to be a calculation which should be our adjusted total multiplied by that percentage so again this is just a basic this plus this and this is our adjusted total multiplied by the tax rate so we got the tax and our total balance will not be this anymore so we'll take that off and it's going to be our adjusted total plus the tax so this grid over here if i go here let's say we need to add more items and i add a row see how it goes gray white gray white which is great so that's done using this alternate colors so if i open that see it shows me the range that was used for alternate colors and if you wanted to change that you can change the gray to some different color and white to different color but i'm going to leave it as is i like the design but that's the reason when i'm adding a new row it automatically formats it as it's needed to be formatted but the problem with this is that this new rows when i go here and type quantity and i type price the total is not gonna populate so we're gonna change that so we want this to be more dynamic so let me delete some of this i also don't want the zeros here basically i want to only do the calculation if there is some quantities and prices entered and the other thing that could be potentially a problem here is this total so right now this total refers to this range so let's see what happens if we decide apparently they have some hidden stuff here so let's see what happens if we decide to go in here and add maybe a new line if i go to this see it's no longer including that in our total which is not great so i'm going to undo that so that's primarily what we're going to work on we need to make sure that this is auto-populating and this is changing automatically so let's see how we're going to do that so usually if you're doing something like this you would use an array right i'm going to demonstrate this here on the side and then we'll kind of bring that over as we get it to work so for example if i was trying to do an array i would say let's take all of these and multiply by all of these and then i would do an array formula wrapper for this which you could type or you can do ctrl shift enter or command shift enter to get it around this thing so now if i hit enter you'll see how it basically drags the formula down from this first cell going over so with this new setup if i just go here and add a new row see my formula recalculates it gives me the total which in this case is zero and if i go here and add some quantity in price this also recalculates but it's likely not gonna work if we add it to the bottom see that did not recalculate so now i can go here and type quantities and prices that doesn't do anything so if we add in the middle of the range it's fine but otherwise it's not so it fails so it would be nice if we can make this work even if we add to the bottom of the range or to the middle or something like that so i'm going to undo that i am going to need some space here so i'm going to add a couple of columns here for the time being so let's see how we can make this happen so i need this to work no matter if we add to the bottom to top or in the middle so basically the way i'm going to do that i want to say i want my range to start right below this total price cell or unit price or whatever one of these column headers and i want it to go right above this so in this case the subtotal line so the way i'm going to do that i'm going to go here and use first of all offset function close this so offset function gives us a cell reference so for example i can use this column header as a cell and then after the comma i can say how many rows i want to move down or up from that cell or how many columns i want to move right or left from that cell now in this case i'm going to say move one row down so that's one comma and then the next one is how many columns we want to move right or left so i want to stay in the same column so i'm going to do 0. so if i do this and hit enter see how it went 1 down so that gives us this 200 so let's do another offset and this time i'm gonna take this subtotal comma and then from here i'm gonna do minus one to go one row up comma and zero the same column now there's nothing in that cell over here so we get nothing let's actually type something for the time being so we can see we get that 50. now what's cool about offset is that offset if you pay attention to what this says if i open this help it says that the return is a range reference what that means is that i can take this offset without the equal sign i'm going to remove it out of there and come back in here and then this is my first offset that i did so imagine like this was like f 18. actually i'm gonna undo that for a second so i can illustrate this so if i was trying to do this range i would do something like this see f18 through f22 so f18 colon f22 so we're giving it the first cell and then colon and then the last cell and that gives us the range now i'm going to use that same logic with this now the offset function returns a range reference which means what we could do we can cut that out without the equal sign go back to this this is similar to having that f 18 or whatever it was and then i'm gonna do a colon and paste my second offset function which is the reference to the last cell so i'm going to hit enter for a second and you can see how that now returns well not formatted very well but it returns this range from this cell through this cell now i'm going to do this same thing with quantity column so it's literally gonna be the same formula so if i just copy this hit escape go to this column on the right and instead of f column this is e column so i'm going to change this to e and this to e hit enter and now it should give us 1 and 2 because that's what we have here if i go here and add like a 7 you'll see it shows up in here so now i'm going to multiply these two together to get this total price column so to do that we're going to take this hit escape without the equal sign go back to this formula and we need to take this and multiply by that other formula so i'm gonna put this in parenthesis just to make this clear even though i don't think it's really necessary and i'm gonna multiply it by again parentheses and paste that other offset function and close parentheses again so i'm taking that first range from the first offset that i did and multiply by the second range of offset that i did here on the right so i hit enter right now you can see i didn't really get what i wanted to get i wanted to get this whole thing and that's because this is supposed to work as an array formula and we need that array formula wrapper again for this to happen because when you multiply two ranges you have to use array formula to make it happen similar to what i did before with my basic array formula so i'm going to do that ctrl shift enter or command shift enter to do the array wrapper hit enter and you can see how we got all of these now i don't need this anymore so i'm going to get rid of that formula so now i'm going to go here remove all these formulas they did here and i'm going to replace that with my own array formula which is basically this so cut that paste it in here and you can see we got the same result right now let's try what happens when we add a new row so if i go here in the middle add a row and do some sort of numbers you can see that works as you would expect now let's see what happens when we add to the bottom so if i go to this line 23 and add one below you can see how my formula is still working so that part is good so far now let's see what happens when we add to the top of this though so if i add one above my formula failed and the reason that it failed is that our formula is no longer in that first row where the formula was here on top so as we add a new above this it pushed the formula down and it's trying to now still do the range it doesn't fail because of the range the range is still getting it but it's trying to fill the range now there's one more than it needs so it's covering this so the position of the formula is the problem we don't want this formula to actually be in the first row we want this formula to be somewhere outside of this range where people are adding so this whole situation doesn't happen so i'm gonna undo this so i want to take that formula and somehow put it in here where it says total price so what i'm going to do i'm going to go to this formula that's our array formula just cut this formula for a second so i'm going to go back to this place this is where i want to place my formula so it doesn't get on a way of doing things here so i'm going to go here and just create a new formula right here where the header is and i'm going to start with an equal sign and for now i'm just going to paste what i had so let's get rid of two equal signs we don't need that so the problem with this formula is going to be if i hit enter see that auto fills this but that's not in the right place anymore so this should be one down and we should have a header here on top so i want to create that header on top so what i'm gonna do i'm gonna go here let me do some spacing here this parenthesis and this parenthesis are for this array formula that we're doing here and this is the actual formula we have in the middle i'm going to create an array here by adding a bracket and then i'm going to close this array in the end by adding another bracket in the bottom and i want to add a new row on top of this array that's going to say total price so what i'm going to do i'm going to go right after that curly bracket and in quotes i'm going to do total price quotation again and i'm going to do a semicolon to add a row so the rest are going to act as a row below this first row which is now gonna be total price so let's hit enter now to see what happens so you can see how now we have total price on top and we have all of these totals but there is really no formula here anymore in this range of totals it's a formula on top here that just populates this whole thing so now let's try this again so let's add a new row here on top works just fine as you can see so add in the middle works just fine add in the bottom works so now we have an actual working formula that will automatically populate this totals without having to come back and make any changes and there are really no formulas in front of our user here it's basically hidden in our total price header right here on top of this whole thing so now let's clear some of these totals so the last thing i would like to do with this formula i don't want this to calculate and do any zeros here until we put some quantities here in this column so to handle that we're going to go back to our formula and this was that previous formula with offsets and everything that was doing what it's doing so that's everything that's happening after the semicolon so what i'm going to do here i'm just going to add a little bit of if statement to handle that situation now the if statement needs to be on that quantity column now if you remember the way we did that quantity we did it using this offset when we started no actually that's not quantity the quantity is this e column right here this thing right here so from offset plus one to minus one from this cell on top through this cell in the bottom so i'm going to just copy that part you want to be very careful with this parentheses because this outside parentheses are for this multiplication i don't want to get that just the range itself i'm going to go in front of this whole thing and we're going to place an if statement here we'll say if open parenthesis and we'll do that if logic in just a second right now i'm just going to say true so we can come back and replace it i'm going to do comma so i'm going to say if true then i'm going to do double quotes meaning to leave it blank comma otherwise what we need to do after this comma we need to basically close this if statement and that if statement is going to close right before this curly bracket so we have the parentheses here so now we have to do the statement so we're going to say if the quantity is blank then we're going to do this blank and that's what we need to do instead of this true so what i'm going to do instead of this tool i'm just going to paste that offset thing i used to have before that's creating the range and i'm going to say if that equals to blank then we're gonna leave it blank the total price part is still staying on top of this so i'm gonna hit enter so you can see what happens see how we cleared up all the blanks so if i go here and add some quantity you can see how the total price starts working again and we do the calculation but if we go back and remove those quantities it doesn't really calculate the price for that particular line anymore so that should do it for our total price formula so there it is we have one formula on top here that takes care of this whole situation and we don't have to worry about adding new rows or deleting rows or whatever it's just gonna automatically work out so now let's take care of this thing so in here we have again the sum function that currently just sums this range up to here so we need the sum function 2 to work with this logic that it always has to go from this cell in the bottom of total price up to this cell right above sum total so i'm going to use that same sort of thing i did before i'm going to remove this and instead of g18 i'm going to overwrite that g18 with offset function so remember it just returns a range so that's basically the part before the colon so the beginning part again i'm going to do the same thing i'm going to say let's take this cell which is our total price comma go one row down which is let me open this so you can see the reference see row offset is one comma and column offset is zero so i want to go one row down from that cell that i just clicked on so right now if i'd entered this should still work so what it does it again finds this cell right below and goes up to g21 now again i'm going to replace g21 also with an offset function so i'm gonna do offset and then this time i want to offset from the cell that i'm currently in i want to go one cell above of where i am where i am right now is g24 so i'm gonna do g24 comma then i want to go one row up so that's minus one comma and i want to stay in the same column so that's zero so i'm going to hit enter so we got 732 now let's see what happens when we do our changes so this should work if we add some quantities here it's no problem now the issue was if we were adding something here to the bottom this was not calculating here in the total now let's see if it does now take a look here it's almost 900 dollars we're gonna do some quantity and price and you can see how that's automatically added to our total no problems now we can add it here no problem to that should add and we should be able to add to the top as well so right now it's about 1300 so let's add one more row buff do some numbers now before i hit enter let me just move out a little bit so you can see that adds up just fine no problem all the new rows i'm adding no matter where they are it's automatically a part of this total and i can go ahead and delete some rows as well and that should just adjust fine for both formulas now the rest of these are going to be fine because they are static so we're not planning to add rows and remove rows and stuff like that so we'll just leave them as basic formulas and here anytime we want to add something we can add anywhere we like and all we have to do just go here and type our description now i've noticed like this one somehow it's not merged so is it because we're adding a new row that's not merging it probably so let's just try to apply that merged cell to all of these all right so now we have that problem apparently when we add a new row here see even though our formulas work this cell they use the merged cell and this is why i hate merge cells and now this is not a merged cell anymore so we need to take care of that so basically what we need to do we need to get rid of all the merged cells and just do one simple row so we don't have this problem so i'm gonna go ahead and select all of this let's go ahead and unmerge all of that now we shouldn't have any merged cells this one is still merged so i need to basically unmerge this entire column something here that goes across see like on merge that let's try this one more time see there's one more happening below so let me unmerge this far and there is something happening here too i hate merged cells so this is all the type of issues you have because of them so up to here it's fine here we have this situation happening so let's unmerge this see as i'm selecting i know something is wrong by the time when i go one more and it jumps to the next column i know something is happening see here so we need to unmerge that as well and i think i'm fine i deleted all the weird stuff so now i'm gonna go ahead and remove the c and d columns so we have just one column so we don't have to worry about this mess so now we can just resize this one to make it bigger so i'm gonna delete this and print and see if that fits seems pretty good so it fits so now when we add a new one we don't have to worry about that whole situation because it's just gonna be one cell so there's no merged cell to fix okay so all of that should be fine and our notes are gonna be now below here so you can write your notes below if you have any notes so that's pretty good maybe we just need a little bit of line above this similar to like this line let me just select this go under borders and we're going to add a line above which is this one let's make it a little gray so it's a little nicer looking this total balance now looks a little too big to me something like this so i think that sits pretty well if i go under file print we should have our invoice template let's just clear a few of these and put some numbers over here so finally i'll just take this adjusted total and make it dark and maybe we'll take our tax amount too and color it the same so basically this grayish ones are the ones we're entering here in the middle if you want to do some adjustments and then here you have your tax rate which is going to be manual entry and i think that's good enough let's just rename this and that should do it for this video thanks for watching please subscribe and i'll see in the next one