How to Create an Excel Invoice Template That Auto-Fills Client Data (XLOOKUP Tutorial)
If you create invoices manually in Excel, you already know how repetitive it can be. Typing client details, entering pricing, calculating totals, and setting due dates takes time and increases the chance of errors.
In this tutorial, you will learn how to create an Excel invoice template that auto-fills client data using XLOOKUP, data validation, and a few simple formulas. Once your template is set up, you can generate professional invoices in just a few seconds.
If you want to follow along with the full tutorial, watch the video below.
Learn how to create an Excel invoice template that auto-fills
Why Use an Automated Excel Invoice Template?
Creating invoices manually can lead to:
Data entry errors
Inconsistent formatting
Wasted time
With automation, you can:
Instantly populate client details
Automatically pull pricing
Generate invoice numbers
Calculate due dates and totals
This is especially helpful for small business owners, freelancers, and professionals who rely on Excel daily.
What You’ll Learn
In this tutorial, you’ll learn how to:
Create a client database in Excel
Use data validation to create drop-down lists
Apply XLOOKUP to auto-fill client information
Automatically pull in product/service pricing
Generate dynamic invoice numbers
Calculate due dates based on payment terms
Build a clean, professional invoice layout
Step 1: Set Up Your Data Tables
To make your invoice dynamic, you’ll need:
A Clients sheet (name, ID, contact info, payment terms)
A Products/Services sheet (items and pricing)
These act as your data sources for automation.
Step 2: Create a Drop-Down for Client Selection
The first step on the invoice sheet is to create a drop-down list for the client company name.
To do that:
Select the cell where you want the client name
Go to the Data tab
Choose Data Validation
Under Allow, select List
Set the source to the client name column on your Clients sheet
Now you can select a client from the list instead of typing it manually each time.
Step 3: Use XLOOKUP to Auto-Fill Data
XLOOKUP allows you to match the selected client and return related information from your database.
You can use it to pull:
Client details
Payment terms
Pricing information
💡 Tip: Wrap your formulas in an IF statement to keep cells blank until a selection is made.
Step 4: Generate Invoice Numbers Automatically
You can combine:
Static text (like “INV”)
Client ID
Today’s date
Using formulas like:
TODAY()
TEXT()
This creates a unique invoice number every time.
Step 5: Calculate Due Dates
If your client list includes payment terms, you can use those terms to calculate the due date automatically.
For example:
Net 15 adds 15 days to the invoice date
Net 30 adds 30 days to the invoice date
Due on receipt displays that text directly
This makes the invoice even more useful because the payment deadline updates based on the client you choose.
Step 6: Pull Product or Service Pricing Automatically
Next, create another drop-down list for products or services using your Products and Services sheet.
When you select an item, use XLOOKUP again to return the correct unit price automatically.
This means you only need to:
Select the item
Enter the quantity or hours
Let Excel calculate the line total
This saves time and helps ensure pricing stays consistent.
Step 7: Auto-Calculate Totals
Once you select a product/service:
Price is pulled automatically
You enter quantity
Excel calculates totals
You can also:
Add tax calculations
Create a subtotal and final total
Step 8: Format the Invoice to Print on One Page
Once your invoice is complete, adjust your page layout settings so it prints cleanly or saves as a PDF.
A few simple formatting steps can help your invoice:
Fit to one page
Look more professional
Be ready to email to clients
This is a great final touch if you plan to send invoices directly from Excel as PDFs.
Download the Template
If you’d rather skip building from scratch, you can download the ready-to-use template here:
Final Thoughts
Creating an Excel invoice template that auto-fills client data is a smart way to simplify your workflow. With XLOOKUP and a few supporting formulas, you can reduce manual entry, improve consistency, and create invoices much faster.
If you enjoy practical Excel tutorials like this, be sure to check out my other posts and videos focused on automation, templates, and productivity.
What to Watch Next
Want to take it further?
Check out my next tutorial where I show you how to:
👉Create and email invoices in bulk using Excel
Stay Connected
If you found this helpful:
Subscribe to my YouTube channel
Visit my website for more templates and tutorials
Follow along for more Excel automation tips
Related Excel Tutorials
Frequently Asked Questions
Can I create an invoice in Excel without special software?
Yes. Excel can be used to create professional invoices using formulas, drop-down lists, and formatting tools.
What formula is best for auto-filling client information in Excel?
XLOOKUP is one of the best formulas for auto-filling related data from a client database.
Can I automatically calculate invoice due dates in Excel?
Yes. You can use IF formulas along with payment terms like Net 15 or Net 30 to calculate due dates automatically.
Can this Excel invoice template work for services and products?
Yes. You can use the same setup for both service-based and product-based invoices by pulling in the correct unit price and entering quantity or hours.
Can I save the invoice as a PDF?
Yes. Once formatted properly, you can print the invoice or save it as a one-page PDF directly from Excel.
