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

How to Create Invoice in Excel with Database: Step-by-Step Procedure

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.

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.

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.

• Then press Enter.

• Now select cell D6 and insert the following formula in it.

• After that, press Enter.

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

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.

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

• Then press Enter.

• For cell F14, write down the following formula.

• Then press Enter.

• Finally, insert the following formula in cell F15 for the “Grand Total” calculation.

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

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.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

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.

Related Articles

• How to Create a Cash Book in Excel
• How to Do Bookkeeping for Small Business in Excel
• Excel Test for Accounting Interview
• Advanced Excel Test
• How to Create an Order Form in Excel

<< Go Back to Excel for Accounting | Learn Excel