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.

Watch the Video

Get the Template

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

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 Margins tab > Center horizontally & vertically
• On the Page tab > Fit to 1 page wide x 1 page tall

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
    Dim invDate As Date
    Dim dueDate As Date
    Dim amount As Currency
    Dim customer As String
    Dim invoiceSheet As Worksheet
    Dim trackerSheet As Worksheet
    Dim tbl As ListObject
    Dim targetRow As Range

    Set invoiceSheet = Sheet2
    Set trackerSheet = Sheet3
    Set tbl = trackerSheet.ListObjects("InvTracker")

    ' Read values from invoice
    inv = invoiceSheet.Range("G9").Value
    invDate = invoiceSheet.Range("G10").Value
    dueDate = invoiceSheet.Range("G11").Value
    amount = invoiceSheet.Range("G12").Value
    customer = invoiceSheet.Range("G14").Value

    ' Safely decide where to write the data
    If tbl.DataBodyRange Is Nothing Then
        ' Table has no rows — add one
        Set targetRow = tbl.ListRows.Add.Range
    ElseIf Application.CountA(tbl.DataBodyRange.Rows(1).Resize(1, 5)) = 0 Then
        ' First row is empty — reuse it
        Set targetRow = tbl.DataBodyRange.Rows(1)
    Else
        ' Add new row
        Set targetRow = tbl.ListRows.Add.Range
    End If

    ' Write values to the table
    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

Assign this macro to a Shape button for easy use.

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

' Issue new invoice number
inv = Range("G9")
Range("G9") = inv + 1

' Clear cells ready for new invoice details
Range("G14:G18,InvItems[[Product Code]:[Discount]]").ClearContents
Range("G14").Select

' Confirm new invoice is ready and number
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.