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.

image

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. Molesto

image

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.

image

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. Pulgar hacia abajo

Excel 2016 Errors

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. Molesto

image

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. Enseñando los dientes

image

4 Comments

    1. José Luis Rojas Gamboa,

      Buenos días. Espero te encuentres excelente. Una sincera disculpa por el artículo publicado en inglés. Como suelo ocupar Excel en inglés y ayer tuve algunos errores en el programa, fue que decidí escribir este pequeño artículo sólo para acordarme de los mismos en el futuro e intentar averiguar cómo resolverlos.

      Yo tampoco soy “gringo”. Soy de México. ¿De dónde eres? ¿Qué estudias(te)? ¿Te gustaría saber más de computación o Matemáticas?

      Saludos,

      Julio César.

      Me gusta

      Responder

  1. I happened upon this blog while working on the same issue.

    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.”

    Me gusta

    Responder

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s