Fix "Cannot Shift Objects Off Sheet" When Inserting Columns

Excel shows "Cannot shift objects off sheet" when inserting a row or column would push an object, such as a shape, chart, picture, form control or note, past the last column or row of the worksheet. The fix is to make hidden objects visible, find the stray ones and delete or move them. This guide is for anyone stuck on that message while trying to add a column to a report or a job-test file.

What "cannot shift objects off sheet" means#

A worksheet has a fixed size: 16,384 columns (A to XFD) and 1,048,576 rows. When you insert a column, everything to its right moves one column over, including objects that are set to move with the cells. If any object would end up past column XFD (or past the last row, when you insert rows), Excel refuses and shows the message.

An object is anything that floats on top of the grid rather than living inside a cell:

• shapes, text boxes, arrows and pictures
• charts placed on the sheet
• notes (the yellow pop-up boxes, called comments in older Excel)
• buttons, checkboxes and other form controls

The puzzle is that you often can't see the object that is causing it. It may be hidden, tiny, or sitting thousands of columns to the right.

Fix 1: Show hidden objects#

The most common cause is that Excel has been told to hide all objects. They still exist and still block the insert, but you can't see them. To show them again on Windows:

1. Go to File > Options > Advanced.
2. Scroll to Display options for this workbook.
3. Under For objects, show, choose All.
4. Click OK and try the insert again.

On Windows, the shortcut Ctrl+6 switches between showing and hiding objects in many Excel versions, so a stray key press can cause the problem. On a Mac, look under Excel > Settings (called Preferences in older versions) > View for the matching objects option.

If the insert works now, you're done. If not, the objects are visible but in the way, so move on to Fix 2.

Fix 2: Find and delete stray objects#

Go To Special can select every object on the sheet at once:

1. Press F5 or Ctrl+G (Mac: Ctrl+G) to open Go To.
2. Click Special, choose Objects, and click OK.
3. Every object is now selected, with small handles around each one.

Look at what was selected before you delete anything. If the sheet has a chart or a logo you want to keep, don't press Delete straight away. Instead, use the Selection Pane (Home > Find & Select > Selection Pane). It lists every object by name, including hidden ones, so you can click each one, see where it is, and delete only the junk.

Common culprits are:

• dozens of tiny invisible shapes copied in from a web page or another program
• old text boxes or arrows dragged far to the right
• notes on cells near the end of the sheet

To jump to the far right edge of the sheet and look for yourself, press Ctrl+→ on an empty row (Mac: ⌘→). The guide on jumping with Ctrl and the arrow keys explains how these jumps work.

Fix 3: Stop objects moving with the cells#

Each object has a setting that decides whether it travels with the cells under it. Right-click the object, choose Format Shape (or Format Picture, or Format Chart Area), open Size & Properties, and look under Properties:

An object that moves with the cells is what gets pushed toward the edge. Setting the stray object to Don't move or size with cells, or simply dragging it back next to your data, usually lets the insert go through. Deleting it is still the cleanest fix if you don't need it.

The other error: "cannot shift nonblank cells"#

A similar message says Excel "cannot shift nonblank cells off of the worksheet". This one is about cell contents, not objects. It means something is in the last column or the last row, such as a value, a formula, or formatting applied to whole rows. Inserting would push that content off the sheet.

To fix the second one:

1. Press Ctrl+End (Mac: Control+End, or Control+fn+→ on a laptop) to jump to the last cell Excel thinks you used.
2. If that is far beyond your data, click the first empty column after your data, then press Ctrl+Shift+→ (Mac: ⌘+Shift+→) to select every column to the end.
3. Right-click and choose Delete. Do the same for empty rows with Ctrl+Shift+↓.
4. Save the file, so Excel resets where the sheet ends.

The guide to inserting and deleting rows and columns covers the right-click and shortcut options, and how to delete blank rows helps if the empty rows are inside your data rather than below it.

How to stop the error coming back#

A few habits keep the far end of the sheet clean:

• Don't format whole columns or rows unless you mean it. Select only your table.
• Paste as values when you copy from web pages, so hidden shapes don't come along.
• Check before you delete a column. Use COUNTA on it to see whether anything is still there, as in the exercise on this page.
• Hide instead of push. If you only need space on screen, hiding rows and columns moves nothing.
• Insert many at once. When you need several new columns, select that many and insert once; see how to insert multiple rows for the method.

Practise it#

Errors like this are easier to fix when you know how Excel's grid works. Practise working with cells, rows and columns in a live spreadsheet: you'll insert, delete and select ranges, and each step is checked instantly. Then try the jump-to-the-edge lesson to move to the end of your data with Ctrl and the arrow keys, the same moves you need to hunt down stray content.