4

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 '17 at 15:04
  • Do you have multiple windows open?   Are you in the middle of doing something in another window? – Scott Aug 30 '17 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 '17 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 '17 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 '17 at 16:13
3

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 '20 at 17:12
  • Yes, that is a better answer than mine. Thanks! – PProteus Feb 28 '20 at 17:11
8

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 '19 at 12:48
1

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 '20 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

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 '20 at 0:27
-2

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 '19 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.