Build a Self-Updating Excel Invoice & Tracker (Free Template)

Setting Up the Invoice Template

• 💾 Save the file as an Excel Macro Enabled Workbook (.XLSM) to enable automation.
• 🎨 Format the invoice area with a pale gray shade and add a bold, right-aligned heading.
• 🏢 Input company details and essential invoice information, including invoice number, date (using TODAY function), and due date.
• 📦 Create an invoice items table with columns for Product Code, Description, Quantity, Unit Price, Discount, and Price, formatted using Ctrl+T.
• 💰 Calculate the price using a formula: Quantity * Unit Price * (1 - Discount), rounded to two decimal places.
• 🚫 Apply custom number formatting to hide zero values in the price column for a cleaner look.
• 📊 Add subtotal, tax, and total fields, ensuring the total amount is correctly referenced and formatted as currency.

Automating Invoice Tracking

• 📄 Rename the invoice sheet to 'Invoice' and create a new sheet named 'Tracker'.
• 📝 Set up tracker columns for Invoice, Date, Due, Amount, Customer, and Paid Status, formatted as a table.
• ✅ Insert checkboxes in the 'Paid Status' column for easy tracking (or use data validation).
• ⚙️ Enable the Developer tab in Excel by right-clicking the ribbon and selecting 'Customize Ribbon'.

VBA Macros for Automation

• 💻 Open the Visual Basic editor from the Developer tab to write macros.
• 🚀 Create a Record Invoice macro to copy invoice details to the tracker table.
• ➡️ Declare variables for invoice number, dates, amount, customer, sheets, and the table object.
• 🔗 Set worksheet and table objects using their VBA object names (e.g., Sheet1, Sheet2, 'Invoice Tracker').
• ✍️ Read values from the invoice sheet (e.g., G9 for invoice number, G10 for date) and assign them to variables.
• 📍 Determine the target row in the tracker table to write the data, handling cases where the table is empty or has existing rows.
• 📤 Write the captured invoice data to the appropriate cells in the tracker table.
• ✉️ Display a confirmation message like 'Invoice recorded' upon successful data transfer.
• 🔄 Create a Reset Invoice macro to clear the invoice form and prepare for a new entry.
• 🔢 Increment the invoice number in cell G9 by one.
• 🧹 Clear contents of the invoice items table (Product Code to Discount) and the 'Bill To' section (G14 to G18).
• ▶️ Assign macros to buttons (shapes) on the invoice sheet for easy execution of 'Record Invoice' and 'Reset Invoice' functions.

Enhancing the Tracker

• 🎨 Apply conditional formatting to the tracker table to highlight overdue invoices.
• ⚠️ Set a rule using the AND function: if 'Paid Status' is not 'True' AND 'Due Date' is less than today's date, format the row in a shade of pink.
• 🔍 Utilize filter buttons on the tracker table to sort and analyze data by customer, date, or payment status.
• 🔗 Download the free template from the video description or explore advanced Excel automation techniques in related videos.