|
AutoFill is nothing new. If you've used Excel more than a couple of times, you know the basics. My goal is to show you some features you maybe don't know.
First, here's the basic concept:
1. Type Jan or January or 6/10/2006 or 8:00 PM in a cell.
2. Click and drag the little square Autofill handle (located in the bottom right corner of the cell) either down the column or across the row.
Excel will fill the adjacent cells with Feb or February or 7/10/2006 or 9:00 PM.
A little more control
Want January, March, May or 8:30 AM, 8:45 AM, 9:00 AM? You'll need to show Excel a pattern.
This time type values in the first two cells, select both cells, and then drag the single AutoFill handle.
1, 2, 3 instead of 1, 1, 1
Another simple concept. Dates and times increment when you AutoFill them (Jan, Feb, Mar.); text and numbers copy (5,5,5.). If you want the opposite (Jan, Jan, Jan. or 5,6,7.), Ctrl + drag the AutoFill handle.
Inserting and deleting cells
This time, select a blank cell on any side of the data (top, left, etc.), then Shift + drag the blank cell's AutoFill handle over the data. You'll quickly insert if you dragged right or down, or delete cells if you dragged up or left.
This is a great trick when you want to move a large section of data without having to select the whole thing.)
Right-dragging
When you drag an AutoFill handle with the right mouse button, then let go, you'll get one of Excel's 5,000 pop-up menus. Fill Weekdays is a particularly useful feature, giving a range of dates without weekends.
Custom Lists
North, South, East, West. This is one of the few Custom Lists I've created. Whether your typical list is cats, dogs, turtles and bears or Mon, Wed, Thurs, Sat, you'll need a custom list. It's easy.
1. Type North in a blank cell.
2. Type South in the cell below it or next to it.
3. Type East in the 3rd cell, then type West in the 4th.
4. Select all 4 cells, then select Tools/Options/Custom Lists.
5. Click the Import button at the bottom, then click OK.
6. Type North in one cell, then drag the AutoFill handle. Excel will fill in the adjacent cells with South, etc.
If you've got more than one custom list on a worksheet, select the first list, Ctrl + drag each additional list, then select Tools/Options/Custom Lists. When you click the Import button at the bottom, Excel will create a new custom list for each range you selected. Hard to explain, but easy to do.
Formulas
I won't begin to cover formulas in this little article but here's the basic concept behind AutoFilling formulas. If you have a formula that sums a column (or row) of numbers, then AutoFill the formula down (or across), Excel will sum the remaining rows by adjusting the formulas appropriately.
Formatting cells
Unfortunately, the most unknown use of AutoFill could save you the most time. When I learned this trick, I couldn't believe how much time I had wasted formatting cells. If you've got a formatting pattern (and who doesn't), Excel can repeat it.
Confused? Try this.
First, create a quick table. For example, just type a 1 in cell A1, drag the AutoFill handle across to cell F1, then drag the AutoFill handle down the page to cell F10. You'll create a table of 60 number 1's.
We want to format alternate rows, but I'm too lazy to format 5 rows one at a time.
1. Highlight the first row of numbers.
2. Format! For example, change the font, the fill colour, the border style and the number format.
3. Select the formatted row 1 and the unformatted row 2, then right-drag the AutoFill handle down to row 10. Select Fill Formats.
Basically, as long as there is a pattern, whether it's time, date, formatting or formulas, let AutoFill do the work for you.
| 
Tips
