E-Commerce System - Spreadsheet Instructions
This spreadsheet is designed to contain all of the information needed to make your products available for purchase on the
Internet. It is divided into several sheets which will be listed across the bottom of your screen:
Products: The most important sheet. This is where you list all of your products, which category they are in,
how much they cost, and so on.
Currencies: A list of which currencies you are giving prices in. If you wish to list prices in only one currency,
only put one currency in this list.
Shipping: How to calculate the cost of shipping; Heavier products may cost more to ship, and costs may
vary depending on the destination country and shipping method.
International: A list of countries, shipping types, which currency should be shown in which country, and
information about how VAT is to be added for each country (if at all).
Options: Various options that can be set which change the way the e-commerce system works.
Payment Types: Optional sheet describing alternative methods of payment available to the customer.
These sheets are described in more detail below. It is recommended that you print out a copy of this instruction sheet to
use as a quick reference while entering the contents of your online shop.
Please note that this spreadsheet, as initially supplied to you, should contain some example values in each section. Take
note of these examples, and then when you are ready to enter your own data, just delete them.
The "Products" sheet lists all of the products to be shown on your web site. The following fields should be used:
Code A code identifying this product. It must be unique - no two products in your list can have the
same product code. Product codes should consist of only letters, numbers, and hyphens.
Please do not put any other punctuation in a product code - for instance "ABC!" is NOT valid,
and neither is "X,Y,Z".
Image Code By default, the picture for this product will be the one with the same name as the product code,
eg product ABC's picture will be ABC.JPG.
If you wish to use a picture from another product, enter its product code here. Otherwise just
leave this field blank.
Category Put a category name here. This determines the hierarchy of your shopping system. If all of your
products are in the same category, they will all be shown together; Thus if you have many
products it can be helpful to put them into appropriate categories.
Name This should be brief, as it is given as an alternative for a miniature version of the product picture.
It is shown in the product listings.
Short Description This is a very brief product description of the product - about one line - which will be shown next
to the small picture in the product listings.
Long Description A full paragraph describing the product in more detail, shown when a customer clicks on a
product for more information.
Specials Anything entered in this field will be shown in red next to the product. This can be useful if you
want a particular product to stand out from the others - for instance you could put "New" in here
if the product was a new item you were promoting.
Discount If the product is being sold at a discount (less than the price given in the Price field) then this
field should contain the percentage discount.
Variant name 1 If a product has things selectable about it, eg colour, put the thing that is selectable here. For
Variant values 1 Put the choices for the first variant in this field, separated by commas. For instance,
Variant prices 1 (GBP) The changes in price associated with each of the variants. 0 means keep the original price, +1
means add 1 pound, -1.20 means subtract 1 pound and 20 pence, and so on.
For example, combined with the above, "-1.20,0,1" means 1 pound and 20 pence less for the
"small" variant, the usual price for the "medium" variant, and 1 pound more for the "large"
You can leave this field blank if all variants have the same price.
NOTE: This field should be repeated for each currency you are using, eg (EUR), (GBP),
The "Variant" fields should be repeated for as many variants as a product has - eg "Variant
name 2", "Variant values 2", "Variant prices 2 (GBP)", etc.
Weight The weight of the product, in kilos, for the purposes of calculating the shipping costs (see the
Price (GBP) The price of the product before shipping or VAT is added and before any discounts are taken off,
in GBP. This field should be repeated for each currency you deal in, eg "Price (EUR)", "Price
(USD)", and so on.
VAT exempt If the product is VAT exempt, eg if it is a book, put "Y" in this column, otherwise leave it blank.
If it is VAT exempt only in particular zones (see the "International" sheet) then put the zones in
which it is VAT exempt in this field, separated by commas (eg "1,2,3").
See Also Put the product codes of any related items here, separated by commas, and links to them will be
shown when a customer asks for more information on the product.
See Category If you put category names here (eg Clothing\Men), links to those categories will be shown when
a customer asks for more information on the product.
In the "Products" sheet, you will have a "Price (GBP)" column, and possibly others such as "Price (EUR)" for pricing in
In the listing on the "Currencies" sheet, give the names (GBP, EUR, etc) as they are used in the Products sheet, and
alongside, give the symbol the prices are to be displayed with (£, etc).
Each product has an associated weight, which is added to the weight of all other products in a customer's shopping basket
and used to calculate the cost of shipping. The "Shipping" sheet contains the information necessary to make this
The following fields should be used:
Zone Which shipping zone this particular row applies to (see the "International" sheet). It can be a
single number ("1") or a comma-separated list ("1,2,3"). If you are not using zones at all, just put
Lower limit The shopping basket must weigh at least this much, or this row does not apply.
Upper limit The shopping basket must weigh less than this much, or this row does not apply. If there is to be
no upper limit (effectively saying "if the shopping basket weighs at least the lower limit or
anything higher") then just put "-".
Cost (GBP) How much to charge, in GBP, for shipping if the shopping basket weighs at least "Lower limit"
and less than "Upper limit".
Per extra (GBP) How much to charge per extra weight unit over "Lower limit".
Weight chunks What size chunks to round the weight up to. If this was 1kg and in the above example the basket
weighed 6.4kg, it would be rounded up to 7kg before the cost was calculated. If the chunk was
0.5kg then it would be rounded to 6.5kg instead.
Note that all weights are assumed to be in the same units as those used for weights in the "Products" sheet. It is suggested
that you use kilograms, though this is not mandatory so long as you pick one unit and stick to it throughout.
The customer is never shown anything to do with a product's weight, only the resultant shipping cost.
The "Cost (GBP)" and "Per extra (GBP)" fields should be repeated for each currency you support, i.e. If you are giving
prices in GBP and EUR then you must add "Cost (EUR)" and "Per extra (EUR)" fields so that shipping can be calculated in
The "International" sheet allows different countries to have different "zones" assigned to them, and you can also allow for
different shipping options such as "Standard", "Express", and so on.
Using zones means the shipping table can be extended to have different shipping costs for different countries or shipping
This sheet also allows you to set which currency should be used in preference for each country, and whether VAT should
be added when pricing for that country.
The following fields should be used:
Country The country name. The customer will be asked to select their country from the list given in this
column when they are in the shopping basket, so that their shipping cost can be calculated.
Currency The preferred currency to use. If you have multiple currencies (see the Currencies sheet), then
the code given here - eg GBP - specifies which currency's pricing will be displayed first.
VAT Whether VAT is applicable in that country - Y indicates that VAT should be added to the price of
any product which is not marked in the product list as VAT exempt, whereas N indicates that
VAT should not be added.
As a special case, A indicates that VAT should ALWAYS be added to the price of any product
not marked as VAT exempt, even if the customer supplies a VAT number.
In general, you should put A for the UK, Y for countries in the European Union, and N for the
rest of the world.
(anything) Any other column headings will be taken as alternative shipping options - for instance
"Standard", "Express", and so on. The field should contain a number, which is the "zone" to use
when calculating the shipping cost (see the "Shipping" sheet).
Each of the available options can be changed on the "Options" sheet. To change an option's setting, alter the contents of
the "Setting" column for that option.
For more details about a given option, refer to its "Possible values" and "Description" columns.
This sheet is optional; delete it if you will only need the Credit Card and/or Pro Forma Invoice payment types.
The "Payment Types" sheet comes in two parts. The first part defines what payment types will be available to the customer;
the second defines the questions the customer will be asked for any custom payment types.
For the first part, which should be at the top of the sheet, the following fields should be used:
Payment Type The name of this payment type, eg "Credit Card", "Cash on Delivery", etc.
Action What to do when this payment type is selected. The available choices are:
Credit Card Take credit / debit card details from the customer.
Invoice Send the customer a Pro Forma Invoice.
(anything) Ask the questions defined in part two below.
Surcharge (GBP) How much to charge the customer for choosing this option, in GBP. If you use other currencies,
add more Surcharge columns, one for each currency - eg Surcharge (EUR), Surchage (USD),
Next comes the second part, in which the following fields should be used:
Payment Type The payment type this question is for. This must be one of the payment types defined in part 1.
Field The short question to ask (eg "Your bank account number").
Type The type of answer to expect. This should currently always be TEXT.
Size How long an answer to expect (how big to make the text box). If you are expecting a multiline
response, like an address, put a large number like 200 here.
Required Whether this question must be answered. Put Y or YES if so.
Sensitive Whether the answer to this question is sensitive information, like a credit card or bank account
number. Put Y or YES if so.
When an e-commerce order is marked as processed, all sensitive information is removed from it
for security reasons.
Description If the question needs clarification, put a longer description here, and it will be displayed below
the answer box on the customer's form.
Code Image Category Name Short Description Long Description Specials Discoun
ABC01234 Widgets Widget 1 Widget 1, for doing Widget 1 is a general- 20.00%
things with. purpose item for all your
ABC01235 Widgets Widget 2 Widget 2, for doing Widget 2 is a slightly more Hurry -
different things with. specific widget than Widget Limited
1 but is less specific than Stock!
ABC01236 Widgets Widget 3 Widget 3, for doing Widget 3 is a type 3 widget.
specific things with.
Variant Variant Variant Variant Weight Price Price VAT See Also See Category
name 1 values 1 prices 1 prices 1 (GBP) (EUR) exempt
Colour Red, 3 4.60 5.00 ABC01235,
Size Small, 0, +1 0, +1.5 2 4.70 5.20 ABC01234,
2 4.80 5.40 ABC01234,
Currency name Currency symbol
Zone Lower limit Upper limit Cost (GBP) Cost (EUR) Per extra (GBP) Per extra (EUR) Weight chunks
1,2,3 0 1 0.00 0.00 0.00 0.00 1
1,2,3 1 2 2.00 2.00 0.00 0.00 1
1,2,3 2 5 5.00 5.00 0.00 0.00 1
1,2,3 5 10 6.00 6.00 0.50 0.50 1
1 10 - 9.00 9.00 1.00 1.00 1
2 10 - 10.00 10.00 1.50 1.50 1
3 10 - 12.00 12.00 2.00 2.00 1
Country Currency VAT Standard Delivery
United Kingdom GBP A 1
Mainland Europe EUR Y 2
Rest of World EUR N 3
Option Setting Possible values Description
E-Commerce enabled enabled, disabled Whether to enable e-commerce; If disabled, products will be displayed, but
the shopping basket and checkout will not be available so customers will
not be able to buy online. Useful if you want to just display a catalogue.
Show Codes enabled enabled, disabled Whether to display product codes. If disabled, product codes will not be
shown to customers at any point.
Show Prices enabled enabled, disabled Whether to display prices. If disabled, product prices will not be shown to
customers. This is useful in conjunction with disabling e-commerce and
product codes, above, if for example you want to display a list of services
rather than products.
Ignore VAT disabled enabled, disabled If this is enabled, no VAT calculations are ever done. No prices will be
modified due to VAT. Enable this if your prices are all inclusive of VAT.
Show VAT enabled enabled, disabled Whether to add VAT to the displayed price of a product. If this is enabled,
then in the product listings, all prices will have VAT added to them if
If "Show VAT" is disabled, VAT will not be added to a product until it goes
into a customer's shopping basket.
Show VAT in enabled enabled, disabled Whether to show the VAT breakdown of the displayed prices in the
basket shopping basket.
If "Show VAT in basket" is disabled, VAT calculations will not be shown
until the checkout.
Shipping VAT enabled enabled, disabled Whether to charge VAT on shipping costs. If disabled, VAT will only be
added to the cost of eligible goods, not to the shipping cost as well.
VAT rate 17.50% (any percentage) The VAT rate.
VAT name VAT (any name) The name to give VAT or its equivalent, eg "VAT", "Sales Tax", etc.
Cards Accepted VISA, (comma-separated Which credit cards you accept. When a customer goes to the checkout,
Mastercard, card names) they will be asked to specify which type of card they are paying with, and
Switch, the list they can choose from will be the list you give here.
Pro Forma disabled enabled, disabled Whether to enable the Pro-Forma Invoice payment option. Enable this if
you want customers to be able to select an "invoice me" payment option
when buying products. This will send them an invoice by email when the
order details are sent to you.
If you want people to only be able to pay by Pro-Forma Invoice, and not by
credit card at all, remove all credit card names from the "Cards Accepted"
Pro-Max-Card - (comma-separated If Pro-Forma Invoicing is enabled, and the total price of all goods before
amounts or just "-") shipping and VAT exceeds this amount, then Pro-Forma Invoice becomes
the ONLY method of payment available.
For no limit, put "-", otherwise put amounts of money in each currency
separated by commas - for example "GBP1000,EUR1500" for £1000 max
and 1500 euros max.
Pro-Discount 0.00% (any percentage) If you want customers to be given a discount for paying by Pro-Forma
Invoice, put the percentage discount here. Otherwise leave it at 0%.
Delivery Address enabled enabled, disabled Normally, customers can enter a delivery address that is different to the
cardholder address. Put "disabled" here to disable this feature.
VAT number enabled enabled, disabled Normally, customers can enter their company's VAT number in the
checkout so that they can claim back the VAT. Put "disabled" here to
disable this feature.
PayPal disabled disabled, Instead of holding payment details on the server, if you put your PayPal
firstname.lastname@example.org email address here and enable Instant Payment Notification, payment can
ss go via PayPal - see the manual.
Payment Type Action Surcharge (GBP) Surcharge (EUR)
Credit Card Credit Card 0.00 0.00
Pro Forma Invoice Invoice 5.00 8.00
Cash on Delivery 8.00 10.00
Bank Transfer 8.00 10.00
Payment Type Field Type Size Required Sensitive
Cash on Delivery Secret word TEXT 30 N N
Bank Transfer Name of bank TEXT 40 Y
Bank Transfer Your sort code TEXT 10 Y Y
Bank Transfer Your account number TEXT 10 Y Y
Bank Transfer Address of bank TEXT 200 Y
EXAMPLE ONLY - SHEET DISABLED.
(Delete this row if you wish to re-enable this sheet)
For your safety, our delivery person can
be given a secret word or phrase which
you can ask them for before you open
your door to them.
able this sheet)