How To Track Supplier Prices in an Excel Sheet: UK Guide

Written by: JJ Tan, Founder, Jelly

Key Takeaways

• A flat single-sheet price tracker overwrites prices and erases history. A four-sheet workbook (Suppliers, Products, Price History, Comparison) logs every price event as a new row.
• Price History works as an append-only log. Record invoice date, effective date, supplier, SKU, old price, new price, change %, and invoice reference for clear evidence in negotiations and credit note requests.
• Core Excel formulas include XLOOKUP to pull the latest price, MINIFS to find the cheapest supplier, and IF statements to calculate change percentage and flag increases above a set threshold (commonly 3–5%).
• Landed cost must factor in delivery charges and MOQ, and VAT rates must be tracked separately to comply with Making Tax Digital rules.
• Jelly scans invoices, sends price alerts, and updates dish costing without manual entry, giving the same margin visibility as the Excel workbook while removing spreadsheet admin.

See How Jelly Automates Supplier Price Tracking

Before You Begin

Gather the following before opening Excel:

• Access to supplier invoices, or a dedicated supplier email address where invoices arrive
• A list of your SKUs or item IDs, ideally matching the codes on supplier invoices
• Current supplier quotes for your highest-spend lines
• POS or sales data if you want to calculate the cost impact of a price change on a dish

Once you have these, decide who will own the workbook. Ownership matters. The finance manager or operations manager typically owns the workbook. The head chef provides unit conversions and flags new SKUs. Finance confirms VAT treatment. Assign one named owner. Shared ownership without a single accountable person is the most common reason the sheet goes stale.

Set realistic expectations. The build takes roughly 30 minutes. The ongoing maintenance, entering every price change from every invoice, is where most operators fail. Step 7 explains how to handle that failure mode and when to move to automation.

Why Supplier Price Tracking Protects Your Margin

A dish that was profitable last week can lose money today if an ingredient price creeps up. Without a price history log, the operator only discovers this when the accountant’s monthly report arrives. By that point, margin has already been eroded across every service. A properly structured supplier price tracker delivers four operational outcomes:

• Margin Visibility: live cost data rather than end-of-month surprises
• Faster Reaction: price alerts trigger action within days, not weeks
• Stronger Negotiations: concrete evidence replaces vague suspicion when challenging a supplier
• Fewer Manual Errors: a relational structure reduces the risk of overwriting history

See How Jelly Automates Supplier Price Tracking

Step 1: Build The Four-Sheet Workbook Structure

A flat single-sheet tracker, with one row per product and one column per supplier, only supports point-in-time comparison. Updating a price overwrites the previous one, so you lose history. A relational structure separates master data from events. Suppliers and Products hold reference data. Price History stores every price event as a new row. The Comparison sheet reads from Price History using formulas so no historical data is lost.

Create four sheets with the following columns. The Suppliers and Products sheets hold master data and rarely change structure. The Price History sheet logs every invoice event as a new row. The Comparison sheet pulls the latest prices and change percentages from Price History.

Suppliers Sheet

Products Sheet

Price History Sheet

Comparison Sheet

Step 2: Track Supplier Price Changes Over Time In Excel

Overwriting the current price when a new invoice arrives is the most common spreadsheet failure. The Price History sheet fixes this by treating every price event as a new row. Follow these steps each time an invoice is processed:

1. Record the date the invoice was received in the Date column. This acts as the audit timestamp.
2. Record the effective date of the price change if the supplier specifies one that differs from the invoice date.
3. Enter the Supplier Name and Item ID/SKU exactly as they appear in the Products sheet. Even a trailing space will break every lookup.
4. Enter the Old Price. Use a formula to pull the previous New Price for that SKU automatically rather than typing it manually.
5. Enter the New Price from the invoice line item.
6. Calculate the Change % using the formula in Step 3.
7. Record the Invoice Reference number. This provides evidence in a supplier negotiation or credit note request.

This log becomes the evidence base for every supplier conversation. A supplier who denies raising a price can be shown the exact invoice reference, date, and percentage increase. For more on building a broader food cost picture alongside this tracker, see How To Create A Food Cost Spreadsheet For UK Kitchens.

Step 3: Use Excel Formulas To Track Supplier Price Changes

Copy these formulas directly into the Comparison sheet. Each formula references the Price History sheet by name. Adjust the sheet name if yours differs.

XLOOKUP: Pull The Latest Price For A SKU

=XLOOKUP(A2, PriceHistory[Item ID/SKU], PriceHistory[New Price], "Not found", 0, -1)

The -1 match mode searches from the last row upward and returns the most recent entry for that SKU. XLOOKUP is the modern replacement for VLOOKUP and INDEX/MATCH in supported Excel versions because it searches in either direction, returns exact matches by default, and survives column insertions.

INDEX/MATCH: Alternative For Older Excel Versions

