Help CenterContact support
All Columna: Order Export & Sheets articles

Add calculated columns and format numbers and dates per column

Updated

On this page
  1. Add a calculation column
  2. How the calculation works
  3. Choose what an empty cell shows
  4. Replace one value with another (“if it equals A, write B”)
  5. Number format per column
  6. Date format per column
  7. Verify it worked
  8. Troubleshooting

Add a column that works out a number from your order fields, such as a line total, a net amount without tax or a price with VAT. You pick fields and operations from lists, so there is no formula language to learn. In the same window you can choose what an empty cell shows, how many decimals a number column has, which decimal separator it uses, and how a date column is written.

Add a calculation column

  1. In the app, open Templates, then open a template or create one.
  2. Next to Add column, click Add special column, then Calculation. The new column starts as Unit price × Quantity, so the preview shows a line total right away.
  3. Click the settings icon on the Calculation column. The window “Settings for Calculation” opens.
  4. Under Calculation, choose Value 1: a money or number field, or A number and type it, for example 1.19 or 1,19.
  5. For each step, choose the Operation (+ Add, − Subtract, × Multiply, ÷ Divide) and the next value. Click Add step for a third value; a calculation has at most three values. Remove a step with its x icon.
  6. Optionally choose Decimals and Decimal separator under Number format.
  7. Click Done, check the Preview, then save the template. Rename the column header like any other column, for example to “Gross”.

Examples:

  • Line total: Unit price × Quantity
  • Net amount: Total − Taxes
  • Gross price with 19% VAT: Unit price × Quantity × 1.19
  • Discount in percent: Discounts ÷ Subtotal × 100

How the calculation works

  • Left to right. The values are worked out in the order you see them: Value 1, then step 1, then step 2. There is no “multiply before add”: Total − Taxes × 2 is (Total − Taxes) × 2.
  • Exact amounts. The app calculates with exact decimals, so 0.10 + 0.20 is 0.30, never 0.30000000000000004. A division keeps up to 6 decimals; choose Decimals to round it. Rounding is half away from zero: 2.345 becomes 2.35.
  • Empty or text values give an empty cell. When a value is empty or is not a number, the cell stays empty instead of counting it as 0, so a missing amount never turns into a wrong total. This happens, for example:
    • With One row per line item, order amounts such as Total or Shipping are written on the first row of each order only, so a calculation with an order amount is filled on that row only. Your column sums stay right.
    • With One row per order, a line item field holds the values of all items (such as “2 | 1”), which is not one number.
  • Division by zero gives an empty cell.
  • To show something else in an empty cell, use If empty (below).

Choose what an empty cell shows

Every column except Row number has If empty in its settings. Under If the cell is empty, use, choose:

  • Nothing (leave empty), the default.
  • Fixed text, then type it in Text, for example n/a or 0.
  • Another field, then choose it in Field, for example the billing phone when the shipping phone is empty.

The fallback is used after Replace values: if a replacement turns a value into an empty cell, the fallback fills it.

Replace one value with another (“if it equals A, write B”)

Under Replace values, click Add replacement. When a cell equals the Find value exactly, it is replaced with the Replace with value, for example PAID with Paid or Standard Shipping with STD. The value is compared as Shopify sends it, before the number format. See “How do I create and edit export templates?”.

Number format per column

Money, number and calculation columns have Number format:

  • Decimals: As Shopify sends it, or 0 to 4. A value is rounded or padded to that many decimals, for example 48 becomes 48.00 with 2.
  • Decimal separator: Point (1234.50) or Comma (1234,50). If you use a comma in a CSV file, also choose the semicolon delimiter in Settings, so spreadsheets split the columns correctly. See “How do I change the CSV delimiter and date format of my exports?”.

In an Excel (XLSX) file, calculation and amount columns are real numbers, so you can sum them right away. Excel shows the decimals you chose and uses the decimal separator of your computer’s language.

Date format per column

Date columns, such as Created at, Paid at, Fulfilled at and Pickup/delivery date, have Date format. Same as Settings follows the date format in Settings. You can also choose:

  • YYYY-MM-DD, for example 2026-03-06
  • DD/MM/YYYY, for example 06/03/2026
  • MM/DD/YYYY, for example 03/06/2026
  • YYYY-MM-DD HH:mm, for example 2026-03-06 06:30
  • ISO 8601, for example 2026-03-06T06:30:00+07:00

Dates use your store’s time zone. A date without a time, such as a pickup date, keeps only the date part.

Verify it worked

  • The Preview shows the calculated, filled and formatted values as soon as you change a setting.
  • After you save, the template keeps the settings. Exports from Orders, History, scheduled exports and Google Sheets syncs that use the template write the same values.
  • Copying the template as a code keeps the calculation, the fallback and the formats. See “How do I share an export template with a code?”.

Troubleshooting

You see “Enter a number such as 19 or 0.19.” The number you typed is not a plain number. Use digits with one point or comma, without thousands separators or currency signs.

The calculation column is empty. One of the values is empty or not a single number on that row, or you divide by zero. Check the row mode and the fields you chose, or set If empty.

The column does not show Number format or Date format. Number format is offered for money, number and calculation columns, Date format for date columns. Other columns are written as they are.

An export for a supplier leaves the calculation empty. When an export is narrowed to a supplier’s items, whole-order amounts are never shown, so a calculation that uses one is left empty and a fallback to one is dropped.

Built and supported by StoreFleet, Shopify app developers since 2021.