Búsquedas

Estadísticas del Blog

Excel 2016 and Nasty Errors

I have always wondered why Excel shows these errors when trying to add/delete any rows or columns in a table. This usually happens when working with several tables on the same worksheet.

I re-arranged my layout to be horizontal in order to please Excel 2016 but no luck. When trying to delete the right-most column on table 4, this error keeps showing up: “This won’t work because it would move cells in a table on your worksheet.”

OK!, right, but what if I try to repeat the same procedure on the others tables? Tables 2, 3 and 6 do allow the operation! Tables 1, 4 and 5 do NOT.

The very same error shows when tables are stacked, there is not gap between them and you want to insert the total row from the design menu.

Another “error” occurs when tables are stacked, there is no gap between them and we want to use the TAB key to insert new rows: cursor keeps flowing to the right and it does NOT stop at the end of the table which by the way it’s its regular behaviour. This is so frustrating.

Finally, when I need to re-arrange the order of a column in a table, Excel shows this error. This is happening on table no. 4 in horizontal layout which I think it must NOT be expected: “You can’t rearrange cells within a table this way, because it might affect other table cells in an unexpected way”. However, others tables at the left side do allow the operation.

All I need Excel to do is the following:

Tables are stacked or in horizontal layout.

TAB key must work to insert new rows.

Total row can be enabled/disabled anytime.

Column re-arraging must work anytime.

I have tried many “hacks” but I keep wondering why this kind of tasks are not working on the last release of Excel 2016 from 8 days ago? There’s no much information regarding this because people do not use tables a lot or even, they don’t know they exist. They prefer traditional references (A1:H50) which I dislike because it’s a pain to maintain a moderate complex worksheet. I rather prefer structured references.

Any additiontal hints/comments why some tables become so really conflictive are welcome. =)

P.S.
And sometimes –like this one- Excel 2007/2010/2013/2016 get really nasty when working with several tables.

The issue is that you have tables with different numbers of columns on the same sheet.

For example,
– you have three tables
– Table1 has 10 columns and is the top table.
– Table2 has 12 columns and is the middle table.
– Table3 has 11 columns and is the bottom table.

In this example, you can expect the following behaviour.
– You cannot add a row to Table1.
– You CAN add a row to Table2.
– You can add a row to Table3.

Why? Adding a row to a table fails if there is a table below it with more columns. Table1 fails because Table2 has more columns, but Table2 works because Table3 has less columns. Table3 works because there are no more tables below it.

Because I needed this to work I expanded the tables to have the same number of columns, then changed the style of the headers and unused columns to remove lines, and changed the text colour to match the background. It’s an ugly hack but it works.

Incidentally this only happens if you add rows using the table insert row methos, or table.ListRows.Add in VBA. If you highlight the full row in excel and just use insert it will also insert the row into the table.

Took me a while to figure this out, so hopefully it’ll help you or others who find this page.

P.S. I would have found your blog sooner but there is a typo in the error text, and I was searching for the exact string of the error message. “This won’t work beause it would move cells in a table on your worksheet.” is missing a “c” in “because”. It should be “This won’t work because it would move cells in a table on your worksheet.”