Error in Excel after inserting formula on entire worksheet

Asked By 10 points N/A Posted on -

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?


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.


Best Answer by Marie Chan
Best Answer
Best Answer
Answered By 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:–microsoft-excel-cannot

Answered By 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.

Related Questions