Unable to insert columns [solved]

Sometimes you'll encounter a spreadsheet that doesn't allow you to insert a new column. When you try, it will give you the following error:

To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.

I adore its use of the "off of" artificial construct.

It's basically saying that there's something at the far right of your worksheet (column IV pre–Excel 2003; column XFD thereafter), or else at the very bottom (row 65,536 pre–Excel 2003; row 1,048,576 thereafter).

Often you don't know why it's happened, and you can't find any data anywhere near those extremes. Here's how to solve it.

  • Find the last column in which data appears (column AQ, say)
  • Highlight the next column to the right (column AR)
  • CTRL+Shift+Right
  • ALT, E, A, A.

This last step is an old shortcut that still works to this day. In earlier versions of Excel, it selected Edit | Clear | All. Essentially, it gets rid of the contents and formats of all cells highlighted.

The equivalent steps to sort out the rows are:

  • Find the last row in which data appears (row 100, say)
  • Highlight the subsequent row (row 101)
  • CTRL+Shift+Down
  • ALT, E, A, A.

To allow you to insert rows and columns, you'll need to save your spreadsheet and, in true Microsoft style, close it and open it up again. Now you should be able to add columns and rows to you heart's content.

This entry was posted in How to. Bookmark the permalink.

105 Responses to Unable to insert columns [solved]

  1. Ifza Ejaz says:

    very helpful solution, Thanks

  2. Brian C Johnson says:

    perfick-many thanks.

  3. Saurabh Shrivastava says:

    This is working in Excel 2003, Please suggest same for Excel 2007.
    I tried in Excel 2007 but it is not working.

  4. Johann says:

    Very informative tip. Thanks

  5. phk says:

    Unbelievable.. Works in Excel 2k10. Today, Oct'2012

  6. Deepak Panda says:

    It worked,... thanks a ton....

  7. gami says:

    Thanks so much - I have been searching everywhere for a solution to this problem!

  8. karan says:

    it worked, thanks a alot

  9. Rae says:

    Straight forward and easy. All the other genius's I Googled assumed I know IT from A-Z so their explanations were lost on me. Thanks Dan.

  10. lei says:

    it didn't work. πŸ™
    i'm using pro plus 2010 and pretty sure followed all your steps. maybe it is because of another worksheet? but when i deleted it still doesn't work. please help. thanks in advance.

  11. Ahsan says:

    Good job . . . its working good thanks.

  12. Chinni says:

    Great.........it worked.......Thanks

  13. Colin says:

    Bingo, works perfectly, thanks!

  14. Niamh O'Neill says:

    brilliant! I wish YOU worked in our IT department!

  15. Paddy says:

    Thanks , this has been bugging me for weeks!

  16. Tobi says:

    thanks. very helpful. it saved me tons of time to figuring out.


    Thanks a lot

  18. Suzanne says:

    I CANNOT thank you enough for this!!! i have been struggling with this thw whole time!!

  19. Richie says:

    Excellent! It worked πŸ™‚ Thanks very much.

  20. sun Bhagawati says:

    stupid solution

  21. Kevin says:

    Worked for me using Excel 2013.

  22. Arun says:

    Thank u so much..

  23. Emmi says:

    Thank you. You are a big help. πŸ™‚

  24. Nwe War Win says:

    Excellent! Thank you very much.

  25. royalH says:

    Perfect!! It works!! Thanks heaps for your help!!

  26. Felicia says:

    Hi, I tried this but now it says:
    "Excel cannot complete this task with available resources. Choose less data or close other applications"
    But i have nothing else opened?
    please help

  27. DC says:

    Perfect! Tried several other solutions to my "can't insert rows in Excel" problem. This is the only one that worked. Try it.

  28. Paula says:

    How about for rows? Thanks! πŸ™‚

  29. Apoorva Maheshwari says:

    Thanks for this. Loved the shortcut

  30. shubham says:

    Perfect solution man....its works on 2013 aswell..

  31. samkeenan says:

    Brilliant! Been trying all sorts of things! It works!

  32. Lucila says:

    It works!!!. Thank you!

  33. Nuwan Perera says:

    great.... its working. Thank you very much

  34. Nho says:

    Thanks, so much. It's very helpful. I had gone crazy with it for hours then I luckily found you!

  35. dewi says:

    work very well...

    God bless you

  36. Abhaya says:

    thanks so much it saved me so much time

  37. Gettingsomuchshitfromexcelthaticantcomefromthetoilet says:

    Didn't work for me

  38. Bonnie says:

    Thanks for this solution! The microsoft proposed solution was nonsensical gibberish! You're awsome!

  39. Srinivasan Nair says:

    Didn't work still for me. Finally copied valid data from the sheet into a temp new book, deleted the troublesome sheet, recreated a blank sheet and copied back the valid cells.

  40. Diana says:

    Many thanks - so very helpful....

  41. DVN says:

    This solution solved my problem on Microsoft Excel Professional Plus 2010.

    After months researching for a solution, this solution eventually solved my problem. Thank you !!!!!!

  42. hossam eldin says:

    Thanks you are genuis

  43. hossam eldin says:

    sorry you are Genius

  44. ropata says:

    Works in Excel 2013 as well. Someone had added formatting to columns XFC and XFD, so I selected column XFB and applied your magic trick (Ctrl+Shift+Right, then Alt, E, A, A).


  45. Aamir says:

    Worked perfectly for me in excel 2010... Thanks

  46. Suhail Rafiq says:

    It works with a little tweaking in my case....

    I am running MS Excel 2007, when i tried to do this step it gave me same error as it was giving me while trying to insert a row "about the resources", i however managed to do it on a small amount of cells at a time which then worked fine. Finally got Excel to work fine...

    Final Conclusion:
    Instead of formatting entire rows or columns for specific data like date, currency or accounting etc., just format the cell-range containing data. That will decrease the amount of necessary data in the workbook hence making it consume less memory and processor resources.

  47. arun says:

    thanks a lot, really helpful

  48. Shammy Guleria says:

    It didn't work for me, still Alt+I+R command is not working and also right click insert button is greyed out. Only Ribbon bar Insert row tab is working nothing else, if you have a solution for my problem please tell us and one more thing when is use excel in another user account of my window it is working correctly.

    • Amit says:

      MS Excel -2013 - Adding a Column or Row - Problem

      File-> Options
      Click on 'Customize Ribbon'
      Click 'New Tab', Rename if you want, Say 'X'.
      Click 'Insert Sheet Columns'. This action will add this to 'New Tab' created.
      Click 'Insert Sheet Rows'. This action will add this to 'New Tab' created.
      Click 'Close'.
      Select any Column.
      You will see a new tab, 'X'.
      Click on 'Insert Sheet Columns', from the new tab 'X'.

      • Subbu says:

        Amit: Thanks a lot. Worked easily (MS Excel 2013). I wonder why right-click option, keeping the cursor on the row or column, doesn't work.

  49. Ayub says:

    Oh tq, worked perfectly!

  50. Gabu says:

    Thanks soo much.. really helpful

  51. Gill says:

    We were mystified why we could not insert a column: this is genius!

  52. raptor says:

    Did the job.


  53. Hui QIng says:

    it works!!! Thanks so much!

  54. Dani says:

    Thank you! Worked on the first try. Adding this to my list

  55. BK SHARMA says:


  56. Vinoth says:

    Thanks, i mid of work i got struck. and your post helped me instantly.

  57. shrikant says:

    Worked for excel 2K13, thanks..

  58. karthik says:

    Thanks for the solution. worked for Outlook 2013.

  59. Martin says:

    Excellent. Thank you for this - it worked with Excel for MAC 2011 and made sense!



  60. Rajeswari says:

    Thanks Much, it worked πŸ™‚

  61. Michelle says:

    I don't know WHY this worked, but it did, I'm using Office 13. Another forum suggested an alternate solution that did nothing for me (renaming an excel file in AppData by changing the extention from .xlp to .db)

    Thank you so much.

  62. Kim says:

    Thank you so much for this! Saved my ass for a report I'm doing. God Bless you!

  63. Anamika says:

    Millions of "THANKS" to you Sir.

    By biggest problem were solved!

  64. Dave says:

    Issue resolved. Thanks.

  65. Swati Kumari says:

    This was verry helpful.
    Thanks a lot!!

  66. Monika says:

    You are a genius! Thank you so much

  67. aa says:

    it works ....perfect.
    Thank u.

  68. GAURAV says:

    GOD BLESS U πŸ™‚

  69. Mushu says:

    Thank you very much, it worked! πŸ™‚

  70. Abraham Rohith Roy says:

    Thanks. This solution worked.

  71. dd says:

    Worked like a champ!

  72. Lori says:

    Thank you, it worked for Excel 2013

  73. AH says:

    Thanks! You are a wonder! Scratch that, you are a "wizard!" This solution worked for me in Excel 2016.

  74. Matt says:

    perfect thx

  75. Imran Anwer says:

    It worked, thanks a lot.

  76. Ben says:

    Great thanks....

  77. awal says:


    great thanks

  78. Farhan Khan says:

    Thanks a lot, that really helped.

  79. Satyajit says:

    Thanks a lot.. It really helped me for solving my Problem..

  80. AEV says:

    What helped the most was knowing to save, exit, and return. That is the trigger! I had deleted rows and columns endlessly, then tried to insert a new column and nothing. But after saving, exiting, and reopening...it worked! THANK YOU!

  81. Jam says:

    This doesn't work.

  82. Kevin says:

    None worked for me until I noticed a row was merged across the entire document. Removed the merge. Then inserting worked.

  83. Graceson says:

    Thanks a lot!!! very helpful πŸ™‚

  84. Jakir says:

    Super! Thanks

  85. Karthik says:

    Karthik P

    It works. Thank you.

  86. Andrew says:

    Thanks - very helpful! In particular, the key for me was:
    "...you'll need to save your spreadsheet and, in true Microsoft style, close it and open it up again".

  87. Marian Lokken says:

    Thank you Dan! You saved me a ton of time and frustration.

  88. Swarupa says:

    Very good tip. Thanks ! it worked !!

  89. Lee Elmer says:

    I just had the issue. Read the bog, and decided to save and close my spreadsheet. Bingo. When it came back up, I could insert. If that didn't work, then I was going to go through the select and delete steps.

  90. Vijayakumar says:

    wow, Awesome.. Thanks lot

  91. Sanjay says:

    Thanks Dan. This indeed helped me.

  92. Wendy says:

    Knew I was onto a winner when you'd picked up on their poor english πŸ™‚
    Saved me loads of frustration

  93. Aleksandras Cyras says:

    Perfect! Helped. Thanks πŸ™‚

  94. Molly says:

    Awesome! Thanks!

  95. Kiran says:

    It really worked

  96. Me says:

    I really need to say this: Thank you so much:)

  97. Max says:

    i did this & my 50x150 spread sheet was 146Mb! -- delete the cells after

Leave a Reply

Your email address will not be published. Required fields are marked *