How do I make a price list template with special prices per customer?

By Ranjit Bhinge, founder of Blur Studio · Updated · 8 min read

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.

Ranjit Bhinge

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.

ColumnWhat goes in itWhy it matters
SKUYour own item code, never reusedThe key that every other sheet looks up
DescriptionPlain name and sizeSo a human can check the row
Unit of measureEach, box, case, palletCase price against unit price is the classic mistake
Pack quantityUnits in a box or caseLets you convert between the two
List pricePrice for one unit of measure, as a plain numberThe fallback when no deal exists
Break 1 quantity and priceFor example 50 units and a lower priceQuantity discount
Break 2 quantity and priceFor example 200 units and a lower priceBigger discount for bigger orders
Minimum order quantity (MOQ)The smallest quantity you will sellProtects your margin on small orders
CostWhat the item costs youLets you check margin before you agree a price
Effective dateWhen this price startsOld prices stay readable
StatusActive, discontinued, special orderStops 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.

ColumnWhat goes in it
Customer IDA short code, the same spelling every time
SKUThe same code as the master sheet
Special priceThe agreed price per unit of measure
Or: percent off listUse this when the deal is a discount, not a fixed price
Start date and end dateWhen the deal begins and stops
Agreed by and noteWho 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.

  1. Is there a customer row for this SKU that is in date? If yes, use that price.
  2. If not, does the customer have a price level? If yes, take list price and apply the level discount.
  3. 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.

CustomerOrderRule that winsPrice a box
New walk-in buyer5 boxesList price$48.00
Hartley Supply, Level B (5 percent off list)5 boxesLevel discount$45.60
Hartley Supply, Level B30 boxesQuantity break is lower than the level price, so use the break$45.00
Dalton Fabrication, special row at $41.00 until 31 March30 boxesCustomer 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 IDSKUList pricePercent offSpecial priceStartEndAgreed by
C104BLT-250$48.00$41.002027-01-012027-03-31Maya, spring promotion
C104WSH-100$12.00102027-01-012027-12-31Maya, yearly deal
C217BLT-250$48.0082027-02-012027-07-31Omar, trial period
C305NUT-500$30.00$27.502027-01-152027-01-14Error: 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

Ready for the next step?

A 30 minute call. If you do not need me, I will tell you.

Ranjit Bhinge You talk to me, Ranjit. I reply within a few hours.

Not sure what you need? Tell me what is going on. I will point you the right way, even if it is not me.

Blur Studio · 30 minute call

Book a 30 minute call

You talk to me, Ranjit. If you do not need me, I will tell you.

  • We talk about your business and what is going on.
  • You leave with a clear next step, even if it is not me.
  • I reply to every message within a few hours.
Loading the calendar…