Extending a Series and Creating a Custom List in Microsoft Excel 2003

Extending a Series and Creating a Custom List in Microsoft Excel 2003


Microsoft Excel allows you to fill a range of cells by dragging on the fill handle (the small black square in the bottom right corner of a selected cell or cells). Where a cell contains a formula, the formula is copied to the cells as the handle is dragged across them. For each new cell the formula is automatically amended to reflect its different row or column. Where the cell contains a value, the value is copied across the range unless it is one in a pre-defined series.

This document introduces you to these pre-defined series and also shows you how to create your own.
Extending a Single Cell

Starting on a new worksheet:
Type any number (eg 33) into cell A1 and press
Move back to cell A1 and, using the mouse, point to the fill handle (the pointer should change shape to a plain black cross)
Drag the fill handle down to include cell A15

You should find that the number has been copied down the column - the range A1 to A15 contains identical values. Whenever you use the fill handle, the Auto Fill Options button appears - ignore this for the time being.

You can also fill in a range using commands from a menu (or a control key combination):
Move to cell B1 and type in your name - press
Select cells B1 to B15 by dragging through them, using the mouse
Open the Edit menu and choose Fill then Down

Note that this command can also be issued from the keyboard using . To try this out:
Click on the [Undo] button - or use Undo from the Edit menu or press
Press to fill the selected range

Tip: If you double click on a fill handle, the value/formula is copied down the column until Excel finds an empty cell in the column to the left.
Built-in Data Series

Microsoft has pre-defined some commonly-used data series for you. For some of these, you just need to type in a single value:
Move to cell C1, type in Jan - don't press
Double click on the fill handle (or drag it down) to fill cells C1 to C15

You should find that you have the sequence Jan, Feb, Mar, etc. If you wanted Jan in all the cells you could do so by first selecting cells C1 to C15 and then using Fill Down ().

Repeat steps 1 and 2 using the following values in the named columns:
In cell D1, start with any month of the year spelt out in full - eg July
In cell E1, start with any day of the week abbreviated - eg Tue
In cell F1, start with any day of the week in full - eg Sunday
In cell G1, start with 1st (first)
In cell H1, start with X1 (numbers at the end of any text are incremented)
In cell I1, start with Q1 (Q is taken as an abbreviation for Qtr or Quarter, both of which also work)
In cell J1, start with 9:00 (the colon signifies a time)
Extending Two Cells

If you want a data series to expand in a different sequence, you have to type in more than one value (to define the sequence required). For example, you might require quarter-hour intervals rather than the hourly ones just created:
In cell K1, type 9:00 - press
In cell K2, type 9:15 - press
Select both cells by dragging through them
Double click on the fill handle (or drag it down) to fill down to K15

