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.


43 comments to Cannot Shift Objects Off Sheet in Excel 2007

  • Greg

    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.


  • Monika

    Thanks Greg, this worked for me!
    I was going crazy figuring it out!!!!

  • Chris

    Thank you for solving my problem.

  • Yashdeep

    control + 6 . It solved the problem immeidately.

  • Greg

    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.


  • Vernon

    Ctrl 6 worked for me. Thx alot.

  • Jagger

    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.

  • Wafaa

    wow...amazing...i was going crazy with this problem and you solved it in a second..thank you thank you thank you

  • mhatet

    ctrl + 6 solved cannot shift object off sheet

  • Mike J

    Thank you.
    ctrl 6 worked like a charm

  • Tom

    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?

  • Shaun

    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.

  • Noku

    It was the comments. Thanks Greg!

  • Lea

    The control 6 worked for me! Thanks so much.

  • Jody

    Thank you so much! Ctrl 6 also worked for me :)

  • Judy

    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.

    • @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.

  • Judy

    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!

  • Gilles

    Great! your last hint Debra worked for me!

  • Jeng

    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.

  • Venkateswararao

    Thank u so much greg .



  • dANIEL

    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.


    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.

  • Ed

    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.

  • Enamul Haque

    My excel version is excel 2003.

    When I delete all of the comments from each cell then this problem is solved.

    Thanks everybody.

  • Dany

    Thanks a lot!!! It works, I deleted all comments and now my excel is really fine.


  • Linda

    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.

  • Sreejesh V.S

    Problem solved


  • Dave

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

  • Imran

    The first suggestion worked good. thanks for help

    Thanks A Lot
    I spent almost 1 hour to find the solution

  • Shane

    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.

  • Shane

    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.

  • Arfabee

    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.

  • dddd

    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)

  • Elham

    Greg you are genius :) thank you



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=""> <s> <strike> <strong>