Effortlessly Create Invoice from Excel Database for Administration

Create invoice from excel database for Administration

in any business organization we use Excel to organize data as needed and make databases for the future moreover one interesting thing is that we can make invoices very easily in Excel hey there Excel enthusiasts welcome to excel Demi your go-to destination for mastering Excel and Excel viia related challenges I'm Shah rafid and in today's video I'll be showing a detail guide on how to create invoice with database in Excel so let's roll up our slips and get started for this tutorial I'll be using Microsoft Excel 365 I'll accomplish the task in various steps first I'll make a draft of the invoice to ensure which information you want to see in the invoice and how the invoice will look however I'll gradually go forward and use Excel formulas to make an automatic system for this in the product section I'll manually select ID and give units and I want to get the rest of the calculation automatically first thing first I need a dat database for the goods here is a sample database I'm taking for demonstration this list contains a series of IDs for different products along with the product names and prices for each unit next I'm going to show the core invoice template with this in mind I have created this data range as the invoice template you can input the invoice number and date manually in this C here are The Columns of ID product name price units and subtotal in addition to to that I kept dedicated sales for total orders with tax calculations keep in mind that you can always create new rows between them in Excel so you don't have to worry about that while making this invoice template at this point you can manually insert data inside the template but the main point is to automate the process as much as possible when we are creating the particular invoice template with the database in Excel select cell C6 and write down the formula into it equal to here I'm using the IF function press tab to insert the highlighted function as The Logical test argument I'll use another function which is the E blank function as the value argument I'll select cell B6 close the parenthesis comma as the value if true argument I'll insert a blank space as the value if false argument I'll use the V lookup function as the looka value argument select cell B6 comma as the table array argument I'll select cells in the B5 to D14 range in the database worksheet comma as the column index argument I'll insert two comma and as the range lookup argument I'll use false which is for exact match close the parenthesis again I'm closing the parenthesis and press enter here the vlookup function searches for the value Insel B6 on the Range B5 to D14 in the database sheet and shows the output from the second column of the range where the exact match occurs this e blank function checks if cell B6 is blank or not and returns a Boolean value which is true or false finally the E function checks if the E blank function returns true or not if it does then it returns an empty string otherwise it proceeds with the VCA portion of the formula in summary this formula will fetch the product name from the database sheet ing to the ID and it will keep this cell blank if the ID cell is blank also now in cell D6 insert another formula to fetch the price from the database sheet this formula is also similar to the previous one here in this formula the only difference is that I use three instead of two in this because prices are in the third column of this data set as this formula is also the same to the previous one so there is no need to explain this separately now to test the values and check if the formula are indeed working or not let's insert a value in C B6 matching an ID from the database I'm writing a 002 press enter as you can see the input portion is working correctly next I need to automate the subtotal calculation and total order calculations so select cell F6 and write down the formula is equal to if is blank select cell B6 close the parenthesis comma input a blank comma D6 into e 6 close the parenthesis so this formula will check if the cell B6 is blank or not if it is blank then it will keep the cell F6 blank also otherwise it will return the product of cell D6 and E6 into cell F6 press enter now I'll apply accounting format in the columns related to currency so select this sales go to the Home tab in the number group of commands click on the accounting number format icon now select the range C6 to F6 and drag the field handle down to replicate get these formulas for the rest of the cells then select cell f13 and write down the formula equal to sum as the number one argument select the range F6 to F12 this function adds up all the values in this range press enter after that I'll apply a tax of 7.5% so Insel F14 write down the formula equal to f13 into 7.5% and enter for the grand total calculation I'll add up the value of the upper two cells equal to f13 plus F14 and enter at this point I can say our invoice template is complete let's test this out by inserting a sample unit for our first product I'm inputting three and enter as you can see the template is working as intended now let's fill out the template with product IDs in the ID column and their ordered units to make it more credible add the invoice number and date also I've inserted the date as 26th October of 2023 also make this column headings Center aligned here is what it looks like finally in this tutorial I have shown a step-by-step procedure to create invoice with database in Excel follow them carefully to apply this knowledge in your practical field download the workbook from the description box so you can practice it yourself if you have any questions suggestions or feedback please let us know in the comment section you can have a glance at xl.com or join our thriving xeli Community Forum where you can post your EXL and VV challenges and get solutions from experts and fellow users if you like this video consider subscribing and click the Bell icon thanks for watching