Excel tip – “corner” copy

Today’s #TipTuesday post is a little Excel tip. It’s one of those things that I assume most people know it, and then I work wth someone and watch them use Excel and end up teaching it to someone. I call it corner copy but I’m sure there’s some official name!

There are 2 parts to this tip really: one is the copy via double clicking on the lower right corner and the bigger tip is the little pop-up window that gives you options on how that command is executed. That little pop-up window is the *gem* of this tip in my opinion as it appears in numerous instances of doing things in Excel.

What is it?

If you want to copy values down a column, there are several ways to achieve this.

• Copy the cell, highlight where you want it to go, then click Paste to paste the values or formulas to the other cells.
• Grab the corner of the cell you want to copy and drag it downwards across the cells you want to copy it to. Don’t go too fast though, you’ll end up way beyond the last row where you want to copy! (Not that I’ve done that before. Nope, never. LOL).
• My favourite, hover over the lower right hand corner of the cell you want to copy and double click on it. If you had values in adjacent cells (the key to this), it’s *magic*. It copies the values or formula down and stops at the first blank in the adjacent cells.

How does it work?

Here’s an example. I have a value I want to *copy* to other rows in this column. I hover over the bottom right corner until the cursor changes to a different plus sign icon (+). Now double click. Because I have adjacent rows on the left side, it copies down until there is a blank in my data. It’s wonderful!

However, I’m copying a number and it thinks it’s a series, not a copy function. What the heck? Don’t freak out, there’s a magic button that will help you here. Here is what you might find with dates, numbers and similar data types if you do this:

… But watch out for…

At the bottom of the curren *screen* (not the end where the data was copied to). you will find a little magic button with options. This doesn’t stay visible for long so if you make the mistake of scrolling down to see that the copy went all the way to the bottom of your dataset, you will be out of luck here. Trust me, once you see the data is in series and should be copy (or vice versa), click on this little button first.

Above is an example of what you might see. The options will differ depending on the type of data you’re working with. In my case, it guessed I wanted to see a Series of data but I wanted a Copy instead so I simply need to select Copy Cells and all of the values it created are now a copy.

The other useful features in this pop-up are the Fill with and without formatting. I use Fill Without Formatting surprisingly often. It seems I work a lot with tables where rows are formatted with special shading, borders or other features so a copy overwrites those. In those cases, I can’t pick both options so if the “copy” part did what I want, I can use Fill Without Formattng to just copy the formulas down, for instance.

All in all I love this as it’s so much faster that the alternative options. Most times I find I am working with data that is in contiguous cells therefore the “adjacent cells” rule works and the copy is successful. If you’re often copying where you have blank rows, this won’t be as successful.

Other useful info

As I mentioned above, it copies down to adjacent rows until it finds no data. I have found inconsistent behaviour at times when there is data in the column already and to be completely honest, I’m not sure what the proper default behaviour should be.

Example: I’m copying from row 5 in column C. I have data in that column in row 20. Sometimes when I do the double click it will copy and stop at row 19, recognizing there’s data in row 20. Sometimes it copies right over it. I’m sure the answer is in some cases I’ve had cell values and in others it might have been a formula, I’m not sure. Test on your own data to be sure you are getting the results you expect!