How to Create an Invoice Template in Excel - [ Excel 2021 Tutorial ] - Bookkeeping Small Business

hi and welcome to this dcp web tutorial in today's tutorial i'm going to show you how to create an invoice template in excel okay so before we start this tutorial if you're interested in bookkeeping you want to learn more about bookkeeping the process behind it or the process that i particularly use in terms of managing my business accounts then you can go over to youtube and i'll put links in the youtube description to my bookkeeping training tutorial it comes in four parts and um you can see it's quite popular it's got over 184 000 views and lots of people like that particular video so i assume that you're going to like it too and it'll give you a bit more detailed information about bookkeeping and the processes behind it um the ones that i use typically when i'm managing my books for my business so i'll put a link to the training videos inside the youtube description but for now let's go ahead and close down the web browser and we'll go ahead and open up microsoft excel so we're going to create a custom invoice today so we want to start from a blank workbook so i want to explain everything in detail how you go about creating this custom invoice so we've got a blank workbook here and don't worry if you don't know too much about so you'll learn quite a few things about excel during this tutorial as well and we're going to go to file we're going to go to save as here and we want to just save this works always save your work before you start it's always a wise thing to do we'll go to my desktop and i've got this folder now the name of the file that we're going to create is called template [Music] invoice let's actually call it invoice template dash01 we're going to use this invoice template to create more invoices that we'll typically send to our customers so i'll explain that as well so let's go ahead and click the save button and i never like to leave it as sheet one so i'm going to just call this invoice template just like this so we'll save our work and the first thing we want to do is just type into this column a so this is column a this is the first cell i'm just going to type in invoice and we're going to go to the file menu here and then click print all right it seems a strange thing to do but we can see the word invoice written in there i'm just going to click the back button when we do that what happens is you'll see these dotted lines you might just about to see the dotted line here and you'll see a dotted line going this way and you'll see one going that way if i zoom right out you can see them much more clearly now so this is the area that we're going to focus on creating that invoice because this represents one page within excel so these are all pages all of these blocks will be a separate page right so we want to create our invoice within the realms of this one block here anything that we put outside of it or underneath here is going to print on a second page and we want to avoid that so just remember to do that just type in the word invoice you can delete that now but then you'll get these dotted lines and those dotted lines will give us some guidelines as to the the realms of where where we can put our content for our invoice so let's just save this okay so we need to sort out the margins of our document and the maximum width that we can use we want to get as much space in our document as possible so you don't have to do this but i'm just going to go to the home here i'm going to select this just to show you and highlight yellow just as well you don't need to do this but it's going to explain what we're going to do i'm going to highlight this row yellow go to file and then go to print and we can see there's a big gap here there's a gap here on the side there's a bigger gap on this side and we want to just sort out excel so that the gaps at the top and at the bottom and around the edges is at the smallest amount possible but still there'll be a gap there right so to do that first thing we want to do is grab this um this um this handle here between i and j and we only drag it to the side and when we start to drag it to the side you'll see that this line this dotted line will jump back so we just want to move it back a little bit until um the line sits on the right hand side is dotted line right you can move it a little bit to the side and on your screen it might be a slightly different adjustment so around right around here you can see that it's almost aligned with this line here just like this so that's going to be our maximum work so when we go to file and print now we can see the gap on this side and the gap on this side is pretty even right last time it was cut around here so that's that's one thing we can swap is just to sort out the width here next thing we want to do is ready to swatch the margins let's go back to our page layout go to the margins here and go to custom margins here and inside custom margins i'm going to set the top one to 0.5 i'm going to copy this value so press ctrl a ctrl c or command a command c on the mac and then we're just going to press ctrl v or command v on the mac and paste in the 0.5 value or you can just type it in yourself right so i want 0.5 value all the way around and we'll click ok that gives us a bit more space to work with so we can now select this row can you see this this is now white here so if we select it again and make it yellow make everything yellow let's just go back to file and print and now we can see we've got a smaller gap here and here we can actually increase the width of this last cell just to fill in that little space there so it should be like almost this size gap this just gives us much more space in our document to create our spreadsheet right so when we print it will still print fine but we're just going to have a really small margin at the top you can increase that or decrease it as you like using those settings but i like to give as much space i'm going to drag this handle to the side a bit more and so you can see this dotted line snaps this side i'm just going to drag it back a little bit to just around here just around here so here it's nice so we'll go to file print we can see we've got a nice gap here here and everything is quite even now yeah and then on my computer it's almost a range it's almost aligned with this word arranged just kind of like in that sort of like so let's select all of these cells we can get rid of this yellow color we don't want that and you know in the invoice template typically you're going to have your logo and you're going to have your trading address and a few other bits of information so let's go ahead and click save and i need to just grab a sample logo that we're going to use in this tutorial so i'm just going to open up the web browser you need to go and get your logo and i would recommend that you speak to your design or your developer i'm going to use a test one here but speak to your designer your developer and ask them to give you your logo on a white background or a transparent background either one right so these all different logos i've designed for my clients i'm just gonna pick a random one let's take this um let's just find something that looks nice they all look too nice that's the problem so let's find something maybe like this canary wolf shoppers right personal shoppers so here's the logo in fact we won't use that one we'll use uh let's just use this el toro right this one so here's the logo try and get your logo in the best quality possible and i'm just going to drag and drop that into the folder on my desktop so let's just drag and drop that into here and we can close down this browser and inside of this folder uh we'll just call this company logo and we're going to create a folder and call it assets all right call it assets and we can drag that logo into here now we've got it inside of the assets here and we want to add that to excel so let's go to let's just left click in this first cell here and we're going to go to insert insert picture and then this device here click it and then it will go to our computer we can go to the desktop we can go to this folder go into assets and click that logo and insert it so now we've got the logo here but you can see there's quite a big wide gap at the top and the bottom let's first resize this logo a little bit right that's probably about the size i want it to be once you're quite large but not too large and we need to crop this logo so you could do that in photoshop but you know why use photoshop when excel can do the work for you so if we left click outside if we click on the logo itself when we click on the logo there's a crop tool over here so if we click on that crop tool you'll see these black handles up here and that allows us to drag that black handle carefully and we drag it up so that it just sits right underneath the word spanish restaurant and we're going to drag this one down so that it sits right here just above the text and we're even going to crop it from the right hand side here you have to be a little bit careful you can hold down the control key and use your mouse wheel to zoom in that makes life a little bit easier for you right you can zoom in i'm going to crop close as possible but not exactly to the edge but as close as possible just leaving a little bit of a gap around the edge a tiny amount right like this and once you're happy with that hit the enter key in fact what do we do we left click outside i believe let's left click yeah left click outside and when you click on the logo it will be cropped out like this and now you can drag it into position so we want it quite close to the top here like this we might make it a tiny little bit smaller something like this will be good let's hold down the control key and use the mouse wheel to zoom back out and we can zoom all the way back out remember this dotted line is our maximum page width and right down here you'll see the bottom of the page as well so let's go to our save and let's just go to file print and we can see the logo has been positioned here and you can adjust it slightly if you want but like i was saying to you i want to try and have as much space in the document for our content for the invoice right we don't want big margins this should be fine and when you print this out there's a gap here so you shouldn't have any problems with the printing so next thing we want to do is add our company address um we can do that in excel directly so probably the first line or the first um row let's just double click in here and let's just type in um i'm just going to make the address up right so i'm just going to call it 67 glen grove road and then we will um click here and we'll type in stratfor that's the area the city is going to be london and then the postcard will be e15 4dl right so this isn't quite right so what we want to do is select all of the cells and right align them so now they're sitting like this and really what we could do is just select all of these so hold down the left mouse button hold it down and select all of them press ctrl x to cut them click on the letter j and then press ctrl v to paste it so we want to have it sitting right on the edge over here and if we go back to save our document and go to file and then go to print now we see the address in the top corner here so when a nice clean simple address i'm using calibri font at font size 11. you can adjust that to whatever sort of preferences you want inside of the document right you can adjust it however you feel so really what we want to do now is create a line to separate all this content so i'm going to select this row here so i'm going to left click hold down the left mouse button to select all the way across and i'm going to go to the border tool and i want to set top border here top border i'm going to click that it's just going to draw this line across the top here and we'll go to file print we're going to have this line to separate the content above and the content below if you want to add a little gap there you can just click between um rows four and five left click and drag down a little bit just a little bit all right in fact that's going to cause a problem so if we click here uh let's see we want to top align this so if we click this button that will make sure the text sits at the top and then we can have that gap there as well right between the text and the the the line and the logo so if we print preview now the address looks correct and we just got a bit more space it was quite close to the top there so we had that little bit of space there uh by increasing the the height of this row and then clicking on this cell and telling excel that we want to align the content to the top in this particular cell the other ones are fine as they are okay so the next thing we want to do is add an invoice number and a date to our document so to do that what we'll do is uh click on this row number six and we're going to type in invoice in verse number and we'll put two um two little dots there now normally when i create a unique invoice number using acronyms i use like the l and the t so maybe lts sr so e for el toro t for um e for l t for toro s for spanish and l4 the restaurant so we do e l t s r right so that's an acronym of that that word or that name of the restaurant and then we just put in the date it says 2021 and then we put in three zeros or four zeros three or four and then one at the end so that'll be the first invoice number i explained that more in the bookkeeping tutorial so if you want to watch the bookkeeping tutorial how to generate unique invoice numbers then you should really watch that tutorial that'll explain it in more detail so the next thing we want to do is go to this column or this cell here and type in date and we'll put in today's date which is the second of the listing factors put the first of the 10th 2021 and we'll hit the enter key and that will automatically right align it here it's going gonna write a line and you can see that here so we can um grab this row five and drag it up a little bit so we have a little gap there let me zoom in right here let's undo that let's just undo that okay let's just zoom in here so you can see a bit more clearly so between here and here i'm just going to drag up this cell so we leave a little gap here uh you we can do this as a specific number so let's just see it's around five pixels let's call it three uh three three and height so to do it more accurately you can click on here right click and then we should be able to do row height and inside row height i'm going to type in the number three and click ok and that will leave a three gap here i'm going to click on this one right click go to row height and also set that to a number three so we leave this little gap in between the content and then we can decide do we want to add a border there so we what we can do is select this row here and then we can go to our border tool and it's already selected the top one but you can click on the drop down and select top border and then we have a line that draws above or below above and below the invoice number and then the date you can then go ahead and select this if you want and you can change its background color you can use a different color like an orange color you can go to more colors and you can select a specific orange color maybe that will suit um the logo so that's kind of like a pale orange and this is like a pale orange here you can click on custom and then really tweak out the orange color i'll get the rgb value and paste it in here exactly what it is uh let's just click okay so if we want to be very specific they're kind of off colors they're not exactly the same so if we were to go to something like photoshop or you can do this in software as well let's do this as accurately as possible because i want your invoice to look as good as possible so if we open up photoshop we can use the color picker tool to pick out the exact color of that orange and then we can use that in excel so really what we should do is make a little notepad file here and we can just call it colors and then we can get the red and the orange color and find out the exact values let's drag that logo into photoshop you can do this in software as well um you should be able to open this let's see i think it's thinking about it here it is and we'll use the foreground select tool so here's our foreground select tool here you can see the rgb value for the blue color but i'm gonna use the color picker tool so when you just roll your mouse or move the icon over it you can click here and this will be the exact orange color so we can go to our little notepad file and in here we'll just type in orange and that will be the color for orange and then the other one is red right it's like this dark red color so we can click on the red color now and we can get that value as well now we have those specific values and you can ask your designer for those color codes but now we can make that color exactly in excel and then it will go with our branding a bit better so let's copy this orange color copy that value i'll leave that to the side for the moment and i know this is a little bit longer than you'd normally do but let's be accurate as possible so we can select that we can go back to our color go to more colors and then we can go to custom color and now we can paste in that value uh let's put a hash at the beginning so it needs a like a hash at the beginning and we can click ok and now that orange is exactly the same as that orange they match right or very close to what they should be so you can make it orange you can make it red you can change the font color inside if you want to change it to white you can make it white like this but i would advise leave it black because if anyone prints this out on a black and white computer then that's going to show out a little bit better so i kind of like that the invoice number what i'm going to do is double click inside this cell i'm going to select this text here and make it bold and then i'm going to double click inside this cell select the date text and make that bold as well so now the invoice number and the date stand out i'm trying to keep you know reserve as much space for our content as possible because that will just help us with our design later when we want to put in all of the details for the uh values for what we're charging now so the other thing we want to do is um add a three value here so we're going to right click on this row go to row height and just set it to three so just remember that number three that's going to be our default row height for our gaps then we can start to create more content here and we want to put um an address in here right so we're going to put the billing address and also the delivery address so what we'll do is click here and we'll type in billing address and then we can just type the address in here whatever that's going to be so we just make up an address but for now what we want to do is um select these three rows these three here and we're going to put um in fact we'll do the the lines and everything after it's a billing address let's make that bold and we'll type in a fictitious address here so let's just type in we just put a placeholder address in here so that we can replace it later so it'll be test road um let's put it in as ilford s6 and then the postcode ig1 2nr ig1 3 nr so that's just a made-up address let's just see how us how our document is looking so we go to print and now we can see our document with the billing address i think maybe we need a bit more of a gap there right the three is probably a little bit tight across the top here so let's go to that row this one here let's right click here and let's go to format cells and format cells let's right click and go to row height and we set it to a value of six there's just a little bit more of a gap here now between the billing address and the invoice number above and this will be our billing address let's copy this text and we're going to paste it here paste it here and this will be the delivery address so we'll put the billing address here the delivery address over on this side and then we can just do a little bit of work in excel to add some borders and stuff around it so we might just put a line above and a line below might be sufficient enough so let's just try that let's select these rows here these three and we'll go to the top border so we can just click that already and we'll set the ones below and we do top border here so in fact we need to select this row here right this one here the empty one and then the top border so we've got a line above and a line below you could put it in a box if you want um we can select this one and let's just try it and see what it looks like like this so now we've got lines uh separating the content if we go to our print preview then we can see that in excel now right you can hold down the control key and zoom in a little bit i think it might be nice if we put like a full box around it so what we can do is select all of the content select all of it and then we'll go to the border tools and we'll do uh outside outside border now there's a box around it and we'll select this one and we can also do um outside border here as well outside border so they're not exactly the same width and height and stuff that doesn't really matter too much as long as you've got enough space to type in the full address across here it should be fine right and we could make this font slightly smaller i think it needs to be a little bit smaller so i'm going to set it to a 10 value so it's just a little bit smaller on this side and then on this side as well we'll set it to 10 and the font will be a little bit smaller than the titles above which i think looks a little bit better and let's just go to file print and we can see that now we've got the billing address and the delivery address i'm not convinced that that looks good so what we might do is put a border around the whole element and then get rid of the ball is down the side here i think that will look better so what we'll do is select everything here go to borders and say no border and then go back to borders and then say outside border now we've got one that sits around the whole content let's see how that looks i think that's a bit better right like this looks a bit better um then we've got to put the rest of the content so now we need to design the elements that will be for our actual uh invoice information so let's save this document as we go along i'm going to zoom out a little bit because we need to see more of the content and on this row um 14 i'm just going to right click on it go to row height and set that to a value of 6. we're going to leave that this sixth gap here then we're going to start our content underneath here okay so now we want to start adding the information in for our uh invoicing that the actual figures and the numbers that we're going to be invoicing so over here in this final column the one right on the far right here on row 15 i'm going to type in net net and then the one next to it i'm going to type in unit price and then the one next to that i'm going to type in quantity and then over here on the left hand side i'm going to type in product description or you can just type in description doesn't have to say product description it can just say description you can get rid of the word products if it doesn't apply to your business if you're not selling a physical product or some sort of service you can get rid of that so what we can do now is um select all of these cells here up until g right these ones for the product description and then we're going to click here and click merge or just click merge cells here merge cells and that will become one big cell itself right and then we can um now highlight all of these cells and we can make it a color maybe we'll make this that red color so let's see how that works out i'm going to make this bold so i'm going to select everything here and press ctrl b and then we can go to a little notepad file and over here i've got the red color right so i'm going to copy this copy it and it's already selected so now we can just go ahead and go to the cells more colors here the colors and we'll go to custom and we'll paste that value in here just put a hashtag at the beginning and click ok now we've got that dark red color you can see the text is quite dark so i'm going to select this and make the font white so it stands out so now you've got the product description you've got the information about the product and all that's going to sit underneath and we want to allocate a specific number of rows right and we're going to try and allocate quite a few rows i'm going to zoom out a little bit here just so i can see everything and this is the bottom of our spreadsheet so we can fit in quite a lot of rows here right we can fit maybe up to row 40 even up to row 50 right up to 50 we can do so we can have that many rows of content in our spreadsheet for our invoice whether you need that many or not that's kind of optional but i'm going to leave a little bit of space towards the bottom so i'm going to go up to like row 45 because we want to do some calculations down here and then maybe put something about billing information where to send the invoice to and the payment terms and some notes so in fact let's go to row 40 i think this should be enough this would be quite a few rows to work with so what we need to do is um let's select this row and then we'll click we can just right click and then select uh let's see in fact what we do select this row and then go to here and select merge cells and we just need to repeat this across uh going down i'm not sure if we can copy this and paste it let's see if we can just copy and paste it that's a lot quicker so we can select this first one go down and paste it press ctrl v ctrl v here in fact let's undo that and let me explain that a bit better so we've got each one of these as a cell right and we want to make it one big cell like this so first thing we'll do is select this cell and let's make sure we left align it as default so then all the content starts getting typed from the left we'll select that cell press ctrl c or command c on the mac and then go down to the cell below and then press ctrl v or command v and then use your arrow key on your keyboard the down arrow key and that will take it to the next row and then just press ctrl v or command v on the mac right and just keep doing that all the way down the spreadsheet we might even be able to select multiple rows and paste them let's see you can select multiple ones with your mouse so you can select all of these up to row 40 and that should give us like one two three four five six seven eight nine ten 11 12 13 14 15 16 17 18 19 20 21 22 23 24 so let's make it 24 should be good right that's 24 different rows of information you could put into a spreadsheet uh about the services that you're providing that should be sufficient um if you're doing a sale and you've got 24 you know rows to put in the data that should be pretty good um we've made a mistake here though so we need to kind of undo this sometimes we make mistakes so let's show you how to fix them because we've taken the the quantity column we merged that into the product description we didn't want that so i'm just going to press ctrl z or to just undo all of that let's just undo all of this and i'm going to select this this row here and i want to unmerge cells unmerged cells and that will put it back to its original state now we can select just these ones and not the quantity one and we can go back to merge cells sometimes we make mistakes we need to know how to fix them so if i ever make a mistake let's show you how to actually fix it i'm going to copy this value so just click on it press ctrl c to copy and or command c on the mac and we're going to select let's see we're going to select let's try and select all of these first and press ctrl v or command v and that should paste it down and each one will be a separate row now i want to get down to row 40. so i'm going to hold down the shift key and click here left click and then press ctrl v and that will paste it paste that cell down and emerge it for us right all the way down so now that we've got that done we can select all of this up to row 40. let's just zoom out a little bit for you so i'm going to click on this first cell go down to row 40 and click on this one here so i'm selecting this whole block and then i want to go to um the borders and select all the borders and now we have the the border set out but for this top one i'm going to select the whole row and i'm going to go back to borders and i'm going to say let's see no border for the first one and then i'm going to select it again and let's see i want to do a bottom bottom border in fact what we could do let's see let's go to file print and let's see what it looks like this one i want to just have um i don't want to border underneath this part here you see where the um the product description is so to do that let's let's go back let's select this row here row 17 and we'll select all borders now that one has all of the borders and really we want some we just want borders down the side here like on the sides right so if we select all of these ones and go to borders and we want um left border and then let's do right border and that should put one on the far side here and here let's see if that worked let's go to print now we can see the border on the side here and here but we just need to do it for these two here so we can click on this cell go to left border go to right border and then do the same on this one really well you need the right one on this one right one because we've already got it set on the left let's check it again print and now we've got the spreadsheet here you could put a border around this top one as well maybe it will look okay maybe we'll give that a go let's select it and then do all borders i think that will look okay actually let's see print preview yeah that looks fine right so that top one's got borders around it as well so now we're now able to do our data entry and put our content in there but we need a couple of extra rows at the bottom so what we'll do is scroll down the spreadsheet and in here we're going to type in total cost and then here we're going to put total v80 and then here we're going to put total ch charge right total charge we're going to select these cells only these ones and we're going to do all borders for these ones all borders and now we've got borders down here where we're going to put values in and do some calculations um in excel for calculating v80 and calculating the total charge and so forth so let's zoom out a little bit here and in the product description this is typically what i do in my business um i'm not sure i'm not convinced this should be red to be fair let me just click on this one right click and go to its row height and we can see we set the row height to 15 here right 15 so i'm going to click ok i'm going to go to this row this actual row 15 right click here and go to its row height and it's also set to 15 as well um so expecting the other one to be a bit taller i think this one looks a bit taller maybe because it's got the gap above and below it the little gap above and below it let's click this one and center it we use this centering tool just want to make it a bit bigger something like this i'm going to say to let's right click format cells and right click and go to row height and i want to set it to like 18. this one i want a little gap above it and below it looks it looked a bit too tight there i think this should be orange i'm not convinced it should be this red color so you know we have to um change things sometimes so we can go into our assets colors and we've got our orange color here i'm just going to make that orange i think it'll look better so let's select this go to our colors more options custom and we'll paste in this orange color and click ok i think it looks a bit more consistent and now we can select these and make them black font again so you can click automatic and it will turn it back to black i think that looks better it's entirely your choice what you think you can change that or leave it as is it's up to you but here we want to type in of what we were going to invoice to the customer right whatever the work is i'm going to type in website design and in fact i'm going to select all of these cells all of these ones so i'm going to left click here scroll down hold down the shift key and select this one and i want to set them to a smaller font size i'm going to set it to 10. so all of these now will have a 10 value in them and the headers will have the headers are set to 10 as well so let's see this one was set to 11. so let's select all of these and we'll set them to an 11 value up here so the headers will be slightly bigger than the content that goes below so let's say website design logo design uh social media setup and then we could do website hosting and we could do um domain registration reg and we could keep listing all of the different services that we're providing to the customer some of them might be multiples right so things like newsletter design maybe more than one uh you may even do more than one logo you might have done ten websites for the client you're building them all in one go but we can put all of the different values into here um i'm just going to put in some typical stuff that i do for my clients like leaflet design we also do um letter heads and social media artwork right so here's some examples you can see i've got plenty more space to type in more of the services that i'm providing you might put t-shirts in here like if you're selling t-shirts and it might be t-shirts if you're selling um other types of services like um consultancy then you can type in the consultancy information into here you can describe it you've got plenty of space to describe these things so for this client as an example i did one website i did uh three logos right and we did we set up three social media accounts so in social media i might type in facebook twitter instagram so we set up those accounts it's worth describing in here which social media accounts you can see these are like right aligned and these are left aligned so let's select all of these rows here so let's click on the first one here this one in quantity scroll down hold down the shift key select these uh by clicking here left clicking and then just do left align do left the line here and then everything will be left aligned or you could center it if you want center it but i think left the line is fine um for website hosting it will be one and normally i type in one year so it's one year one year of website hosting and then that might renew every year and then the same with the domain name right the domain name but we might have registered two different domains so i might type one year and then type in uk and dot com so i registered both at dot co uk and a dot com for the client newsletter design we did let's say three examples leaflets we did one letterheads we did one and social media artwork we did 10 different social media artwork let's make it in fact um nine because we've got three accounts facebook twitter and instagram we did three different designs for those three different social media networks so now we've got the quantity we've got the services that we're providing we need the unit price so for website design we what we need to do is select these uh hold down the left key left mouse button select this row as well so you're selecting both of these columns let's say and then scroll down and then click here left click and that will select all of that data i'm going to right click on it go to format cells and we want to set this to a currency value i'm going to leave that two decimal places you can do like 10 pound 95 or whatever it is and i'm going to leave it as a default value on your computer it may say dollars or pounds or whatever currency that you have mine happens to be pounds right i'm going to click ok and we're going to type some values in here so i'm going to type in um for website design let's do 895 and that's the unit price and then what we'll do is in here we want to type in a formula so we're going to type in equals so this quantity times by the unit price so this will be this value so i'm going to left click on it which is h16 i'm going to hold down the shift key and press number eight on my keyboard which is the star sign which is the multiplication sign in excel and i'm going to click on the unit price and then i'm going to hit enter so it's going to do 1 times eight nine five equals eight nine five logo design let's say we charged um as an example let's say we tried 45 pound per logo so the unit price for each logo is 45 pound i'm going to click on this cell here press ctrl c and then press ctrl v to paste it down and three times 45 pound is 135 social media set up we charged 45 pound in account we can cut and paste this down one year website hosting 195 and normally it's one year website hosting ssl certificate right normally that renews once every year says 195 we can just click this sell cut it and paste it down and it's going to do the math for us domain registration each domain is 15 pound and you can see it's already doing that excel is kind of learning what we're doing it's going to work out that value uh using a bit of intelligence let's say it's going to work out that value 2 times 15 is 30. newsletter design uh was 125 and then that equals 375 leaflet design was let's say 60 and then that would be 60 value 1 times 60 right letterheads was 75 and then social media artwork was at 15 pound per artwork and it was 135. so now you've got all of the values calculated for you you haven't got to do the maths but you have the quantities here and the unit price for each one of those so each one of these three was 45 345 equals 135 so that's how we use excel to run a formula to do those calculations now we need the total cost so we can click here let's try one thing i'm going to left click here drag all the way up to the top here here and then click auto sum and it's going to give us the value 2035 right so i always like to check that these things are working quickly because you don't want to invoice the incorrect amount not too much and not too little so we'll just double check this i know 100 is correct but we'll just double check it anyway because i like to double check things so 895 and we can do plus one three five plus another one three five plus one nine five plus thirty plus three three seven five plus 60 plus 75 and then finally plus uh 135 and equals 2035 so we know our calculations in excel are correct i'll normally do that once just to double check make sure everything is good and then i know this spreadsheet is working correctly this value down here i want to left align it left align and i'm going to make it bold and then we need to work out the v80 so this is the price excluding v80 so now we need to calculate the v8 and we're going to use excel to do that and it depends on your country right so in uk it's 20 in your country it might be 15 it might be 22.5 it might be different values so let's show you how to do the um calculations in here so i'm going to click on this cell and i'm going to type in the equal sign and i'm going to click on this value here and i'm going to type in times by and i'm going to type in 0.2 and i'm going to hit enter and 407 pounds is 20 of this value so if i left the line this and make it bold let's just copy this value 2035 we'll open up this spreadsheet and we'll paste that in and we do times 0.2 and that gives us 407 pounds let's just double check that calculation on the internet so we can go to my website dcp web we can go to tools and then we can go to v80 calculator here and enter the cost excluding v80 is 2035. you can type in any value here so this is 20 we could calculate we have to get rid of uh this pound sign and get rid of the commas right so it would just be like 2035 like this and then click calculate we can see it's 407 and we can see the total is 200 2400 so you can use this tool on my website just go to dcp web click tools and you can use the v80 calculator right here v80 calculator and that will give you those values uh in here so we know that that's that value that's been calculated is correct now let's just say that um your business charge is 22 for example so then we can just change that um value in here in excel this one to put a 22 at the end so 0.22 and that will be 22 right of the of the value so if we type in 22 here and hit enter which is 447.70 seven 447.70 let's say it was seventeen point five percent so it's a bit of an extra number so we do one seven five seventeen but you don't put the decimal placing you just put the five in there and that will be seventeen point five percent in the uk at one point we were charging 17.5 percent for v80 click calculate 156 356.13 356.13 right so this is how you use excel to calculate the different vat values i'm going to leave it at 20 or just 0 2 that's 20 percent the total charge we're going to click the equal sign we're going to click on this value and then we're going to hold down the shift key and press the plus sign so that's on your keyboard use the plus sign and then we'll click on the bottom value and hit the enter key now we've got our grand total we can click that and we can right align it and we can make that bold let's save this work and let's go to file and let's go to print and now we can see our invoice we can see the invoice number the date the billing address the delivery address the product descriptions all of our calculations the total vat and we've got some space down here to to ask clients where they need to pay their bill payment terms additional notes uh we can put a bit more information towards the bottom here so let's go ahead and do that um let's just make sure we can see this a bit clearly for you let me just zoom out and just check this so we've got up until this dotted line to fit the rest of our content what i want to do is i want to leave a gap here so i'm going to right click here and i'm just going to select this row left click right click go to row height and i'm going to set it to a value of 6 and click ok so it's going to be a little bit of a gap here right between our next bit of content and i want to do one two three four five rows i'm going to click here one two three four five rows and i'm going to select all the way across these five rows i'm going to go to merge cells merge cells here so let's uh let's try that again merge merge cells here so that's one big block and i'm gonna go to the borders and i'm gonna select outside border only so now we've got a section in here that we can type additional information so i'm going to click inside and let's just see we want to click in here and set it to top align top align that way you can start the content from the top i'm going to say payment to be made to the following bank account you can put in your bank deals in here if you click inside here you should be able to see so right now we if we click entire it doesn't go down onto the next um the next row so to fix that we've done this slightly wrong but we can fix it quite quickly what we'll do is just press ctrl z to undo this we'll need to just unmerge this or we can click everything in here just select everything select that block let's unmerge it we did a slight mistake here let's unmerge it and we can then um select everything and just draw the border around it right say outside borders so now it will look the same in when we although it's got separate cells in here when we do the print preview and we print out the document it still looks like it's one big block it doesn't have individual cells because we haven't drawn borders around everything right now we can type in each row separately so we can do payment to be made to the following bank account and then we're just going to type in the word bank and we're going to call it bojo bank anyone knows who bojo is in london then you or the uk you understand what that is but if you don't there's a mouth uh account name and that will be the company name right so the name would be el toro spanish restaurant we're just going to call it el toro e-l-t-o-r-o limited or something like this and then the account number and that's just going to be some random numbers i'm going to make up so don't send any money to that number because it won't arrive and uh you know it's just a fake number sort code and in your country the details that you type in here might be slightly different but these typically the the information that i would give to a uk based customer you can put an iban number in here and a swift code but normally if i'm doing payments overseas like if i've got a customer that's based overseas normally i send them this information plus this uh the iban number and the sort um the swift code in the email when i send them the document right i don't normally put it in here because 99 of my customers are uk based and this is all they ever need to make payment so then underneath here what we're going to type in is pay payment terms and we'll put two dots there and we'll make that bold and then i'm going to select let's right click here i'm going to go to the height let's do that right click and we go to uh row height and i'm going to set it to a value of uh what was it before 18 right 18. so that would be the row height and i'm going to center out this content center and that will be the same height as this one here and i want to write in here 30 days from invoice date i'm going to make that bold as well and then maybe we'll highlight this color maybe we'll make this section let's make it all orange right let's be consistent so let's take that orange color and we'll highlight this whole row and we'll go to uh here in fact we can just click here right it's already like a predefined color i believe let's click it and check it yeah so that will be like the last color that we used and we can draw a border around this as well maybe drop border let's do uh outside border and we'll save this you can make this information a bit bigger if you want it to be a bit more prominent um but i think that's okay right let's just check it in this print preview and then you have like your payment terms down here right it's almost got a nice looking uh spreadsheet sorry um an invoice right pretty decent we can improve it in terms of visuals but for me invoices need to be functional it's not necessarily about so much the design it needs to look half decent don't get me wrong but most importantly it needs to be able to have enough space that you can fit your content in there it needs to be laid out and be clear as long as it does those things then you've got a pretty good invoice to work from right you shouldn't have any problems um so down here what i want to do is click here and i want to add one of these gaps right which was line height six so we're going to go to here row height and we're going to do six value so we've got a little gap there and in here i'm going to select three rows and we're going to um create a border around them all borders and in here um [Music] we're just going to type at the top here additional notes and inside additional notes we're going to select these cells just these ones and we're going to click merge cell here additional notes and in here we can type in whatever we want we can maybe make this bold as well so let's just go to file print and we can see that now we've got a section here we can type additional notes so this might be in fact we want to click here and top align it let's just top align it so inside we can start typing any additional notes so you might type something um to the customer um this is this is your final payment for completed work or you might type something like this imagine if you're delivering physical products uh your client might say if i'm not in the office or if i'm away or if you're delivering to someone's house you might say deliver to neighbor if you're sending something like t-shirts or products physical products to the customer to the delivery address you might put put a note in here to say um deliver it to the neighbor for example right so i'm going to delete this because normally we don't we type only stuff in there that we actually need and on this very bottom row um in fact let's see on this one here what i want to do let's see i want to type in um the registered office so normally on the invoice you put your registered office address you don't have to but typically it's a good idea to put your registered office address it makes you look a bit more established as well so we're just going to make this address up 321 glenn so road and then we'll do um so that'll be the registered office address i'm going to select this one and i'm going to make it a font size of 8. i'm going to make it pretty small maybe nine will be okay font size nine and i'm gonna click inside here and then i'm gonna put a space in and a hyphen or a little dash and i'm gonna put in company number and then you're in your com in your country you might have a company number in uk we have company numbers right when we register a business so we can just put in a company and i'm just going to make that up i'm going to put another space dash and then a space i'm going to type in v80 number so we have to have a v18 number in the uk and i'm going to make that number up like so maybe we can get away with making it slightly bigger we put it to 109 when we set it to 10 10 value all right and we could select all of this this whole um row now we can select the whole row and we can go to we can just actually click merge and center and that will center out the text at the bottom like this you could put a line there to separate it from the content above but i think it should be fine let's just go to file and print preview and now we can see the whole document here from top to bottom completely so now you can go and take your logo your branding design at your invoice template i know we made a few mistakes in here but sometimes when we're designing things especially on the fly when i'm doing this you know literally as i'm speaking and trying to to get this to work we make mistakes and when we make mistakes we need to learn how to fix them so we made a few mistakes in here but that's not a problem i like to sometimes not necessarily make mistakes but if they do happen i'd like to show you how to fix them so that you know how to fix them in the future you can create all types of documents might be a packaging note now you can put create this style of document and create a packaging note typically those ones will just have quantity and the product price so the quantity and the description but not the product descript prices and stuff and the v80 um and all of this other stuff wouldn't really be there in a packaging note but you'll have the billing and the delivery details maybe some packaging no invite a number or something like this right but you're learning some good skills in excel we've got a nice thin border around the edge so one thing we want to do is click save and close this for them for the moment i just want to explain a couple of other things let's close down this web browser and we're going to go back to here so we've got an invoice template here now right and now we want to send an invoice to one of our customers one of our customers so what i'll normally do is just right click drag this down and copy it here so i make a copy of this invoice template and i'm going to right click and rename it or you can press f2 or you can uh double left click to uh rename it as well let's just give this a second i don't know why it's not responding let's give it one second let's see let me just close and try and close this let's just open it again the folder and you can just click on it and press f2 on your keyboard you should have to just right click and rename but some reason it's not doing that and then i have to look at that afterwards i'm just going to click on it and then press f2 to rename it so when we rename the invoice we need to give it a sensible number now normally it's the invoice um number that i put first right so it'll be zero zero zero one that's the invoice number and then i put um the date so it will be the 12th it'll be the second today's date is the second um of the 12th the second of the 10th 2021 and then i put the company name so i'm just gonna put in um test company right so i've got the invoice number which is a unique number i've got the date and then i've got the um the company name so if i do order by it will always sit um with the invoice number at the bottom so when i open this now and i explain a lot of this in the bookkeeping tutorial you know you'd go in here and you'll change the invoice number to where it's actually set to zero zero zero one right already and then the date you would set that to two and then you'll change the um uh the address deals in here you can change the address deals um i think there's one thing we really missed out in here which would um make sense to have it there so let's just close this let's not save it let's delete this uh temp this that temporary invoice let's open up this invoice template there's one kind of critical thing that we're missing from here is the company name right we don't have the name of the company that we're billing so what we could do is um we're a bit tight on space but we can fix that quite quickly either we can delete a row here and have that row to to add in here i think that'll be wired so if we right click and insert a row so we've got a row now that we can add for the company company name um but then our spreadsheet is going to drop down here so if we click here and delete one of these uh rows here let's just see and let's just go to file and print and then go back again we can see our line sits correctly at the bottom here and now we've got this extra basically we deleted one of these rows and added one here so we can just call this um example company ltd and it will left click outside click in here and then we'll press ctrl b or click here to make it unbold and we can copy that and paste it across here so the delivery deals and the billing deal should really contain the name of the company that you're going to be sending it to it makes sense right so now we can save this document and we can close it and like i explained in my bookkeeping tutorial i'll show you how to replicate this invoice template and make many versions of it to send that to many different companies so let's just right click on this copy it down we'll make a copy here and then we'll click on this press f2 i'm going to put the invoice number in first because then all of the invoices that we create will always be in in numerical order so we do zero zero zero one that's the first invoice we're gonna create so we can create 9999 invoices before we have to rotate back around to zero zero one again so you you know you're going to be have to generate a lot of invoices uh in order to wrote in order to come all the way around to that value zero one again all right so zero zero zero one then we want to put in the date the date will be the second of the uh 10th 2021 and i don't put spaces in there i just like to do the day like this and then it will be the name of the company was example company right this is a copy of that master spreadsheet we never touched the master spreadsheet that's our our default template we always make copies of it and we can open up this now and we would simply type in the company name here we'll type in our invoice number so it might be invoice number two if it's the second invoice then you'll put a two in here this one happens to be the first invoice the date is going to change to zero two all right and inside the invoice template if you have services that you typically provide quite often then what you can do is just delete the services that you're not providing anymore so for this particular client you can just delete them and then you can just change these values so you can say well we already set up two social media accounts and we did facebook and instagram only so you can just delete this and all the calculations will be done for you automatically all of the updates here will be done automatically as well so a lot of the functionality will be automated for you logo design you may only have done one logo design so you just type in number one and this 135 will change to 45 so it'll be one times the 45 right and domain registration you merely registered a dot co dot uk for the client and you type a number one here and then that will update as well and then you can just go to file and go to print uh sorry go to save as pdf and uh we want to do actual size here and then the sheet the current sheet that we selected click convert to pdf and we're gonna save it into the same folder click save give it a second and then we can close down excel and inside the folder we've got a spreadsheet that we never sent to the customer we never send them the excel we send them the pdf version so when we open up this pdf now we've got the invoice template in here you can see that and we've got all the information laid out normally when you print this it will be you know a bit clearer as to what's going on we've got kind of a line here that shouldn't be there this line here right so we need to fix that uh we'll fix that in a second but you've got the whole document here ready to go so when i when i use this invoice template in my own business all over changes this to the next number i change the date i change the address and the delivery address normally i leave that blank because a lot of the times i'm doing work that is digital so i never actually delivered a product to a physical or have a physical product to deliver so normally i don't even have this delivery address i just normally leave it blank but the billing address i update and the product quantities and everything i update this all does its own work this you never need to change the payment terms with the customer you might say to 14 days or seven days or pay immediately you can add your notes in here and then you've got your delivery your registered office address and your company number and that good stuff down at the bottom and that's probably everything you need inside of this document um to send to the customer so we need to just fix this here this seems to be like an error here can you see this line we want to get rid of that so we'll fix it in the master template and we can fix it in that temporary invoice if we scroll in here we can see there's a line here so we're just going to select um these two cells in here these two we can just select this one this one should be fine and we can go to here and we can say no borders no borders and then that line will get removed now that that that line that was an error we can save this now and then we can close this and we just need to fix it in this invoice that we copied right uh that error so we'll click here go to here and we'll go to no borders save it go to a file go to save as pdf again click convert and we're going to overwrite the original pdf save it and then we can close this and then we should have a copy here on that pdf and that error has been fixed there as well so hopefully you can now go and create your own invoice template um it's quite easy to do and rather than you know just downloading one from the internet uh you can now create your own custom version i think that's quite important when you can customize the invoice yourself and lay out exactly what you want to have the amount of rows that you want and it just gives you a bit more control plus you'll learn some skills in excel which is uh worth having i believe right it's worth having these these skills in excel let's go ahead and close this and we'll close this down i know that we made a few mistakes in this tutorial but that's just how sometimes tutorials go i like to leave those mistakes in there and explain how to fix them i've seen a lot of tutorials where people make mistakes but they just edit them out so you never get to see how to fix those problems so when you have these problems now at least you know how to fix them and you learn some new skills in excel as well okay if you'd like to learn about that bookkeeping tutorial i'll put links in the youtube description remember there's over 180 000 views on those bookkeeping tutorials so if you're just starting up business and you don't want to pay for online software there's a lot of companies out there that you can do your bookkeeping online but you know for 17 years in my business this is how i've done it using my bookkeeping system and it's saved me a lot of money and it can save you a lot of money too okay that's the end of this tutorial i hope you find it useful and i look forward to seeing you in the next dcp web tutorial [Music] you

Channel: DCP Web Designers

Views: 221

Rating: 5 out of 5

Keywords: excel invoice template, invoice template in excel, excel invoice, excel tutorial, invoice in excel, professional invoice in excel, creating professional invoice in excel, tax invoice template, how to create invoice in excel, free invoice template, how to make an invoice, how to make a professional invoice in excel, invoice design tutorial, how to make invoice in excel, create an invoice, how to create an invoice template in excel, invoice template excel, free invoice, invoice

Id: _E1ca8bB9pk

Length: 65min 6sec (3906 seconds)

Published: Tue Oct 05 2021