In a professional context where every minute counts, knowing how to create and automate an invoice in Excel can really make a difference. I still remember my first internship: I spent hours manually adjusting each amount, copy-pasting totals, hunting for errors… A nightmare. Today, I offer you a complete guide to set up a smart invoice template that calculates everything automatically, from the product list to the total including tax. Result? More time for your clients and less stress for you.
1. Designing the structure of your invoice
Even before adding the slightest formula, you need to clearly define your layout. Here are the key areas to plan:
- Header: logo, company name, contact details, and invoice number.
- Client information: company name, address, contact.
- Items table: description, quantity, unit price, line total.
- Summary: subtotal, VAT, total including tax.
- Footer: legal notices, payment terms.
If you create a template to keep, place these areas in a separate tab that will serve as a “template,” and leave another tab for raw data. This way, you can easily duplicate the template and automatically feed it with new data.
1.1. Organizing cells and managing formatting
For a professional look, merging or splitting cells correctly is often essential. For example, the header that spans several columns must be merged, while the detailed rows of the items table do not require merging. To know how to proceed properly, check out our tip on how to merge and split cells without degrading the structure of your file.
2. Feeding the invoice with dynamic data
The advantage of an automated invoice is that by simply modifying your database (product list, prices, discounts, etc.), all invoices update instantly. For this, we rely on two pillars:
- Structured tables to reference your products.
- Lookup formulas to retrieve the necessary information.
2.1. Creating a product table
Select your product data (reference, designation, unit price, VAT rate) and convert it into a Table (Insert > Table). This table will allow you to:
- Filter and sort easily.
- Automatically add new rows without disrupting your formulas.
- Use structured references in your formulas (e.g.
=[@Désignation]).
2.2. Retrieving product details
To automatically fill each line of your invoice, you can use the VLOOKUP function, ideal as long as your product list is sorted by reference. For example:
=RECHERCHEV($A2;Table_Produits;2;FAUX)
to retrieve the “Designation” corresponding to the reference in A2.
If you prefer more flexibility (lookup by column regardless of position), the INDEX / MATCH function also works very well. Discover our tips in the guide on VLOOKUP.
3. Calculate totals and taxes
Once the basic data is filled in, you need to calculate the pre-tax amount (HT), the VAT, and the total including all taxes (TTC). Here are the key formulas:
- Line total HT:
=Quantité * PrixUnitaire - VAT amount:
=TotalLigneHT * TauxTVA - Total TTC:
=TotalLigneHT + MontantTVA
To get the Subtotal HT of all lines, you can use SOMME:
=SOMME(C2:C20)
Or, if you want to filter certain items (for example, only those subject to 20% VAT), the SUMIF function becomes very useful. It allows you to sum HT totals conditionally. To learn more about its use, see this article on SUMIF and SUMIFS.
4. Automate the generation of multiple invoices
To issue multiple invoices without starting from scratch each time, two main approaches:
- Copy-paste the template: simply duplicate the “Invoice” tab and modify the key cells (number, client, date).
- VBA Macro: record a macro or write a small VBA script that will create a new tab, replace the values, and save the sheet as a PDF. It’s more technical but ultra-fast on a daily basis.
Even without being a VBA developer, you can record a Macro that:
- Copies the template tab.
- Asks the user to enter the number and client.
- Updates the appropriate cells.
- Saves as PDF in a chosen folder.
The advantage? Zero risk of forgetting fields or shifting a column: the macro does everything.
5. Customize and secure your document
Some tips to give a professional look and protect your invoices:
- Use cell styles to quickly apply the same formatting.
- Insert your logo at the top (Insert > Image) and adjust the size so it remains sharp when printed.
- Lock cells containing formulas (Format Cells > Protection > Locked), then protect the sheet (Review > Protect Sheet).
- Add an automatic date with
=AUJOURDHUI()to always display the edition date.
Conclusion
In just a few steps, you have seen how to structure your invoice template, integrate product tables, work with VLOOKUP, SUMIF and other formulas, and finally automate the creation of multiple documents. Result: a considerable time saving, reduced errors, and a guaranteed professional result.
Don’t hesitate to experiment and adapt these techniques according to your needs. Your next invoices will be done in the blink of an eye!
FAQ
- Can an invoice be generated automatically in PDF?
- Yes, by combining a VBA macro that saves the tab as PDF as soon as the data is updated.
- How to add a global discount on the invoice?
- Insert a “Discount” line below the HT subtotal and subtract it before calculating VAT.
- Is it possible to send the invoice by email from Excel?
- With VBA, you can automate sending it as an attachment via Outlook.
Additional resources
- Text functions in Excel: to format references and labels.
- COUNTIF and COUNTIFS: for conditional counts on your database.
