Frequently getting the following error: “To prevent possible loss of data Microsoft Excel cannot shift nonblank cells off the worksheet” whenever tries to add a new row or column?
Looking for the fixes to resolve Excel cannot shift nonblank cells off the worksheet error then you are at the right place. Here I’ll share the complete detail about to prevent possible loss of data Excel cannot shift nonblank cells error and ways to fix it.
About The Excel Cannot Shift Nonblank Cells Error:
Here is the complete declaration of the error message:
To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells or delete data from the end of your worksheet.
Why You Cannot Shift Nonblank Cells Off The Worksheet?
- This cannot shift nonblank cells off the worksheet error mainly occurs when any you try to insert rows or columns in their respective worksheets.
- You may encounter the error if the formula had been added to an entire worksheet or if formatting (like a border) was added to an entire column or row.
- This specific Excel error occurs when you consider the used range of your worksheet to include all the rows and columns.
- When the form layout contains an expanding column and as it is expanded it creates more columns than Excel can cope with.
These are some of the common reasons responsible for the error “to prevent loss of data Excel cannot shift nonblank”.
How To Fix Excel Cannot Shift Nonblank Cells Off The Worksheet Error?
To fix the error message To Prevent Possible Loss of Data Microsoft Excel Cannot Shift Nonblank Cells off the Worksheet here I have listed both the automatic and manual solutions. If you are trying manual solutions then make sure to follow them carefully for any further issues.
Solution 1# Repair The Corrupt Excel Worksheet
Most of the time it is seen that Excel stops working when corruption issue encountered so this can be the reason behind this Cannot Shift Nonblank Cells off the Worksheet issue.
Try the professional recommended Excel Recovery Tool to resolve any type of corruption problem, this is the highly advanced tool that just by scanning once detects and fix Excel errors and recover data stored in it. With this, you can repair and recover corrupted, damaged and inaccessible data Excel workbook data.
This is a unique tool and is capable to restore entire data including the charts, worksheet properties cell comments, and other data without doing any modification. It is easy to use and supports all Excel versions.
Steps to Utilize Excel Recovery Tool:
Solution 2# Remove Formatting
To fix the error the very first solution that you must try is to clear each cell in a row or column. After that remove formatting from the remaining rows and columns.
Follow the steps to do so:
- First, in the first blank column to the right of your data click heading cell. (Heading cell is the first cell in each column, and shows which column you are in).
- Then press CTRL+SHIFT and RIGHT ARROW key to select entire columns to the right of the first column that you clicked.
- On the Edit menu > click Delete.
- And click the first blank row below the last row in data.
- Press CTRL+SHIFT, and DOWN ARROW key to select entire rows below the first row that you clicked.
- On the Edit menu > click Delete
Please Note: For removing any formatting from the remaining cells continue to follow these steps.
- Now click the small cell between the first row heading > first column heading for choosing the entire worksheet.
- Then on the Format menu,> click Cells > and click the Border tab.
Please Note: well the formatting added to the remaining cells might vary and step 8 adopts that you need to remove border formatting. And to remove other types of formatting, you might need to change settings under another tab in the Format Cells dialog box.
- Click None > OK.
- Next, in the row click any cell where you need to insert a row.
- On the Insert menu > click Row > to insert a column > Column.
Hope this works for you to fix to prevent possible loss of data Excel error. But if not then make sure to clear the entire recent changes you made to your workbook and after that check if the issue is resolved or not.
Solution 3# Use The Visual Basic Editor For Adding Rows And Column
When Excel won’t let you insert columns or rows after the occurrence of this error then you can use the VB editor to fix this issue.
In the Visual Basic Editor, you have to enter only a single line of code which will reset the used area of your Excel spreadsheet.
- Make a right-click on the worksheet tab mainly where you are rendering issues to add new rows or columns. After then choose the View Code option.
- Now hit the Ctrl+G button from your keyboard to open the Immediate window, like this:
- In this opened Immediate window, you have to type the ActiveSheet.UsedRange. After this press the Enter button. This command will change the used range of the worksheet.
- Choose the File and then tap the Exit option. This will close the Visual Basic Editor.
Now you can easily insert new columns or rows as per the need in your worksheet.
Solution 4# Remove Blank Cells In Excel
Deleting up the empty cells will surely gonna fix Excel cannot shift non-blank cell issue.
Before trying this method it’s better to make a backup of the worksheet.
Now carry out the below-given steps to delete blank cells in Excel:
- Make the range selection first from where you have to remove the blank cells. Choose all the cells having data in them. After that hit the upper left cells and then press the Ctrl + Shift + End. This will automatically extend the last used cell’s selection.
- Now from your keyboard press the F5 button and tap the Special…. Alternatively, hit the home> Formats group> Find & Select > Go to Special:
- From this opened Go To Special dialog box choose Blanks and tap the OK. Choose entire blank cells present in the range.
- Make a right-click over the selected blank cells and then from the context menu choose the Delete button.
- As per the data, layout chooses either the shift cells up or shift cells left option and then click the OK button.
Here I am choosing the “ shift cells left” option. Now you will see that blank space is been removed from your table.
Tip: If any step goes wrong meanwhile performing the above step then you can correct it by pressing the Ctrl + Z option.
It is estimated after following the given solutions to prevent possible loss of data Microsoft Excel cannot shift nonblank cells off the worksheet error is resolved.
Make sure to follow the given steps carefully to fix the Excel error.
Despite this, as I always say don’t forget to backup your data, scan your Excel file with a good antivirus program to remove various and malware infections.