=INDEX(PriceHistory[New Price], MATCH(A2, PriceHistory[Item ID/SKU], 0))

INDEX/MATCH works in every version of Excel and is not broken by inserting columns, so it suits workbooks that must open in Excel 2016 or earlier.

MINIFS: Find The Cheapest Supplier Per SKU

=MINIFS(PriceHistory[New Price], PriceHistory[Item ID/SKU], A2)

MINIFS returns the minimum value subject to criteria. Use it to find the lowest current price for a product across multiple suppliers.

IF: Calculate Change Percentage And Flag Increases

=IF(D2=0, "", (E2-D2)/D2)

Here D2 is Old Price and E2 is New Price. Format the result as a percentage. Add a second IF to flag increases above your threshold. =IF((E2-D2)/D2 > 0.05, "ALERT", "") flags any increase above 5%.

Conditional Formatting: Highlight Changes Automatically

Select the Change % column. Apply a custom formula rule: =$F2>0 with a red fill for increases, and =$F2<0 with a green fill for decreases. A conditional formatting formula rule applied to a whole column, with the row anchored by the dollar on the column reference, formats every row with one rule. The sheet then tells the operator when to act instead of relying on manual scanning.

Step 4: Compare Supplier Quotes With MOQ, Delivery, And VAT

Unit price alone rarely reflects true procurement cost. Total landed cost per unit should amortise a supplier’s delivery charge across its minimum order quantity: =B2+(DeliveryCharge/MOQ). This distinction separates a reliable comparison sheet from one that misleads.

Consider a worked example. Supplier A quotes £12 per case of 6 units. Supplier B quotes £2.20 per each. Supplier A’s per-unit price is £2.00, which looks cheaper than Supplier B, but only if the MOQ of one case is realistic. Supplier A charges £8 delivery on a minimum order of one case. That adds £1.33 per unit, bringing the landed cost to £3.33. Supplier B becomes the better option. For a price comparison workbook to compare like-for-like prices across suppliers, each product row should include a standard unit of measure, and if one supplier lists by case while another lists by unit, the prices must be normalised before entry.

UK suppliers commonly quote ex-VAT prices, but the VAT rate varies. Most goods and services are standard-rated at 20%, while most food is zero-rated. Because a single invoice can contain both rates, HMRC’s MTD for VAT rules require each rate to be recorded separately to ensure accurate reporting. That requirement makes a VAT Rate column in the Products sheet essential. Without it, you risk mixing VAT-inclusive and VAT-exclusive prices in the same comparison, which is one of the most common errors in hospitality procurement spreadsheets.

Under Making Tax Digital for VAT, all VAT-registered businesses must keep digital VAT records and submit returns using MTD-compatible software. Where multiple software products are used, VAT data must be transferred using digital links; manual re-keying of VAT figures between systems is not permitted. Under MTD, digital records may be kept on spreadsheets, but all transfers of data, including submitting quarterly updates and making corrections, must be made digitally; copying and pasting or manually retyping entries is not permitted.

Step 5: Set Up A Price Increase Alert In Excel

The Price Alert column in the Comparison sheet uses the IF formula from Step 3 to flag any Change % above a threshold the operator sets. For UK hospitality businesses, a supplier price variance alert threshold of around 3% on high-volume categories such as proteins and dairy is commonly used. Operators often use a slightly wider band on lower-spend, less volatile lines, while 5% is a common starting point for menu price increases.

When an alert fires, it should trigger one of three actions:

• Supplier Conversation: contact the account manager with the invoice reference and the percentage increase
• Credit Note Request: if the increase was not agreed in advance, request a credit for the difference
• Menu Re-Pricing: if the increase is confirmed and sustained, update the dish cost and review the selling price

The conditional formatting rule, with a red fill on positive Change %, makes the alert visible the moment the sheet is opened. No filtering or sorting is required. Most one-off comparison sheets omit this feature entirely, yet it is what turns a static tracker into a margin management tool. For a deeper look at how real-time price alerts work in practice, see How To Track Real-Time Food Costs With Auto Supplier Prices.

Step 6: Use The Sheet In Supplier Negotiations

Filter the Price History sheet to the last 30 or 90 days and export the result as a PDF or printed summary before a supplier meeting. The summary should show Item ID/SKU, Product Name, Old Price, New Price, Change %, Effective Date, and Invoice Reference.

This summary gives the operator concrete evidence rather than a vague suspicion that prices have crept up. A supplier who has increased the price of chicken breast by 12% over three invoices cannot dispute a log that shows the exact date, the exact invoice reference, and the exact percentage on each occasion. The Price History sheet turns a loose conversation into a structured negotiation.

Credit notes work in both directions. If a supplier has overbilled, the invoice reference in the Price History log is the starting point for recovery. For bar-specific supplier price tracking, see Bar Supplier Price Tracking UK: Ditch The Spreadsheets.

