Sunday, January 6, 2013

Inserting Rows o Columns in Excel Not Permitted

I'm opening an Excel file and when I try to insert a new row or column I get a message error giving me a large text about how this is not possible. I have seen many people facing this message, although this gives the solution, sometimes it's hard to understand what this message is trying to say.



But, what's happening? Let me tell you that you' re using all the rows or columns of the file, yes, believe it or not you are having information (even blank space) at least at the last row or column allowed. So, when you want to add a new one, Excel tries to delete this row or column because it will be out of range with the new element inserted.

The solution is going to the last column or row, to do so you can use this shortcut: Ctrl + Down arrow if you want to go to the last row or Ctrl + Right arrow to go to the last column, as shown in the images.





Then, you just have to select the entire row or column and delete, the next step is to save the file to to let Excel to detect this change, once you did this you'll be able to add one row or column, if you need more just follow the same procedure but deleting more rows or columns.


36 comments:

  1. Yes, I think most of ous know this. When all this is tried and you still get the message, then what?

    ReplyDelete
    Replies
    1. I have seen many people who do not read this message, because if you do what I say this message will not appear again.

      Delete
    2. Nice response, Ron. What do you propose when the columns do not delete? For instance, I select column(s), right click & delete... and NOTHING happens - still have column XFD.

      Delete
    3. But, that's the trick, if you select the columns and delete them they are not going to dissapear, you have to save first the file but before that you need to select the first cell of the sheet.

      Delete
  2. thanks, it works. I can insert rows now.

    ReplyDelete
    Replies
    1. Excelent! Sometimes the solution is really easy =)

      Delete
  3. thank you Ronald, good stuff.

    ReplyDelete
  4. Cool, thanks a lot Ronald, it's really help me... ;)

    ReplyDelete
  5. Thanks for posting - just the answer I needed to get me on the right track!

    ReplyDelete
  6. Hi, Its not working for me, I get error as Excel cannot complete the task, choose less data or close other application, can you please help here !!!

    ReplyDelete
    Replies
    1. Hi! what are your PC specs? Probably you need more virtual memory.

      Delete
  7. Thank you very much, it worked for me.

    ReplyDelete
  8. Hi, I did not work for me. I deleted the column, and it still there. Should I write some text in this column and then delete it?

    ReplyDelete
    Replies
    1. Hi Alex! did you delete the lastest column? and did you save it after doing so?

      Delete
  9. thanks so much for this tip Ronald, it helped a lot.

    ReplyDelete
  10. After frustrating 45 minutes, I found this post and it works! Thank you!

    ReplyDelete
  11. I'm trying... but failing. I got lots of columns (at column IU now) and need to add more. I can't however, since I get that message. I'm selecting the last column, delete it. Save my Excel and add a new column. I see a new column being added, but then it just replaces the existing column. Not getting the error: great. But I don't have any new columns.

    ReplyDelete
    Replies
    1. Hi! what Excel version are you using?

      Delete
    2. HI Ronald, I'm having the same issue. I followed your instructions and could not add more columns. Same message. I am using Excel 2013. Any ideas?

      Delete
    3. Hi! if you copy all the Excel to another one what happens?

      Delete
  12. I'm having the same problem. I'm using version 2010. I copied info into another spreadsheet to keep on going. I can enter columns in that one, but cant use the set Print area..

    ReplyDelete
    Replies
    1. Hi! have you copied all the spreadsheet or just the information?

      Delete
  13. Replies
    1. Hi! I didn't realize about this comment, sorry.

      Delete
  14. Hi! I am using Excel 2007 trying to add extra cloumn after IV but couldnt,I tried all the trick you mentioned but no success, I see columns add but it still shows IV no columns beyond this. I also copied the spreadsheet but did not work :(

    ReplyDelete
    Replies
    1. Hi! have you tried creating a new workbook and copying only the cells with data?

      Delete
  15. I've tried all of these suggestions, and to be honest I'm not working with a ton of data. I've deleted columns to the right, done the visual basic editor, copied and pasted data to a new workbook, and this worksheet will not allow me to insert columns. I had three worksheets of data in the original workbook, and 2 of the 3 allowed me to insert columns, but just one worksheet doesn't work. Any suggestions?

    ReplyDelete
    Replies
    1. Hi! it looks interesting, could you send me the file?

      Delete
    2. Hi Ronald,
      I've tried many things but cannot insert Rows - the insert option is disable when the click a Row. I've tested this even on a blank excel sheet. The insert row button just wont show.
      I've shared my excel sheets with others and they start behaving properly in other systems.

      Delete
    3. Hi, have you tried updating or reinstalling your Excel?

      Delete