If you come across a situation where Excel won’t insert a column or row and gives you a message of ‘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’ you will need to follow a few simple steps to allow the insertion.’
Before we explain this, there are also other reasons why it won’t allow columns or rows to be inserted (but it won’t give this same message). These can include that the sheet:
- is protected from allowing row or column insertions- see how to do this on our free protecting spreadsheets course
- has a array formula in the region you are trying to insert- see what we mean on the protecting rows without a password
- has a pivot table or similar object in the region.
The proposed method below won’t work for these items. You would need to move/ address the particular issue to complete your insertion.
YouTube ‘Excel cannot shift nonblank cells off worksheet
Excel won’t insert a column or row with this specific error message
In this case, Excel thinks that every column or row contains at least one item, so when you try and insert the column/ row, Excel would need to drop the last row or column which may contain valuable data. Excel refuses to do this.
In the example below we have put a 1 in every cell right to the end of the spreadsheet, but sometimes they look blank and Excel still thinks there is something there (something as simple as changing the format of an entire row can cause this).
The first step is fairly obvious.
- Highlight all the columns or rows after the used part of the spreadsheet (for a quick way for columns, highlight the column, hold CTRL & SHIFT and click right until it hits the end of the spreadsheet)
- and delete them (not remove the contents but right click on a column and choose DELETE).
But even if you delete the column, when you try insert a column it will give you the same message. Not sure why, but once you delete the columns, you must
- SAVE the file,
- close it down,
- open it up and now you will be able to insert the column.
The same applies to rows.
Related
Stop excel users from inserting or deleting rows
Move and insert an Excel column (quickly)