Invoice Database Excel - Free Template

1. Open the Lists & Instructions tab and review the customer records in rows 20 through 119. Replace the sample customer information with your own customer IDs, names, addresses, email addresses, and phone numbers.
2. Go to the Invoice tab and enter or select an Invoice Number, Invoice Date, Customer ID, Payment Terms, Due Date, sales tax rate, payment status, and notes. Selecting a Customer ID fills the customer details from the customer list.
3. Complete the line-item area beginning at row 16. Enter the item or service, description, quantity, unit price, discount percentage, and whether the line is taxable.
4. Review the calculated Subtotal, Discount Amount, Tax Amount, and Line Total columns. The invoice status indicator uses the due date and recorded payment information to show OPEN, PAID, or OVERDUE.
5. Copy the invoice information into the next available row of Invoice Database. Enter the invoice details, amount paid, sales representative, payment method, and notes in the register.
6. Check the calculated Invoice Total, Payment Status, Balance Due, and Days Outstanding columns. Use the filters on the register to review unpaid or overdue invoices.
7. Return to Lists & Instructions to review totals by customer, payment-status counts, state-level open balances, and the three charts. Save the XLSX file after each update.

A small service company can use this workbook when it needs a presentable invoice and a basic accounts receivable register without keeping customer details in several separate files. The Invoice tab is designed for one document at a time, while the Invoice Database stores the broader billing history.

For Service Businesses And Contractors

A consultant can select CUST-1001, enter a consulting line with a quantity of 8 and a unit price of $150.00, and let the worksheet calculate the $1,200.00 subtotal before discount and tax. The sample workbook also shows a second service line, which demonstrates how recurring plans and project work can appear on the same invoice.

An office manager at a contractor can use the register after sending each invoice. The database includes invoice date, due date, customer, city, state, subtotal, discount, tax, invoice total, amount paid, sales representative, payment method, and notes. That is enough detail to review a customer account without reopening every invoice.

For Bookkeepers And Office Managers

Bookkeepers can use the Invoice Database during a weekly collection review. The Payment Status column identifies Open, Paid, or Overdue records, while Balance Due and Days Outstanding show which accounts still require attention.

The Lists & Instructions tab adds a management view rather than another invoice form. It summarizes invoice totals by customer, counts records by payment status, and groups open balances by state. The three charts make those same ranges easier to scan during a billing meeting.

The workbook uses formulas rather than requiring you to calculate every amount manually. On the Invoice tab, the customer ID in B6 retrieves customer fields from the Lists & Instructions range with VLOOKUP. The lookup fills the customer name, billing address, city, state, ZIP Code, email, and phone fields.

Line-Level Calculations

For each populated line from row 16 through row 115, Subtotal is Quantity multiplied by Unit Price. Discount Amount is calculated from the discount percentage, and Tax Amount is calculated only when Taxable? is set to Yes. Line Total combines the discounted amount and tax amount.

For example, the sample consulting line uses 8 units at $150.00 and a 5% discount. Its $1,200.00 subtotal produces a $60.00 discount before the applicable tax calculation. Treat the sales tax rate as an editable workbook input; the file does not determine whether a transaction is taxable or establish a current state rate.

Register Status And Aging

In Invoice Database, Invoice Total is calculated from subtotal, discount, and tax. Balance Due is Invoice Total minus Amount Paid, with a floor of zero. Payment Status returns Paid when the balance is zero, Overdue when the due date has passed, and Open otherwise.

Days Outstanding counts days from the due date while a balance remains. The register has prepared rows 2 through 101, and the customer list has records through row 119. Because the workbook is delivered in XLSX format, check formula recalculation in your spreadsheet application after changing dates or payment amounts.

The most expensive errors in a billing register usually begin with inconsistent identifiers. If you type a customer name instead of selecting the correct Customer ID, the Invoice tab may not retrieve the matching address and contact fields. Use the available customer dropdown and keep each ID unique.

Manual Edits That Break The Billing Trail

Another problem occurs when an invoice is prepared but never entered into Invoice Database. The customer may receive a document, yet the dashboard will not include its total, status, or open balance. A simple control is to compare the latest invoice number on the Invoice tab with the register before closing the billing session.

Payment records also need discipline. Entering $1,308.00 as Amount Paid for a $1,308.00 invoice correctly produces a zero balance and Paid status in the sample structure. Entering a partial payment without updating the register leaves Balance Due and Days Outstanding wrong, which can lead to an unnecessary collection email.

Tax And Discount Mistakes

Applying Taxable? as Yes to a non-taxable service, or entering 9% as 9 instead of 0.09, materially changes the tax calculation. The workbook formats rates as percentages, so enter a percentage value such as 9% or 0.09 rather than a whole-number 9.

Do not overwrite formula columns while correcting an invoice. On Invoice, the calculated fields are H through K for the line area; in Invoice Database, the calculated fields include Invoice Total, Payment Status, Balance Due, and Days Outstanding. Keep source inputs and calculated outputs separate so later reviews remain traceable.

Use the workbook at a fixed point in your billing cycle instead of waiting until several invoices are overdue. A practical routine is to prepare invoices, record them in Invoice Database, and review the dashboard during the same weekly administrative block.

A Repeatable Three-Part Check

• Prepare: select the customer ID, confirm the due date and payment terms, and complete every populated line item.
• Record: enter the invoice in the next available database row and add the amount paid, payment method, sales representative, and notes.
• Review: filter the register for Open or Overdue records, then compare the dashboard's open balances with the detailed rows.

Use the existing dropdowns to reduce spelling differences. Select payment terms from Due on receipt, Net-15, Net-30, or Net-45; select payment methods from ACH, Credit Card, Check, or Bank Transfer; and use Yes or No for taxable lines.

When To Move Beyond The Workbook

This file is a practical register for the prepared 100 invoice rows and customer records supplied in the workbook. If your team needs more capacity, automatic invoice delivery, bank-feed matching, user permissions, or an audit history, move the process to dedicated accounting software or an invoicing platform.

Until then, save dated copies of the XLSX file and avoid changing the formula columns. That preserves the calculations and gives you a clear month-by-month record of invoice totals, collections, balances, and aging.