Here, for once, is some Microsoft Intelli-sense that actually does make some sense because it is a real timesaver.
With AutoFill, you can fill out a sequential series of entries (such as months of the year or days of the week) across a row or up or down a column simply by entering the first entry in the series and then dragging the cell pointer by its Fill handle - the tiny black square that appears in the lower-right corner of the active cell. In addition, you can have Excel increment the entries in the series by values other than just one (be it one day, one month, or one number). And as if that isn't enough, Excel also enables you to create your own custom fill lists that tell Excel how to fill in a list whenever you enter one of its unique members and then go for the Fill handle.
All three of these procedures are covered in this tutorial on AutoFill, which is undoubtedly one of my favorite Excel features. You also use this same AutoFill feature to extend formulas in the tables in your spreadsheet. When using AutoFill to copy formulas, the feature naturally adjusts its cell references instead of generating a sequential series.

Getting Your Fill of AutoFill

Straight out of the box, AutoFill is about as versatile and easy to use as any Excel feature can be. Table below shows the kinds of initial entries you can make - with examples of the kind of series that they generate - when you drag the Fill handle across or down new cells. As you can see from the examples shown in this table, Excel can create a fairly wide array of sequential date and time series as well as some highly individual ones (for item and code numbers), all of which don't require you to customize the feature whatsoever.
Samples of Series you can Generate with AutoFill
Initial ValueSeries Created by AutoFill in the Next Three Cells
SeptemberOctober, November, December
SepOct, Nov, Dec
MondayTuesday, Wednesday, Thursday
FriSat, Sun, Mon
3/13/19993/14/1999, 3/15/1999, 3/16/1999
4-Jan5-Jan, 6-Jan, 7-Jan
Dec-04Jan-05, Feb-05, Mar-05
8:00 AM9:00 AM, 10:00 AM, 11:00 AM
11:0312:03, 13:03, 14:03
Quarter 1Quarter 2, Quarter 3, Quarter 4
Qtr 4Qtr 1, Qtr 2, Qtr 3
Q2Q3, Q4, Q1
40 Mill Road41 Mill Road, 42 Mill Road, 43 Mill Road
'00945'00946, '00947, '00948
L17-800L17-801, L17-802, L17-803
When you drag the Fill handle over the next cell, you can tell right away what kind of sequential series your initial entry will generate. The ToolTip trailing the mouse pointer shows you right away the next item in the sequence that Excel is creating.
The AutoFill Options button gives you access to menu options that enable you to convert a series in the fill range into copies of the initial value (and vice versa to convert copies into a filled series) as well as to fill the range with formatting only or to fill the series without copying the formatting of the initial cell. To suppress the display of this button, deselect the Show Paste Options check box on the Edit tab of the Options dialog box (Tools → Options).

Using AutoFill to Generate Sequentially Numbered Series

