Cannot Shift Objects Off Sheet in Excel 2007

Recently, while working on a small worksheet in Excel 2007, I tried to insert a new row. Up popped an annoying error message, “Cannot shift objects off sheet.” I couldn't insert a new row, and had to drag things around, to get the new data inserted.

Cannot Shift Objects error message

Keep reading, to see how I investigated the problem, and fixed it with a simple keyboard shortcut.

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, you could change the properties of the comment box, so it would move with the cells.

Comment Properties

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.

Find and Select

It lists all the visible and hidden shapes on the worksheet, including comments, and a data validation dropdown. 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.

Cannot Shift Objects 02

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.

Display Options

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

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 on a worksheet page on Ron de Bruin’s web site.

________________________

36 comments to Cannot Shift Objects Off Sheet in Excel 2007

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>