1

An Excel 2016 workbook on my Dell desktop will not allow me to insert sheet rows on one worksheet, but it will allow me to on the others.

I have tried the add Tab solution: it did not respond, the new tab is grey with no options to insert a row.

I have tried the Ctrl + shift + down arrow key to highlight all rows below and remover extra formatting, but again no result.

When you right click to insert a row, the option is grey. the sheet is not locked for editing by any user. There is no error message, it's like the function was never applicable.

Can anyone help me?

1
  • Can you insert column? Did you get any error messages?
    – Lee
    Oct 24, 2018 at 9:54

2 Answers 2

1

Delete a few rows/columns For me, I had something similar to https://answers.microsoft.com/en-us/msoffice/forum/all/help-cant-insert-rowscolumns-in-excel-2016/da98bb00-85b7-4f09-becc-422812830798 I selected a few columns to the right of the data I wanted, and deleted them, and then was able to insert columns again. For some people save+close the file, and open it again works, not sure why. (Office 365)

0

The most likely problem, judging from a number of similar questions and their answers, revolves around Tables.

There is a lack of exactness in most of the variables in the question, for instance, in the exact error message, so one cannot really differentiate between the Table related problem and the more "usual" (as in "been here for decades" types of problems).

The basic idea is this: if Tables "overlap" (use the same rows or columns) then you cannot insert rows where rows are used by both Tables, nor columns where columns are used by both Tables. So if you have a Table using rows 1-10,048 and a Table using rows 1-6,509, then you cannot insert rows in the 6,509 rows used by both Tables. Given that example, it would be likely you only tried in that set of rows and so did not notice you could have inserted a row between, say, rows 8,234 and 8,235. Same idea for columns.

Several ways to get around it. One is to change one or both (or all 50, if you have 50 of them overlapping the rows you want to insert between), to a Named Range, do the insertions needed, then make it (or them) back into Tables. This is a routine, though unhappy operation because you must do it to a Table if you need to change a formula for going forward rows of data, but keep the old one/s above that point. So it is not dangerous, nor particularly hard. And often suggested.

What it misses though, is that the other Table/s involved get the same insertions... and maybe you reallllly don't want that...

Another solution would be (using columns this time) to insert some cells to push the offending second Table over, insert your new column/s, then remove the added cells PLUS those added by inserting the column/s thereby restoring the positional relationship of the two (or more) Tables. Tedious, but pretty simple, though harder if there are thousands of rows to highlight.

Better than either would be to set up the Tables like we used to set up spreadsheets when you did not have multiple pages. ONE page. You worked on a diagonal, if you will, setting up the first region in, say, cells A1-D10, then leaving a row AND column empty, and setting up the next region in, say, cells F12-H150. Incredible amounts of empty space (my first spreadsheet program wasted as many as 600 rows that way when upgraded memory, 600 wasted rows, imagine that...), but you could ALWAYS insert rows or columns at will because no region was either above/below or right/left of any other regions. You could do this with Tables and never encounter this issue. Of course, its weakness is maybe you want the Tables near particular other Tables, etc. and this arrangement would not work for you.

And, of course, perhaps not having more than one Table per sheet might do it for you. You will find other solutions too, but probably for exact problems, not the general one, and so not necessarily useful to you. (Today... reading such often helps you another day, eh?)

Other issues, not Table related, include the very last row of the spreadsheet having a cell with data in the very last row or column so no rows or columns can be inserted whether YOU like it or not. (NOT by accident: jerk spreadsheet makers DO try to keep you from adding rows or columns (a cell in the last column has something in it in that case) but the error message is a bit different than the Table related error message.)

And you can accidentally find your way there and accidentally place some data in a cell without realizing it. Or it might have been on purpose though with a million rows, people don't copy a formula into a whole column so much anymore.

If you have objects and comments on your sheet, one's anchor might have been shifted by other things you did and now be anchored to the last row (or column). Think, rolling off the right of a screen and coming back in on the left side, like an old arcade game. You will need to find such and delete or re-anchor them. And... hard part... they often STILL appear where you put them... only their anchor is different, so you don't go to the last row and look and yay, find them always. They're hiding by LOOKING perfect when they're not.

Search a few of the other related questions. One had an answer that used VBA code in the "immediate window" - super easy! Really, super easy! Just copy and paste his code, he tells you how to make it happen and it's simple, and the immediate window can only take one line of code per effort, so there's no difficulty at all. I promise.

But it truly sounds like you have a Table related issue. I just can't tell from the information given as each bit of it is sort of only 50-75% there, so to speak. (For example, 100% there is "I tried inserting after row 43 and got this copied and pasted error message" rather than "I tried to insert a row and got an error" - not trying to be harsh about it, just, it helps YOU more to include more and be a little precise and since your goal is helping YOU, it seems like something to value, right?

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .