Hi ,

Before we dive into today’s Excel Newsletter, just a quick heads-up that for a limited time all our courses are 20% off, including upgrades and renewals.

Ok, back to business.

Excel Automated Invoice Template

If you've ever wanted to create an invoice system in Excel that automatically updates, records, and clears itself—you're in the right place.

In this post, I’ll walk you through building an automated invoice template from scratch using Excel formulas, tables, and a touch of VBA magic. Whether you’re a freelancer, small business owner, or just want to level up your Excel game, this tutorial has you covered.

Prefer to read this on our website? 

Then click this link >>> Excel Automated Invoice Template

Video: Build a Self-Updating Excel Invoice & Tracker (Free Template) | No Add-ons

Watch on YouTube

Step 1: Design the Header Layout

Open a new Excel file and save it as a .xlsm Macro Enabled Workbook. Then set up the invoice header area. Mine looks like this, but you can modify it to suit your needs:

For a clean design:

• Shade the invoice cells B3:I77 a pale grey (don’t do this if you print your invoices).
• In cell H8, enter “INVOICE” with a bold 28pt font.
• Add a logo in the top left.
• Insert company info in C12:C17 (e.g., address, email, tax identifiers).
• Add invoice details in F9:F18 including:
  • Invoice #: Cell G9 → Start at 1000 or 10000 (or your next number)
  • Invoice Date: =TODAY()
  • Due Date: Use a formula to calculate. E.g. net 30 days: =G10+30 or net 30 EOM: =EOMONTH(G10,0)+30
  • Amount: This will be linked later.

Step 2: Build the Invoice Table

Create a table for itemised entries:

• Start in cell C22 with headers: Product Code, Description, Qty, Unit Price, Discount, Price (modify to suit your needs as required).
• Select the headers and press Ctrl + T to format as a table
• Name the table (Table Design tab): InvItems

• Set formatting:
  • Currency format for Unit Price and Price
  • Percentage for Discount
  • Custom number format on Price column to hide zeros: $#,##0.00;-$#,##0.00;

Price Formula

In the Price column, use:

=ROUND([@Qty]*[@[Unit Price]]*(1-[@Discount]), 2)

Step 3: Calculate Totals

Below the table (e.g., cells G27:G30), insert:

• Subtotal: =SUM(InvItems[Price])
• Tax Rate: Enter 10%
• Tax Amount: =ROUND(H27*H28,2)
• Total: Subtotal + Tax Amount (=H27+H29)
• Format totals with borders and centred text.

Then, define a name for the Total cell (e.g., Total) and link G12 (Amount) to it: =Total

Step 4: Add Notes and Bank Details

• Add a Notes section in cell C33
• Add Bank Details starting at cell C37
  This includes bank name, account number, etc. required for your customer to pay you.

Step 5: Set the Print Area

Select B3:I45 (or the relevant cells for your invoice area) and go to:

Page Layout tab > Print Area > Set Print Area

Adjust page setup:

• On the Page tab > Fit to 1 page wide x 1 page tall
• On the Margins tab > Center horizontally & vertically

Step 6: Create the Invoice Tracker

Add a new sheet and rename it Tracker. Insert headers - these are the fields from the Invoice that you want to record. Modify as required:

• Invoice, Date, Due, Amount, Customer from the invoice and an additional column to track the Paid Status

Format as a table (Ctrl + T) and name it InvTracker.

Insert a check box in the Paid Status column.

Step 7: Write the Record Invoice Macro

Enable the Developer tab (right-click Ribbon > Customize > check the box for the Developer tab), open the Visual Basic Editor:

In the Visual Basic Editor, select the Automated Invoice Excel file and via the Insert menu > insert a new module:

In the module, enter this macro to copy the data from the Invoice template to the Invoice Tracker:

Option Explicit
                        
            Sub RecordInvoice()
                           
            Dim inv As Long, invDate As Date, dueDate As Date
            Dim amount As Currency, customer As String
             Dim invoiceSheet As Worksheet, trackerSheet As Worksheet
            Dim tbl As ListObject, targetRow As Range
                        
            Set invoiceSheet = Sheet2
            Set trackerSheet = Sheet3
             Set tbl = trackerSheet.ListObjects("InvTracker")
                        
            inv = invoiceSheet.Range("G9")
            invDate = invoiceSheet.Range("G10")
            dueDate = invoiceSheet.Range("G11")
            amount = invoiceSheet.Range("G12")
            customer = invoiceSheet.Range("G14")
                        
            If tbl.DataBodyRange Is Nothing Then
            Set targetRow = tbl.ListRows.Add.Range
            ElseIf Application.CountA(tbl.DataBodyRange.Rows(1).Resize(1, 5)) = 0 Then
            Set targetRow = tbl.DataBodyRange.Rows(1) Else
            Set targetRow = tbl.ListRows.Add.Range
            End If
                        
            targetRow.Cells(1).Value = inv
            targetRow.Cells(2).Value = invDate targetRow.Cells(3).Value = dueDate
            targetRow.Cells(4).Value = amount
            targetRow.Cells(5).Value = customer
                        
            MsgBox "Invoice recorded!", vbInformation
            End Sub
            

Step 8: Write the Reset Invoice Macro

This clears the old invoice and increments the invoice number. 

Add the following code below ‘Option Explicit’ and above the ‘Sub RecordInvoice()’ macro:

Sub ResetInvoice()
            Dim inv As Long
            inv = Range("G9")
            Range("G9") = inv + 1
                            Range("G14:G18,InvItems[[Product Code]:[Discount]]").ClearContents
            Range("G14").Select
            MsgBox "Template is reset. New invoice number is " & inv + 1, vbInformation
            End Sub
            

Again, assign this macro to a shape or icon.

Step 9: Add Conditional Formatting

In the Tracker sheet, use Conditional Formatting to highlight overdue unpaid invoices.

Select the table rows (excluding the column headers). Then Home tab > Conditional Formatting > New Rule > ‘Use a formula to determine which cells to format’.

Formula:

=AND(NOT($F2), TODAY() > $C2)

Apply red fill if the invoice is overdue and not marked as paid.

Auto Create PDF and Email It

If you want to take your automated Excel invoice template to the next level, check out this post on how to create a PDF from an Excel worksheet and then email it.

Next Steps

Ready to take your skills deeper? Whether it’s core Excel skills, dashboards, or Power BI, I’ve got a full range of Excel and Power BI courses that walk you through everything step-by-step.

They come with a certificate of completion, and support from me if you ever get stuck.

Thousands of students have taken them, not just to level up their skills. I’ve heard from people who’ve built tools that save their team hours every week, landed promotions, or finally felt confident enough to freelance on the side.

So, if you’re ready to stop Googling Excel and actually master it, there’s no time like the present. Check out our Excel and Power BI courses here.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub

Want to sponsor our newsletters?  Just reply to this email to get in touch with us.