How do I make a price list template with special prices per customer?
Short answer: Make one master sheet with one row per product (SKU, the code you give each item), a list price, a unit of measure and quantity breaks. Add a second sheet for customer prices, with one row per customer and product, and look it up first. If a customer has no special row, the order falls back to the list price. Excel or Google Sheets can run this for a while. When several people edit it or customers need their own login, it needs to become a live price list.
You need a price list you can edit in a spreadsheet today, with room for special prices per customer.
I'm Ranjit, and I run Blur Studio: 15+ years building software, 120 apps taken live, and clients who have gone on to raise $10M+. I build AI systems and processes that help businesses do a lot more with a lot less time. I also work differently from most of what else is out there, more on that further down this page. Say hello.
What a price list needs to hold
A price list answers one question: what does this customer pay for this item, at this quantity, today? Most sheets answer only the first half. They show a list price and leave the special deals in someone's head or in old emails. Related reading: price list management.
A usable template has three layers. The first is the list price, the price anyone gets with no deal. The second is quantity breaks, a lower price when someone buys more. The third is customer-specific pricing, a price agreed with one account, often for a set time. When a customer asks for a quote, you check the layers from the most specific down: customer price first, then quantity break, then list.
Sheet one: the master price list
Keep this sheet plain. One header row, one product per row, no merged cells, no colors that carry meaning. A plain sheet can be imported into other tools later. A decorated one cannot.
| Column | What goes in it | Why it matters |
|---|---|---|
| SKU | Your own item code, never reused | The key that every other sheet looks up |
| Description | Plain name and size | So a human can check the row |
| Unit of measure | Each, box, case, pallet | Case price against unit price is the classic mistake |
| Pack quantity | Units in a box or case | Lets you convert between the two |
| List price | Price for one unit of measure, as a plain number | The fallback when no deal exists |
| Break 1 quantity and price | For example 50 units and a lower price | Quantity discount |
| Break 2 quantity and price | For example 200 units and a lower price | Bigger discount for bigger orders |
| Minimum order quantity (MOQ) | The smallest quantity you will sell | Protects your margin on small orders |
| Cost | What the item costs you | Lets you check margin before you agree a price |
| Effective date | When this price starts | Old prices stay readable |
| Status | Active, discontinued, special order | Stops you quoting items you no longer sell |
If you buy the same items from several suppliers, keep the supplier cost sheet apart from this one. My supplier price list template covers that side. This sheet is what you charge, not what you pay. For more detail, see Shopify B2B pricing.
Sheet two: customer-specific prices
Do not add a column per customer to the master sheet. With 40 customers you get 40 extra columns, and nobody can read it. Use a second sheet with one row per customer and product, like a list of deals.
| Column | What goes in it |
|---|---|
| Customer ID | A short code, the same spelling every time |
| SKU | The same code as the master sheet |
| Special price | The agreed price per unit of measure |
| Or: percent off list | Use this when the deal is a discount, not a fixed price |
| Start date and end date | When the deal begins and stops |
| Agreed by and note | Who said yes, and why |
Many distributors also use price levels (also called tiers): a customer is Level A, B or C, and each level gets a set percent off list. That cuts the number of rows a lot. Use levels for the broad deals and the customer sheet only for the exceptions.
How to look up the right price
The lookup order is the part that makes the template work.
- Is there a customer row for this SKU that is in date? If yes, use that price.
- If not, does the customer have a price level? If yes, take list price and apply the level discount.
- Otherwise, use the quantity break that matches the order quantity, or the list price if no break applies.
In Excel, the lookup formulas are XLOOKUP and the older VLOOKUP. Microsoft documents both on its XLOOKUP page and its VLOOKUP page. Google Sheets has its own XLOOKUP. To look up on two things at once (customer and SKU), the simple trick is a helper column that joins them, such as C104-BLT-250. Look up that joined code instead.
Put a drop-down on the customer ID and SKU cells so people cannot type a typo. Google explains how on its data validation help page, and Excel has the same feature under Data, then Data Validation.
A worked example with made-up figures
Every name and number below is invented.
Item BLT-250 is a box of 250 stainless bolts. List price is $48.00 a box. Break 1 is 20 boxes at $45.00. Break 2 is 100 boxes at $42.50. Cost is $31.00. MOQ is 2 boxes.
| Customer | Order | Rule that wins | Price a box |
|---|---|---|---|
| New walk-in buyer | 5 boxes | List price | $48.00 |
| Hartley Supply, Level B (5 percent off list) | 5 boxes | Level discount | $45.60 |
| Hartley Supply, Level B | 30 boxes | Quantity break is lower than the level price, so use the break | $45.00 |
| Dalton Fabrication, special row at $41.00 until 31 March | 30 boxes | Customer row | $41.00 |
Look at the last row. $41.00 against a $31.00 cost is a margin of $10.00 a box, or about 24 percent of the price. If the deal had been 15 percent off list instead, the sheet would have shown $40.80 and a margin of about 24 percent. The sheet lets you see that before you say yes, which is the point of keeping cost in the master list.
One rule to decide in advance: does the quantity break beat the level discount, or do they stack? Pick one and write it on the first sheet. Most small distributors let the lowest price win, not stack, because stacking quietly erodes margin.
A second example: a filled-in customer sheet
Here is what sheet two looks like with a few rows filled in. Every customer, code and figure is made up. The list prices come from the master sheet, and "Percent off" is used when the deal is a discount, "Special price" when it is a fixed price.
| Customer ID | SKU | List price | Percent off | Special price | Start | End | Agreed by |
|---|---|---|---|---|---|---|---|
| C104 | BLT-250 | $48.00 | $41.00 | 2027-01-01 | 2027-03-31 | Maya, spring promotion | |
| C104 | WSH-100 | $12.00 | 10 | 2027-01-01 | 2027-12-31 | Maya, yearly deal | |
| C217 | BLT-250 | $48.00 | 8 | 2027-02-01 | 2027-07-31 | Omar, trial period | |
| C305 | NUT-500 | $30.00 | $27.50 | 2027-01-15 | 2027-01-14 | Error: ends before it starts |
The last row is a deliberate mistake, and it shows why a check column helps. Add one more column with a formula that flags a row when the end date is before the start date, or when both a percent and a special price are filled in. Then compute the price a customer actually pays: for C217, 8 percent off $48.00 is $44.16. For C104 on WSH-100, 10 percent off $12.00 is $10.80. A row that fails its check should never be used for a quote.
How live tools model the same rules
When you outgrow the sheet, the rules above carry over, because live tools use the same ideas. Two examples from their own help pages, as of November 2026.
- Odoo pricelists. Odoo (an open-source suite of business apps) assigns a pricelist to each customer. Each rule applies to a product or a category and is one of three types: a percentage discount, a formula (a discount, then rounding, then a fixed extra fee) or a fixed price. A rule can have a minimum quantity and a start and end date, which are the same columns as in the sheets above. Salespeople can still override the result on a sales order.
- Shopify B2B catalogs. Shopify (an online store platform) lets you set quantity rules per product or variant, with a minimum, a maximum, an increment or a combination, and up to 10 volume price breaks per product. Each variant must meet the break quantity on its own, so 5 gray and 5 blue hats do not add up to a 10-hat break. Once volume pricing is set on a product, the price is fixed and a catalog-wide discount does not apply to it.
The Shopify point is a useful warning. Even in a tool, whether two discounts stack is a rule someone chose, so write yours down now and the move later is easier.
Common mistakes
- Mixed units. One row in boxes and the next in each. Always state the unit of measure beside the price.
- Expired deals that never expire. If a special price has no end date, it lives forever. Make the end date a required column.
- Several copies. Sales has one version and the front office another. Keep one file in one place, and send customers a PDF or a link, never the working file.
- No history. When you raise prices, copy the old list to a new tab with its date. When a customer disputes an invoice, you can see the price that applied.
- Prices typed as text. A price like "$45" with a symbol in the cell may not sort or calculate. Keep the number plain and format the display.
When a sheet stops working
A spreadsheet price list is fine for a handful of staff and a few dozen special deals. It starts to hurt in a few clear ways. Two people edit at once and one overwrites the other. A rep quotes from an old copy. A customer asks "what is my price on this?" and someone has to look it up by hand. Price increases take a week because every deal must be checked.
At that point the answer is not a bigger spreadsheet. It is the same data, kept in one place that everyone reads from. The sheet is where the rules get worked out. The next step is a live price list: the same SKUs, levels and customer rows, held in a small database, with a login so customers see their own prices and reps see the right price when they quote. My guide to moving from Excel to a web app shows the options and how to move the data safely. If your quotes are where the pain is, a quote template and CPQ for distributors (configure, price, quote software) cover the next step.
How I'd approach this
Start with the sheets above this week. Do not wait for software. The hard part is not the tool. It is deciding what your price levels are, which deals are real, and which ones ended two years ago.
Do the clean-up first. Export your open customer deals from wherever they live, put each one in the customer sheet with an end date, and delete the ones nobody can explain. That alone usually finds a few customers still on a price that costs you money.
Move to a live price list when you notice the same three symptoms: quoting from old copies, price changes that take days, and customers asking for prices you must look up. I build these as an owned system on top of your existing data, so reps and customers see the right price without anyone opening a file. It is the same data you are about to clean, so the work above is not wasted.
Questions people ask
What should a price list template include?
At minimum: SKU, description, unit of measure, list price, quantity breaks, minimum order quantity, cost, an effective date and a status. Add a second sheet for customer-specific prices with customer ID, SKU, price, and start and end dates.
How do I handle different prices for different customers in Excel?
Use a second sheet with one row per customer and product, plus price levels for the broad discounts. Look up the customer row first, then the level, then the quantity break, then the list price. Do not add one column per customer to the master sheet.
Can I use Google Sheets for customer-specific pricing?
Yes. The same layout works. Google Sheets has XLOOKUP and data validation drop-downs, and several people can edit at once. The limit is control: anyone with edit access can change a price, so protect the master sheet and keep a dated history tab. Google's protect sheets and ranges feature lets you restrict who can edit, or show a warning when someone changes a cell, but Google says protection is not a security measure because people can still copy and export the data.
Should quantity discounts stack with a customer discount?
Decide once and write it down. Most small distributors let the lowest price win rather than stacking, because stacking quietly cuts margin. Check the result against your cost column before you agree a deal.
When should I move from a spreadsheet to a live price list?
When more than a couple of people edit it, when reps quote from old copies, when price changes take days, or when customers need to see their own prices. See moving from Excel to a web app for the options.
More in this topic: B2B: quotes, CPQ and pricing (8 guides) →
Sources
- Microsoft Support: XLOOKUP function
- Microsoft Support: VLOOKUP function
- Google Docs Editors Help: XLOOKUP function
- Google Docs Editors Help: Data validation
- Google Docs Editors Help: Protect sheets and ranges
- Pricelists (Odoo documentation)
- Quantity rules and volume pricing for catalogs (Shopify Help Center)