Prevent Excel from pasting a cell across an entire row

Question

Excel has a behavior that I guess has been purposefully designed but which has made me lose quite a lot of time over the years. This behavior happens after the following steps:

• Copy a finite range of cells (Ctrl + C)
• Select a line or a column (Shift + Space or Control + Space)
• Insert new (Ctrl + "+")

I do this a lot, always involuntarily, when trying to insert a new row or a new cell before pasting my cell or range or cell.

When this happens, the cell or range is inserted not once but across the entire line or column, e.g. if I have selected a 2x2 range and insert in on one row, it will be repeated until the end of the row.

If I copy then insert this range:

It then repeats it across an entire row (until column XFD!):

This is very annoying because it sometimes take quite a bit of processing power to process the entire row(s) of pasted stuff, especially when it contains formulas; and it takes even more time to Ctrl + Z for some reason (cancelling the insertion of an entire row of pasted formulas often makes the computer freeze for a few seconds, and has even, on some occasions, caused Excel to crash altogether). There have been literally zero occasion of which I have triggered this behavior purposefully.

Of course, one solution would be to never insert something that you don't want to insert (duh) but that is actually not that easy and I know some of my colleagues sometimes make the mistake as well.

Is there a way to disable this behavior?

Answer 1

Unfortunately, as you are probably aware, this is a design feature of Excel, not a bug. If you highlight a whole column or row, then paste anything, it will (and should) fill all the cells. If you want to copy a 2x2 range and only want to paste into a similar 2x2 grid, you should not highlight the entire row or column.

I have 3 suggestions.

1. You can add a new command to your home Ribbon by going to File -> Options -> Customize Ribbon, in the list box on the right called "Main Tabs", select "Home", then click "New Group". Add "Insert Sheet Columns" and "Insert Sheet Rows" to the group. These options are different than the standard "Insert..." options, because those still default to "Insert copied cells". Now I can insert a new row or column with the keyboard shortcut ALT,h,y for column insert or ALT,h,r for row insert, even with cells copied to my clipboard. Just to clarify, the keyboard shortcuts are individual key presses, not held down combinations, which commas separating the keys to press...don't press the comma key. The last key of your key combinations might be different than mine, but if you press ALT,h Excel will put a prompt on the ribbon for the next key for you to press, similar to the following screenshot.

2. Use your mouse, right click the row or column where you want to insert a BLANK row or column, and click "Insert", not "Insert Copied". When you have cells copied to your clipboard, "Insert Copied" is the default, which is why you are experiencing the problem. This cannot be changed to my knowledge.

3. Use your mouse to use the "Insert..." tool in the "Cells" group on the Home tab. Select "Insert Sheet Rows" or "Insert Sheet Columns".

What you are your colleagues are experiencing sounds a lot like a, for lack of a better term, "bad habit" that any user can develop over time. It will take time for you and your colleagues to break this habit, but hopefully, over time it will improve.

Answer 2

It does look like a dumb design decision to me, especially since there's no way to insert blank rows at all via the context menu while there's something in the clipboard.

This answer may not be as thorough as Michael's, but one little change to your habit could make a big difference. Assuming there's nothing else to the left or right of the data you're copying, you should select the rows rather than the cell range before copying. When you paste that, the inserted rows will look identical to the ones you copied; nothing will be auto-filled across the empty cells.