8

I have a worksheet formatted as a table. I was able to insert a row immediately prior to running into this issue, but now I cannot insert a row (right click+Insert is greyed out). Not sure what might have changed. Inserting columns works fine. Any guesses why this might be the case?

I'm using Excel 2010.

5
  • Did you protect the sheet?
    – Ramhound
    Aug 30, 2017 at 15:04
  • Do you have multiple windows open?   Are you in the middle of doing something in another window? Aug 30, 2017 at 15:09
  • No to both questions, I'm afraid, and I restarted my computer, so nothing else should be using the sheet.
    – PProteus
    Aug 30, 2017 at 16:05
  • I reloaded an old copy I had backed up, and I get the same behaviour. I can add one row, then insert is disabled...
    – PProteus
    Aug 30, 2017 at 16:09
  • 1
    After some more experimentation, it seems that the formatted table has a limited size for some reason. If I delete a number of rows, I can add that many more. Perhaps formatting the sheet as a table caused all possible rows to be filled out in the sheet. That seems like a pretty stupid feature of Excel, however.
    – PProteus
    Aug 30, 2017 at 16:13

9 Answers 9

9

Very likely there is no room for more rows, as your table already reaches the last possible row of the sheet. I assume you have selected the columns only when you defined your table, so the table already cover all of the lot-lot available rows. To solve this, go to Table menu, select "Resize Table", and make your table smaller covering less rows (the necessary ones only). Then you can add more rows with right-click.

2
  • 1
    Whoever insta-downvoted this answer was being unfair. It's a better explanation of how OP made their mistake than the one offered by OP. +1
    – Alex M
    Feb 27, 2020 at 17:12
  • Yes, that is a better answer than mine. Thanks!
    – PProteus
    Feb 28, 2020 at 17:11
11

It seems that by formatting the sheet as a table, Excel "filled-out" or instantiated all possible rows in the sheet. Thus, new rows cannot be added. Deleting empty rows allows me to add more rows where I want.

2

I was able to get it to work by (using Office 360):

  1. typing the information on a row outside the table,
  2. copying the row,
  3. clicking on a row inside the table
  4. right-clicking and choosing insert copied cells
  5. applying the sort
  6. deleting the original cells that had been copied
1
  • Thanks for the suggestion! Unfortunately, I formatted the whole sheet as a table, so there is no row outside the table.
    – PProteus
    Oct 4, 2019 at 12:48
2

This is beyond beyond late, but I just fixed this issue on a formatted table. Rather than just clicking the top row of columns to select the data to include in your table, you need to select the specific range of rows as well. You can do this on an already formatted table by clicking Resize Table in the Table Tools ribbon, then click & drag from top left to the very bottom right of the whole data set you want to use. This prevents Excel from acting as if you've already included every empty cell below the ones you're actually using, and will then allow you to insert rows where you want them throughout the formatted data set.

1
  • Nothing wrong with late answers, but does this one add anything that wasn't covered in the accepted answer, which includes the explanation "you have selected the columns only when you defined your table"?
    – Alex M
    Jun 2, 2020 at 0:27
1

Final answer and I know it's late.

You formatted as a table and that eliminates insert. Convert to a range and it's back working and still holds the format.

1
  • Late and wrong. Formatting a range of data as a table does not 'eliminate' insert. Try it.
    – Alex M
    Jun 2, 2020 at 0:27
1

I found myself here after being unable to add a table row to a defined table area. In the context menu (right-click) I was offered to add "Entire sheet row", but that failed with an error "This won't work because it would move cells in a table on your worksheet"

None of other answers were right for me.

Turns out that it was just because a column filter was applied! Cleared the column-filter and I could add and delete table rows again.

0

I got stuck on this issue as well but none of the typical solutions worked for me. I was not using filters, and I could not "Convert to Range" otherwise my solution would break.

My file had a few worksheets w/data converted to "Tables" which were being consolidated into a Power Query to be displayed on another single worksheet which refreshed at specified intervals changes were made.

Anyhow at some point I realized after setting up the file I could no longer INSERT NEW ROW in my data worksheets.

No solutions I found here/elsewhere solved the issue w/out breaking my needed functionality.

I then went in to my data worksheets and deleted all the empty rows below the data on each worksheet. Once I did this, I could now INSERT NEW ROW once again. I guess/it appears that the tables I created for my data worksheets maybe had the range using the full/vertical space of the worksheets (i.e. tons of empty rows) and I guess something about this was the core issue (?). In any case once I removed these I was able to INSERT NEW ROW again.

This was tricky to write out (quickly) so apologies if not super clear but sharing in case anyone else falls into the same situation.

0

I encountered the exact same issue and managed to solve it. In my case, the previous answers did not work (though I can affirm they do work in other cases). Here is the cause, diagnosis, fix and prevention in my case, which might be the same as in yours:

CAUSE: If you formatted tour data as a table, it is possible to (accidentally) have formatted the data as TWO SEPERATE tables (or more) side by side. If this is the case, you can no longer insert or delete rows (because it would affect two tables at the same time), while you can insert or delete columns (affecting only one table).

DIAGNOSIS: The way find out if this is the case: go to 'Table Design', select 'Resize table'. Check if either the first column of the array is not the actual first column of your intended table, or if the last column is not the actual last column of your intended table. If this is the case, you have accidentally created multiple tables next to each other.

FIX: Select a cell in each of the tables and for all of them click 'Convert to range'. This will undo the definition of your data as tables. After that, select any cell in the range you want to convert to a table and on the home button click 'Format as table'. This time, make sure all your data is in its range.

PREVENTION: Whenever you add a column to the left or right of your table, make sure you adjust the range of the table correctly. To the right and in the middle of a table this usually works automatically, to the left I think not or not always. In any case clicking 'Resize table' and selecting the correct range will prevent things form going pear-shaped.

Since @Pproteus did not mention a message saying "can't insert new cells because it would push non-empty cells off the end of the worksheet" and because he says he can add columns, I think chances are he had the same problem as I described above. In that case, removing the bottom rows will not work. I feel silly for creating this problem, but wanted to share my relief in fixing it. Especially since so may of you have unknowingly helped me so many times by sharing your problems and fixes. Thank you for that!

-3

Hello this is obviously too late, but of you have created a table in excel then you should be able to simple click on the cell above or below the row you want to inerst and then right click and select 'INSERT - TABLE ROW ABOVE'. Hope this helps.

1
  • Thanks, Daniel. That is the behaviour I was expecting, but it didn't work in my case. If you go back an re-read the question, I think it may become more clear.
    – PProteus
    Aug 20, 2019 at 13:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .