How to Create Invoice in Excel with Database (with Easy Steps)

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.

how to create invoice in excel with database

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.

Formation of Invoice Template

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.

Input Data in invoice from Database

  • 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


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.

Calculations in Invoice

  • 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.

automation of invoice in excel

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.

final output of invoice with database in excel

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.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo