Invoicing is one of those business tasks that is deceptively simple but easy to get wrong — wrong totals, missing VAT calculations, inconsistent formatting, or forgotten payment terms can delay payment and create a poor impression with clients. Creating a properly automated invoice template in Microsoft Excel solves all of these problems. Once set up, the template calculates everything automatically, looks consistently professional, and can be completed and sent in under two minutes. This guide walks you through building one from scratch.
Planning Your Invoice Layout
Before opening Excel, sketch out what your invoice needs to contain. A standard UK business invoice typically includes:
- Your business name, address, phone number, and email
- Your company registration number (if applicable) and VAT number (if VAT registered)
- Invoice number and invoice date
- Client name and address
- Payment due date
- A line-item table with description, quantity, unit price, and line total
- Subtotal, VAT amount (if applicable), and grand total
- Payment terms and bank details (sort code, account number, or bank transfer reference)
Note that HMRC requires VAT invoices to include specific information. If your business is VAT registered, ensure your template is compliant before using it with clients.
Setting Up the Spreadsheet
Step 1: Configure the Sheet
Open a new Excel workbook. Rename the first sheet “Invoice” by double-clicking the tab. Set the page layout to A4 (Page Layout > Size > A4) and the orientation to Portrait. Set all margins to 1.5 cm (Page Layout > Margins > Custom Margins) so the content fits neatly on a printed page.
Adjust column widths to create a practical layout. A good starting configuration: Column A at 0.5 cm (a narrow left margin), Column B at 7 cm (description), Column C at 3 cm (quantity), Column D at 3 cm (unit price), and Column E at 3.5 cm (line total). Merge cells in the header area as needed to create wider zones for your business name and logo.
Step 2: Build the Header Section
In rows 1 to 8, create your business header. Merge cells B1:E1 for your business name and format it with a large, bold font (14pt or 16pt). Below that, enter your address, phone, email, and registration details in smaller text (9pt or 10pt).
If you have a business logo, insert it via Insert > Pictures > This Device. Position it to the right of your business name using the “Square” or “Top and Bottom” text wrapping mode, and fix its size and position (right-click > Format Picture > Properties > “Don’t move or size with cells”).
Step 3: Create the Invoice Details Section
In rows 10 to 16, create two side-by-side blocks: one for “Invoice To” (client details) and one for the invoice metadata (invoice number, date, due date). Label cell B10 with “Invoice To:” in bold, then leave cells B11:B14 blank for the client name and address — these will be filled in manually each time.
In column D, rows 10 to 13, create labelled fields for Invoice Number, Invoice Date, Due Date, and VAT Number. Format the date cells using the dd/mm/yyyy format, which is the standard UK date format.
Building the Line Item Table
Step 4: Create the Table Headers
In row 18, create the column headers for your line items. Use cells B18:E18 with the labels: Description, Quantity, Unit Price (£), and Total (£). Format this row with a background fill colour matching your brand (or a dark grey/navy for a neutral professional look) and set the text to white, bold. Add a bottom border to visually separate the header from the item rows.
Step 5: Enter Line Item Formulas
Create 10 to 15 line item rows (rows 19 to 33). In column B, enter the description (left blank for manual entry). In column C, enter the quantity. In column D, enter the unit price. In column E (Total), enter the formula =IF(B19=””,””,C19*D19). The IF formula prevents the column from showing £0.00 for blank rows — it only shows a value when a description has been entered. Copy this formula down through all line item rows. Format columns D and E as Currency with the £ symbol and two decimal places.
Step 6: Use Conditional Formatting for Alternating Rows
To make the template visually clear, use conditional formatting to shade alternating rows in the line item section. Select B19:E33, go to Home > Conditional Formatting > New Rule > Use a formula. Enter =MOD(ROW(),2)=0 and set the fill colour to a very light grey. Click OK. This creates alternating row shading that makes the table easier to read.
Adding the Totals Section
Step 7: Calculate the Subtotal
In row 35, add the subtotal. In cell D35, type “Subtotal:” (bold, right-aligned). In cell E35, enter =SUM(E19:E33). This sums all line item totals automatically.
Step 8: Calculate VAT
In row 36, add VAT. In cell D36, enter “VAT (20%):” and in cell E36, enter =E35*0.2. If your VAT rate varies, replace 0.2 with a reference to a cell that holds the rate. This makes it easy to update the rate without modifying formulas throughout the template.
Step 9: Calculate the Grand Total
In row 37, add the grand total. In D37, enter “Total Due:” (bold) and in E37, enter =E35+E36. Format E37 with a box border, bold text, and a background colour to make it stand out. This is the most important number on the invoice — it should be unmissable.
Adding Payment Details and Terms
Step 10: Payment Information
In rows 39 to 44, add your payment details including bank name, account name, sort code, account number, and payment reference instructions. For businesses that accept PayPal or other payment methods, include those details here too. Format this section in a smaller font (9pt or 10pt) with a light background box to visually separate it from the invoice body.
Step 11: Add Standard Terms
In the final rows, add your standard payment terms: for example, “Payment is due within 30 days of the invoice date. Late payments may be subject to interest under the Late Payment of Commercial Debts Act 1998.” This protects you legally and sets clear expectations for clients.
Automating the Invoice Number
One useful automation is a sequential invoice number system. Create a second sheet called “Settings” in the same workbook. In cell A1 of that sheet, type “Next Invoice Number:” and in B1, enter the number you want to start from (e.g., 1001). On the Invoice sheet, set the invoice number cell to reference Settings!B1. Update the Settings sheet manually each time you issue a new invoice, or use a simple macro button to increment it automatically.
Protecting the Template
To prevent accidental edits to your formulas and formatting, protect the sheet while leaving the input cells editable. First, select all the input cells (description, quantity, price, client details, invoice number, dates) and uncheck the “Locked” option (Home > Format > Format Cells > Protection > uncheck “Locked”). Then protect the sheet (Review > Protect Sheet) with a password. Now only the designated input cells can be edited, and all formulas and formatting are safe.
Saving and Using the Template
Save the completed file as an Excel Template (.xltx) via File > Save As > Excel Template. Each time you need to issue an invoice, open the template (which creates a new, unsaved copy), fill in the client details and line items, then save the completed invoice as a regular .xlsx file with the invoice number as the filename. Send it to the client as a PDF — go to File > Export > Create PDF/XPS — for a format they cannot inadvertently edit.
Build Your Template with the Right Tools
This entire template can be built in Microsoft Office 2024 Professional Plus or Office 2021 Professional Plus, both available from GetRenewedTech for £29.99. If you are on a tighter budget, Office 2019 Professional Plus at £22.99 includes the full Excel feature set needed for this template. A well-built invoice template is an investment that pays for itself the first time it prevents a billing error or speeds up a payment.