Step 7: Keep Your Supplier Price Spreadsheet Up To Date

The operational failure modes of a shared Excel workbook are predictable:

• Version Drift: two people save different versions and the most recent price entry is lost
• No Single Owner: everyone assumes someone else updated it after the last delivery
• Manual Entry Lag: invoices sit in an email inbox for days before anyone logs the prices
• No Linkage: the price record has no connection to the invoice, so there is nothing to show a supplier when challenging an increase

The decision rule for moving off Excel stays simple. Calculate the hourly cost of the person doing the data entry. Multiply by the hours spent per week. Compare that figure to the cost of automation. Manual invoice and price reconciliation commonly consumes 10–20 hours a week for growing operators. The average cost to process a single invoice manually is $10.18, while organisations using AP automation reduce that to $2.36, a 77% cost reduction, according to Ardent Partners’ 2025 AP Metrics report. For a broader comparison of when software outperforms spreadsheets, see Stock Control Software Vs Excel: A UK Operator’s Guide.

Move From Spreadsheets To Automated Price Alerts

Common Mistakes And How To Fix Them

Most failing supplier price trackers suffer from the same small set of issues. Use this list as a quick health check on your workbook.

• Overwriting The Current Price Instead Of Logging A New Row: keep the Price History sheet append-only and protect the sheet structure with Excel’s sheet protection to prevent accidental overwrites.
• Mixing VAT-Inclusive And VAT-Exclusive Prices In The Same Column: add a VAT Rate column to the Products sheet and apply it consistently before any comparison is made.
• Failing To Normalise Case Vs Each Units: always convert to a per-unit price before entering New Price in the Price History sheet.
• Ignoring Delivery Charges When Comparing Suppliers: use the landed-cost formula from Step 4. A lower unit price with a high delivery charge and a low MOQ often costs more in practice.
• Letting The Sheet Go Stale Because No One Owns The Update: assign a named owner, set a weekly calendar reminder, and treat invoice entry as a standard process step.
• Lookup Failures Caused By Trailing Spaces: a lookup can fail even when values look identical because of a trailing space from a web export. =LEN(A2) against the visible length reveals it, and =TRIM(A2) fixes ordinary spaces.

Get Invoice Scanning And Price Alerts In One Place

How To Measure Success

A correctly built and maintained workbook shows clear signs of working.

• The Price History sheet has at least three months of entries, with no gaps corresponding to delivery weeks.
• The Comparison sheet flags every price increase above the operator’s threshold without manual filtering.
• At least one supplier negotiation in the past quarter has cited specific price change data from the log.

Directional indicators of a working process include reduced time spent reconciling invoices at month-end, faster reaction to price swings, and clearer margin tracking at the dish level. The sheet is working when the operator can answer the question “when did that price change, and by how much?” in under 60 seconds.

Advanced Tips And When To Move Beyond Excel

Power Query (Get & Transform) is the standard way to pull and refresh supplier data in Excel without manual copy-paste. It connects to source files, cleans and unpivots data, and reloads on refresh. Power Query connects to and cleans data with repeatable steps, automating data-prep work that would otherwise be manual. Excel’s Copilot can assist with formula suggestions and data lookups for subscribers on Microsoft 365.

Excel has a ceiling that appears once invoice volume and site count grow. The workbook depends entirely on someone remembering to enter data after every delivery, and it cannot read invoices or update itself. Using spreadsheet formulas to link invoice data to VAT calculations is a recipe for frustration because such formulas can break easily and cells can be accidentally overtyped, instantly losing the data or formula.

For operators who have outgrown the spreadsheet, Jelly automates the entire flow. Its invoice scanning digitises every line item from a photo or email, including quantity, SKU, price, and tax, without manual entry. Its Price Alert feature flags every price increase or decrease automatically, giving operators the same evidence base as the Price History sheet while removing the admin. Jelly onboards in the first week and is priced at a flat £129/month per location with no per-user charges.

Start Tracking Prices Automatically

Frequently Asked Questions

How Often Should I Update The Supplier Price Tracker Excel Sheet?

The sheet should be updated every time an invoice is received and processed, not on a weekly or monthly cycle. Price changes take effect from the invoice date, so a lag between receipt and entry means the Comparison sheet shows stale data for every service in between. For most UK restaurant and public house operators, which average around 3.0 and 5.0 core goods deliveries per week respectively, this means updating the Price History sheet roughly two to five times per week, although hotels are a clear exception, averaging around 24.5 deliveries per week. Batch-processing invoices at the end of the week is a common compromise, but it means the Price Alert fires days after the change has already affected food cost. Assign the update task to the person who physically receives deliveries or processes the invoice email inbox, and treat it as part of the delivery acceptance process rather than a separate admin task.

Who Should Own The Sheet In A Multi-Site Operation?

