Wednesday, 6 November 2013

Error message-To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet

To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet
When you try to insert rows or columns into a Microsoft Excel 2000 worksheet, you may receive an error message that is similar to the following:

To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet.



CAUSE :

This issue may occur if a formula has been added to the entire worksheet, or if formatting (such as a border) has been added to an entire column or row.



Microsoft RESOLUTION :

To resolve this issue, clear each of the cells in the row or column, and then remove any formatting from the remaining rows and columns. To do this, follow these steps:
  • Click the heading cell in the first blank column to the right of your data. (The heading cell is the first cell in each column, and indicates which column you are in).
  • Press and hold down CTRL+SHIFT, and then press the RIGHT ARROW key to select all of the columns to the right of the first column that you clicked.
  • On the Edit menu, click Delete.
  • Click the first blank row below the last row in your data.
  • Press and hold down CTRL+SHIFT, and then press the DOWN ARROW key to select all of the rows below the first row that you clicked.
  • On the Edit menu, click Delete.
To remove any formatting from the remaining cells, continue to follow these steps.
  • Click the small cell between the first row heading and the first column heading to select the entire worksheet.
  • On the Format menu, click Cells, and then click the Border tab.
The formatting added to the remaining cells may vary.To remove other types of formatting, you may need to change settings under another tab in the Format Cells dialog box.
  • Click None, and then click OK.
  • Click any cell in the row where you want to insert a row.
  • On the Insert menu, click Row. To insert a column click Column.
My Simple Solutions : 
Solution 1 :
First select the Entire Sheet (By Clicking on the Left most corner of the sheet)
Next Go to Borders then select No Border.
Now you can Insert the desired no.of rows or columns any where in the Work sheet.
Solution 2 :
First select any last blank column after your data and then delete that entire column.
Next select any  last blank row after your data and then delete that entire row.
Now you can Insert the desired no.of rows or columns any where in the Work sheet.

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts