There are some things that we do intrinsically. And there are some that grate.
When left-clicking your mouse, you're happy to drag. You might want to select a block of cells, highlight a range of text or group select a bunch of objects.
When right-clicking your mouse however, you never drag. It's not the done thing. Instead, you expect a little pop-up menu to appear inviting you to do something to the thing that you've selected.
But there is one specific time when right-clicking can be accompanied with a drag to achieve some wonderful results. But here's a word of warning: it will feel as odd as putting your socks on in the opposite order. (With respect to socks, try it. Go on.)
Type "January 2012" in cell A1. Now hover over the bottom-right corner of that cell and your cursor will turn into what looks like a plus sign.
If you left-click and drag all the way down to A12, then the column will auto-populate with February 2012, March 2012 […] December 2012. Rather neat. Excel does the same with days of the week and dates.
Now instead try doing exactly the same action, but instead of holding down the left mouse button, hold down the right one. It'll feel very odd, as likelihood is you'll never have dragged in that way before.
Drag the corner of cell A1 all the way down to A12. Let go of the right-click and you'll be presented with a pop-up menu.
You're given the option of doing the following tasks:
- Copy Cells: This will simply copy cell A1 down the column. (You probably didn't want to do this. If you did, then CTRL+C, CTRL+V would work just as well.)
- Fill Series: This will replicate what would have happened had you left-click-dragged instead. It will use its intelligence to auto-fill the series.
- Fill Formatting Only: This acts the same as Format Painter. (If you wanted to do this, you'd probably choose to use Format Painter instead, right?)
But then it gets interesting. You're presented with some options that are simply delightful.
- Fill Days: This will fill with sequential days. So if A1 contained 1 January 2012, it will fill through to 12 January 2012
- Fill Weekdays. Oh hello! This will skip weekend days. 2, 3, 4, 5, 6, 9, 10, 11, 12 13, 16 January. Neat.
- Fill Months: 1 January 2012 in A1 will fill with the first of each month thereafter. Cleverly, 31 January 2012 will fill with the last calendar day of each month. Beautiful.
- Fill Years. You get the picture.
If these options aren't sufficiently varied, then hit "Series…".
This gives you a range of other options, the most useful of which is the step value. If you change this to -1, it will do everything backwards. So you can make it fill with the last day of each calendar month, but going backwards in time. Or choose a step value of 2 to show every other weekday.
The function doesn't only work for dates. You can also use it for numeric series.
So go on. Have a play. And try to get used to right-clicking while dragging.