Cannot Shift Objects Off Sheet Excel Error

Cannot Shift Objects Off Sheet

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.

Cannot Shift Objects error message
Excel error: Cannot shift objects off sheet

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

Excel error: Can't push objects off the sheet
Excel error: 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.
Find and Select
Find & Select command on Excel Ribbon

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.

Selection and Visibility Pane
Selection and Visibility Pane

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.

Display Options

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
Comment Properties
Format Comment dialog box – object positioning

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

44 thoughts on “Cannot Shift Objects Off Sheet Excel Error”

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

    1. Greg, Thank you very much. I tried everything and only your advice helped me. Thanks a lot. I am so happy now!!!

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

  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. wow…amazing…i was going crazy with this problem and you solved it in a second..thank you thank you thank you

  5. 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?

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

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

  8. 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!

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

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

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

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

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

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

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

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

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

  18. 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”
    Click”OK”
    Then repeat process again…
    Click “Office Button”
    Select “Excel”
    Select “Advanced”
    Scroll down to “Display”
    Select “Indicators only comments on hover”
    Click”OK”
    Worked for me.

  19. 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 Linda Cancel reply

Your email address will not be published.

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