What to do when Excel won't let you insert columns

Blogger
Share this content
19

By David H. Ringstrom, CPA

From time to time you might encounter the prompt that appears when Excel thinks you can't add additional columns or rows.
 
This occurs when Excel considers used range of the worksheet to encompass all columns or rows. In this article I'll describe some techniques you can use to overcome this problem.
 
Figure 1: It's frustrating when Excel won't allow you to insert columns or rows.
 
 
The first, and usually easiest, method is to delete all columns to the right of the active area of your worksheet. If you can't insert rows, delete all rows below the active area of your worksheet.
 
For instance, assume you have data in columns A through M of your worksheet. To delete the remaining columns, place your cursor in cell N1, and then press Ctrl-Shift-Right. This will take you to the last column of the worksheet, which is column XFD in Excel 2007 or 2010, or column IV in Excel 2003 or earlier. Once you've done so, the cells in row 1 starting from column N through the right should be selected. Right-click on any of the selected cells, choose Delete, Entire Column, and then OK.
 
Further, let's assume our data goes down to row 28. Place your cursor in cell A29, and then press Ctrl-Shift-Down. This will take you to the last row of the spreadsheet, which is row 1,048,576 in Excel 2007 and 2010, or row 65,536 in Excel 2003 and earlier. Right-click on any of the selected cells, choose Delete, Entire Row, and then OK.
 
You may now try inserting new columns or rows. If that doesn't work, the next step is to use the Visual Basic Editor to enter a single line of code that will reset the used area of the spreadsheet:
     1. Right-click on the worksheet tab of the sheet where you can't insert columns (or rows), and then choose View Code.
     2. Press Ctrl-G to display the Immediate window, as shown in Figure 2.
 
Figure 2: The Immediate window in Excel's Visual Basic Editor.
 
 
     3. In the Immediate window, type ActiveSheet.UsedRange and then press Enter.
     4. It will appear as if nothing has happened, but the command in Step 3 forces Excel to change the Used Range of the worksheet to conform to just the area where your data is.
     5. Choose File, and then Exit to close the Visual Basic Editor.
 
You should now be able to insert new columns or rows as needed in your worksheet.
 
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.
 
Related articles:
 

Replies

Please login or register to join the discussion.

this great information i've learn on here so keep up the good inforamtion you've posted here guys

thank you so much, I've got it solved alreadly

Hi! I've just posted how to fix this:

http://runakay.blogspot.com/20...

It didn't work for me. Any other suggestions

It happened to me as well. the above did not work. I tried a trick. I opened a work book where I could incert colums. then with that workbook I opened my file which was having probmem (by file Open mode "CTRL+O" and not by going through folder) then tried to incert column. It worked. What I feel is there could be file extension issue. Just try this out it may work for you as well. Thanks, Hemendra.

Had the same issue, turned out to be the file name was too long (sounds weird), when I renamed to a smaller one it worked.

The three paragraphs under Figure 1: Very helpful! Thank you!

Worked well gr8 help thanks

thank you

If there is a problem, then I would seek help that could help resolve this issue. There are so many menus that can be tried one by one. Learn about Seattle IT Consulting James Murray

Entering the column will be an easy job for those who know basis. They will be able to enter the field in accordance with the needs of how many.Best Delux Prince Of Wales Island Fishing Lodges

Tnx. I fix my problem with insert new columns.

awesome! worked like a charm - thank you!

Didn't work at all.

Insert column will be a difficult thing if you and a lot of people do not know how. You must use the menu on the right way. led high bay lighting

The first didn't work, but the Visual Basic Editor one worked perfectly! Thank you for posting this.

Thank you so much so helpful! Was driving me crazy!! Solved a work problem!