Repeat steps 1 to 4 using the following values in the named columns:
In cells L1 and L2, start with Jan , Apr
In cells M1 and M2, start with 1-Jan-00 , 1-Feb-00 (widen the column if you get #####)
In cells N1 and N2, start with 1/1/00 , 8/1/00 (a slash or hyphen signifies a date)
In cells O1 and O2, start with 1 , 2
In cells P1 and P2, start with 1 , 3
In cells Q1 and Q2, start with 10 , 20
In cells R1 and R2, start with a , b (Excel doesn't know the alphabet so copies the sequence)
In cells S1 and S2, start with A , 1 (Excel extends part and copies part of the sequence)
In cells T1 and T2, start with Mon , 9:00 (an example of two sequences intermixed)
Extending Multiple Cells

Sometimes you may want to use only part of a built-in series. A good example would be working days of the week - ie missing out Saturday and Sunday. For this you have to base your sequence on more than two cells:
In cell U1, type Mon
Using the fill handle attached to cell U1, drag down the sequence to U5 (ie Fri)
In cell U6, type Mon - press
Select cells U2 to U6 (note: start at U2)
Double click on the fill handle (or drag it down) to fill cells U7 to U15

Because this is a common requirement in the business world, Excel provides another much quicker way of achieving this particular sequence:
In cell V1, type Mon - don't press yet
Using the right mouse button, drag the fill handle down to V15
From the pop-up menu which appears select Fill Weekdays

The same procedure allows you to select weekday dates:
In cell W1, type today's date (eg 21-July-2005) - don't press
Using the right mouse button, drag the fill handle down to W15
From the pop-up menu which appears select Fill Weekdays

Other options are Fill Days (gives all dates, including weekends), Fill Months (the same date for each following month) and Fill Years (the same date for each following year).

Note: You can also get to these options via the Auto Fill Options button.
Linear and Growth Series

With numeric series, Excel has the ability to increment (add) or grow (multiply) by a fixed value. These produce extended linear and growth series. Examples of linear series have already been seen (in columns O, P and Q). Here's an example of a growth series:
In cell X1, type 1 - press
In cell X2, type 2 - press
Select cells X1 and X2
Using the right mouse button, drag the fill handle down to X15
From the pop-up menu which appears select Growth Trend

This produces the series 1, 2, 4, 8, 16 - ie multiplication by 2.

Note: Other options allow you to Copy Cells (gives sequence 1, 2, 1, 2, ...) and Fill Series (gives 1, 2, 3, 4 ... - using this you need only type in a single value to extend a sequence). You can also Fill Formatting Only (which copies just the formats of the source cells, leaving the destination cells empty) and Fill Without Formatting (which extends a sequence omitting any cell formatting). These options are also available via the Auto Fill Options button (see later).

You can also set your own step value via this pull-down menu:
In cell Y1, type 10 - don't press
Using the right mouse button, drag the fill handle down to Y15
From the pop-up menu which appears select Series... - the following window appears:


In the new Series window set the required Step value (here type 0.5)
Keep the Type as Linear (ie 10, 10.5, 11, ...) - press or click on [OK] to fill the cells
[Undo] the results and repeat steps 7 to 10 but this time choose Growth (ie 10, 5, 2.5, ...) and set a Stop value of 0.01 - only the first 10 values appear
Trends

A final option available from the pull-down menu fits a trend line through your data (a best fit straight line through the values), amending them to fit the closest linear or growth series. This is rather mathematical for most users but it's interesting to see how it works:
Starting in cell Z1, type 0.7 - press
In cells Z2 to Z4 type 1.5, 3.1 and 5.9
Click on Z1 then, using the right mouse button, drag the fill handle down to Z4
From the pop-up menu which appears select Series...
In the Series Window click on the Trend option
Set the Type to Linear and click on [OK]

The resultant values (0.22, 1.94, 3.66 and 5.38) may look confusing but they are the values obtained by fitting a straight line (a linear trend) through the data. Each value is 1.72 bigger than the previous one in the series.
Press or click on [Undo] to reset the values
Repeat instructions 3 to 6 above, but this time set the Type to Growth

The new values might seem even more confusing, though they match the original ones better. Each is roughly twice the previous one - the growth factor is approximately to double each time.
Customising a Series

The series built into Excel are very useful but each user will also have need for their own data values. You can create your own lists, which can then be used in exactly the same way as the pre-defined ones. Starting on a new worksheet, create a list of names (eg students on a course or members of staff in a department):
Move to Sheet2 then type the first name into cell A1 and press
Type further names into cells A2 to A6 - if you want the names in alphabetical order, click on the [Sort Ascending] button
Open the Tools menu and choose Options... then click on the Custom Lists tab
Click in the Import lists from cells: box and type A1:A6 (or drag through the cells to fill in the box) then click on [Import]
Click on [OK] to close the Options window
Move to cell B1 and type in any name from the new list then double click on the fill handle to fill cells B2 to B6

You can also set up a list by typing it directly into the List entries: box on the Custom Lists tab:
Open the Tools menu and choose Options... then click on the Custom Lists tab
Click in the List entries: box to set the typing position here
Type in another list (eg the alphabet), pressing <,> or between each list item
Click on the [Add] button to add the list to those already defined
Click on [OK] to close the Options window
Move to cell C1 and type in any name from the new list then double click on the fill handle to fill cells C2 to C6

To edit a custom list:
Open the Tools menu and choose Options... then click on the Custom Lists tab
Select the list to be edited from the Custom lists: box - eg the list of names
Make the required changes (corrections, additions or deletions) in the List entries: box
Click on the [Add] button to amend the original list
Click on [OK] to close the Options window
Move to cell D1 and type in any name from the new list then double click on the fill handle to fill cells D2 to D6

To delete a custom list:
Open the Tools menu and choose Options... then click on the Custom Lists tab
Select the list to be deleted from the Custom lists: box
Click on the [Delete] button to remove the original list
Press or click on [OK] to confirm the deletion
Click on [OK] to close the Options window

Note that customised series like the above can be used for sorting data (as can the built-in series - eg to sort by the days of the week or months of the year). See the Advanced Sort Options notes in Sorting, Subtotals and Outlines in Microsoft Excel 2003.
The Auto Fill Options Button

Whenever you use the cell handle to extend a series or copy a formula, the Auto Fill Options button appears.
Move to cell E1 and type in a date
Drag the fill handle down to E15 - the Auto Fill Options button appears
Click on the Auto Fill Options button and select the series required - eg Fill Months
Now move to cell F1, type in the number 1 then move to F2 and type in 2
Select both cells, apply a [Currency] style then double click on the handle to fill down to F15
Click on the [Auto Fill Options] button again and note the options have been suitable modified
Select the series required - eg Fill Without Formatting

You can investigate other series, if you like, to see what options are provided. If you like this new feature then make use of it. Note that a related Paste Options button appears whenever you use Cut and Paste. Both buttons can be turned off, as follows:
Open the Tools menu and select Options...
Click on the Edit tab and, under Settings, click on Show Paste Options buttons to turn it off - press for [OK] to close the Options window
Try filling a series - you'll find the button no longer appears




taken from :
http://www.reading.ac.uk/its/documents/training/excel/series/