Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can make invoices very easily in Excel. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you a step-by-step procedure to create an invoice in Excel with a database. Hence, read through the article to learn more and get free templates.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Step-by-Step Procedure to Create Invoice in Excel with Database
In this article, I will discuss how to create an invoice in Excel with a database. First, make a draft of the invoice to ensure which information you want to see in the invoice and how the invoice will look. However, the invoice will contain the Invoice Number, Date, ID, Product Name, Price, Units, Subtotal, etc, in the Invoice paper. However, I will gradually go forward and use Excel formulas to make an automatic system for this. In the Product section, I will manually select ID and give Units, and I want to get the rest of the calculation automatically.
Step 1: Create Database
First thing first, we need to a database for our goods first. Here is a sample database we are taking for the demonstration.
Again, the list contains a series of IDs for different products, along with the product names and prices for each unit.
Read More: How to Create a Simple Database in Excel VBA
Step 2: Form Invoice Template
Next up, we are going to make the core invoice template. With this in mind, we have selected the following template with these headers for our invoice template.
- In addition to that, we will need to dedicate cells for total orders with tax calculations.
- Let’s keep some slots for template creation and add those under the invoice.
Keep in mind that you can always create new rows in between them in Excel, so you don’t have to worry about that while making this invoice template from the database.
Read More: How to Create a Database with Form in Excel
Step 3: Input Data to Create Invoice from Database
At this point, we can always manually insert data inside the template. But the main point is to automate the process as much as possible from the database when we create this particular invoice template with the database in Excel.
- In order to do that, first select cell C6 and write down the following formula in it.
=IF(ISBLANK(B6),"",VLOOKUP(B6,Database!$B$5:$D$14,2,FALSE))
- Then press Enter.
- Now select cell D6 and insert the following formula in it.
=IF(ISBLANK(B6),"",VLOOKUP(B6,Database!$B$5:$D$14,3,FALSE))
- After that, press Enter.
🔎 Breakdown of the Formula
- Firstly, VLOOKUP(B6,Database!$B$5:$D$14,2,FALSE) searches for B6 on the range B5:D14 in the sheet “Database” and shows the output from the 2nd column of the range where the exact match occurs.
- Secondly, ISBLANK(B6) is a function that checks if the cell B6 is blank or not and returns a boolean value.
- Finally, IF(ISBLANK(B6),””,VLOOKUP(B6,Database!$B$5:$D$14,2,FALSE)) checks if the ISBLANK function returns TRUE or not. If it does, then it returns an empty string. Otherwise, it proceeds with the VLOOKUP portion of the formula.
- Now, to test the values and check if the formulas are indeed working or not, let’s insert a value in cell B6 matching an ID from the database.
As we can see, the input portion is working correctly.
Read More: How to Use Database Functions in Excel (With Examples)
Similar Readings
- How to Maintain Customer Database in Excel
- Create a Relational Database in Excel (With Easy Steps)
- How to Create a Searchable Database in Excel (2 Quick Tricks)
- Intro to Relational Database Management System (RDBMS) Concepts!
Step 4: Perform Necessary Calculations
Next, we need to automate the subtotal calculations and total order calculations while we create this invoice template with the database in Excel.
- Initially, to automate the calculations in the “Subtotal” column, select cell F6 and write down the following formula.
=IF(ISBLANK(B6),"",D6*E6)
- Then press Enter.
- Now, select the range C6:F6 and drag the fill handle down to replicate these formulas for the rest of the cells.
- Next, select cell F13 and write down the following formula in it.
=SUM(F6:F12)
- Then press Enter.
- For cell F14, write down the following formula.
=F13*7.5%
- Then press Enter.
- Finally, insert the following formula in cell F15 for the “Grand Total” calculation.
=SUM(F13:F14)
- After that, press Enter.
At this point, we can say our invoice template is complete. Let’s test this out after inserting a sample unit for our first product.
As we can see, the template is working as intended.
Read More: How to Create a Database That Updates Automatically in Excel
Step 5: Final Output
Now let’s fill out the template with product IDs in the “ID” column and their ordered units. Here is what it looks like, finally.
Thus the creation of the invoice with the database in Excel is now complete.
Read More: How to Create Inventory Database in Excel (3 Easy Methods)
Conclusion
These are all the steps you can follow to create an invoice in Excel with a database. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.
