Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.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.
very helpful solution, Thanks
perfick-many thanks.
very good idea.
thank you very much..
This is working in Excel 2003, Please suggest same for Excel 2007.
I tried in Excel 2007 but it is not working.
Unfreeze Panes in the View menu fixed the problem for me.
This is the only solution that seems to have worked for me. I "Freeze Framed" everything first then "Unfreeze framed". Thanks.
Thanks for this, freezing then unfreezing was the only thing that worked for me also - why do we have to scour the web to find each other?
Very informative tip. Thanks
Unbelievable.. Works in Excel 2k10. Today, Oct'2012
It worked,... thanks a ton....
Thanks so much - I have been searching everywhere for a solution to this problem!
it worked, thanks a alot
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.
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.
if right click insert not working, on top (home) go to cells there insert, delete are there. use it. it work.
i was struggling for a long time.
Great..This is the only solution that worked for me.
Tried all other ways...nothing else worked.
It did not work for me either way
Same for me, Lati. Did you find a solution?
Good job . . . its working good thanks.
Great.........it worked.......Thanks
Bingo, works perfectly, thanks!
brilliant! I wish YOU worked in our IT department!
IT department member are no need to be excel expert!
Thanks , this has been bugging me for weeks!
thanks. very helpful. it saved me tons of time to figuring out.
Thanks a lot
I CANNOT thank you enough for this!!! i have been struggling with this thw whole time!!
You're welcome. 🙂
Excellent! It worked 🙂 Thanks very much.
stupid solution
Worked for me using Excel 2013.
Thanks!
Thank u so much..
Thank you. You are a big help. 🙂
Excellent! Thank you very much.
Perfect!! It works!! Thanks heaps for your help!!
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
I have tried everything suggested here without success!
Perfect! Tried several other solutions to my "can't insert rows in Excel" problem. This is the only one that worked. Try it.
How about for rows? Thanks! 🙂
Thanks for this. Loved the shortcut
Perfect solution man....its works on 2013 aswell..
Thnx:-)
Brilliant! Been trying all sorts of things! It works!
It works!!!. Thank you!
great.... its working. Thank you very much
Thanks, so much. It's very helpful. I had gone crazy with it for hours then I luckily found you!
work very well...
God bless you
thanks so much it saved me so much time
Didn't work for me
Thanks for this solution! The microsoft proposed solution was nonsensical gibberish! You're awsome!
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.
I did this last November but it is just a temporary solution. Now the problem is back. I have a very large spreadsheet ...
Many thanks - so very helpful....
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 !!!!!!
Thanks you are genuis
sorry you are Genius
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!
Worked perfectly for me in excel 2010... Thanks
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.
thanks a lot, really helpful
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.
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'.
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.
Worked for me...no other solution worked. Thank you!
Oh tq, worked perfectly!
Thanks soo much.. really helpful
We were mystified why we could not insert a column: this is genius!
Did the job.
Thanks.
it works!!! Thanks so much!
Thank you! Worked on the first try. Adding this to my list
to solve the problem for ALT IR JUST PRESS ALT HIR AND RESET THE SETTING AS DEFAULT ITS WORK PROPERLY after reseting
Thanks, i mid of work i got struck. and your post helped me instantly.
Worked for excel 2K13, thanks..
Thanks for the solution. worked for Outlook 2013.
Excellent. Thank you for this - it worked with Excel for MAC 2011 and made sense!
Cheers
Martin
Thanks Much, it worked 🙂
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.
Thank you so much for this! Saved my ass for a report I'm doing. God Bless you!
Millions of "THANKS" to you Sir.
By biggest problem were solved!
Issue resolved. Thanks.
Awesome
This was verry helpful.
Thanks a lot!!
You are a genius! Thank you so much
it works ....perfect.
Thank u.
THANKS A TON MAN!!
GOD BLESS U 🙂
Thank you very much, it worked! 🙂
Thanks. This solution worked.
Worked like a champ!
Thank you, it worked for Excel 2013
Thanks! You are a wonder! Scratch that, you are a "wizard!" This solution worked for me in Excel 2016.
perfect thx
It worked, thanks a lot.
Great thanks....
worked
great thanks
Thanks a lot, that really helped.
Thanks a lot.. It really helped me for solving my Problem..
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!
This doesn't work.
None worked for me until I noticed a row was merged across the entire document. Removed the merge. Then inserting worked.
Thanks a lot!!! very helpful 🙂
Super! Thanks
Karthik P
It works. Thank you.
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".
Thank you Dan! You saved me a ton of time and frustration.
Very good tip. Thanks ! it worked !!
Thanks
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.
wow, Awesome.. Thanks lot
Thanks Dan. This indeed helped me.
Knew I was onto a winner when you'd picked up on their poor english 🙂
Saved me loads of frustration
Perfect! Helped. Thanks 🙂
Awesome! Thanks!
Worderfull!!!!!
It really worked
I really need to say this: Thank you so much:)
i did this & my 50x150 spread sheet was 146Mb! -- delete the cells after
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.
No does not work
Awesome, It's worked for me,
but tell me actually what's the Problem and how the above resolved it...
Thank you very much. It worked
it works! thanks guy
You are a champion - worked like a charm
great thnx for the solution
Thanks! Out of 10 other sites- your solution was the only one that worked
Yay this worked!
Amazingly simple and effective! Thank you
Thanks! God Bless
Thanks, much better than the Microsoft solution, which might be correct on some level, but leaves out the details you provide.
AMAZING! You are a genius! Thank you!
Thank you very much... it's help me to solve my problem
Thank you so much! I wasted way too much time trying to figure this out myself.
Many thanks. solved my problem in secs
Thanks! Very helpful
I did it, many Tks!
I did it, super thanks!!!!!!!!!!!
Thanks a lot! You saved my life.
Perfect solution!
Wow, this worked like a charm. Thanks!!
OMG! Thank you for saving my sanity!
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.
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.
Thx a lot!
thanks a lot.
Still cannot add a column without getting the dreaded "box", To prevent possible loss of data... TOTALLY frustrated. I am on a mac.
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
Very helpful. Thanks
It worked perfectly! Thank you very much!
It works, but the problems all my data on the highlighted cells was deleted using the command ALT,E,A,A.
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.
It worked thank you!!
Many thanks, great solution!!
Thanks for this!
wow awesome. thank you so much!
Thanks
Thank you so much. I've had this problem so many times and get frustrated with it. This worked exactly as you said!
Thats pretty cool. I have had the same problem and the this trick has done magic.
Thanks work for me.
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!
VERY VERY THANK YOU DEAR -9922085878
Thank you! I have Excel 2016 and it worked for me
Confirmed, works in Excel 2016
thanks very much
Thanks. It is worked in Excel 2013
Very helpful, it worked. I had to do the Alt-E-A-A across and down as well, thanks.
I know this is old but it really helped me! Thanks! 🙂
thanks so much
I tried so many ways. But, this is very simple and Solved my problem. Thanks
Thanks a lot it did work for me too 🙂
Wow.. It worked..!! Thank you so much.. 🙂
Thank you very much! It works!
Thank very much!
Thank you!!!! This saved me a lot of headaches!
Thanks, it works !
Thanks.Worked very well indeed
It worked for me Dan.
I went to column XFD by pressing control and right cursor key. Then pressed "Merge & Center". I could then insert a column at the beginning. Thanks a lot.
It works! Thankssss a lot!
Works like magic, thank you!
Very helpful solution.
Thank you very much !
Excellent , i thanked you
thankyou
Thank you so much, this was driving me mad! Worked perfectly.
Thank you so much for the solution.
Thank you! This was very helpful.
Thanks!!
Great..
Thank you...
Thank you so much! You truly are a wizard. Saved me!
Amazing!
IT WORKS......
8 years on and still works like a charm! Thanks so much.
thank you so much!
Truly appreciate for sharing it.
Thank you , its solved
Brilliant ! Thanks for helping me keep my sanity
You're a genius! Thank you!!!
Thank you so Much. This was awesome and ive been struggling wiht this all day. All hail you and the internet
Bruh It worked. Thanks My Man. ^_^
Ah i tried everything said here it didn't work but finally i realised that it was due to the fact that i had some rows highlighted in a different background colour (and it does highlight it until the last column that excel supports if you select the entire row) which stopped it from introducing any new columns.
Didn't realise it could be as silly as this but i removed the highlight of rows in the blank columns till the last one that spreadsheet has and that worked like a charm.
Hope this helps those stuck due to similar scneario
Most helpful solution out there. Thanks!
Thank you! It worked!
Dude thanks a bunch! You really are a wizard!!