Automatically Fill a Column with a Sequence

You can enter a sequence or pattern of data automatically into a spreadsheet column using one of the column fill dialogs. These are all accessed from the Spread | Calculate menu as shown below. You can fill a column with values using a numerical sequence, a list, by groups, or with date or times values.

Note: You can also copy an existing pattern using copy and paste.

The following example shows different ways that columns can be filled with values by specifying a numerical pattern. The table below shows the starting and ending values, and the increment that is used to generate the values. The results are shown in the spreadsheet below.

Column	C1	C2	C3	C4	C5
Starting value	1	1	1	-1	1
Ending value	10	10	10	-10	4
Increment	1	3	2	-2	1
Number of repeats	1	1	1	1	3

Column C1 is a list of numbers from 1 to 10. In column C2, the pattern 1, 4, 7, 10 is repeated to fill the whole column. Column C3 does not include the ending value of 10. Column C4 uses a negative increment. Column C5 repeats each value in the sequence 3 times until the column is full.

Fill column with numerical sequence

This menu fills columns with data generated in cyclic patterns (for example when creating block and treatment factors). Generate your sequence by providing a starting and ending value along with an incremental value.You can also repeat numbers within the pattern. By default the pattern starts in the first cell of a column and is repeated as necessary to fill the column, but you can change this and set the starting and ending positions for the generated pattern using the relevant options.

Select Spread | Calculate | Fill.
From the dropdown list select the column you want to fill.
Enter values as required then click Apply to fill the column and keep the dialog open.
To fill other columns repeat from step 2 then click OK when all required columns are filled.

Starting value	Specify the first value in the sequence.
Ending value	Specify the last value in the sequence. If this differs from the Starting value by an exact number of increments it will be included in the pattern, otherwise the pattern will terminate at the value less than the Ending value (greater than in the case of a negative increment).
Increment	Specify the increment used to form intermediate values in the sequence. If the Starting value is greater than the Ending value you must specify a negative increment.
Number of repeats	Lets you specify a pre-multiplier so that each value is repeated a specific number of times.
Copy down existing values over missing	Any missing values in a column will be replaced with the first non-missing value that can be found above it in the same column.
Ignore restricted/filtered rows	Rows that have been restricted/filtered will be excluded from the cells filled. The sequence of values will be inserted into the cells, jumping over the filtered/restricted cells. For example, if cells 2 and 5 are restricted you will get the sequence 1 * 2 3 * 4.
Fill selected rows only	This option is only available when there is a row selection active in the spreadsheet. The fill operation will then just apply to the selected rows, i.e. skipping the unselected rows. The numerical sequence applied to the selected cells will be contiguous. For example, if the fill sequence was 1,2,3,4 and rows 1,3,5,7 where selected, then rows 1,3,5,7 would be given the values 1,2,3,4 respectively and rows 2,4,6,8 would have their values unchanged.
Fill all columns in selection	This option is only available when there is a column selection active in the spreadsheet, or row selection and the Fill selected rows only option has been selected. The fill operation will then be applied to all columns containing selected cells. This allows multiple columns to be filled in one operation.
Fill from cell	Top – Fills from the first cell in the column. Current – Fills from the cell specified in Current cell to fill from. By default this is the cell that has the cursor focus.
Fill to	Bottom – Fills all the cells, recycling the list if needed. End of list– Fills the cells until the list runs out, then stops.
Current cell to fill from	Specify the starting cell for the sequence.

Fill column using a list

List fill lets you specify a general patterned sequence of numbers to be entered into a column.

Select Spread | Calculate | List Fill.
From the dropdown list select the column you want to fill.
Enter values as required then click Apply to fill the column and keep the dialog open.
To fill other columns repeat from step 2 then click OK when all required columns are filled.

List formulae for sequence	Specify a formula to generate a sequence of numbers to be inserted into the column. A list is a sequence of numbers separated by commas. The list can contain progressions and pre- or post-multipliers. A progression is a set of numbers ascending or descending with equal increments, and can be specified succinctly using the form “number, number…number” where the first two numbers define the first two elements in the list (and thus the increment) and the list ends with the value beyond which the third number would be passed. For lists with an increment of plus or minus one, the second number can be omitted, to give the form “number…number”. For example the progressions 1…5 = 1,2,3,4,5, 10…6 = 10,9,8,7,6 and 0,2…9 = 0,2,4,6,8 (this excludes 9, which is not in the sequence of adding the increment 2). A multiplier allows repetitive lists to be specified concisely. A pre-multiplier occurs immediately before the initial (round) bracket of a pair enclosing a list of numbers and has the effect of repeating each item in turn, the specified number of times. For example, 3(1,2) = 1,1,1,2,2,2. A post-multiplier is given immediately after the second of a pair of round brackets enclosing a list of numbers and has the effect of repeating the entire list as a whole, the specified number of times. For example, (1,2)3 = 1,2,1,2,1,2. The progressions and multipliers can be combined as shown in the examples below. 3(4.8,9.3) = 4.8,4.8,4.8,9.3,9.3,9.3 2(1…4) = 1,1,2,2,3,3,4,4 2(1,2,4)3 = 1,1,2,2,4,4,1,1,2,2,4,4,1,1,2,2,4,4 (2(1),4(0))2 = 1,1,0,0,0,0,1,1,0,0,0,0 (1…3)2,3(4,5) = 1,2,3,1,2,3,4,4,5,5 (5…1),(1…5) = 5,4,3,2,1,1,2,3,4,5 3(0,(1,2)2) = 0,0,0,1,2,1,2,1,2 (1,3…11)2 = 1,3,5,7,9,11,1,3,5,7,9,11
Fill from cell	Top – Fills from the first cell in the column. Current – Fills from the cell specified in Current cell to fill from. By default this is the cell that has the cursor focus.
Fill to	Bottom – Fills all the cells, recycling the list if needed. End of list – Fills the cells until the list runs out, then stops.
Current cell to fill from	Specify the starting cell for the sequence.

