Updated July 6th, 2016.
— How to Save Time with Auto-Fill in Excel —
Take a look at the Excel document below. Notice anything? Of course you did– each column is a series, progressing from a starting item to an ending item in some sort of regular way.
It took me 59.4 seconds to enter this data. Really. That’s one cell of data per second– very, very, very quick, and it’s not because I’m such an amazing typist. Actually, I let Excel do most of the typing for me, using a feature called Auto Fill. All I did was type the first row of data. Excel did the rest. Here’s how it works.
1. Type something into a cell and hit Enter (or Return).
2. Click on the cell again to select it, and let go.
3. Look for a little box at the lower right-hand corner of the selected cell. Move the mouse over that until the cursor looks like a little black “+” as shown below.
4. Click the lower right-hand corner with that black plus, hold the mouse button down, and drag down (or to the right). Excel fills the cells with the right stuff. Amazing.
The four steps above will work in a lot of cases. In fact, they work for the first four columns in our example. But, when you get to the last two columns, those steps don’t work, and that’s because Excel doesn’t know whether we want to go up by one, or two, or five, or whatever. We have to tell it.
The trick is to enter not just the first item in the column, but also the second. So, in the example here, I typed a 1 and then below it a 2. Then I selected BOTH cells, via click-and-drag, and THEN I clicked the lower right-hand corner and dragged down. For the column at the far right, I entered 5 in the top row, 10 in the next row, selected both cells, then dragged that lower right-hand corner.
You’ll know you’re clicking in the right place when the cursor looks like the black “+”.
Next time that you have a lot of data to enter into Excel look for the chance to use this Auto Fill method. It will save you a LOT of time.
Bonus: if you type “Jan” Excel will enter “Feb” and “Mar” etc. If you type “1st Quarter” Excel will enter “2nd Quarter” and “3rd Quarter” etc.
Bonus 2: you can select more than one cell in the top row, then grab the lower right-hand corner of the selection, and then Auto Fill all of those columns at once. Try it.