Last updated: 22.09.2023

Valid from: 2023.04.00 and later

Pasting prices from Excel into the price matrix

You may already have a working and up-to-date price list in Excel. In that case those prices can be pasted into the price matrix in Opter.

It is not possible to paste individual rows or columns from Excel. If you want to add rows and columns to an existing price matrix but prefer to work in Excel, first export the price matrix to Excel and make the changes in that file. Then copy the whole spreadsheet and paste it back into the price matrix.

If new prices are being pasted into an existing price item, make a backup of the price item before pasting in the new prices.

The prices in Excel must have a comma (,) as a decimal separator. For example, if a decimal point (.) is used, the prices cannot be pasted into the price matrix and an error message with the incorrect price is displayed.

Step 1: Prepare the Excel file

The horizontal unit in Excel should not have any column or row headings, only values, and they must be on row 1 of the file (1). In the example below, row 1 is the kilometre interval.

The vertical unit must have a column heading that matches the name of the condition in Opter (2).

  • Upper/lower case letters do not need to match.

  • For price units, what is in brackets does not have to be included. It is sufficient that the Excel file contains "kg" to be interpreted as the condition "KG (Pricing weight)".

  • Decimal separators must be commas (,). For example, if an attempt is made to paste the price "50.5" (with a point as a decimal separator) into the price matrix, an error message will appear stating which prices are incorrect.

The values of the predefined conditions are added automatically in Opter. It is sufficient to add a leftmost column in Excel with the name of the condition on row 1 and it will be automatically created when the prices are pasted in.

Step 2: Prepare the price matrix in Opter

Add the conditions contained in the Excel file to the price matrix. If the names of the conditions match between Opter and Excel, the values will be pasted correctly into the matrix.

In our example, we only need to add the conditions "KM (Distance)" and "KG (Pricing weight)" to the matrix. It does not matter what is selected in the Columns drop-down list. It is even possible to choose [None].

Step 3: Copying and pasting

Select the entire table in Excel and copy it. Then go to Opter and click on Paste in from Excel at the bottom of the price matrix. Pasting cannot be done with Ctrl + C.

It is possible to paste single rows from Excel, but the top row with the horizontal condition must always be included, so we recommend pasting the whole table.

Troubleshooting

  • Check that the name of the column in Excel matches the condition to be displayed vertically in Opter.

  • Check that rows that should only contain values do not contain letters. For example, if the column for 'kg' says '1 kg', '2 kg' and so on.

  • This can happen if the Excel file has a header row or an empty row at the top. The first row in Excel must contain the values that are to be displayed horizontally.

  • Check that only the leftmost cells of row 1 contain letters (A1, B1 and so on). This error may occur if any other cell contains letters.

  • Search for the price in Excel and check that it has a comma (,) as a decimal separator.

  • Delete the condition to be displayed vertically in Opter and save the price item. Then check that the name of the column in Excel matches the name of the condition in Opter.

  • Make sure that the condition to be displayed horizontally is selected in the Columns drop-down list. The condition to be pasted horizontally should be on the first row without a heading in Excel.

The first row in Excel must contain the values that are to be displayed horizontally. If there is an empty row at the top, Opter will not find any prices.

  • Check that the spreadsheet has been copied in Excel.

Reference Materials

The price matrix