Consultancy Invoice Format in Excel for Research and Development Made Easy

Consultancy invoice format in excel for Research and Development

[Music] hello everyone i'm riya from zermatt and today we'll go through how to create this invoice template in excel if you'd rather skip that part and just start using the template you can find a link to the download page from the video summary below we also have plenty of other free templates for small businesses to use and once you feel like your business has outgrown excel templates i highly recommend checking out our software the basic version is free and should already offer plenty of functionality to make managing your invoicing easier but for now let's jump into excel let's start by defining the canvas for our template so select everything by clicking on this corner over here and change the background color to light gray then select everything from a1 to g39 and change the background color for this area to white and at this point we should also change the font to arial and invite should clearly state that it's an invoice so let's write invoice to a2 and change the font size of this cell to 46 bold the text and then we want to select the color for the text at this point you can use whatever color you like and then use that for all the highlights in the invoice but we're gonna go with the specific shade of turquoise uh with this hex code so zero five b three c two okay next we wanna create the line from a3 to g3 so select borders then choose the line color in this case we will use the from recent colors to color color that we just set up and then choose line style go with the boldest one on the bottom here and then just press on this icon to create a bottom border for these cells and voila then we want to add the logo for the header so you probably have some image on your computer in our case we have this one here and just put it there next we'll add our company details so click on a5 and write your company name there the actual company name and then increase the font size to 12 and hold the field into a6 we'll write address line 1 to a7 advanced line two to a a8 uh city and postcode and to a9 phone and with whatever number then select everything from a6 to a9 decrease the font size to 10 and make it italic after this we want to add the customer details so to a 11 we just write 2 and change the font size to 12 again and bold this the a12 will write the buyer company by your name then um buyer company name 2a13 the name of the buyer contact person to a14 there um address to a15 cc and postcode and again um change the formatting for a 12 we or for all of these we'll change the font size to 10 and for a 12 we'll bolt that and for a 13 to a 15 we'll just make it italic almost forgot that we should also change the font color for these so select everything from a 12 to a 15 and go for this shade of gray here the same goes for a6 to a9 and select the darkest gray from here next we'll add the invoice details so select everything from f1 to g17 and change the background color for these cells to light gray then we want to write invoice date to f12 number to f13 client reference to f14 chase order to fifteen and do date with all capitals to f16 then let's select all of these and change the font color to the dark gray we used last time and then bold the last due date field at this point it may make sense to widen this a bit so we'll we'll be playing with the layout more later but but just to make it a bit clearer and change the alignment for these cells to right then we'll start adding the detail server this cell you can always just write it yourself or you can write equals today and brackets to have it always be the current date for invoice number um use whatever is the next number and for client reference well we'll just go with client one in this case but again use your own purchase order something something something due date for this one well if you want to use an automatic formula uh by default then you can go with something like two day plus say 30 days so it's always gonna be that day but you can manually change that also then change the alignment for all of these cells again to the right and now we see that it's on the right edge of the cell we don't that's not gonna look very nice so let's make one more change so select everything from g12 to g16 right click on the selection and go to format cells here we go to the alignment and make sure that we have right indent selected here and add one to this field and click ok so now we get a bit of a margin between the text and the cell border let's also reduce the font size for all of these cells to 10 except for cell g16 which we want to keep at 11 and then bold because this is kind of the key piece of information and then let's move the logo back to the right side next we'll write in additional to a19 increase the font size to 12 and make that bold then select everything from a20 to f20 merge these cells click align them the text left and then top and you can add a bit of height to these cells maybe two two rows and then here you can write any additional instructions or terms that you use in your invoicing and change the font size to for this to 10 and then use the darker shade of gray that we've used before all right next we'll move to the actual table part of the invoice so select everything from a21 to g21 and click on the bottom border icon over here we should have the same settings that we had when we were creating this line over here so just clicking on that should give us what we want yes then we select everything from a22 to g22 and change the line a bit so line color let's use the light gray and line style we'll choose the first one so a bit slimmer line and click on the bottom border so now we have a very um light gray line here we'll do the select everything from a23 to g23 this time and do the same thing click on the border icon do the same for the next line then one after that and then once more so we'll have four lines in this invoice if you need more you can make more uh but then you might have to have a different um row height for for these uh these rows because we're gonna now select all of these so from a uh from row 22 to row 26 so we can press press on 22 and then hold shift and press on 26 to select them all and then right click on any on somewhere here and click on row height and we want this to be 30. so do that then while we have this selected we should go up here and align everything in the middle and change the font size to 10 and that's it next select the row a or the first row so a22 to g22 and bolt that and now we can write our table headers so first one is description send to b 22 we write q t y as in quantity c 22 is the unit and next one is unit price then v 80 percentage then v a t and finally total at this point we might want to do some change small changes to the layout so let's start by wide adding column a which holds the description information let's do the same for d so that the v80 header doesn't start right after unit price then decrease the width of column f so the v80 column is not quite that wide and now we see something funny happening here to fix this let's select everything from a11 to f17 then click on the small arrow up here and click merge across and that should fix it partly but we still need to clean up here let's change the background color to light gray and light gray all right now we can start inputting the actual invoice details so start by writing client work or whatever you want to invoice for to sell a23 then the quantity of this is 5 in our example invoice the unit is hours unit price 60. v80 20 and then um go to a24 write product that's our product quantity 10 unit pieces unit price 105 v80 20 again and uh at this point we want to fix the layout a bit again so let's select everything from a23 to g26 and change the alignment to left next we'll move on to the formulas so let's click on f23 here we want to calculate how much the vat for this line will be so let's start by writing equals then we want to multiply the quantity with the unit price and then with the vat so b23 times d23 times e23 and click ok then to the total column column we calculate the total without the v80 so that's going to be b23 times d23 and we're happy with that then you can select both f and and g23 and pull from the corner down here to the bottom row and now we should have the same formulas implemented on all rows we have the 0 here to get rid of that we need to do some formatting so select everything from again from f23 to g26 and go up here click on this arrow and more number formats this part might be a bit tricky for the less seasoned excel users but you know just follow what i do and it should be fine let's start by clicking on custom so i'm going to create a custom number format where the pound sign is before the the number so empty what's in a reason type and we'll write here first pound then a space tag space hashtag zero comma zero zero then a semicolon then minus pound space hashtag space hashtag hashtag zero comma zero zero semicolon and semicolon so what we actually did here we defined first the number format for positive numbers so this part then the next part is a number format for negative ones and finally the format for when the value is zero so we want zero to be empty in this case now click on ok and voila we should we have empty cells down here we'll want to use the same formatting for the unit price column so select the cell with the format formatting that we want then click on format painter on top left and then select everything from d23 to d26 and we should have should have the correct formatting for the whole column um next let's do the total calculations so to f27 let's write stop total to f28 total v80 and to f29 total amount due at this point we could select all rows from 27 to 29 again holding down shift right click on that and adjust the row height to 22 then select them actually keep them selected and change the vertical alignment to middle then select everything from f27 to f29 and change the text alignment to right and right click on that selection click format cells and change the alignment to right indent so you have to go to the alignment tab and write one here okay and then select everything from f27 to g28 change the font size to 10 and bolt the text then select f29 and g29 and change the font size to 12 bold text and use the highlight color that you've defined before then do the actual formulas so here we'll write the subtotal so equals sum and then you can just pull from g23 to g26 or whatever is the last row on your table close the bracket and we have a sum then for the v80 again equals sum open bracket select everything from the f column close bracket and finally here we'll just sum up subtotal plus total vat and we'll want to align this to the right so select these cells and click on align left all right i see that the total amount 2 is not formatted correctly so to fix that we'll select that cell click on the small arrow again go to warm number format custom and then we try to find the format that we created it's usually on the bottom of this list so we can see it here and click on ok and now it looks better then all we have left is the footer part of the invoice so select everything from a 32 to g32 and we want to add a line here so go to line style i'm going to make it a bit thicker than the previous one that you used that we might as well use the same color so select this line here and click on bottom border if we're happy with how that looks we can continue um to a 34 will write register address then below that we'll write whatever is our street address finish line one then after that address line two and below that city and post code then to be 34 will write contact information and below that contact name underneath that phone and here we can actually use uh equals cell a9 because we have the phone number there then email and then your first last add opening dot com or whatever then to if we'll write payment rf2038 for right payment details it's very difficult uh the f35 back name below that sort code underneath that account and to g30 well whatever the bank name is you know actual bank name sword code you know whatever number then all we have left is a bit of formatting so select everything from a34 to f34 bolt this text and select everything from a 35 to g37 and change the color of this to the dark gray we used before then select everything and align the texture right on our left and change the font size to eight okay it looks a lot better then um select everything from a39 to g39 and choose the background color sorry wrong button to the highlight color that we used before um now we basically have everything we need and it's just about fixing the margins and adding a bit of space here and there so before we do that let's select the whole invoice so a1 to g39 and go to page layout and click on print set print area and set print area now we see where this is cutting if we would print it so this line here means that all of this will be fitting on on one page and this would be printed on the second one so so we need to fix that somehow uh making the the columns a bit bit narrower so start by pulling uh the g column a bit and okay did not fix that then we might need to make the b column a bit slimmer still didn't fix that then try with the c1 we don't need that much space for unit and there we have it now it all fits on one paper we can see that the highlight down here is not fitting so we could actually delete row 31 so select that and click on delete then i think we might want to add a bit of space between your company and your customers details so increase with the width here okay we have the same problems before where we're not fitting on a paper so we can make these a bit um smaller and uh yeah you can you can do these kind of tweaks to the template if you if you want or you can even make these rows a bit uh lower uh to to have more space somewhere else but yeah that's basically it