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:

1. Click on the first cell in first blank column of the right of data.
2. Press and hold Ctrl + Shift button then press the Right Arrow key to select all columns to the right of first column.
3. In Edit menu, click on Delete.
4. Click on the first blank row below the last row in data.
5. Press and hold Ctrl + Shift then press Down Arrow Key to select all rows below the first row.
6. In Edit menu, click on Delete.
7. Click on small cell between first row heading and first column heading to select entire worksheet.
8. In Format menu, click on Cells then click on Border tab.
9. Click on None then click on OK.
10. Click on any cell in row where you want to insert /add a new row.
11. In Insert menu, click on Row. To insert a new column, click on Column.

That’s it. Hope it will be settled.

Kate

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:

1. Select the blank cell on the right after the data in the workbook.
2. 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.
3. Right-click the highlighted row and select Delete.
4. Select “Clear” option under Editing and select Clear All.
5. Try shifting the cell again and check whether the issue is resolved.

Hope the information helps.

Cheers,

Renniee

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

• 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.