Forcing Excel to fill like you want it to fill

Excel is really clever. If you're looking to use your mouse to fill something to the right, Excel will try and show you what's going to happen next. See this example, with the little "1" hanging off underneath. Mid-fill Excel is displaying what's about to arrive in your spreadsheet.

Lift off your mouse and this is what you get:

Notice what happens when I do the same with months. The "Jul" hanging out to the right shows me what's going to happen next.

It feels to me that Excel is trying to predict what it thinks I'd like to happen next - which I'm grateful for. But, although Excel is doing a good job of trying to read my mind and show me my future, it doesn't always predict exactly what I really want for myself. Sometimes I'd like it to do something different.

Forcing Excel to do what you want

There's an easy way to force Excel to behave the way you want it to.

The first exercise had us ending up with a series of 1s across our spreadsheet. This time, start to fill the number 1 across to the right. Don't lift off your mouse, and press the ctrl (control) key on your keyboard. Can you see the power you have over Excel?

When Excel is seeing that you might be filling some kind of series, you can change its behaviour by pressing the ctrl key at exactly the right time, keeping ctrl held down until you lift off your mouse.

Do you remember the default behaviour for months from above (Excel was going to see Jan and then fill it across the page Feb, Mar, Apr and so on)? Imagine you actually wanted a whole lot of Januaries across the page. Pressing the ctrl key (before you lift off your mouse) would force Excel to its alternative behaviour.

One alternative keeps the sequence flat across the page. The other sees the sequence incrementing. Pressing ctrl before you lift off your mouse allows you to switch between the two behaviours.

With or without ctrl, you have the power: try giving Excel a hint about the sequence you want

If you're used to using the mouse to fill, and Excel is not giving you the fill pattern you'd expect, you can force it to the alternative with one extra key stroke. All you have to do is press ctrl before you lift off your mouse. There is another way though. If Excel's default is to spread a line of 1s across the page. Try typing 1 and 2, select those, and then fill.

Similarly, if Excel's default behaviour is to fill months Jan, Feb, Mar, Apr across the page, you can change that behaviour by pressing ctrl before you complete you fill or (slightly less fiddly) you can type two Jans in a row, then select and fill those.

Or, mix it up. If you want quarters 1, 2, 3, 4 across the page, type 1, 2, 3, & 4 in, select the 4 entries, press ctrl and then fill.

See, now you can use your new-found mouse power to combine ctrl, plus a bit of judicious typing, to make Excel bend to your every whim when you fill. Sometimes the tricks you can play with filling (which get tacked on to my list of mighty mouse cuts), really can be pretty satisfying.

But the lesson on this one really is very simple. If you're filling to the right and you find Excel is anticipating a sequence you don't like, try pressing ctrl before you lift off your mouse. Or give Excel a strong hint by typing in the first few elements of your pattern (the Jan Jan above) and then filling. Soon you'll feel the awesome power of having Excel really yield to your every whim!