Cannot Shift Objects Off Sheet Excel Error

Recently, while working on a small worksheet in Excel, I tried to insert a new row. Up popped an annoying error message, “Cannot shift objects off sheet.” Here’s how I investigated the problem, and fixed it with a simple keyboard shortcut – Ctrl + 6

Here is the pop-up message that appeared on my screen.

[Update] In later versions of Excel you might see this error message: “Can’t push objects off the sheet”

Video: Fix Cannot Shift Objects Error

This video shows the steps to fix the problem, and the time-saving shortcut. There are written steps below the video.

View a List of Objects

It was odd – as far as I knew, the worksheet didn’t have any objects or comments, and only a few rows. What was causing the problem? Maybe there were some hidden shapes that I’d added, and forgotten about, or a comment tucked in a dark corner.

To see what’s on a sheet in Excel 2007, you can use the Selection and Visibility Pane.

1. Click the Home tab on the Ribbon.
2. At the far right, click Find & Select, then click Selection Pane.

It lists all the visible and hidden shapes on the worksheet, including comments, and a data validation drop down. Visible objects have an eye icon at the right, and hidden objects have an empty icon.

In this example, there’s a comment, data validation drop down, and a Bevel shape on the worksheet.

A Fix For Excel 2007

However, in my problem workbook the Selection Pane list was empty. The old Properties fix wouldn’t work, because there were no objects to change.

Fortunately, a search of MSKB turned up an article with the solution:

http://support.microsoft.com/kb/211769

Method 1 solved the problem for me – in Excel Options, change the workbook Display setting for objects, from Nothing (hide objects) to All.

The keyboard shortcut to toggle this setting is Ctrl + 6 – much quicker than opening the Excel Options window.

Problem solved, and I’ve had no more trouble inserting rows.

If that didn’t work for your Excel worksheet, try changing the object properties, as described in the next section.

Change the Object Properties

In older versions of Excel, this problem usually occurred if you tried to insert columns and there was a comment hidden in one of the columns to the right.

To fix the problem in those versions:

1. If the comment isn’t showing, right-click the cell with the comment, and click Show/Hide Comment
2. Right-click on the border of the comment, and click Format Comment
3. Click the Properties tab (if you only see a Font tab, click Cancel, and try again — be sure to click the comment border, not inside the comment)
4. For Object Positioning,
   • choose Move and size with cells
   • OR, choose Move but don’t size with cells
5. Click OK, to apply the change

More Solutions to Cannot Shift Objects

[UPDATE] Thanks for adding your comments below, and here are other solutions to this problem, based on those comments.

1. Greg found and deleted 4 comments, and that solved the problem in his workbook. To find the comments, he clicked the Review tab at the top of Excel. Then, in the Comments group, he clicked the Next button.
   • In newer versions of Excel, click the down arrow in the Notes group, then select Next Note.
2. Shaun found it tedious to change each comment’s Move/Size properties  individually, and wished it could be done with a single setting. I’ve added macros to do that on my Contextures site.
3. Judy solved the problem by removing hyperlinks from all her graphics.
4. Shane fixed the problem by moving the comments, instead of deleting them.

Working with Shapes and Objects

For more information on working with Excel Shapes, and some of the problems you can encounter, visit the Delete or Hide Objects/Controls page on Ron de Bruin’s web site.
________________________