In a multi-site operation, the workbook should have a single named owner at the group level, typically the operations manager or finance manager, with a defined process for each site to submit invoice data. A shared Excel file with multiple editors creates version-drift risk because two people saving simultaneously can overwrite each other’s entries. The practical options are a shared OneDrive or SharePoint file with edit notifications enabled, or a process where each site emails invoice data to a central inbox and one person makes all entries. The head chef at each site should be responsible for flagging new SKUs or unit-of-measure changes, but should not have write access to the Price History sheet itself. Once more than three people need to update a shared spreadsheet simultaneously, as with a manufacturing business running 12 sites on a shared Google Sheet, the coordination overhead typically exceeds the time the tracker saves. That point usually signals the time to evaluate automated invoice scanning.

How Do I Handle Credit Notes In The Price History Log?

Credit notes should be logged as a separate row in the Price History sheet, not as a correction to the original entry. Use a Type column, or a Notes column, to mark the row as “Credit Note” and record the credit amount in a dedicated column rather than adjusting the New Price. This preserves the original invoice record, which is the evidence that the overcharge occurred, while also capturing the recovery. The Invoice Reference column should reference the credit note number, not the original invoice number, so both documents can be traced. For VAT purposes, where a decrease in consideration occurs in a later VAT period, a credit note reduces the input VAT recoverable in the period in which the decrease occurs, not the period of the original invoice. If the decrease occurs in the same VAT period as the original transaction, the adjustment is made in that same period. Under Making Tax Digital for VAT, the digital records for each supply made must include the time of supply (tax point), the value of the supply (net value excluding VAT) and the rate of VAT charged. Credit notes must additionally include the same information as the VAT invoice, the reason why they were issued, the total amount credited excluding VAT, and the number and date of the original VAT invoice.

Can I Automate Pulling Prices From Supplier Websites?

Scraping supplier websites is technically possible but fragile in practice. Supplier sites change their layouts without notice, which breaks any web query or Power Query connection that relies on a fixed page structure. Power Query’s web connector works for some suppliers who publish structured price lists in a consistent format, but most UK food and drink distributors do not publish prices visible without an account, with price visibility varying by category. Some categories are mostly list-public while others are frequently account-gated or quote-only. The pragmatic middle ground is manual entry into the Price History sheet combined with automated alerts. The entry is manual, but the alert fires automatically when the Change % threshold is breached. For operators who want genuine automation, where the price data flows from the invoice into the tracker without any manual step, invoice scanning software that reads every line item from a PDF or photo is the only reliable option at the scale most hospitality businesses operate.

What Is The Difference Between Tracking Supplier Prices In Excel And Using Supplier Price Tracking Software?

An Excel workbook requires a person to read each invoice, identify the relevant line items, and enter the data manually into the Price History sheet. The alert fires only after that entry has been made. Supplier price tracking software, such as Jelly, reads the invoice automatically on receipt, extracts every line item, compares it to the previous price for that SKU, and fires an alert without any manual step. The practical difference is speed and reliability. A manual process catches a price change days after the invoice arrives, if the person responsible remembers to update the sheet. An automated process catches it within hours of the invoice being received. The Excel workbook is the right starting point for operators processing a manageable invoice volume with a disciplined owner. Automated software becomes the cheaper option when the admin time cost of manual entry exceeds the software subscription, which for most operators processing more than 30 to 50 invoices per month happens sooner than expected.

How Does Jelly’s Price Alert Feature Compare To A Conditional Formatting Rule In Excel?

A conditional formatting rule in Excel fires when someone opens the sheet and the Change % column has been updated with a new entry. It requires the invoice to have been received, read, and manually entered before any alert is visible. Jelly’s Price Alert feature fires automatically when a new invoice is scanned, whether submitted by email or photographed on a mobile device, without any manual data entry. The alert shows which ingredient prices have gone up or down, by how much, and from which supplier, and it is visible to anyone with access to the Jelly dashboard, including the operations manager and finance manager, not just the person who last opened the spreadsheet. The conditional formatting rule is a useful tool for operators who have the discipline to maintain the Price History sheet consistently. Jelly’s Price Alert applies the same concept without the dependency on manual entry and turns the price history log into an automatic process.

Conclusion

The four-sheet workbook, covering Suppliers, Products, Price History, and Comparison, gives UK restaurant, pub, and hotel operators margin visibility, negotiation evidence, and price-change alerts that a flat comparison matrix cannot provide. Built correctly and maintained consistently, it becomes a genuine operational tool rather than a filing exercise.

The workbook also acts as a starting point. The point at which invoice automation and price alerts become cheaper than the admin is different for every operator and depends on invoice volume, staff time cost, and the number of sites. For operators who have reached that point, Jelly is the simplest way to move from manually wrestling with spreadsheets to automated insights.

Book A Demo To See Jelly In Action