Excel cannot shift objects off sheet: how to fix it

Quick answer: Excel shows “Cannot shift objects off sheet” when you insert or hide rows or columns while objects are set to stay hidden. Microsoft names the cause as the Nothing (hide objects) display setting combined with an object, such as a cell comment, in or next to the columns you’re changing. Go to File > Options > Advanced, find For objects, show: and choose All, or press Ctrl + 6. Then insert or hide the rows or columns again. If the error stays, set the objects to Move and size with cells.

Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016 on Windows. Microsoft’s article was written for Excel 2007 and the 2000 to 2003 releases; as general guidance, the same settings sit in the same places in later desktop versions, though wording may differ slightly.

What the message means

When you insert a row or column, Excel pushes everything below or to the right one step further. Objects that float above the grid, such as notes, comments, pictures, shapes, charts and buttons, get pushed too. According to Microsoft’s support article on this error, the message (error number 100185) appears when the workbook is set to Nothing (hide objects) and an object, such as a cell comment, sits in a column you hide or next to it, or when you insert rows or columns. Microsoft says most object types are affected and to pay special notice to cell comments, which are hidden by default.

As general guidance, Excel may also refuse the change when an object already sits at or close to the last row or column of the sheet, because there is no room left to push it into. That may explain why a sheet with no visible pictures can still block a simple insert.

Match your symptom

Pick the line closest to what you see, then go to that fix.

What you seePossible causeGo to
Error on insert or hide, no objects visibleObjects set to Nothing (hide objects)Fix 1
Error comes back after showing objectsObjects fixed in place, not moving with cellsFix 2
You can’t tell which object is in the wayHidden, tiny or transparent objectsFix 3
Error even on a nearly empty sheetObjects parked at the far edge of the sheetFix 4
Dozens or hundreds of objects on the sheetToo many to change one by oneFix 5
Error when collapsing a group or filteringSame object placement problemStill blocked

Fix 1: Show all objects, then insert again

This is the fix Microsoft gives for the setting that hides objects. It changes only how the current workbook displays objects, not the objects themselves.

  1. Select File > Options. In Excel 2007, Microsoft’s steps start from the Office button, then Excel Options. As general guidance, on some screens you may need to select More first to see Options.
  2. Select Advanced and scroll to the display options for the workbook.
  3. Under For objects, show: choose All instead of Nothing (hide objects).
  4. Select OK, then insert or hide your rows or columns again.

Microsoft notes that Ctrl + 6 toggles this setting. As general guidance, the shortcut is easy to press by accident, which may be how it got turned off in the first place. Once your rows or columns are in, you can switch back to hiding objects if you prefer a clean view.

Fix 2: Let objects move and size with cells

An object set to stay put doesn’t travel with the grid. Microsoft’s article recommends giving each object in the affected columns the Move and size with cells property:

  1. Select the object by clicking its edge when the pointer changes to a four-headed arrow, then open its format dialog. Microsoft’s steps use the Format menu command named after the object. As general guidance, in current versions you may right-click and choose Format Picture, Format Shape or Format Chart Area.
  2. As general guidance, in current versions the property sits under the Size and Properties icon, in the Properties section. Microsoft’s steps for older versions use the Properties tab in the Format dialog.
  3. Choose Move and size with cells.
  4. Repeat for each object near the rows or columns you’re changing, then try again.

For a cell comment, Microsoft says to show it first with Show Comment from the cell’s right-click menu, select it by its edge, set the property, then hide it again with Hide Comment. As general guidance, in Microsoft 365 these cell comments are called notes, so the commands may read Show/Hide Note and Format Note or Format Comment. The newer threaded comments may not offer this property, so focus on notes, pictures and shapes.

Fix 3: Find objects you can’t see

A sheet can hold objects that are hidden, shrunk to a dot or have no fill and no outline. As general guidance, two tools can help you find them:

  • Selection Pane: select Home > Find & Select > Selection Pane. It lists every shape, picture and chart on the sheet, including hidden ones, with an eye icon to show or hide each. Select a name to jump to that object.
  • Go To Special: press F5, select Special, choose Objects and select OK. Excel selects every object on the sheet at once, so the Name Box and the selection handles show where they are.

If Go To Special reports that no objects were found, the cause is more likely a note or the edge of the sheet, covered in Fix 2 and Fix 4.

Fix 4: Clear objects stuck at the edge of the sheet

Copying rows from another file, pasting from the web or filling a column all the way down can leave small objects or lines far from your data. As general guidance:

  1. Press Ctrl + End to jump to the last used cell. If it lands far below or to the right of your data, the sheet is carrying more than you think.
  2. Open the Selection Pane and look for items you don’t recognize, for example rectangles, lines or text boxes with generic names.
  3. Select the stray objects and press Delete. Keep any that belong to your report, such as charts or buttons that run a macro.
  4. Save the workbook, close it and reopen it, then try the insert again.

Before deleting objects in a shared or important workbook, save a copy first. Some objects, such as form controls, may be linked to cells or macros.

Fix 5: Change every object at once with a macro

When a sheet has a large number of objects, changing each one by hand isn’t practical. Microsoft offers a sample macro that gives every shape on the active sheet the move-and-size setting. A minimal macro along the same lines looks like this:

Sub MoveAndSizeAllShapes()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        shp.Placement = xlMoveAndSize
    Next shp
End Sub

Warning: Microsoft cautions that this can cause unexpected results when rows or columns are resized, so run it on a copy of the workbook first. To use it, press Alt + F11, select Insert > Module, paste the code and press F5 with the cursor inside it. The workbook must be saved as .xlsm if you want to keep the macro.

Still blocked? Other checks

  • Grouped rows or filters: collapsing a group or applying a filter hides rows, and as general guidance it may raise the same message for the same reason. Apply Fix 1 and Fix 2, then collapse the group again. If the filter itself misbehaves, see our guide to Excel filter not working.
  • Moving whole rows: if you’re inserting rows only to move data, cut-and-insert may avoid the problem. Our guide on how to move a row in Excel walks through it.
  • Pictures placed in cells: in Microsoft 365, a picture placed in a cell, rather than over the cells, as general guidance may move with its cell. Our picture to Excel guide covers both ways of inserting one.
  • Excel freezes instead: a sheet with thousands of hidden objects may also slow Excel down. If it hangs while you work through these fixes, see Excel not responding.

Official sources

How we write our guides: read our editorial policy.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *