5

I'm using Office 2013 on Windows 7. Few times, I have not been able to insert rows / columns in a some of the Excel spreadsheets. I tried the following before searching on the internet.

  1. Use right-click on the column border to select "Insert" option - the option is not grayed out - but it does no action - no rows/columns inserted
  2. Click on the "Insert" icon (which is under the "Cells" group of the "Home" tab) - select the "Insert sheet rows / Insert sheet columns" option from the sub-menu - nothing happens
  3. Tried using a keyboard shortcut Ctrl+Shift++ to add rows / columns after selecting a particular column or row - no rows/columns inserted.

When I googled, I found lot of search results pointing to the error What does 'To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet" mean? But I'm not getting any such error message when I attempt the insertion of row / column.
There are no patterns of files for which this row/column insert operation fails. Sometimes, it happens to a file on a network server or sometimes to the files on my PC.

The present workaround is to restart Excel and most times it allows row/column insertion on the same file. Has anyone come across similar issues? Any fixes found so far?

6
  • Sounds like you need to reinstall office. – Raystafarian Nov 10 '14 at 19:34
  • Are these worksheets protected? – Mr. Mascaro Nov 10 '14 at 20:04
  • @jbarker2160 - the worksheets are not protected. They are normal worksheets – Prasanna Nov 11 '14 at 3:18
  • 1
    @Raystafarian - I'm wondering how much re-installing Office 2013 would help. Because there seems to be lot of issues with this version of Office. See my other question.... superuser.com/questions/784661/… – Prasanna Nov 11 '14 at 3:20
  • Try Compatibility mode? I'm using Office 2010 so I don't know about version 2013 but already in 2010 there are a lot of terrible bugs that make it totally unusable for me in Word and Excel. I only use Powerpoint in non-compatibility mode but there are scrolling bugs there which can't be fixed at all. – user21820 Jan 27 '15 at 12:15
4

I am using Office 2013 Pro Plus and I have also lost my "Insert" option and even "Cut" option along with that. To overcome that, go to following file path:

C:\Users\(user ID)\AppData\Roaming\Microsoft\Excel\Excel15.xlb

rename Excel15.xlb file to Excel15.db.

P.S : Keep Excel off while change.

4
  • 3
    What does this do? Does it affect any other feature when when rename this file? – Prasanna Aug 31 '15 at 8:42
  • Two people noted in now deleted answers that this did indeed work. But indeed, it would be really nice to know why. – Arjan Sep 14 '15 at 13:00
  • 1
    I'd love to try this but I've been disallowed access to that directory. Any other workarounds? – Alexx Jan 28 '16 at 15:06
  • This worked! but how and why – InCh May 14 '19 at 14:05
2

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).

2
  • Please read the question again carefully. Your answer does not answer the original question. The question is for Excel 2013 not Excel 2007 as referred to in your answer. – DavidPostill Aug 31 '15 at 8:29
  • It fixed it for me using 2016... – ComfortablyNumb Jun 14 '16 at 8:51
0

MS Excel -2013 - Adding a Column or Row - Problem

  • File-> Options
  • Click on 'Customize Ribbon'
  • Click 'New Tab', Rename if you want, Say 'X'. Click 'Insert Sheet Columns'. This action will add this to 'New Tab' created.
  • Click 'Insert Sheet Rows'. This action will add this to 'New Tab' created.
  • Click 'Close'.
  • Select any Column.
  • You will see a new tab, 'X'.
  • Click on 'Insert Sheet Columns', from the new tab 'X'.

This will Add a Ribbon on the top to help you in inserting a row or column, alternate to Right click to add a row or column. Not fixing the issue. Just a workaround.

2
  • Amit, I do not want to have a new tab for for inserting rows and columns. The normal right click insert row or insert column is not working sometimes – Prasanna Aug 20 '15 at 8:09
  • This worked for me, I inherited a sheet that I couldn't insert rows into and this resolved that. – davidjwest Jun 25 '19 at 10:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.