As you may have noticed in the last two examples in previous section Table, in initial entries that mix numbers and text - as in 40 Mill Road - or entries whose numbers are entered as labels rather than values - as in '00945 or L17-800 - Excel is really good at identifying what number to increment.
As a result, you'd probably expect Excel to be an ace when it comes to generating a sequentially numbered series such as 1, 2, 3, 4, and the like (useful in numbering your data list records so that you can refer to them and sort them by record number). Unfortunately, AutoFill falls flat on its face (can a software feature have a face?) when it comes to doing this. To prove it, all you have to do is enter the number 1 in any blank cell and then drag the Fill handle over just a few blank cells in columns to the right or below to prove this point: Instead of generating the simplest of sequentially numbered series (1, 2, 3, 4 . . .), Excel just stupidly copies the number 1 to all the cells you drag through.
Fortunately, Excel does provide a way to force AutoFill (however reluctantly) to create a sequential series from an initial value rather than just copying it everyplace you drag the Fill handle. The only problem is that it requires the use of the Ctrl key, which is used in other mouse operations (cell drag-anddrop, for instance) to switch to making a copy of the selected cells or objects. In this case, however, depressing the Ctrl key as you drag the Fill handle prevents Excel from copying the number and forces it to generate a true sequentially numbered series.
If you, like me, routinely forget to hold down the Ctrl key and therefore end up with the same number copied into a range of cells, select Fill Series on the pop-up menu attached to the AutoFill Options button (which automatically appears on the cell pointer's Fill handle as soon as you release the mouse button) to convert the copies into a bona-fide numeric sequence.

Copying an entry instead of filling in a series

You can also use the Ctrl key to force Excel to copy an initial entry in the cell range you drag through in those situations where Excel would otherwise use the entry as the starting point for generating a sequential series. Here, the association of the Ctrl key with copying in drag-and-drop operations makes perfect sense (reinforced by the appearance of a tiny plus sign that appears above and to the right of the black cross mouse pointer).
You don't have to press the Ctrl key before you drag the range of cells. You can convert a filled range into copies of the initial cell value by pressing Ctrl after you've selected the fill range as long as it's before you release the mouse button. After that, you have to click the AutoFill Options button and select Copy Cells on its pop-up menu to make this conversion.

Incrementally Speaking

As you may have noticed, whenever Excel generates a series from an initial entry, it automatically increases the series by an increment of one (be it by one day, one month, one hour, one minute, one widget, you name it). Fortunately, the AutoFill feature is very teachable so that you aren't stuck with always generating a series that increases the base value by one. As long as you provide at least two entries that exemplify how many units Excel is to increase (or decrease) the entries in the series, you can generate series with almost any kind of increments.
Well, what about a sequential series that decreases rather than increases (in other words, one that uses a negative increment)? To generate this kind of series with AutoFill, all you have to do is enter the larger value in the first cell of the range to be filled, the smaller value (the one that exemplifies the negative increment) in the next cell (either in the cell in the column to the right if you want to generate the series across the row or in the row below if you want to generate it down the column). Select both cells - the one with the entry showing the starting value and the next one showing the amount of decrease - and then drag the Fill handle to extend the series as far as you want to go.
For example, suppose I want to create a numerical series going down column A - starting in cell A2 with a value of 1000 - that decreases by 100 units in each cell below. To generate this decreasing series, you enter 1000 in cell A2 and 900 in cell A3. Then select the range A2:A3 before you drag the Fill handle down the rows of the column. Excel then enters 800 in cell A4, 700 in cell A5, 600 in cell A6, and so on. (Note that when you select the cells that demonstrate the increment to use, Excel knows right away that you want to generate a series, even when your entries are purely numerical.)

Fill Lists Made to Order

Excel is quite capable of generating a new series on its own when the initial entry in the series (or first two entries, when using an increment besides one) contains a numerical component that it can increase or decrease. However, to have the program generate a series containing no numerical elements (such as the days of the week: Monday, Tuesday, Wednesday, and so on), Excel needs to know beforehand all the items in the series and their order.
To do this, you create a custom list. When creating a custom list, you can have Excel generate it from a cell range in a worksheet that already contains the items, or you can enter the items manually. I suggest that you have Excel create a new list from existing entries in the spreadsheet whenever possible. That way, you have a chance to proof the entries in the worksheet before you turn them into a custom list.
To create a custom list from existing worksheet entries, follow these steps:
  1. Enter the entries for the custom list in their proper sequence down a column or across a row of the active worksheet.
  2. Select the cell range containing the entries for the custom list.
  3. Choose Tools → Options to open the Options dialog box and then select the Custom Lists tab.
    When you open the Custom Lists tab after selecting the cell range with these entries, this cell range (in absolute values) appears in the Import List from Cells text box. If this range is not correct or you forgot to select it before opening the Options dialog box, click in this text box and then select the range in the worksheet. (Excel automatically collapses the Options dialog box as you drag through the cells.)
  4. Click the Import button.
    As soon as you click Import, Excel adds the list to the Custom Lists pane on the left and displays all its entries in the List Entries pane on the right. You can then edit the individual entries in the List Entries box if necessary. (This is where you would type the entries for a new list that you create from scratch before clicking the Add button.)
  5. Click OK to close the Options dialog box.
After creating a custom list, you can then generate the list across any row or up or down any column simply by entering the initial entry in a cell and then dragging the Fill handle in one of the four directions.

Post a Comment

Blogger Tips and TricksLatest Tips And TricksBlogger Tricks


Cloud Power For You

Website Hosting At Low Price


Empire Views