Error in Excel after inserting formula on entire worksheet

Asked By D Manob 10 points N/A Posted on - 01/28/2012

I can’t insert columns and rows on MS Excel. This happens when I tried to add a formula on my entire worksheet.

It appears that inserting new columns and rows are locked up. I'm running MS Excel 2003.

How can I fix this?

Error:

Microsoft Excel

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

Try to locate the last non blank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used.

Or, you can move the data to a new location and try again.

OK

• Status: Open
• Question Views: 1298
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Marie Chan

Go To Solution

Best Answer

Best Answer

Answered By Marie Chan 15 points N/A #158620

Error in Excel after inserting formula on entire worksheet

There is a clue in the error message where it says "Microsoft cannot shift nonblank cells". You have to make sure that there are no nonblank cells as well as remove any formatting that these cells may contain. Do these steps:

• Go the first cell immediately to the right of your data where the blank column is. Press CTRL+SHIFT and the RIGHT ARROW on your keyboard. Doing this selects all cells to the right. Go to the EDIT menu and click DELETE. Now click the first blank row after the last row of your data. Again CTRL+SHIFT, this time press the DOWN ARROW. Go to EDIT again and DELETE.

To remove any formatting, do this:

• Repeat the actions above where you select the cells on the right of your data. Instead of going to the EDIT menu once you have the cells selected, go to FORMAT. Depending on what formatting you can see that you have to remove, select the appropriate formatting and just select None.

Once there you have removed cell contents and formatting, try to add your formula once more. The KB link seen below discussed this error as well as the steps mentioned above:

https://support.microsoft.com/en-us/help/305568/error-message-to-prevent-possible-loss-of-data–microsoft-excel-cannot

About Marie Chan

Questions
3

Answers
111

Best Answers
37

• Vote Up 0 Vote Down

• Posted on - 01/28/2012
• Question Category: MS Excel

Answered By Sherin Viluel 0 points N/A #158622

Error in Excel after inserting formula on entire worksheet

I think this answer will help you to understand the situation and solve the problem.

Usually there are limited amount of  rows in an excel sheet. Although we insert or delete rows the total no of rows will not change.

 

In your excel sheet I guess you have entered data in the last row. Therefore if you want to insert another blank row you have to delete the data in the last row. 

 

 

In the same way there may be data in the last column of your excel sheet. If you want to insert a new column to the sheet you should delete the data in the last column. 

 

 

 

 

Then you will be able to enter new column.

 

I think this answer will be helpful to your problem.

About Sherin Viluel

Questions
0

Answers
50

Best Answers
37

• Vote Up 0 Vote Down

• Posted on - 06/21/2012
• Question Category: MS Excel