Creating an Excel price list online: how to set up a price calculation

Putting your Excel price list online is more than just uploading a file to your website. Customers don’t want to have to search through tabs and size columns. They want to select their size and options and see the price straight away.

The practical approach is simple: identify the options in your price list that determine the price, convert these into clear pricing rules or formulas, and then check that every possible combination has a price. This way, your Excel file becomes the basis for an online price calculation rather than a manual calculation for every enquiry.

Which choices determine the price in your Excel price list?

Don’t start by copying all the information from your file. First, check which details actually affect the price. For bespoke products, these are often dimensions, materials, finishes and extra options.

A veranda is a good example. The base price may depend on the width, depth and roofing material. Side walls, sun blinds, lighting and installation are added as separate items. A note for the fitter or an internal supplier code is usually something a visitor doesn’t need to see.

First, set out the price-determining choices on paper. Keep it clear and organised:

• Dimensions: for example, width, depth, area or quantity.
• Specification: material, colour, roof type or model.
• Extras: installation, lighting, controls, side walls or finishes.
• Rules and exceptions: combinations that aren’t possible or require a separate price.

Limit yourself to the options needed for a good price estimate. The more questions you add, the longer the form becomes. Details that only become important during the site survey are best discussed in a follow-up meeting.

When should you use a price matrix and when a formula?

Not every price list works in the same way. Does your Excel file contain fixed amounts per size range and specification? If so, a price matrix is a good fit. In this, each combination of options and size ranges is assigned its own pricing rule.

Suppose a glass patio canopy measuring 300 to 400 centimetres wide and 250 to 300 centimetres deep has a different price to the same canopy made of polycarbonate. In that case, you create a rule for each size range and roofing material. The online price calculator then finds the rule that matches the visitor’s choices.

Does your price vary neatly per square metre, linear metre or per item? If so, choose a formula. For a floor, for example, you could use a base price plus a charge per square metre. Skirting boards can be calculated based on the perimeter.

You don’t have to choose a single method for your entire range. Within a single form, you can use a matrix for one product and a formula for another. This is useful when your basic product is purchased in size ranges, but an additional option is charged per metre.

How do you import prices from Excel without having to retype everything?

If you already have a working Excel price list, you don’t need to re-enter the amounts row by row. You can import prices from Excel by first exporting the existing price rows. This export provides the correct structure for the file you’ll be importing.

Only then should you edit your data within the same structure. For example, by placing your own price list into the columns or by replacing amounts following a price change by a supplier. Before you confirm the import, you’ll see what’s changing. The previous version is retained, so you can revert any changes.

When preparing your file, pay particular attention to the following points:

• For sizes, use clear ranges, such as 300 to 400 centimetres, rather than listing each size individually.
• Write options in the same way throughout. You don’t want ‘anthracite’ and ‘anthracite’ to be treated as two different values.
• Check whether options such as fitting and lighting have their own pricing rules or are already included in the base price.
• Only include current retail prices. You may also record purchase prices for your internal margin, but these are never visible to the customer.

If you have a large list of suppliers, importing data saves a lot of time. More importantly, you prevent prices in your Excel file and your online form from becoming out of sync.

How does an online price calculation for a conservatory work?

Take a company that sells aluminium verandas. In Excel, there are tabs containing dimensions, profile colours, roofing materials, side walls and installation details. For each enquiry, a member of staff looks up the relevant row, adds up the options and enters the result into a quotation.

For an online form, you break this down into logical steps. The visitor first selects the width and depth. They then choose the profile colour and the roofing material. In the next step, they may add side walls, sun blinds and lighting. The price updates instantly with every choice.

Behind the scenes, the base price can be derived from a matrix: size range plus roof material. Extras such as lighting and installation have their own pricing rules. When the visitor selects a size and specification, the price is calculated using precisely those rules.

This allows the customer to see how a tinted glass roof or extra lighting affects the total cost. After the enquiry is submitted, you’ll receive all the dimensions and selections, along with the calculated price. In Klantly, that enquiry can enter the pipeline immediately as a contact, quotation or map. See how a configurator with live price calculation works in this context.

How do you check for missing prices before going live?

A pricing matrix can grow faster than you think. With multiple sizes, materials and options, there are many possible combinations. If just one is missing, a visitor might get stuck on that very combination.

You should therefore set up all the combinations that determine the price in advance. Enter the amounts or import them from Excel. Then check which combinations do not yet have a price and fill in the missing ones. If a particular situation requires a separate arrangement, you can define a fallback rule.

It’s still important to keep checking even after going live. If you add a new colour or size later on, this could create a gap in your pricing. Klantly can alert you when a visitor selects a combination for which a price hasn’t yet been entered. The enquiry will still be submitted, so you won’t lose the customer.

An online price calculation is no substitute for a site survey in special circumstances. A sloping wall, difficult access route or unusual foundations aren’t visible in a form. You should therefore make it clear that the price is based on the options selected and discuss any exceptions at a later stage.

Would you like to find out how to convert your Excel price list into a form that suits your product range? Get started for free and discover, with no obligation, what Klantly can do for your business.