After using Freeze, the values inside Excel data tables become identical across the entire column

Created by Darya Egorushkina, Modified on Tue, 4 Feb at 11:20 AM by Antony William

Applies to:

Velixo NX

Issue description
Root cause
Workarounds
- Workaround 1: do not use Excel data tables
- Workaround 2: disabling data table formula auto-fill

Issue description

The Freeze operation corrupts Excel table columns containing Velixo formulas: the value of the last cell in such columns is copied across the entire column, replacing all original values.

In the example below, before Freeze, the Excel data table contains two columns: a list of hard-coded account codes in the first column and the ACCOUNTNAME formula in the second column. The table displays the correct account name for each account code.

Before freezing the table

After the Freeze operation is performed, the second column becomes corrupted with the last value repeated across all rows:

Table corruption after running Freeze

Cause

This is a known issue with the Freeze functionality in Velixo NX 2024.12 or newer. It is expected to be fixed in a future release.

Workarounds

Workaround 1: do not use Excel data tables

Run Unfreeze, then move any Velixo formulas outside Excel data tables.

On the left — after Freeze, the data is displayed incorrectly inside the Excel table. On the right — after Freeze, the same data is displayed correctly outside the Excel table.

If you cannot avoid the usage Excel data tables, follow the steps outlined below in Workaround 2.

Workaround 2: disabling data table formula auto-fill

Important note: applying this workaround affects the way Excel handles calculated table columns: the formulas will no longer be copied automatically across the entire column. If you use the table mirroring functionality, you should not use this workaround (or you will need to manually fill down any formula columns after each refresh).

To apply this workaround, follow the guidance for the relevant product below:

Excel for the Web

If you are using Excel for the Web, you will need to open the file in a desktop version of Excel (for Windows or Mac) to apply the workaround instructions.

Excel Desktop for Windows

To avoid identical values in the table after Freeze, you can turn off the option called Fill formulas in tables to create calculated columns:

If your workbook/worksheet is frozen, run Unfreeze.
Navigate to File > Options.
In the Options menu, from the left panel, select Proofing (Item 1 on the screenshot below).

A screenshot of a computer

AI-generated content may be incorrect.

Click AutoCorrect Options (Item 2).
In the AutoCorrect window, select AutoFormat as you type tab.
Uncheck the box for Fill formulas in tables to create calculated columns (Item 3) to turn this option off.
Click OK to save your changes.
In the Excel Options window, click OK.
The changes will take effect immediately, and you do not need to reopen Excel.

Excel Desktop for Mac

If the table is frozen, run Unfreeze.
Go to Excel > Preferences.
In the Preferences window, go to Formulas and Lists > Tables & Filters.
Uncheck the box for Automatically fill formulas and close the Preferences window. The changes will take effect immediately, and you do not need to reopen Excel.