Fill column by groups

Group fill lets you fill columns with a separate numerical sequence, group by group, such as plots within blocks. The pattern or sequence of numbers which is used to fill each group is inserted from row 1 onwards and is repeated as necessary to fill each group. If the length of a group is not an exact multiple of the pattern length, the remainder of the group will be filled with part of the pattern.

Select Spread | Calculate | Fill by Groups.
From the first dropdown list select the column you want to fill.
From the second dropdown list select the factor column to define the groups.
Enter values as required then click Apply to fill the column and keep the dialog open.
To fill other columns repeat from step 2 then click OK when all required columns are filled.

Within groups defined by factor	Specify which factor to use to define the groups. Each group will have an identical, but independent sequence applied to the corresponding column that is to be filled.
Starting value	Specify the first value in the sequence.
Ending value	Specify the last value in the sequence. If this differs from the Starting value by an exact number of increments it will be included in the pattern, otherwise the pattern will terminate at the value less than the Ending value (greater than in the case of a negative increment
Increment	Specify the increment used to form intermediate values in the sequence. If the Starting value is greater than the Ending value you must specify a negative increment.
Number of repeats	Lets you specify a pre-multiplier so that each value is repeated a specific number of times.
Ignore restricted/filtered rows	Rows that have been restricted/filtered will be excluded from the cells filled. The sequence of values will be inserted into the cells, jumping over the filtered/restricted cells. For example, if cells 2 and 5 are restricted you will get the sequence 1 * 2 3 * 4.
Fill selected rows only	This option is only available when there is a row selection active in the spreadsheet. The fill operation will then just apply to the selected rows, i.e. skipping the unselected rows. The numerical sequence applied to the selected cells will be contiguous. For example, if the fill sequence was 1,2,3,4 and rows 1,3,5,7 where selected, then rows 1,3,5,7 would be given the values 1,2,3,4 respectively and rows 2,4,6,8 would have their values unchanged.

Fill column with dates or times

Date fill lets you fill a column with a series of dates or times. The sequence or series that is used to fill the column is inserted from row 1 onwards, and is repeated as necessary to fill the column. If the length of the column is not an exact multiple of the pattern length, the remainder of the cells will be filled with part of the pattern.

Select Spread | Calculate | Date Fill.
From the dropdown list select the column you want to fill.
Enter values as required then click Apply to fill the column and keep the dialog open.
To fill other columns repeat from step 2 then click OK when all required columns are filled.

Date format	Specify the format used to display dates.
Base date	Specify the base date that all other dates are numbered from. 1/3/1600 The nominal starting day for the Gregorian calendar. 1/1/1900 The starting day used by spreadsheets such as Excel, Lotus and Quattro
Starting value	Specify the first value in the sequence.
Ending value	Specify the last value in the sequence. If this differs from the Starting value by an exact number of increments it will be included in the pattern, otherwise the pattern will terminate at the value less than the Ending value (greater than in the case of a negative increment).
Increment	The increment used to form intermediate values in the sequence. The type of increment is chosen from a drop down list of the following types: Days – n day intervals. Weeks – n week (7 day) intervals Months – n month intervals Years – n year intervals Hours – n hour intervals Minutes – n minute intervals Seconds – n second intervals Daily – 1 day intervals Weekly – 1 week (7 day) intervals Monthly – 1 month intervals Yearly – 1 month intervals Hourly – 1 hour intervals Twice daily – 12 hour intervals Fortnightly – 2 week (14 day) intervals Non-weekend days – 1 day intervals, skipping Saturday and Sunday. Pentade – 5 day intervals within each month, with the last interval reduced to 3 or 4 or extended to 6 days so that there are 6 periods per month. Decade – 10 day intervals within each month, with the last interval reduced to 8 or 9 or extended to 11 days so that there are 3 periods per month. You can generate a decreasing series or sequence by specifying a negative multiplier along with a Starting value that is greater than the Ending value.
Number of repeats	Lets you specify a pre-multiplier so that each value is repeated a specific number of times.
Ignore restricted/filtered rows	Rows that have been restricted/filtered will be excluded from the cells filled. The sequence of values will be inserted into the cells, jumping over the filtered/restricted cells. For example, if cells 2 and 5 are restricted you will get the sequence 1 * 2 3 * 4.
Fill selected rows only	This option is only available when there is a row selection active in the spreadsheet. The fill operation will then just apply to the selected rows, i.e. skipping the unselected rows. The numerical sequence applied to the selected cells will be contiguous. For example, if the fill sequence was 1,2,3,4 and rows 1,3,5,7 where selected, then rows 1,3,5,7 would be given the values 1,2,3,4 respectively and rows 2,4,6,8 would have their values unchanged.
Fill all columns in selection	This option is only available when there is a column selection active in the spreadsheet, or row selection and the Fill selected rows only option has been selected. The fill operation will then be applied to all columns containing selected cells. This allows multiple columns to be filled in one operation.
Fill from cell	Top – Fills from the first cell in the column. Current – Fills from the cell specified in Current cell to fill from. By default this is the cell that has the cursor focus.
Fill to	Bottom – Fills all the cells, recycling the list if needed. End of list – Fills the cells until the list runs out, then stops.
Current cell to fill from	Specify the starting cell for the sequence.

Updated on February 28, 2019

Was this article helpful?

Yes No