You may have come across various errors and issues while working with Excel. One such error is the “Cannot Shift Objects Off Sheet” error. This error occurs when you try to insert or delete rows or columns, and Excel is unable to move objects like charts, images, or shapes on the worksheet.
In this article, I will guide you through the steps to resolve this error and ensure a smooth Excel experience. So, you will be able to do column and object insert, delete, and movement-related operations in your Excel worksheet.
First of all, we will see the possible causes of this error. The Excel error “Cannot Shift Objects Off Sheet” may occur because of one of the following reasons,
- Object display setting to “Nothing” (most common)
- Hidden objects
- Protected worksheets or Freeze panes
Now let’s take a look at the possible solutions to fix this error.
How to remove “cannot shift objects off sheet” in Excel
In most cases, this error goes away after changing the object display setting from the Excel advanced settings option. Here is how to fix this,
Step 1: Open File->Options->Advanced
Step 2: Now scroll down to the section “Display options for this workbook” settings
Step 3: Under For objects, show, select All instead of Nothing (hide objects).
Step 4: Close Settings & Check if the error is gone or not
Alternatively, you can just press Ctrl + 6 to toggle this setting
If an error is not gone after doing the above setting please check for the following fixes.
1. Remove objects or charts:
If there are objects such as comments or charts on the worksheet that are blocking the insertion or deletion of cells or rows, you will need to remove them. Right-click on the object and select “Cut” to remove it from the worksheet.
2. Unprotect the worksheet:
If the worksheet is protected, you will need to unprotect it to insert or delete cells or rows. Go to the “Review” tab and select “Unprotect Sheet.” If the worksheet is password-protected, you will need to enter the password to unprotect it.
Shortcut Key : Alt+R+P+S
3. Unfreeze panes:
If you have frozen panes in your worksheet, you will need to unfreeze them to insert or delete cells or rows. Go to the “View” tab and select “Freeze Panes,” then select “Unfreeze Panes.“
Shortcut Key : Alt+W+F+F
4. Disable add-ins or macros:
If certain add-ins or macros are causing the “Cannot Shift Objects Off Sheet” error to appear, you will need to disable them. Go to the “File” menu, select “Options,” and then “Add-Ins.” Select the add-in or macro you want to disable and click “Go.” Select the add-in or macro and click “Remove.“
By following these steps, you should be able to resolve the “Cannot Shift Objects Off Sheet” error in Excel and insert or delete cells or rows without any issues. If you are still encountering this error, it may be a good idea to seek the help of a professional or contact Microsoft support for further assistance.