Friday, May 28, 2021

Why do i see a cannot shift objects off sheet message in excel

Sometimes, the following message prevents you from inserting or hiding columns or rows in a worksheet.

Cannot shift objects off sheet message box

If you see this message, refer to the following table for possible solutions.

Issue

Why it happens, and what to do about it

You can't insert rows or columns

The "Cannot shift objects off worksheet" message occurs when you attempt to insert rows or columns in a worksheet, and the option Nothing (hide objects) is selected under the For objects, show section in Excel options. This is a known bug in some versions of Excel. Although this option is not selected by default, it's possible to set it accidentally by pressing a particular keyboard shortcut (CTRL+6).

To work around the bug, you must change the setting from Nothing (hide objects) to All. The fastest way to do this is by pressing CTRL+6.

Alternatively, you can change the setting in Excel options. To do so,

  1. Click File > Options > Advanced.

    Note: In Excel 2007, click the Microsoft Office Button Office button image , and then click Excel Options.

  2. On the Advanced tab, scroll to Display options for this workbook settings. Under For objects, show, select All instead of Nothing (hide objects).

    Options for showing and hiding objects in Excel Options dialog box

It may be that you intentionally hid objects in your workbook. For example, if you have a lot of objects in your workbook, opting to hide those objects may make the workbook more responsive. If that's the case, you can use the keyboard shortcut (CTRL+6) to switch between hiding and showing objects.

You can't insert or hide columns

If the "Cannot shift objects off worksheet" message (or a similarly worded message) continues to appear even after applying the previous resolution, it may be because there are objects such as comments, images, or charts in cells toward the end of the worksheet grid.

The amount of columns you can hide or insert is limited to the amount of columns between the right edge of the object and the last column on the worksheet (column XFD). If the number of columns that you want to hide or insert anywhere else on the worksheet is larger than that number, the object will be moved off the worksheet, and you will see the message.

Generally speaking, you can fix this issue by finding the offending object and either deleting it or moving it further away from the end of the worksheet. Here are a few tips for finding objects:

  • To quickly move to the last cell in a row or column, press END, and then press the RIGHT ARROW key or the DOWN ARROW key.

  • To go to the last cell on the worksheet that contains data or formatting, click CTRL+END.

  • To quickly find and remove comments, press F5. In the Go To dialog box, click Special. Select Comments, and then click OK. To delete the comment, right-click the cell containing the comment, and then click Delete Comment.

  • To quickly find graphical objects, including charts and buttons, press F5. In the Go To dialog box, click Special. Select Objects, and then click OK. Once you've found the object, you can delete or move it.

Note: This issue usually occurs when you insert or hide columns. The same issue can occur for rows, although it will not be as common because of the large row limit in Excel 2007, which makes it less likely that you have positioned objects at the very end of the worksheet.

For more information and additional troubleshooting tips, see Error message when you try to hide columns in Excel: "Cannot shift objects off sheet".  

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

1 comment:

  1. Do you need Personal Finance?
    Business Cash Finance?
    Unsecured Finance
    Fast and Simple Finance?
    Quick Application Process?
    Finance. Services Rendered include,
    *Debt Consolidation Finance
    *Business Finance Services
    *Personal Finance services Help
    contact us today and get the best lending service
    personal cash business cash just email us below
    Contact Us: financialserviceoffer876@gmail.com
    call or add us on what's app +918929509036

    ReplyDelete