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.

159 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.

  17. CHAITANYA KUMAR M says:

    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.
    Thanks!

  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..
    Thnx:-)

  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.

    • Cathy says:

      I did this last November but it is just a temporary solution. Now the problem is back. I have a very large spreadsheet ...

  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).

    Thanks!

  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.

    Thanks.

  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:

    to solve the problem for ALT IR JUST PRESS ALT HIR AND RESET THE SETTING AS DEFAULT ITS WORK PROPERLY after reseting

  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!

    Cheers

    Martin

  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:

    THANKS A TON MAN!!
    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:

    worked

    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:

    Worderfull!!!!!
    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

  98. ronaldk91335 says:

    I am using Office 2010 and this worked very well for the Excel column insert. However, I am trying to create a linked table in Access to that Excel spreadsheet that has the inserted column and Access generates an error that indicates that the extra columns are still in the Access file. An Access "Link Spreadsheet Wizard" popup appears with the message " Your data source contains more than 255 fields (columns). Access will import only the first 255 fields. OK". Including the inserted field, the Excel file with the inserted column contains only 7 columns after the insert. Is there a method that will actually remove the extra columns from the Excel file.

  99. Glenn says:

    No does not work

  100. Pavan Kumar G says:

    Awesome, It's worked for me,

    but tell me actually what's the Problem and how the above resolved it...

  101. Dinesh says:

    Thank you very much. It worked

  102. Majid says:

    it works! thanks guy

  103. Cam B says:

    You are a champion - worked like a charm

  104. Manoj says:

    great thnx for the solution

  105. Bob Lock says:

    Thanks! Out of 10 other sites- your solution was the only one that worked

  106. Steph says:

    Yay this worked!

  107. Dee says:

    Amazingly simple and effective! Thank you

  108. JM says:

    Thanks! God Bless

  109. Morgan says:

    Thanks, much better than the Microsoft solution, which might be correct on some level, but leaves out the details you provide.

  110. Grateful says:

    AMAZING! You are a genius! Thank you!

  111. A07 says:

    Thank you very much... it's help me to solve my problem

  112. Tim F says:

    Thank you so much! I wasted way too much time trying to figure this out myself.

  113. kasi says:

    Many thanks. solved my problem in secs

  114. Test says:

    Thanks! Very helpful

  115. Kai Le says:

    I did it, many Tks!

  116. Shaharia says:

    I did it, super thanks!!!!!!!!!!!

  117. Ana says:

    Thanks a lot! You saved my life.

  118. Dave says:

    Perfect solution!

  119. SW says:

    Wow, this worked like a charm. Thanks!!

  120. Cynthia says:

    OMG! Thank you for saving my sanity!

  121. Kit says:

    I don't think anyone has mentioned another version of this problem. Namely, that when Excel produces this error message:

    "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."

    it may be because you've used up the maximum permitted number of columns. In the case of Excel 2003 and Compatibility mode, that's 256, or column IV.

    My solution was to save all the data into a spreadsheet with a new name. Then I deleted the earliest 24 columns in my current workbook, and this allowed me another 24 columns to use.

    Alternatively, install Excel 2007 onwards.

    • Jon says:

      Yes. It turns out that I was using xls file and it ran in compatibility mode. The solution was to re-save the xls file as xlsx (or xlsm if using VBA/macros) and then close/re-open the file (xlsx/xlsm) to take it out of compatibility mode. Then the 256 column limit is increased to 16,000+ columns (Excel 2007 and later).

      I kept twisting my head about it until I saw this webpage and dug deeper. Surprised that Excel does not simply tell the user when they have reached an internal limit.

  122. Ioan Oprea says:

    Thx a lot!

  123. Hemanth Reddy says:

    thanks a lot.

  124. Toby Salk says:

    Still cannot add a column without getting the dreaded "box", To prevent possible loss of data... TOTALLY frustrated. I am on a mac.

  125. Martin says:

    Excel 2016, 32bit, german language:
    The "ALT-E A A" did not work for me (german version requires Alt-R LΓ–S), but the DeleteAll-Command is available on the ribbon: Home tab > Editing section > Clear All

    I had to do all of the following:
    * ClearAll on columns and rows
    * Delete columns and rows
    * Correct the behaviour that Ctrl-End still jumped far too far by resetting the last used row with a tiny bit of vba-code: https://stackoverflow.com/questions/16435582/ctrl-end-doesnt-bring-me-to-the-last-cell

  126. liza says:

    Very helpful. Thanks

  127. Andreina says:

    It worked perfectly! Thank you very much!

  128. EDMON says:

    It works, but the problems all my data on the highlighted cells was deleted using the command ALT,E,A,A.

  129. Mark says:

    It didn't do anything, the spreadsheet seems to sink into the screen a bit when I hit Control+Shift+down arrow......but then it bounces right back. I save it and close it down start it up again but still bubkis.

  130. keji says:

    It worked thank you!!

  131. Rama says:

    Many thanks, great solution!!

  132. Jono says:

    Thanks for this!

  133. May says:

    wow awesome. thank you so much!

  134. Shibu says:

    Thanks

  135. Sherry says:

    Thank you so much. I've had this problem so many times and get frustrated with it. This worked exactly as you said!

  136. Patrick says:

    Thats pretty cool. I have had the same problem and the this trick has done magic.

  137. moon says:

    Thanks work for me.

  138. You say 'highlight the next column to the right.' What if there *are* no more columns to the right? πŸ™ Just a big blank white space with no column delineations, nothing to highlight, static upon clicking?

    No matter what I do I can't delete this useless expanse of columns....tried everything I could think of. Nothing happens, or it just adds more to what I just deleted, effectively 'erasing' everything I've just told it to do so I'm always at the same number of columns. Maddening!

  139. mahesh kassa says:

    VERY VERY THANK YOU DEAR -9922085878

  140. Tom Miller says:

    Thank you! I have Excel 2016 and it worked for me

  141. Bobby says:

    Confirmed, works in Excel 2016

  142. AC says:

    thanks very much

  143. Robin says:

    Thanks. It is worked in Excel 2013

Leave a Reply

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