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.

198 Responses to Unable to insert columns [solved]

Leave a Reply

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