BooksBooks
Excel for sommeliers: build a master wine list that runs your programme
The columns, formulas and five Excel features that turn a wine list into a working tool: cost, price, margin, stock and the answers your manager asks for.
Updated October 2026
Rewritten by Alper Billik, Advanced Sommelier
5 min read
The short answer: keep one spreadsheet, the master wine list, with one row for every wine you buy and one column for every fact about it: bin number, producer, vintage, cost, selling price, stock. Add four formulas (gross profit, margin, cost percentage, markup) and learn five features (tables, filters, drop-down lists, conditional formatting and a PivotTable). That is enough to run a wine programme, answer any question in seconds and build the printed list from it.
Nobody becomes a sommelier to work in spreadsheets. But the wine cellar is also one of a restaurant's biggest stocks of money sitting on a shelf, and the person who can show what it earns gets listened to.
The columns of a master wine list
| Column | What goes in it | Why |
|---|---|---|
| Bin number | The wine's unique number | Links the list, the cellar shelf and the till |
| Category | Sparkling, white, rosé, red, sweet, fortified | Sorts the list into its sections |
| Country and region | France, Loire, Sancerre | Filtering and list balance |
| Producer and wine | Producer, then cuvée name | What the guest orders |
| Grape(s) | Main grape or blend | Answers 'do you have a Chenin?' |
| Vintage and format | 2022, 750 ml or 1.5 l | Each vintage and size is its own row |
| Supplier | Who you buy it from | Reordering and price checks |
| Cost price | What you pay per bottle, before tax | The base of every calculation |
| Selling price | List price per bottle, before tax | Margin and cost % |
| By the glass | Yes or no, and the glass price | Glass cost and glass margin |
| Stock and par | Bottles in the cellar, minimum to keep | Reordering |
| Status | On list, off list, allocated, finished | What the printed list shows |
| Staff note | One line on taste and a pairing | Team training |
- Bin number
- What goes in itThe wine's unique number
- WhyLinks the list, the cellar shelf and the till
- Category
- What goes in itSparkling, white, rosé, red, sweet, fortified
- WhySorts the list into its sections
- Country and region
- What goes in itFrance, Loire, Sancerre
- WhyFiltering and list balance
- Producer and wine
- What goes in itProducer, then cuvée name
- WhyWhat the guest orders
- Grape(s)
- What goes in itMain grape or blend
- WhyAnswers 'do you have a Chenin?'
- Vintage and format
- What goes in it2022, 750 ml or 1.5 l
- WhyEach vintage and size is its own row
- Supplier
- What goes in itWho you buy it from
- WhyReordering and price checks
- Cost price
- What goes in itWhat you pay per bottle, before tax
- WhyThe base of every calculation
- Selling price
- What goes in itList price per bottle, before tax
- WhyMargin and cost %
- By the glass
- What goes in itYes or no, and the glass price
- WhyGlass cost and glass margin
- Stock and par
- What goes in itBottles in the cellar, minimum to keep
- WhyReordering
- Status
- What goes in itOn list, off list, allocated, finished
- WhyWhat the printed list shows
- Staff note
- What goes in itOne line on taste and a pairing
- WhyTeam training
Build it the right way from day one
- One row per wine, per vintage, per format. A new vintage is a new row, never an overwritten cell, so last year's cost stays on record.
- Turn the range into a Table (Insert, then Table). Filters appear on every header, and formulas copy themselves down to every new row.
- Freeze the top row (View, then Freeze Panes) so the headers stay put as you scroll.
- Use drop-down lists for Category, Country and Status (Data, then Data Validation). Typing 'Red', 'red' and 'Red wine' makes three categories.
- One fact per cell, no merged cells. Numbers stay numbers: type 48, not '48 USD'.
- Leave tax out of cost and price, or your margins will look better than they are.
The four formulas that matter
| Figure | Formula | Example: cost 12, price 48 |
|---|---|---|
| Gross profit | Price − Cost | 36 |
| Gross margin % | (Price − Cost) ÷ Price | 75% |
| Cost % | Cost ÷ Price | 25% |
| Markup | Price ÷ Cost | 4 times cost |
- Gross profit
- FormulaPrice − Cost
- Example: cost 12, price 4836
- Gross margin %
- Formula(Price − Cost) ÷ Price
- Example: cost 12, price 4875%
- Cost %
- FormulaCost ÷ Price
- Example: cost 12, price 4825%
- Markup
- FormulaPrice ÷ Cost
- Example: cost 12, price 484 times cost
The numbers are an example, not a rule: every venue sets its own targets. Note that a 75% margin and a 4× markup describe the same bottle. Mixing up margin and markup is the most common pricing mistake on a wine list.
In a Table, the formulas read like sentences. If your columns are called Cost and Price, gross margin is =([@Price]-[@Cost])/[@Price], formatted as a percentage. To set a price from a target cost percentage, divide the cost by the target: a target of 25% means =[@Cost]/0.25.
- Glasses per bottle: a 750 ml bottle gives five 150 ml pours or six 125 ml pours. See how many glasses are in a bottle.
- Glass cost: bottle cost ÷ pours per bottle. Then glass margin works exactly like bottle margin.
- Stock value: stock × cost, in its own column, so the whole cellar adds up with one SUM.
Five features that answer questions in seconds
- Filter and sort. 'Which French reds do we have under a given price?' is two clicks on the header arrows.
- COUNTIFS and SUMIFS. If your Table is named Wines (Table Design, then Table Name), =COUNTIFS(Wines[Category],"Red",Wines[Country],"Italy") counts your Italian reds. SUMIFS adds up the stock value of one category or one supplier.
- Conditional formatting. Turn a cell red when the margin falls below your target, or when stock drops below par. Problems find you.
- A PivotTable (Insert, then PivotTable) shows the balance of the list: how many wines per country and category, and the average margin of each. Most lists lean too heavily on one region; this shows it.
- XLOOKUP finds a wine by its bin number and returns any detail, for example to build a tasting sheet. It works in Microsoft 365, Excel 2021 and later; in older Excel use VLOOKUP.
From the master list to the printed list
- Filter Status to 'On list' and sort by category, then country, then region: that is the order of most wine lists.
- Copy only the guest-facing columns (bin number, producer, wine, vintage, price) into your list design.
- Give every wine a bin number and keep it on the list, the shelf and the till. See how to set up bin numbers.
- Date every version you print, so you know which prices were on the table on any given night.
Keep it trustworthy
- One owner, one file. Two copies of the list means two versions of the truth.
- Protect the formula columns (Review, then Protect Sheet) so nobody types over them.
- Back it up, or keep it in the cloud. Google Sheets uses the same formulas and is easy to share with a team.
- Count stock against it regularly. The method is in my wine inventory guide.
The same skills run a bar menu: see how to calculate the true cost of a cocktail. For the whole programme, from buying to staff training, read wine management in restaurants.
Questions people ask
What is the difference between margin and markup?
Margin is profit as a share of the selling price: (price − cost) ÷ price. Markup compares price with cost: price ÷ cost. A bottle bought at 12 and sold at 48 has a 75% margin and a 4× markup.
What is a good margin on wine?
There is no single right number. It depends on your rent, staff, glassware and the guests you serve. Set a target from your own costs, then use the spreadsheet to see which wines sit above or below it.
Excel or Google Sheets?
Either works. The formulas in this guide work in both. Google Sheets is easier to share with a team; Excel is stronger with large files and PivotTables.
Should the master list include wines that are not on the menu?
Yes. Keep finished, allocated and off-list wines with a Status column, so you keep their history and can bring them back.
For readers · next steps