Lỗi excel cannot shift blank cells off worksheet
At times, when trying to insert rows or columns in a worksheet, it may fail, and Excel will display an error message stating, “Can’t push objects off the sheet,” as shown below: Show This error typically arises when graphic elements like maps, embedded charts, text boxes, and shapes are located toward the right or bottom edge of the Excel grid (close to the end of the rows and columns in the worksheet). Note: The error may also occur in older versions of Excel (2007, 2010, 2013) if the option “Nothing (hide objects)” is selected on the Excel Options dialog box in the “For objects, show” section. Dimensions of an Excel WorksheetIn Excel, a worksheet can have a maximum of 1,048,576 rows and a maximum of 16,384 columns labeled alphabetically from A to XFD. Excel cannot allow the placement of objects outside this range. You are highly likely to encounter the “Can’t push objects off the sheet” error when graphic elements are present near the XFD column or row number 1,048,576. This tutorial shows seven ways of fixing the “Can’t push objects off the sheet” error in Excel. How to Fix the “Can’t push objects off the sheet” Error Caused by Objects Near the Edges of the Excel GridSuppose we have the following dataset: The dataset is on a worksheet that has a rectangle shape in the vicinity of column XFD: Notice that there is one column between the rectangle object’s right edge and the Excel grid’s right edge. The worksheet also has a text box near row number 1,048,576: Notice there is one row between the text box’s bottom edge and the Excel grid’s bottom edge. If we try to insert two columns on the dataset, it fails, and Excel displays the “Can’t push objects off the sheet” error: If we also try to insert two rows, Excel returns the error, “Can’t push objects off the sheet,” as shown below: Note: We can only insert as many rows as there are between an object’s bottom edge and the Excel grid’s bottom edge. The same goes for columns – we can only insert as many columns as there are between the right edge of an object and the right edge of the Excel grid. We encountered an error while trying to insert two rows and two columns because the space between the bottom edge of the text box and the bottom edge of the Excel grid can accommodate only one row, and the space between the right edge of the rectangle and the right edge of the Excel grid can hold only one column. We can fix the error using the following methods: Method1: Delete Blank Rows or Columns Equal to the Number of Rows or Columns to Be InsertedWe can delete blank rows or columns equal to the number of rows or columns we want to insert. To delete, for example, two blank columns, do the following:
Note: Similarly, to delete rows, click the numeric header of the first row, drag to the header of the next row, right-click the selection, and press “Delete” on the context menu. After deleting the columns or rows, we can insert two columns or two rows in our dataset, and Excel will not return the “Can’t push objects off the sheet” error. Also read: Can’t Insert a Row in Excel – How to Fix! Method2: Resize the ObjectsWe can make the objects smaller by resizing them to make room for the columns or rows we want to insert using the steps below:
Also read: How to Rotate Text in Excel? Method3: Move the Objects Away From the Edges of the WorksheetWe can move the offending objects further away from the edges of the worksheet to make room for the rows or columns we want to insert. We use the steps below:
Method5: Delete the Offending ObjectsIf you do not require the objects causing the error, you can delete them. To delete the rectangle object, use the keyboard shortcut “Ctrl + Right Arrow” to navigate to the last column. Once you have located the rectangle, select it by clicking it and press the “Delete” key to remove it. To delete the text box, use the shortcut “Ctrl + Bottom Arrow” to navigate to the last row. Select the text box by clicking it and press the “Delete” key to remove it. Note: Sometimes, the objects may not be visible because the option “Nothing (hide objects)” is selected on the “Excel Options” dialog box in the “For objects, show” section. You can press the shortcut “Ctrl + 6” to make the objects visible and then delete them. If you want to delete all the objects on the worksheet at once, do the following:
Method6: Move the Objects to a Different WorksheetIf the objects are not critical to the current worksheet, you can move them to a different worksheet within the workbook where more space is available. You can move objects using the following steps:
Alternatively, you can use the shortcut “Ctrl + X” to cut the selected object.
The object is removed from the source worksheet and pasted into the new worksheet. Also read: Excel Hyperlink Cannot Open the Specified File – Fix! How to Fix the “Can’t push objects off the sheet” Error Caused by the “Nothing (hide objects)” OptionIn case you’re working with older Excel versions (2007, 2010, 2013), you may encounter the “Cannot shift objects off worksheet” error message while trying to insert rows or columns into a worksheet even when you do not have objects near the edges of the Excel grid. The error happens when you have the “Nothing (hide objects)” option selected under the “For objects, show” section in the “Excel Options” dialog box. This issue is a known bug in the older versions of Excel. While the “Nothing (hide objects)” option is not enabled by default, someone could accidentally activate it by pressing the keyboard shortcut “Ctrl + 6.” To work around the bug, you need to adjust the setting from “Nothing (hide objects)” to “All.” The quickest way is to press “Ctrl + 6.” Alternatively, you can change the setting in the “Excel Options” dialog box using the following steps:
This tutorial showed seven techniques for fixing the “Can’t push objects off the sheet” error in Excel. We hope you found the tutorial helpful. |