Written by: JJ Tan, Founder, Jelly

Key Takeaways for UK Hospitality Teams

  • UK hospitality businesses lose hours each month manually retyping supplier invoices into spreadsheets, which creates hidden costs and delays margin decisions.
  • Excel invoice automation relies on named Tables for master data, XLOOKUP formulas to auto-populate templates, and sequential numbering that meets HMRC VAT rules.
  • Correct UK VAT formatting, GBP currency display, and DD/MM/YYYY dates keep every invoice compliant.
  • VBA macros or Power Automate flows can automate PDF export and email delivery, but Excel reaches its limit once invoice volume, supplier variety, or multi-site needs make manual maintenance unreliable.
  • Jelly automates supplier invoice scanning, real-time dish costing, and margin reporting for restaurants, pubs, and hotels at a flat £129 per month per location.

See How Jelly Automates Your Invoicing

Before You Build Your Excel Invoice System

Confirm a few basics before you start building anything in Excel.

  • A Microsoft 365 or standalone Excel licence. Limited Power Automate use rights are included with select Microsoft 365 and Office 365 licences, as detailed in Microsoft's Power Platform Licensing Guide, so check your subscription before reaching Step 6.
  • Supplier invoice data, client or sales records, and product or ingredient data.
  • Access to your accounting records and your VAT registration number.

Ownership of this process typically sits with the owner, finance manager, or operations manager, with input from the head chef on ingredient and supplier data. Set realistic expectations:

This is an ongoing process, not a one-off task. HMRC requires invoice numbering to be sequential within a series, and gaps caused by cancelled or spoiled invoices are acceptable if the cancelled document is retained or the break can be explained.

Why Automated Invoicing Matters for UK Hospitality

Manual invoice entry drains finance time without improving margins. Handling 50 to 100 invoices a month at five to ten minutes per invoice consumes several hours of finance time every month, and that time produces no margin insight. Every hour spent retyping is an hour not spent reacting to a supplier price increase or adjusting a menu line that has turned unprofitable.

Effective invoice automation reduces manual errors, speeds up invoice turnaround, and improves visibility of supplier spending. That extra visibility lets a kitchen running multiple suppliers at fluctuating prices catch margin problems this week instead of discovering them in next month's accountant's report.

There is also a compliance angle. Under HMRC's Making Tax Digital for VAT rules, VAT-registered businesses must keep digital records and submit VAT returns using MTD-compatible software. Building a disciplined, structured Excel system now makes that transition easier and shows clearly when the spreadsheet setup has reached its ceiling.

Talk to Jelly About Automating Your Invoices

With those stakes in mind, walk through the build step by step.

Step 1: Set Up Your Master Data Sheets

Clean, structured master data underpins every automated invoice in Excel. Create two separate worksheets: one for Clients and one for Products or Ingredients.

On each sheet, select your data range and press Ctrl+T to convert it into a named Excel Table. Use the Table Design tab to give each table a clear name, such as Clients and Products. Include the following columns:

  • Clients table: Client Name, Address, VAT Number, Payment Terms.
  • Products table: Description, Unit Price, VAT Rate, Unit of Measure.

Named Tables expand automatically as new rows are added. Your formulas keep working when you onboard a new supplier or add a menu ingredient, which makes the rest of the build far more reliable.

Step 2: Build a Dynamic Invoice Template

The Invoice worksheet becomes your front-end template that pulls everything together. Create a separate Invoice sheet, then use data validation dropdowns so users can select a client name and a product. This approach removes free-text entry errors at source.

Use XLOOKUP to pull data from the master tables automatically. XLOOKUP searches a range or array and returns the item corresponding to the first match, using separate lookup and return arrays, which makes it more flexible than VLOOKUP here.

Example syntax for pulling a client address based on a selected client name in cell A2:

=XLOOKUP(A2,Clients[Client Name],Clients[Address])

To pull multiple fields at once, such as both address and VAT number, extend the return array:

=XLOOKUP(A2,Clients[Client Name],Clients[Address]:Clients[VAT Number])

Apply the same pattern to the Products table so that selecting a product auto-fills its description, unit price, and VAT rate. This Excel invoice template with formulas approach lets the invoice populate itself from a single dropdown selection, without retyping.

