Forum Discussion

laura_protzman's avatar
laura_protzman
Copper Contributor
Jan 07, 2019

Getting this message: "This won't work because it would move cells in a table on your worksheet."

When I try to insert a row in a formatted table, I get the following message: "This won't work because it would move cells in a table on your worksheet."

  • jboett01's avatar
    jboett01
    Copper Contributor

    If your problem is related to a table produced from updating a query in power query, sometimes it helps if you cut and paste the table to sheet by itself.  Then usually excel will let you update the table.  Then you can paste the table back into your original sheet, which might have other items on it.  It will now update fine in the original sheet. For some reason, sometimes that works, even though the table may not change in size.

     

    I have seen this problem occur when there is no table below the table you are trying to update, even when the table you are updating does not change size.

     

    The overall problem dealt with in this thread can be frustrating because several conditions can cause this problem.

    • Mochibocchi's avatar
      Mochibocchi
      Copper Contributor

      This worked for me. I simply created a blank sheet, cut and pasted the columns with the table to the blank sheet, refreshed the query, and cut and pasted back. 

  • GalinMcMahon's avatar
    GalinMcMahon
    Copper Contributor

    This is just ridiculous and extremely frustrating.  I'm using this as a business tool and all of a sudden I can't move cells????  I am specifically trying to move cells.  That's what a spreadsheet does.  What is the actual solution, Microsoft???  Not looking for "Uh, yeah, try these 50 freaking suggestions."  I don't have this issue with the far superior Google Sheets.  Just saying.  

     

    Pardon my frustration.  This is just mind bogglingly stupid.

  • VladimirMaric20's avatar
    VladimirMaric20
    Copper Contributor

    laura_protzman This is because you created a table wiht an infinite number of rows.

    When creating a table in Excel, follow these steps:

    1) give names to your header columns in the first, headers row

    2) select only the header row and one additional empty row below

    3) click on 'Insert table'

    4) save the file

     

    This should make your flow work.

  • TER168's avatar
    TER168
    Copper Contributor

    laura_protzman To remove some no used columns or rows first, then you can easily move or insert any columns or row.

     

    Best regards,

    C. C. Hsieh

  • BMWelch207's avatar
    BMWelch207
    Copper Contributor

    laura_protzman this may not apply to your situation but it's what created that error for me:

     

    I have a MS Power Automate Flow that would check the contents of an excel table and delete rows in the table. Everything worked fine until today I get that error. In the end I discovered it's because the last time I used the file I left that table filtered based on a column entry. Once I turned off the filter and saved it the Flow work again.

    • SKrebs's avatar
      SKrebs
      Copper Contributor

      I have had the same problem but my problem was that I created a table, I didn't even realise I did, selecting a few columns (usually selected to the end of the worksheet). So when I tried to add a row, it was imposible because I already had maximum rows. Don't know how I realised that but problem was fixed by deleting the unused rows, creating more space to add them back in (anywhere you want).

      • beanolabub's avatar
        beanolabub
        Copper Contributor
        Oh my goodness - four years later but this saved me a real headache ! Thank you..
  • Ken_Rose's avatar
    Ken_Rose
    Copper Contributor

    laura_protzman I had a similar message when I tried to refresh a table created with Power Query based on reading a table of data from a source file.  Turns out that I had added a few rows to the source file and so the query couldn't place the newly-expanded table of data into the confines of the existing table.  Once I manually added rows to the "receiving" table (by dragging the small, square fill handle in the lower right corner of the lower-rightmost cell in the table down to add as many rows as needed), the query was able to run again.

  • stephen_b78's avatar
    stephen_b78
    Iron Contributor

    laura_protzman I'm getting the same error message too. I can insert a row easily but I can't 'Insert Copied Cells' which I do all the time.

     

    Read through the other comments and they don't help unfortunately. Any other suggestions?

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP

      stephen_b78

       

      Are you trying to insert copied cells somewhere above a Table?

       

      A Table is treated as an entire object therefore inserting cells above a table tries to "push down" one or more columns  in the table which can't happen

  • RyanPD's avatar
    RyanPD
    Brass Contributor
    I was getting the same error message because I had accidentally chosen the entire columns to be part of the table. I was able to fix by going into Design > Resize Table and selecting the proper range of data.
  • Hi laura_protzman

     

    Do you have another table below the one you are trying to insert the row in?

     

    If so you could just insert a sheet row rather than a table row  (providing that doesn't mess anything up to the right or left of your table)

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      What is interesting, if another table is in exactly the same columns

      Excel doesn't care about that and inserts rows into the first table shifting everything down

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Yes, it’s essentially inserting cells for the row of the table so if that moves the whole lower table it’s fine but if it’s just part of the table that’s not possible

Resources