locked
Unable to add rows in Excel 2007 after 'Format as table' RRS feed

  • Question

  • Hi

    The problem for me was that I had used the 'format as table' option in my spreadsheet, to alternate row colors. Apparently doing so creates a different kind of table where you cannot insert rows. Took me a while to figure out, surfed a lot of sites trying to find answers, nothing worked. It seems impossible to undo whatever is done when you choose the 'format as table' option. Everything was visible, nothing was locked, nothing was protected, nothing was merged.

    To fix it, I had to copy my data into a new table and use the conditional formatting option, with a formula that worked in prior versions of Excel.

    Is there another way to fix this problem? What does 'format as table' does?


    yo

    Thursday, March 8, 2012 8:05 AM

Answers

  • Using "Format as table" creates a table if the selection is not already a table, then applies the selected format.

    I'm not sure why you can't insert new rows in a Table, are you saying when you right click a row header "Insert" is missing or greyed out?

    Peter Thornton

    • Marked as answer by Calvin_Gao Friday, March 23, 2012 9:32 AM
    Thursday, March 8, 2012 10:04 AM

All replies

  • Using "Format as table" creates a table if the selection is not already a table, then applies the selected format.

    I'm not sure why you can't insert new rows in a Table, are you saying when you right click a row header "Insert" is missing or greyed out?

    Peter Thornton

    • Marked as answer by Calvin_Gao Friday, March 23, 2012 9:32 AM
    Thursday, March 8, 2012 10:04 AM
  • It was greyed out.

    yo

    Tuesday, April 10, 2012 10:52 AM
  • It's quite a while since you posted your problem. Are you saying still "format as table" will not insert a Table in which you can't insert rows (if one does not already exists in the selection). You say it "was" greyed" out but is it still?

    If so, starting with a new workbook and sheet describe step by step what you are doing so we recreate your scenario.

    Peter Thornton

    Tuesday, April 10, 2012 1:32 PM
  • step 1 : open excel, start a new work book, insert your data

    step 2 : select a box you want to format, and click "format as table" OR choose any predefined table formatting for the selection

    step 3 : try to add a row (as you will see, the option to "add row" has been "greyed out").

    In this scenario it appears that the "fomat as table" function does way more than just to format the selection.


    yo

    Sunday, May 6, 2012 6:35 PM
  • I can't recreate your problem. If I create a Table, with some or all the Table selected I can click the row headers (or right click cells in the selection) and I see "Insert". From there I can insert row(s) with no problem.

    But I don't follow what you are doing. What is initially selected and from which menu do you see a caption "add row" greyed out (or do you mean "Insert"). Why not upload a screen shot somewhere, eg to Skydrive

    Peter Thornton

    Tuesday, May 8, 2012 3:51 PM
  • It probably doesn't matter anymore, but my guess is that you used external data to create your table. You cannot add lines if the content was external, such as imported from a text file.

    If you don't need the connection to the external data, go to tab "Data", Connections, and remove the connections you no longer need. After that you should be able to insert new row. Make sure you understand the implications of doing this though.

    Friday, May 18, 2012 7:58 PM
  • Disregard last comment...

    I had the same problem. I had selected the entire column for my table, so for Excel there was no way to add a new column. Convert everything back to a range, then select just the rows containing data, convert it back to a table, and now you should be able to add new rows...

    Friday, May 18, 2012 8:08 PM
  • I had the same symptom in Excel 2007, which I fixed as follows:

    - When formatting as a table, I selected a number of columns - I didn't really think about it at the time,  but this created a table with 1048576 rows...

    - The usual methods of inserting rows didn't work / were greyed out

    - I deleted some "empty" table rows, and was then able to insert new rows,  up to the 1048576 row worksheet limit

    In future, I'll select a range to format as a table, instead of selecting columns.

    Hope this helps.

    AB

    • Proposed as answer by abysas Wednesday, January 2, 2013 9:26 AM
    Wednesday, July 11, 2012 2:02 AM
  • I have always had this same problem. Converting the table back to a range THEN format as table worked! Thank you. Microsoft, if you are watching, please make this a simple button on the "Format as table" option so we amateurs can find it! Thank you.

    One note, you have to actually be in the table portion of your data before getting the Design tab.

    Wednesday, April 24, 2013 3:27 PM
  • You are correct. Selecting colums does use "all" of the available rows on the sheet and you can't add new rows because you get an error it would move data off of the sheet. Your workaround will work, but if you happen to just select the columns and then format as table you can then select the last empty row, Ctrl+Shift+End to highlight all the rows to the end, then go to the Home menu and drop down Delete (from the Cells menu) and then select Delete Sheet Rows. This will free up all the space you need. If you right click and select delete it will only delete the row that you right clicked on (trust me...trial and error).
    • Edited by M Coerbell Wednesday, October 16, 2013 4:45 PM
    Wednesday, October 16, 2013 4:44 PM
  • Thank you so much for this answer. I also had the same issue and noticed that when I id formatting as a table, I selected about 10 columns, Excel selected ALL the rows (and 1048576 seems to be the limit)And like you, I didn't really think about it at the time,  but this created a table with 1048576 rows...This prohibited me to add any more rows after 1048576. I simply selected all bottom the rows with no data (eg selected row number 20 and Shift+END to select all rows from 20 thru 1048576) and deleted them. Problem solved.

    Friday, October 10, 2014 5:11 PM
  • This happens because your table probably is defined to the last row of the spreadsheet (1048576) so there is no more "space" to insert a new row.

    Define your table with less rows than the total rows of the spreadsheet, then the insert option will work.

    Wednesday, March 18, 2015 9:25 AM
  • I had the same issue and discovered that it was due to a second table that was formatted on the same sheet. If the second table was directly below the first, I could not insert columns (because the action would affect two tables); however, I could insert new rows for both. If I moved the second table to the right of the first, I could not insert rows (because the action would again affect two tables); however, I could insert new columns for both tables.

    I just moved the second table to a new spreadsheet, although it contained information that was relevant to the first.

    Sunday, June 7, 2015 1:39 AM
  • Hi Guys,

    This is an old post but I had the same problem, excel 2010, where insert column was grey out. 

    Found the solution was to

     - select the formatted table. 

    - Right click

    - Select Table

    - Select Convert to Range. 

    This then allows you to add Columns to the table, but you looks sorting by column header. To re-enable this just reformat the new larger table, using Format as Table.

    Craig

     


    Craig

    Sunday, February 21, 2016 9:57 PM
  • Figured it out after I had the same problem.

    In making my "Format as table," I selected ALL the rows, all the way down to the very end of Excel.

    This will not allow me to insert any rows. because there are no more rows.

    THerefore, I undid that by "deleting" everything under my table -- that's the only way i know to "undo" the format as table.

    Now, my table was, say, 6 columns but only 55 rows.

    I can insert rows as i please

    Sunday, September 17, 2017 2:31 PM
  • I had the same problem, I needed to insert a row in between existing ones and wont allow me.

    1. I navigated to the last row of the table and inserted blank rows just after the last row of the table.

    2. The proper way I would guess would be to resize the table to increase row range using menu/(table tools)/design and you will see the resize button on the leftmost part of the ribbon. I did not have to do this as #1 allowed me to insert rows already.

    • Proposed as answer by cesrlp Sunday, December 10, 2017 9:37 PM
    Sunday, December 10, 2017 9:37 PM
  • I have same problem as original poster.

    Have not had this problem before of greyed out Insert/Delete when right clicking rows or columns of a formatted table. In my case, moved the Worksheet from an external Excel Workbook on a USB stick (my own data) to an existing Excel Workbook in Dropbox on computer. 

    Tried your suggestion of going to Connections in Data tab, but no connections are listed.

    (I have tried suggestions elsewhere including clicking File/Advanced Option/Ignore other applications that use Dynamic Data Exchange (DDE))

    Saturday, July 7, 2018 5:39 PM
  • Thank you. This worked for me after spending a lot of time looking for a solution.

    I had originally only formatted the required cells/rows for the table, so formatting all available rows wasn't the issue. 

    I have multiple formatted tables, but even when converting all bar 1 back to data range, still had the problem with remaining formatted table (unlike poster below).

    Somehow I feel I am missing something here because I am fairly sure I have previously inserted rows into formatted tables without issue.

    (One minor point, when selecting table to convert back to range in Design tab, I found only need to select one cell in the table. When I selected the whole table, didn't work for me)



    • Edited by davebkk Saturday, July 7, 2018 6:07 PM
    Saturday, July 7, 2018 6:04 PM
  • Thank you! Thank you! Thank you!

    I had same issue and fought with it for a long time. Finally found your suggestion, deleted a few hundred rows, and all is good!

    People who share helpful information on the web like this deserve a huge pat on the back for making the world a better place.

    Wednesday, January 29, 2020 5:00 PM
  • Hi. I am sure you would have already figured this out by now as it has been quite a while since you have posted this. However, since I got stuck with the same problem and finally able to solve, thought of sharing. Just as you add a new column in the table by tying in the 'right' adjacent column to the table you have created, in the same way, you can type in the successive row and it would automatically get added in the table sequence. 

    Trust helpful.

    Saturday, April 11, 2020 1:13 AM