Step 3: Automatically Generate Invoice Numbers in Excel

Sequential invoice numbering is a legal requirement for UK VAT invoices. HMRC requires VAT invoices to carry a sequential number based on one or more series that uniquely identifies the document, as set out in the Value Added Tax Regulations 1995, regulation 14(1)(a), and explained in VAT Notice 700/21 section 4.1 and HMRC manual VATREC5010.

Formula method:

  1. Create a sheet named InvoiceLog with a column called Number that records every issued invoice number as a plain integer.
  2. Store the last issued number in that column and add a new row each time an invoice is issued.
  3. On the Invoice sheet, generate the next number with: ="INV-"&TEXT(MAX(InvoiceLog[Number])+1,"0000")

This produces sequential identifiers in the format INV-0001, INV-0002, and so on. HMRC's VATREC5010 guidance confirms that invoice numbers do not have to be digits only, and letters, numbers, or a mix are acceptable provided the series is unique and sequential.

VBA method:

Use a macro when you want Excel to handle the numbering and logging in one click. Open the VBA editor with Alt+F11 and insert the following macro into a standard module. This macro reads the highest number from your InvoiceLog, increments it by one, writes the formatted number to the invoice sheet, and appends the new number to the log so the sequence stays intact.

Sub GenerateInvoiceNumber() Dim ws As Worksheet Dim logWs As Worksheet Dim lastNum As Long Dim nextNum As Long Set ws = ThisWorkbook.Sheets("Invoice") Set logWs = ThisWorkbook.Sheets("InvoiceLog") lastNum = Application.WorksheetFunction.Max(logWs.Columns(1)) nextNum = lastNum + 1 ws.Range("C3").Value = "INV-" & Format(nextNum, "0000") logWs.Cells(logWs.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = nextNum End Sub

Run this macro before issuing each invoice. It keeps numbering sequential and avoids gaps or duplicates caused by manual edits.

Step 4: Apply UK VAT Rules and Clear Formatting

A UK-compliant invoice must show net amount, VAT amount, and gross total separately. HMRC's VAT Notice 700/63, section 3.2, requires that when more than one VAT rate is charged in the same document, each description must carry its own rate, net amount, and VAT amount.

Add a VAT calculation column to your line items table. The standard UK VAT rate is 20%. However, some goods and services are zero-rated or reduced-rate. For hospitality, cold takeaway food is zero-rated, while hot food and hot drinks are standard-rated at 20%. Split each rate onto its own line, giving each one its own net amount and VAT amount.

Format all currency cells as GBP with the £ symbol. HMRC allows amounts to be expressed in any currency for the underlying supply, but the total VAT chargeable must be expressed in sterling whenever positive-rate UK VAT is due. Set all date cells explicitly to DD/MM/YYYY format. Files shared across regional settings often revert to US format unless the cell format is hardcoded.

Include your VAT registration number in the invoice header. For cross-border supplies, HMRC requires the VAT registration number to be preceded by the letters GB.

Making Tax Digital for VAT requires digital records and MTD-compatible software for VAT-registered businesses. Additionally, from 2029, VAT invoices for business-to-business and business-to-government transactions will need to be issued electronically.

Step 5: Save an Excel Invoice as a PDF Automatically

Clients should receive a clean PDF, not a raw .xlsm file that exposes formulas and can break on their side. A simple VBA macro can export the active invoice sheet as a PDF named after the invoice number and save it alongside the workbook.

Open the editor with Alt+F11, insert a standard module, and paste:

Sub ExportInvoiceAsPDF() Dim ws As Worksheet Dim invoiceNum As String Dim savePath As String Set ws = ThisWorkbook.Sheets("Invoice") invoiceNum = ws.Range("C3").Value savePath = ThisWorkbook.Path & "" & invoiceNum & ".pdf" ws.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=savePath, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False MsgBox "Invoice saved as: " & savePath End Sub

Run the macro with Alt+F8, select ExportInvoiceAsPDF, and click Run. The invoice saves as a PDF named after the invoice number, such as INV-0042.pdf, in the same folder as the workbook. Assign the macro to a button on the invoice sheet for one-click export.

Step 6: Use Power Automate to Email Invoices From Excel

Excel alone cannot send invoices automatically. With a Microsoft 365 licence that includes Power Automate and Office Scripts, you can build a flow that generates the invoice file, saves it to SharePoint or OneDrive, and emails it to the client without manual steps. This setup becomes your Excel Power Automate invoice template.

Office Scripts automates repetitive Excel tasks across Excel for the web, Windows, and Mac, and can be integrated with Power Automate directly from the script card's More Options menu via the Automate a Task option. That script becomes the engine behind Office Scripts invoice automation.

The flow works as follows:

  1. Trigger: A scheduled recurrence or a manual trigger in Power Automate.
  2. Office Script: The script runs against the Excel workbook stored in SharePoint or OneDrive, populates the invoice template, and generates the output.
  3. PDF to SharePoint: Power Automate saves the exported PDF to a designated folder.
  4. Email to client: Power Automate sends the PDF as an email attachment to the client address stored in the Clients table.

The invoice workbook must be saved in OneDrive for Business or SharePoint because Power Automate cannot access locally stored files, and invoice data must be formatted as an Excel Table rather than a plain range because Power Automate's Excel actions read from tables.

Refer to Microsoft's official documentation for the full Power Automate and Office Scripts integration reference. If the Automate tab does not appear, verify that your Microsoft 365 licence includes Office Scripts and that your admin has not disabled it in the Microsoft 365 admin centre.

Even with Power Automate, Excel has a ceiling. The next step explains when the spreadsheet stops being an asset and starts being a liability.

Step 7: Recognise When Excel No Longer Scales for Invoicing

Excel works well until volume and complexity push it past its comfort zone. The threshold where it becomes a liability is reached when any of the following apply:

  • Invoice volume has grown to the point where manual log maintenance is unreliable.
  • Multiple suppliers send invoices in different formats, which makes consistent data capture impossible without retyping.
  • Multiple sites need a central source of truth that a shared .xlsm file cannot provide safely.
  • The owner or finance manager cannot trust the margin data because it depends on someone remembering to update a spreadsheet.

Tracking and follow-up become the real operational bottleneck as volume grows, even though invoice creation itself remains manageable. At that point, patching the spreadsheet costs more in management time than moving to a dedicated platform.

For growing restaurants, pubs, and boutique hotels, Jelly is the natural next step. Jelly automatically scans every line item of a supplier invoice, via photo or email, and feeds that data directly into real-time dish costing and margin reporting. It integrates with accounting software including Xero and works alongside POS systems including Square, EPOS Now, Lightspeed, and Toast. Because the pricing is a flat £129 per month per location, with no variable charges per user or feature, you can predict costs as you scale, and onboarding generates initial value in the first week.

Chef-Owner Murat Kilic of Amber, a Mediterranean restaurant in East London, previously used tedious manual costing and pricing with spreadsheets before Jelly automated supplier invoice processing and enabled real-time costing, saving £3,000–£4,000 per month.

Common Mistakes and Troubleshooting Tips

  • Broken XLOOKUP references: This usually means the tables are not named correctly. Rename them via the Table Design tab and update structured references throughout the workbook.
  • Duplicate or skipped invoice numbers: The log sheet was not updated before the invoice was issued. Always write to the log as part of the macro so the sequence stays accurate.
  • VAT calculated on the wrong line items: Zero-rated and standard-rated items sit on one line. Split each rate onto its own line, with its own rate, net amount, and VAT amount.
  • Macros not running: The file is saved as .xlsx rather than .xlsm. Save as a macro-enabled workbook. Saving an .xlsm as .xlsx deletes all VBA code.
  • Power Automate flows failing: The Office Script expects a different sheet name than the one in the workbook. Align sheet names exactly, or parameterise the script to accept the sheet name as an input.
  • UK date format reverting to US format: Files move across regional settings. Set the cell format explicitly to DD/MM/YYYY rather than relying on system locale.

Ask Jelly How to Reduce Invoice Admin

How to Measure Your Automation Success

A correctly built Excel invoice automation system produces clear, repeatable outcomes.

  • Every invoice carries the next sequential number with no gaps and no duplicates.
  • Client name, address, and VAT number populate automatically from the Clients table.
  • Product descriptions and unit prices populate automatically from the Products table.
  • VAT and GBP formatting are correct on every invoice, with each rate on its own line.
  • PDFs save to the correct folder named after the invoice number.
  • Where Power Automate is configured, the email sends without manual intervention.

Directional indicators of improvement include reduced admin time per invoice, fewer data entry errors caught at month-end, faster invoice turnaround to clients, and clearer visibility of supplier spending week to week.

Advanced Excel Extensions and When to Move to Jelly

Once the core build is stable, you can extend it to support deeper margin insight.

  • Link invoice data to a dish costing sheet so that ingredient price changes from supplier invoices flow automatically into recipe costs.
  • Add a supplier price tracking sheet that records unit prices over time, which makes it easier to spot creeping price increases before they erode margin.
  • Integrate sales data from a POS system to compare revenue against invoice costs and produce a working gross profit figure without waiting for the accountant.

For growing hospitality businesses, the next step after Excel invoice automation is often a dedicated platform like Jelly. Jelly automates invoice scanning and provides real-time margin insights across every dish and every supplier, without the maintenance overhead that comes with a hand-built spreadsheet system.

See Jelly in Action for Your Kitchen

Frequently Asked Questions

Can I Create an Automated Invoice in Excel?

Yes. The three components are named Excel Tables for master data, XLOOKUP formulas to populate the invoice template from those tables, and a VBA macro to export and save the completed invoice as a PDF. Each component is independent, so you can implement them in stages, and together they remove manual retyping at every step of the process.

How Can I Automatically Generate Invoice Numbers in Excel?

Use the formula method by creating an InvoiceLog sheet with a Number column, then generate the next number on the invoice sheet with ="INV-"&TEXT(MAX(InvoiceLog[Number])+1,"0000"). Alternatively, use the VBA method from Step 3, which reads the highest number from the log, increments it, writes the formatted number to the invoice sheet, and appends the new integer to the log in a single macro run. As covered in Step 3, HMRC requires a unique sequential series, and gaps from cancelled invoices are acceptable if the cancelled document is retained or the break can be explained.

Can Excel Send Invoices Automatically?

Excel does not send invoices natively. With a Microsoft 365 licence that includes Office Scripts and Power Automate, you can build a flow that generates the invoice, saves the PDF to SharePoint or OneDrive, and emails it to the client on a schedule or trigger, as described in Step 6. Without that licence, the PDF export step requires a manual macro run and the email must be sent manually.

Is Excel Invoicing HMRC-Compliant?

Sequential numbering is achievable in Excel and satisfies the HMRC requirement for a unique sequential series. As mentioned in Step 4, Making Tax Digital for VAT requires digital records and MTD-compatible software for VAT-registered businesses, and HMRC requires that data cannot be manually re-typed or transferred between systems as part of the VAT return submission process. Excel alone does not satisfy the digital links requirement for VAT return submission, and the data must flow into MTD-compatible accounting software such as Xero without manual re-entry.

What Happens When I Have Too Many Invoices for Excel?

The threshold is not purely a number. It is reached when invoice volume, supplier variety, or multiple sites make manual data entry unreliable for accurate margin information. Common signals include month-end closing taking several days, receivables tracking becoming confused, and margin data that cannot be trusted without cross-checking multiple files. At that point, the maintenance cost of the spreadsheet system exceeds the cost of dedicated software. See Step 7 for the full scalability verdict and an introduction to what Jelly provides beyond that ceiling.

Conclusion

Excel can genuinely automate your invoicing when it is built properly. Named Tables for master data, XLOOKUP to populate the template, sequential numbering that satisfies HMRC's VAT invoice rules, correct UK VAT and GBP formatting, and a VBA or Power Automate route to PDF export and delivery together give UK restaurants, pubs, and hotels clarity, control, and repeatability in a process that currently costs hours of admin every week.

Excel invoice automation still has a ceiling. When supplier volume grows, when multiple sites need a single source of truth, or when margin data needs to be live rather than last month's, the spreadsheet becomes the bottleneck rather than the solution. For growing hospitality businesses ready to move beyond that ceiling, Jelly automates invoice scanning, inventory, and real-time menu profitability at the flat rate mentioned earlier, with onboarding value delivered in the first week.

To see how invoice automation works in practice for a kitchen at your stage of growth, book a demo below.

Book Your Jelly Demo