What should a supplier price list template include?

Why a standard format matters

Every supplier sends prices in a different shape. One sends a PDF, one a spreadsheet with merged cells, one an email with a table pasted in. If your own sheet follows a fixed layout, you can paste each supplier's data into it and compare like with like. It also decides how easy later steps are. A sheet with one header row, one product per row and no merged cells can be loaded into a store or a script. A sheet with notes in colored cells cannot.

The columns to include

Column	What it holds	Why it matters
Our SKU	Your own product code, never changes	The key that links the sheet to your stores
Product name	Plain name	For humans checking the row
Supplier	Supplier name, same spelling each time	Lets you filter and compare
Supplier part number	Their code for the item	Matches their invoices and files
Barcode (UPC or EAN)	The number on the pack	Matches products across suppliers
Unit cost	Price for one unit, as a number	The number everything else uses
Currency	USD, EUR and so on	Stops mixed-currency mistakes
Pack size	Units in a case or carton	Case price versus unit price is the classic error
Minimum order qty	Smallest order they accept	Affects real cost and cash
Price breaks	Cost at higher quantities	See below
Lead time (days)	Order to arrival	Feeds reorder timing
Stock status	In stock, low, out, discontinued	Stops you selling what cannot ship
Price checked on	Date you last confirmed	Tells you how stale the row is
Valid until	When the quote ends, if stated	Avoids using expired prices
Notes	Anything odd	Keep it to one column

Landed cost deserves its own columns if you import: freight per unit, duty per unit and a calculated "landed unit cost". Landed cost is what one unit really costs once it arrives at your door. Your profit numbers should use that, not the invoice price. My ecommerce profit dashboard template reads from exactly this kind of cost column.

A filled-in example with made-up figures

The suppliers, part numbers and prices below are all invented to show the layout.

Our SKU	Name	Supplier	Part no.	Unit cost	Cur.	Pack	MOQ	Lead (days)	Stock	Checked
MUG-WHT-11	White mug 11 oz	Acme Ceramics	AC-1101	2.40	USD	36	72	14	In stock	2026-11-03
MUG-WHT-11	White mug 11 oz	Bright Wares	BW-77	2.65	USD	24	48	7	In stock	2026-11-03
CND-LAV-8	Lavender candle 8 oz	Acme Ceramics	AC-C08	3.10	USD	12	24	21	Low	2026-10-28

Two suppliers sell the same mug, and the cheaper one is not always the better one. The first costs $0.25 less per mug but needs 72 to start and takes twice as long. If you sell 40 mugs a month, 72 mugs is nearly two months of stock. Having both rows in one sheet shows that trade at a glance. Add a "preferred supplier" yes or no column if you want one winner per SKU.

How to build it step by step

1. Make the header row once. Use the columns above, freeze the top row, and keep exact names.
2. Lock the SKU list first. Take your SKUs from your store's product export. Shopify's product CSV has SKU, price and "Cost per item" columns, which makes a good starting list. Shopify asks for the cost as a plain number with no currency symbol, for example 9.99, and wants the file saved in UTF-8 format, so keep your cost column in that shape from the start.
3. Add one tab per supplier for raw data. Paste what they send, untouched. Never clean the original.
4. Map to the main tab. For each supplier, copy values into the standard columns. Use a lookup, such as Google's XLOOKUP, to pull your SKU by the supplier part number from a small mapping table.
5. Convert case prices. Unit cost equals case price divided by pack size. Put the formula in the sheet so the logic is visible.
6. Add data validation. Dropdowns for supplier, currency and stock status stop typos. In Google Sheets, use Data, then Data validation, then Add rule. In Excel, the error style can be Stop, which forces a fix, or Warning, which lets the person carry on after a prompt. For a supplier list I use Stop.
7. Add a stale flag. A rule that turns the row red if "Price checked on" is more than 30 days old. In Google Sheets, conditional formatting accepts a custom formula that looks at other cells, so one rule can colour the whole row based on the date column.
8. Add a checks area. Use COUNTIFS to count rows with a missing cost or an old date, and SUMIFS to total stock value per supplier. Both work the same in Excel and Google Sheets.
9. Keep a history tab. Before you overwrite prices, save the old ones with a date. You will want them when a margin falls.

Handling price breaks

Many suppliers lower the price at higher quantities. Two clean ways to store it. One: add columns for Break 1 qty, Break 1 cost, Break 2 qty, Break 2 cost, and so on, up to three. Two: use a separate tab with one row per SKU, supplier and quantity tier. The second scales better, the first is easier to read. For fewer than 200 products I use the columns.

Common mistakes

• Case price in the unit cost column. It looks fine until a product shows a 400% margin.
• Using the supplier's code as your key. They change codes. Use your own SKU.
• Prices typed as text. "$2.40" with the dollar sign can break sums. Store 2.40 and format the column.
• Merged cells and colors that carry meaning. A script cannot read a color.
• No date. Without a checked-on date you cannot tell whether 2.40 is this month's price.

Where the sheet leads

A clean sheet is the first step to updating your channels automatically. Once costs are in a standard shape, a price change at the supplier can flow into your store price and your margin report without retyping. Shopify can already take bulk changes from a file, as I explain in bulk price updates in Shopify. The harder part is getting messy supplier files into the standard sheet, and that is where tools that read PDFs can help, which I cover in using AI to read supplier price lists. If your sheet has grown into the thing your business depends on, turning it into a web app is the next step. For stock sitting in similar sheets, see the multichannel inventory spreadsheet guide.

How I'd approach this

Start with your top 30 products, not your whole catalogue. Get the columns right on those, check them against real invoices, and only then add the long tail. Use your own SKU as the key. Always store unit cost as a plain number and keep the checked-on date. Then pick one habit: every month, refresh the preferred supplier rows and look at the red stale flags. If you are copying supplier files into the sheet more than a few hours a month, that copying is the part to automate, and the sheet you built is what makes it possible.