Asked By
jovyhasle
1170 points
N/A
Posted on - 08/03/2011
Good day to all,
I am at office working on my report using Microsoft Excel when I met an error. When I shifted nonblank cells off of the worksheet, an error message appeared on my screen. I pasted below the screen shot of the error.
Microsoft Office Excel
To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select location in which to insert new cells, or delete data from the end of your worksheet.
If you do not have data in cells that can be shifted off of the worksheet, you can reset which cell Excel consider nonblank. To do this, press CTRL+End to locate the last nonblank cell on the worksheet. Delete this cell and between it and the last row and column of your data then save.
As I looked on the error message above, I am wondering why I can't shift nonblank cells off of the worksheet. I've been doing this before and the suggestion in the error message is not appropriate for me.
Please share some other suggestions that might lead me to getting rid of this error message. Thanks a lot.
Answered By
Renniee
0 points
N/A
#124735
Excel cannot shift nonblank cells off of the worksheet
Hi Jovyhasle,
You will receive the error message mainly when the last cell, row, or column is formatted. Try following the steps provided below to resolve the issue:
-
Select the blank cell on the right after the data in the workbook.
-
Hold the Shift key and press the End key and then the Right arrow. Now the entire row from the selected cell will be highlighted.
-
Right-click the highlighted row and select Delete.
-
Select “Clear” option under Editing and select Clear All.
-
Try shifting the cell again and check whether the issue is resolved.
Hope the information helps.
Cheers,
Renniee
Answered By
jtlim
0 points
N/A
#124737
Excel cannot shift nonblank cells off of the worksheet
Hi Jovyhasle:
Are you sure all cells are empty? The error message explained that shifting nonblank cells off the worksheet is not possible if there are data in the cells. It also occurs if a formula is applied to the whole worksheet even formatting the entire column/row from top to bottom. To make sure you won't encounter this error, you should clear the spreadsheet completely.
To remove added formulas in each column/row:
1. You can either click the Row Number or Column Letter, then press and hold SHIFT and select the range of cells that needs to be cleared.
2. Right-click on the highlighted part of the worksheet and select DELETE.
To remove formatted cells (assuming the entire worksheet was Bordered):
(Repeat Step 1)
Right-click on the highlighted part of the worksheet and select FORMAT CELLS . Select the Border Tab and click "None" from the three Preset choices and apply by pressing OK.
Jackie
Excel cannot shift nonblank cells off of the worksheet
-
First save your file.
-
Go to the next column to the far right data column in your worksheet.
-
Hold down the ‘Shift’ key and press ‘End’ key, then right arrow key.
If you are working in MS Excel 2007,
-
Click on ‘Home’ tab then down arrow in ‘Delete’ tool in ‘Cells’ group.
-
Now click on ‘Delete Sheet Columns’.
-
Now go to the next row to the last row of data.
-
Hold down the ‘Shift’ key and press ‘End’ key and down arrow.
-
Click on down arrow in ‘Delete’ tool in ‘Cells’ group again and select ‘Delete Sheet Rows’.
Hope now your problem is fixed.
Excel cannot shift nonblank cells off of the worksheet
Dear Jovyhasle
To resolve this issue, clear each of the cells in row and column then remove formatting from the surplus rows and column. Do some simple steps:
-
Click on the first cell in first blank column of the right of data.
-
Press and hold Ctrl + Shift button then press the Right Arrow key to select all columns to the right of first column.
-
In Edit menu, click on Delete.
-
Click on the first blank row below the last row in data.
-
Press and hold Ctrl + Shift then press Down Arrow Key to select all rows below the first row.
-
In Edit menu, click on Delete.
-
Click on small cell between first row heading and first column heading to select entire worksheet.
-
In Format menu, click on Cells then click on Border tab.
-
Click on None then click on OK.
-
Click on any cell in row where you want to insert /add a new row.
-
In Insert menu, click on Row. To insert a new column, click on Column.
That’s it. Hope it will be settled.
Kate
