WTF is "Cannot shift objects off sheet", Excel?

posted Apr 11, 2016, 9:07 AM by Ezra Kenigsberg   [ updated Apr 11, 2016, 9:14 AM ]
Q: "Why does Excel give me a 'Cannot shift objects off sheet' error when I try to hide columns?"

A: This error is usually caused by Comment boxes.

Short explanation: Excel keeps track of where Comment boxes are located on the worksheet, and if the Comments are floating above columns I try to hide, it'll return that cryptic error.

Long explanation: Excel displays a given Comment in two different places:
  1. When I hover my mouse over a cell, I typically* get a read-only popup of the comment:
  2. When I right-click a cell and select "Edit Comment...", I get an editable comment box:
The popup always appears right next to the cell. BUT the editable comment box can end up somewhere entirely different:
Comment box placement gets screwed up when I do typical spreadsheety things like moving, inserting, and deleting cells.

Q: "How do I fix screwed-up Comment box placement?"

A: Two ways, depending on whether you mind messing with code.
  1. If you don't want to mess with code:
    1. Open the "File | Options" dialog
    2. Click "Advanced" in the left-hand pane and scroll down to the "Display" section
    3. Activate "Comments and Indicators", then click OK:
    4. Move the Comment boxes as needed.
  2. If you don't mind code:
Comments