Why do I see a "Cannot shift objects off sheet" message in Excel 2007?

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

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

Symptom

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 the Excel Options dialog box. This is a known bug in Microsoft Office Excel 2007. 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 the Excel Options dialog box. To do so, click the Microsoft Office Button , and then click Excel Options. On the Advanced tab, scroll to Display options for this workbook settings. Under For objects, show, select All instead of Nothing (hide objects). 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.

Symptom

Why it happens, and what to do about it

You can't insert or hide columns

If the "Cannot shift objects off worksheet" message (or a similarly worded message) continues to appear after trying the solution described earlier in this article, 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".

We make continual improvements to Office Online based on feedback we get from customers like you. If this article solved your problem, please let us know by clicking the Was this information helpful link at the bottom of this article. If we didn't solve your problem, we want to know that, too. (The more detailed you can be about your issue, the better.)