Is there a better way to move Columns in Spreadsheets containing Data Tables?

Hello there,

the reason I am writing this is because I am experiencing unexpected / unintuitive behaviour (not sure if this classifies as a bug) in your product Microsoft Excel in Office 16.66 

(issue also exists in older versions) on a MacBook Pro 13" 2018 | MacOS 12 Monterrey.

The issue I want pinpoint is switching columns in a "normal" spreadsheet vs. switching columns in spreadsheets containing data tables.

To make it a little easier to follow my argumentation I provided examples with sample data to make my point reproducible.

Starting Point

To demonstrate the behavioural differences I have created two types of spreadsheets, first without data tables, second containing a data table.

In the following I will explain the steps to reproduce.

Theoretically there are 3 methods to switch two columns in Excel:

1. Cut (cmd + x) and paste cut cells (cmd + =)
2. Create empty column (in spreadsheets with data table the extra column has to be inside the data table), copy (cmd + c) into the created empty column and  paste (cmd + v) over the other column.
3. Like 1. Cut and insert data rows first($6:$16), then the result rows ($3:$4)

Switching Columns in Spreadsheets without Data Tables

Initially we have a spreadsheet like this:

References in row 3 and 4 look like this:

A7:A17


Formulas in Column G look like this:

=IF(C7="Germany";1;0)

Applying the first method (C->B) results in this:

Here everything works as expected (and like I would like it to work with data tables). 

⁃	Values applied (correct).

⁃	Formatting is applied correctly.

⁃	In the example the cell references pointing to cells, which position has changed because of switching the columns, are dynamically applied so that the values in the cells remain the same as in the initial table prior to switching columns C and B, 

eliminating the necessity to manually adjust the result. 

However if you apply this method from B->C:

⁃	Values do not change

⁃	references do not change

⁃	seems like nothing happened at all

Applying the second method results in this:

⁃	result rows still referencing the same columns (correct)

⁃	footer formatting applied (correct)

⁃	columns below data table have switched values (correct)

⁃	cell references in col G throwing referror (false)

⁃	column width: column width swapped (correct)

Applying the third method results in this ($6:$17):

⁃	method only directly affects selected cells (correct)

⁃	cell references pointing to the cells are swapped, still showing the expected value (correct)

⁃	cells below row 17 are not affected (correct)

⁃	formatting of the destination is kept (correct)

⁃	column width has not changed. should be 10, 14, 12, 16 (false)

However applying this method from $6:$16 results in this:

⁃	result rows correctly swap references (A, C, B, D) (correct) but do not apply correctly anymore (false)

⁃	everything else is correct like in the last example of the same method ($6:$17)

Switching Columns in Spreadsheets with Data Tables

This one is causing me trouble.

There are three types of references to use in Excel functions / formulas:

1. Absolute references (e.g. "B4")
2. Referencing a column in a data table (e.g. "DT_Example[Example_col]")
3. Indirect chaining (e.g. "INDIRECT(CONCATENATE("DT_Example[";C6;"]")))

Type 1 - Absolute:

References in row 3 and 4 look like this:

A7:A17

Formulas in Column G look like this:

=IF(C7="Germany";1;0)

Formulas in Column H look like this:

=IF(DT_AltStart[@Country]="Germany";1;0)

Applying the first method results in this:

⁃	Result rows falsely applied (D->E)

⁃	footer formatting applied falsely (col D -> col E | false)

⁃	messed up column order below data table (false)

⁃	cell references in col G/H still apply to source data (correct)

⁃	column width of the source is applied (correct) though colwidth of: col B shifted to col D, col D shifted to col E (false)

Applying the second method results in this:

⁃	result rows show correct values, references applied to destination (correct)

⁃	footer formatting applied (correct)

⁃	values below data table applied (correct)

⁃	cell references lost in col G/H (false)

⁃	column width applied to destination (correct)

Applying the third method results in this:

⁃	result rows formulas applied incorrectly (C->B, now C and D point to the empty grey footer line | false)

⁃	footer formatting applied (correct)

⁃	values below the data table including column width have not changed (false)

Type 2 - Direct:

References in row 3 and 4 look like this:

DT_DirektStart[Nr]

Formulas in Column G look like this:

=IF(C7="Germany";1;0)

Formulas in Column H look like this:

=IF(DT_DirectStart[@Country]="Germany";1;0)

Applying the first method results in this:

⁃	Result rows formula applied (correct)

⁃	footer formatting not applied (false)

⁃	messed up column order (false)

⁃	cell references in col G/H kept (correct)

⁃	column width of the source is applied (correct) though colwidth of col B shifted to col D, col D shifted to col E (false)

Applying the second method results in this:

⁃	result rows: B lost cell reference (false)

⁃	footer formatting applied correctly

⁃	values below the data table applied correctly (1,3,2,4)

⁃	cell references lost in col G/H (false)

Applying the third method results in this:

⁃	result rows show correct values, formula still showing value of the column below (correct)

⁃	footer formatting applied (correct)

⁃	values below the data table are kept (false)

⁃	cell references in col G/H kept (correct)

⁃	column width is kept (false)

Type 3 - Indirect:

References in row 3 and 4 look like this:

INDIRECT(CONCATENATE("DT_IndirektStart[";A6;"]"))

Formulas in Column G look like this:

=IF(C7="Germany";1;0)

Formulas in Column H look like this:

=IF(DT_AbsStart[@Country]="Germany";1;0)

Applying the first method results in this:

⁃	result rows references kept (correct)

⁃	footer formatting not applied (false)

⁃	column inserted below data table (false)

⁃	cell references in col G/H kept (correct)

⁃	column width of the source is applied (correct) though column width of: col B shifted to col D, col D shifted to col E (false)

Applying the second method results in this:

⁃	result rows references kept (correct)

⁃	footer formatting applied (correct)

⁃	columns B and C below data table switched values (correct) 

⁃	cell references lost (false)

Applying the third method results in this:

⁃	result rows references kept (correct)

⁃	footer formatting applied (correct)

⁃	columns B and C below data table have not switched values (false)

⁃	column widths have not changed, should be 10, 14, 12, 16 (false)

Summary

If a spreadsheet contains data tables, moving cells outside of the data tables is causing unnecessary work to reassign cell references, move the result lines, 

reformat, change column width, etc. 

OR is prone to errors of the users which can potentially break the whole spreadsheet if not using provided methods with caution.

Chances are that I am missing something, although I am confident to have tested this matter sufficiently. I know more ways to achieve the same goal, all of them

amounting in extra steps, so I left them out for readability.


I am wishing a solution to eliminate the need for a workaround to manually fix formatting, formulas or even the position of the cells while using data tables

on a typical spreadsheet along with data outside of said data table, because data tables in general are a great way to go in excel saving time and effort while creating a visual representation of data. 

But working with it not exclusively, currently is a real pain and my main reason to stay away from it.

Thanks for your time.

PS: If you think I made a mistake, forgot or should definitely add something, do not hesitate to leave a reply!