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:
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.

0 thoughts on “Cannot Shift Objects Off Sheet in Excel 2007”

  1. Solved the message during Auto-filtering simply by using the Comment finder (Review tab/Comments section/Next button)in version 2007, and deleting 4 Comments.
    Something to do with Comments apparently conflicts with hidden rows.
    Worked perfectly afterwards.

  2. Thank you Yashdeep from Texas.
    I will try CNTL + 6 next time this happens.
    This sounds must faster than finding and deleting old Comments in filtered rows.

  3. I wonder what causes this to happen. Because i didn’t change the settings and it just happen, then i suddenly need to change the settings to fix it up. I guess this is kinda sort of a virus or something. The solution works for me though.

  4. Choosing “All” has no effect on my Excel 2007. Nor does Ctrl + 6 (or F6 or any other 6 I can find). What am I missing?

  5. I came accross the issue initially when trying to use the filter to group rows of data.
    I tried the ctrl+6 or choosing “all” but it didn’t work for me but when I changed all the comments properties to “move and size with cells” it solved the issue.
    Applying the fix to all the comments was a bit tedious so it would be nice if MS put something in the Excel Options pages to allow this to be instantly applied to all comments in the workbook/sheet.
    Thanks very much for taking the time to put this info out there though – saved me a load of stress.

  6. Ctrl 6 erased all my graphics. I solved the problem by removing the hyperlinks from all my pics (right click on graphic and select remove hyperlink). I saved the spreadsheet and exited Excel. I then reopened the spreadsheet and I could insert all the rows I wanted. I have Excel 2010.

    1. @Judy, the Ctrl + 6 shortcut will hide or show all the objects in a workbook. I hope you were able to get the graphics back by pressing Ctrl + 6 again.
      Thanks for sharing how you fixed the problem in your workbook.

  7. When I pressed Ctrl 6 the graphics did not come back. Luckily I saved before I tried it! Always a good plan when you try something new and you are very welcome!

  8. Thanks so much! Finally I could solve this problem.
    This one worked for me>>
    Method 1 solved the problem for me – in Excel Options, change the workbook Display setting for objects, from Nothing (hide objects) to All.

  9. I had a grouped set of columns with comments and I inserted a group of 4 colums. I took out the comments and no more error.

  10. Cannot Shift Objects Off Sheet in Excel 2007
    By Rithy Than 06 Oct, 2012
    Recently, while working on worksheet in Excel 2007, I tried to hide row. Up popped an annoying error message, “Cannot shift objects off sheet.” I couldn’t hide the row.
    Please reply me soon,
    Thank you anyway.

  11. similar situtation with “Cannot shift objects off sheet” message when filtering with hidden columns, this occured after using a file for 14 months with no problems.
    Control 6 did not fix the problem long term. Deleteing recently added comments resolved the issue. Thanks doug.

  12. My excel version is excel 2003.
    When I delete all of the comments from each cell then this problem is solved.
    Thanks everybody.

  13. Last week Ctrl+6 worked. Since then I added another column and it doesn’t work. I changed the workbook display settings, but still had the problem. I deleted the comments in the columns near the end of the sheet and still had the problem. Deleting all the comments on the worksheet solved it. Unfortunately I really wanted to keep those comments.

  14. Did not work for Excel 2007. Trying to filter – keep getting the message. I have comments but no comments in the worksheet.

  15. Noticing all the comments in this thread made about “Excel comments” and how deleting them helped…I tried another path. Looking at all my comments I noticed a few of them extended into the columns I wanted to hide. I had already hidden all of the columns to the right except one for a right border and wanted to delete 4 columns to the left of that border. I simply moved each comment to the left so it’s right edge was not extended into any of the remaining columns needing to be hidden. Tried hiding the columns again and they hid properly. Excel 2007 did not like hiding columns where there was not room to show the comments as they were situated.

  16. You are welcome. I need to correct one point and add another.
    Correction: where I said “and wanted to delete 4 columns” it should read “and wanted to hide 4 columns”
    Addition: I had used the CTRL + 6 which by itself did not help. But combined with moving the “comments” location it then worked.

  17. I got the message when i tried to insert a row
    Try this
    Close all worksheets other than the one with the problem.
    Click “Office Button” – top left hand corner of spreadsheet
    Select “Excel”
    Select “Advanced”
    Scroll down to “Display”
    Select “No Comments or Indicators”
    Scroll down to “Display Options for this worksheet”
    Select “All”
    Then repeat process again…
    Click “Office Button”
    Select “Excel”
    Select “Advanced”
    Scroll down to “Display”
    Select “Indicators only comments on hover”
    Worked for me.

  18. If all your comments have been deleted and CTRL+6 doesn’t work. Another cause if if you’ve hidden a bunch of columns.
    If you data table is in columns “A=1” to “C=3”, and then from column “E=5” through “XFD” you hide everything. You will only be able to hide ONE column before you get an error message “Cannot Shift Objects off Sheet”.
    – If you want to hide 2 columns, hide columns “F=6” to “XFD”, so that you have two blank columns (D and E)
    – If you want to hide 3 columns, hide columns “G=7” to “XFD”, so that you have Three blank columns (D,E and F)

Leave a Reply to Anonymous Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.