Today's #TipTuesday post is a little Excel tip. It's one of those things that I assume most people know, and then I work with someone and watch them use Excel and end up teaching it to someone. I call it "corner copy" but I'm sure that is not the 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 the user 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 I want to copy values down a column, there are several ways to achieve this.
- Copy the cell, highlight where I want it to go, and then click Paste to paste the values or formulas to the other cells.
- Grab the corner of the cell I want to copy and drag it downwards across the cells I want to copy it to. I don't want to go too fast though, I'll end up way beyond the last row where I 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 I want to copy and double-click on it. If I have 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 me here. Here is what be displayed with dates, numbers and similar data types:
… But watch out for…
At the bottom of the visible screen (not the end where the data was copied to). users will find a little magic button with options. This doesn't stay visible for long so don't make the mistake of scrolling down too far or it might disappear. Trust me, once I see the data is in series and should have been Copy Cells (or vice versa), I click on this little button first before doing anything else.
Above is an example of some options that might appear. The options will differ depending on the type of data being worked with. In my case, it guessed I wanted to see a series of data but I wanted to Copy Cells instead so I selected 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 Formatting to just copy the formulas down, for instance.
All in all I love this as it's so much faster than 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 I'm often copying where I 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.
