Collaborate on Excel Invoice Template with Database for Finance with Ease Using airSlate SignNow

Excel invoice template with database for Finance

[Music] [Applause] [Music] hello and welcome to excel Highway in today's video I want to share with you a simple but effective invoice generator I created in Excel I want to walk you through how I created it step by step all the different parts all right let's start first of all you have the top section that is your company details like your company name your company number address phone number and an email you can add more details or remove whatever you want you can put a logo here on the top right there's this line which you just have to select the range click over here and either top or bottom thick border and generates this nice line separating the and your company data and the customer data and and actual invoice then we have the customer name with some details so the customer name in this case is a drop- down list and the details change as you change the name obviously that's very simple to do all we need to do is create a customer database so let's jump to that sheet so I have here a table uh with these columns the name number address telephone email and payment terms just fill out the information and that's all you need to populate this um if you're not familiar how to create a table it's very simple click on insert table then you can also resize the table so you want five columns then you could just change the headers not a huge fan of tables but in this case it makes sense to use dat so all there's no formulas here there's these are just data um inputs all manual so you just fill it out now how does the drop down list happen and it's Dynamic of course if I add my name now it will appear and if I erase it it goes away so if you go over here to data data validation usually this appears on any value but you change it to list and in the source I use the offset function I'll demonstrate it over here so offset will return a range or a value or table whatever you decide referencing to a specific um location so I'm going to reference this cell over here first uh argument or second argument is the number of rows you want to move down this case I don't columns in the number of columns you want to move to the right and I don't and you can use negative by the way to go to the left or up height is what I need because I want basically I just want column A I want A2 through the last row I'm going to use count a on the entire column minus one that's going to give me the number of rows that I have and nothing for the width so that's going to return exactly what I wanted if I had two here it's just going to return the second column and you can see how this works so I just used that put that in the formula over here okay it's exactly the same formula and that just gives me the drop down all of these are just simple V lookups so V lookup to that same customer name and the customer database a through F and 2 3 4 5 6 you could also use uh index match for this which would be even quicker but whatever works for you uh you can just use and um since it's a closed drop-down list I don't need to have an if error here because I'll always have values okay this is the customer part um we have the date which is the date of today so that's always going to show today we have the due date which is the date plus the payment terms now notice I do have an if error here because in the case that I uh don't have anybody selected like this then I don't want this to show na um so that's why I have it over here next we have the invoice number that you manually change so invoice number one two three or five six s whatever you track and uh create that invoice number this is a general text that you can use May 2024 invoice for example could be a work for Project X something like that okay so whatever you want then you have the items themselves so um this is just a a r range with borders ah one point to mention you see that everything here is white and Without Borders so we just have to highlight an area and click on color white and that's how it will look like this just looks cleaner especially when you export this to PDF which is what normal normally you do with an invoice the outlier for that is this area this table where I added a border and I added just a color and also the color of the cells so they stand out a bit so you have the description quantity price and total price you have the description you can add lines um Excel file quantity one 100 and you can see the total price will change once you have a quantity so if G is greater than zero g * H otherwise blank so that you don't see um um these let me just ignore errors I don't like the errors don't know what they want next we have the Total Line that's just the sum okay uh have an option for Discount that vat or you just multiply this total well this should actually be called subtotal I guess Clarity so the subtotal times the discount which is 1 minus the discount right that's what's left that you're going to charge times 18% in this case for the vet but if you have a different ifat just change it over here and change the header and the total is the subtotal times the discount so whatever's left plus the vat so that's what you're actually going to charge your customer now the next step that you can do is to PDF this so first of all what you can glue do is click here on print and once you do that you will see the area of the page and you see that it's formatted okay so a through I that's okay and I can always just select the area and either print to PDF for example say I want to print to PDF and I can see the preview and when I print it it's going to generate give it a name so n546 I go over here documents and you can see it's look looks very nice everything looks in line and I can just send that out to my customer okay so this file will be available for you to download when you subscribe to my newsletter I hope you enjoyed um this video I will be creating a improved and more automated file that will help you um generate invoices but also keep track of the invoices um that's going to be um coming out probably next week so keep in touch leave a comment i' love to read what you guys think and have a good day