Fill handle mode examples
Use Excel’s fill handle mode to complete a data sequence. This is normally performed down a column, or in a row to the right, but reverse directions are also possible.
Seed one cell
| Initial selection (seed) | Extended series |
|---|---|
| 1 | 1, 1, 1, 1, … |
| A | A, A, A, A, … |
| 1-Jan-15 | Dates: 2-Jan-15, 3-Jan-15, 4-Jan-15, … |
| 6:00 | Times: 7:00, 8:00, 9:00, … |
| Product 1 | Product 2, Product 3, Product 4, … |
| Item1 | Item2, Item3, Item4, … |
| A1 | A2, A3, A4, … |
| R1C1 | Trailing digits only: R1C2, R1C3, R1C4, … |
| 1st | Ordinal indicators: 2nd, 3rd, 4th, 5th, … |
| 1st Qtr | Quarters 1 to 4: 2nd Qtr, 3rd Qtr, 4th Qtr, 1st Qtr, … |
| Q1 | Quarters 1 to 4: Q2, Q3, Q4, Q1, … |
| Q 1 | Quarters 1 to 4: Q 2, Q 3, Q 4, Q 1, … |
| Qtr 1 | Quarters 1 to 4: Qtr 2, Qtr 3, Qtr 4, Qtr 1, … |
| Quarter 1 | Quarters 1 to 4: Quarter 2, Quarter 3, Quarter 4, Quarter 1, … |
| Half 1 | Half 2, Half 3, Half 4, Half 5, … |
Built-in lists
| Initial selection (seed) | Extended series |
|---|---|
| Mon | Tue, Wed, Thu, Fri, Sat, Sun, Mon,… |
| Wednesday | Thursday, Friday, Saturday, Sunday, Monday, Tuesday, Wednesday, … |
| Mar | Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, … |
| October | November, December, January, February, March, April, May, June, July, August, September, October, … |
| Initial selection (seed and increment) | |
| Jan, Apr | Increment +3: Jul, Oct, Jan, Apr, … |
Seed and increment
| Initial selection (seed and increment) | Extended series |
|---|---|
| 1, 2 | Increment +1: 3, 4, 5, 6, … |
| A, B | A, B, A, B, A, … |
| 10, 10.1 | Increment +0.1: 10.2, 10.3, 10.4, 10.5, … |
| 10, 9.99 | Increment -0.01: 9.98, 9.97, 9.96, 9.95, … |
| 1/32, 1/16 | Increment +1/32: 3/32, 1/8, 5/32, 3/16, … |
| 31-Jan, 28-Feb | Sequence – last day of month: 31-Mar, 30-Apr, 31-May, 30-Jun … |
| 1-Jan-15, 8-Jan-15 | Increment +7: 15-Jan-15, 22-Jan-15, 29-Jan-15, 5-Feb-15, … |
| 9:00, 9:15 | Increment +1/96: 9:30, 9:45, 10:00, 10:15, … |
Custom lists
- Suppose that an analyst wants a custom list containing the names of the geographic regions of the state of Victoria – see figure 1 – range
C3:C12. This list can be used for data fill, and custom sort purposes -
Fig 1: List of Victorian regions: – in the range C3:C12 - To Add or Import the list, you can access the Custom List dialog box from the File tab: File > Options > Advanced> General > Edit Custom Lists – dialog box name – Custom Lists, or
- From the Custom Sort item: Data > Sort & Filter > ZA|AZ > Sort Order > Custom Lists– dialog box name – Options – see figure 2
-
Fig 2: Custom list dialog box: – named Options when accessed via Sort Order > Custom Lists - Select the Import list from cells item – select the range ($C$3:$C$12), then click Import
| Initial selection (seed) | Extended series |
|---|---|
| Mallee | Wimmera, South West, Loddon Campaspe, Central Highlands, Barwon, Goulburn Valley, North East, Gippsland, Metropolitan |
Fill handle mode file
- Related material: Excel pointers and the fill handle
- This example was developed in Excel 2013 Pro 64 bit.
- Last modified: , [Australian Eastern Standard Time (AEST)]