As with all my spreadsheet tutorials, you can use whichever software you like – they are mostly similar enough for the advice to work on any platform. I understand that most of you will be using Excel on Windows, but the Google Docs spreadsheet app is also excellent and I use that to illustrate these examples. I assume a working knowledge of basic spreadsheet principles.
If you have a range of hundreds or even thousands of products stored in a back-end database or ERP system, it can be a challenge to produce a clean, printable price list organised in a way that is logical to you and your customers. Many systems have a function to auto generate price lists, but I have generally found the results to be disappointing – generic, ugly and impossible to organise according to a custom categorisation. I’ll bet that most users are exporting lists of products from their systems and putting together something more presentable in a spreadsheet program like Excel.
Here I make a suggestion for a simple price list spreadsheet that I have found to be useful and flexible. It takes your list of products, and with a minimal amount of simple manual editing, produces an elegant and easily editable price list which can be printed off. It has the following advantages:
- Data is read in from your product list, so no repetitive typing of information and prices.
- It can be organised in any way you see fit.
- It can be layed out in any way you see fit.
- It can easily switch between multiple pricing levels in order to produce different versions for different types of clients.
The requirements are pretty simple, you just need to be able to output a feed or list of products from your system with three of more columns: a unique product code, a description and one or more prices. In the example below, you’ll see my list of products in the tab ‘Product Feed’ – as you can see, there are three price levels: trade, discounted and retail. Don’t get excited, the prices are randomly generated for this example!
1. Import your data
Export a list of your products from your back-end system according to the above specification. If there are other fields you want to include, put those in the feed as well. It doesn’t matter at all what you name the fields. Include as many price levels as will be useful to you. Make sure that the feed is sorted by the first column, which should be your unique product code. Export in CSV or Excel format, open up the file and copy-paste the list into a new sheet (tab) called ‘Product Feed’ in a new spreadsheet.
2. Create a field to hold the key to the price level
When we create the actual price list, we’ll need to tell it which of the price columns you want to use (i.e., in the above example, trade, discounted or retail). Create a new sheet (tab) called ‘Key’ and if you are as pedantic as I am, put ‘Price Index’ in A1 and for the time being, ’3′ in B1. I’ll explain the significance of this number later.
3. Use VLOOKUP to pull description and price based on code
You will manually build up the price list by entering the codes you want in the order you want down the left hand column. The spreadsheet will do the work of pulling up the corresponding data (i.e., description and price) for the next two columns. This gives you the advantage of being able to totally customise the order and categorisation of items on your list – sure, it takes a bit of work at first, but the result is worth it.
Create a new sheet called ‘Price List’. In B1, enter the following formula:
=IF(A1="","",VLOOKUP(A1,'Product Feed'!A:B,2,FALSE))
In C1, enter this formula:
=IF(A1="","",VLOOKUP(A1,'Product Feed'!A:E,Key!$B$1,FALSE))
Now smart drag these formulas all the way down the page.
Before we go on, let me quickly explain the formulas. The VLOOKUP function will take the codes that you are going to enter in the next step and search for them in the first column of the product feed. It will then return the description and price from the feed for each code you enter in the list, meaning that although you do have to compose the list by typing each code in, you don’t have to manually type in all the other stuff. It’s a simple technique, but works well in this instance. The addition of the IF statement just means that blank cells are returned if no code is entered – which is what you are seeing right now.
4. Enter your product codes
Now you can enter your product codes down the first column. You can group by category and subcategory and add any formatting you want. When you want to add some text, like a category title, just delete the formulas and split the cells horizontally. You can always undo this at a later time by smart dragging the formulas from above or below, meaning you can reorder or change the layout of the list at any time without (much) bother.
5. Choose your price level
In step 2, you created a field to hold a ‘key’ which represents the price level you would like to use in the price list. This number is simply an index, pointing to the column in your product feed which contains a price level. This index is counted from the right, so in my example, 3 refers to the trade price, 4 to the discounted price and 5 to the retail price. Just by changing the number in B1 in the ‘Key’ sheet to the required value, all the prices on the ‘Price List’ sheet change automatically to reflect that price level.
6. Format and print
2-Column Price List Layout
I like to make a 2-column price list by repeating this exact layout again to the right and getting the spacing and margins just right so that it fits on one A4 page – see the screenshot above. Of course, you can lay the list out in any way that works for you – just play with the widths of the columns and text sizing so that it fits into the space you want – which will probably be an A4 sheet.
I'm