Is there no way to move table columns and preserve all column widths?

[JenniferMurphy]

Whenever I had tried to move a table column, the widths of many of the other columns get all messed up. I've tried moving just the table columns (by dragging the table header) and moving the sheet column (by dragging the column header). They each mess up other column widths, but in different ways.

The table I am currently working on is way too large to post as a minisheet. If examples are needed, I can try creating a test table.

This was discussed in this thread about 18 months ago with no solution:

Excel Table : moving a column messes up with the width of other columns

- OS : MacOS and Windows (tested on both) - Excel version : 16.64 - Issue When I do SHIFT + drag & drop a column within an Excel Table (it's NOT a Pivot Table, it's a normal Table), it will mess up with the column widths. It doesn't behave like this when it's not a Table. - Screenshots of...

www.mrexcel.com

And it referred to this tread in the Microsoft Community, also with no solution:

Redirecting

Is there a way to move columns and preserve all column widths, or is this just another M$FT bug that will be ignored for 20 years?

 

[ShogunStealth]

Hi Jennifer,

Not sure if I'm missing the issue so apologies in advance. When say move, you mean cut / copy and paste right?

The way I do it is;
- I select the first cell in the original table then drag to select either partial or the whole lot
- then Cut or Copy
- then select the first cell of the new location
- right click - select "paste special"
- click arrow ->
- select second row second option from the left

Then wolla?

 

[frabulator]

I just tested it out, and on my end (office 2019) Excel remembers the widths and adjust the rest of the columns if you select the entire columns then copy/cut and paste. Individual ranges does not retain column widths.

I believed I ran into this issue before a few years back too.

If I remember correctly I wrote a macro that looped each column/row, remembered the width of the origin, created a new column in the desired location and adjusted the width of that column before copying the data over. Doing so pushed all other cells over by one, thus not messing up the original formatting.

But, that was very situational and required legwork every time I used it. Probably not the best solution.

 

[JenniferMurphy]

Ok. Here's a little sample sheet. I'll post screen shots of two moves and then the minisheet.

Here's the table as originally created.

First, I'll try moving an entire column. I selected the entire Price column (D).

Then I dragged that column to the left 1 column. The Comments column gets messed up.

Next I'll try moving just a table column. I selected the Comments column in the table.

Then I tried dragging that 1 column to the left. Both the Comments and Price columns get messed up.

Here's the minisheet.

Tables.xlsx
	A	B	C	D	E
1	Num	Date	ID	Price	Comments
2	1	4/20/24	abc123	$9.99	This field contains some text
3	2	4/03/24	xyz999	$19.99	Here's some more text
4	3	3/05/24		$29.99	And still more and more
Move Columns

 

[Alex Blakenburg]

I have only found 1 reliable work around that doesn't require a macro.
Your sample data isn't sufficiently representative as the sample needs to contain formulas since some of the workarounds corrupt them.
I also moved the table to B2 to show some distintinction as to what makes up the sheet vs the table.

This is a workaround and therefore requires more steps than is ideal. I chose to remove Qty it impacts the width of more columns.
When we move Column G (qty) to the left of Column D (ID), the Data in Columns D-F will move 1 column to the right but the column widths will not move.
• Move just the "table column" Qty (G) to the left of ID (D)
• In the heading row select columns D-F
• ctrl+C
• right arrow once (move across 1 column)
• PasteSpecial Column Widths (Ctrl+Alt+V w [for widths] ok)
• You will still need to fix the width of the moved column

It might be a little easier to pick the columns to fix the width on if you do that first
ie copy the width from columns D-F to E (which copies to E-G) then move the Qty

Sample data used:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Num	Date	ID	Price	Comments	Qty	total	More Cols
3		1	20-Apr-24	abc123	9.99	This field contains some text	10	99.9	abc
4		2	3-Apr-24	xyz999	19.99	Here's some more text	10	199.9	def
5		3	5-Mar-24		29.99	And still more and more	10	299.9	zzz
6
Sheet3

Cell Formulas
Range	Formula
H3:H5	H3	=[@Price]*[@Qty]

 

[JenniferMurphy]

I have only found 1 reliable work around that doesn't require a macro.
Your sample data isn't sufficiently representative as the sample needs to contain formulas since some of the workarounds corrupt them.
I also moved the table to B2 to show some distintinction as to what makes up the sheet vs the table.

. . .

Wow. I'm not sure I completely follow your workaround. I guess I'll just move the columns and then readjust the widths.

So this is a known bug?

 

[Alex Blakenburg]

In principle the column width is a property of the sheet and not of the table.
So moving columns in the table does not move the column widths with it since the column width is attached to the Sheet's column which hasn't moved.

So based on the above in theory it should work better to move entire columns but then it impacted columns to the right of the moved column.
Writing this has prompted me to see if something similar to the above would work and it does seem to. The impact is then to the columns to the right of the moved column.

I added more columns to the sample below because I was finding it difficult to see what moved to where.
Moving Qty (now col H) to before ID col D
• The next column is total (Col I) - click in the header of that column
• ctrl+shift+right arrow (select to end)
- this is probably easier than the previous method which impacted columns between where is was and where it was moved to
• ctl+C
• left arrow (move 1 column to the left)
• PasteSpecial Column widths (ctrl+alt+v then w [column width] ok(
• Select entire column (whole sheet) for Qty (col H)
• shift drag the Qty column to ID (col D)
• fix Qty Col width

Note: It seems inconsistent in the way it treats data that is not in a table but under the table. It moved the data in the column as you would expect but then leaves a gap where the data was, which doesn't make sense given we moved the entirecolumn. That is not how it works had the table not been above it.

Sample data

20240421 ListObject Tables Move Column column width JenniferMurphy v02.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Num	Date	ID	Price	Comments	More Cols another col	Qty	total	More Cols2	More Cols22	More Cols222
3		1	20-Apr-24	abc123	9.99	This field contains some text	abc	10	99.9	abc	abc	abc
4		2	3-Apr-24	xyz999	19.99	Here's some more text	def	10	199.9	def	def	def
5		3	5-Mar-24		29.99	And still more and more	zzz	10	299.9	zzz	zzz	zzz
Final

Cell Formulas
Range	Formula
I3:I5	I3	=[@Price]*[@Qty]

 

[JenniferMurphy]

In principle the column width is a property of the sheet and not of the table.
So moving columns in the table does not move the column widths with it since the column width is attached to the Sheet's column which hasn't moved.

. . .

Ok, I think I see your point about column widths belonging to the table vs the sheet. And I can see how moving just the table columns can present Excel with a dilemma. But the current implementation is. IMNSHO, illogical, inconsistent, and, frankly, STUPID. If the widths belong to the sheet, then moving a sheet column ought to preserve all widths. That's what happens if a table is not involved. What possible rationale could there be for the current behavior?

 

[Alex Blakenburg]

It does appear that in trying to give Tables a life of its own, they introduced some inconsistent behaviour.

 

[JenniferMurphy]

It does appear that in trying to give Tables a life of its own, they introduced some inconsistent behaviour.

It makes me wonder if they do any real testing. Don't they have Alpha Test and Beta Test teams? Maybe they should post new releases here and let the amazingly talented spreadsheet experts here take a whack at 'em.

Maybe the solution is to change tables from the half-baked, half independent life that they have now to a fully independent life. Make them "float" over the sheet like Text Boxes. Then changes to the tables would not affect the sheet and vice versa. And that would make multiple tables easier to manage.