Spreadsheet Column Fill

Select menu: Spread | Calculate | Fill

Use this to automatically generate a pattern or sequence of numbers to fill within a column. Patterns can be generated by providing a starting and ending value along with an incremental value. Numbers can also be repeated within the pattern. By default the pattern starts in the first cell of a column and is repeated as necessary to fill the column. However, the starting and ending positions for the generated pattern can be specified using the relevant options.

The list at the top of the dialog contains a list of the columns within the current spreadsheet. Select the column name that is to be filled with the numerical sequence.

From the menu select Spread | Calculate | Fill.
Fill in the fields as required then click OK.

Sequence

These options control the numerical sequence that is to be used in the column.

Starting value	First value in the sequence.
Ending value	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. A negative increment must be specified if the Starting value is greater than the Ending value.
Number of repeats	Allows a pre-multiplier to be applied to the sequence, so that each element is repeated the specified number of times.

Copy down existing values over missing

When selected, 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

When selected, 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, so if units 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	Start filling from the first cell in the column.
Current	Start filling from the cell specified in the Current cell to fill from option. By default this value is specified as the current cell. When selected, the option Current cell to fill from is enabled and the starting cell for the column to be filled can be specified in the space provided.

Fill to

Bottom	Fill all the cells, recycling the list if needed.
End of list	Fill the cells until the list is exhausted and then stop.

Preview

This list illustrates the sequence of numbers that will be inserted within the column. For very long sequences, the list only displays the first 20 and last 20 items, separated by a ‘…’ item.

Action buttons

OK	Fill column with list and close the dialog.
Apply	Fill column with list and leave dialog open.
Cancel	Close the dialog without further changes.

Example

Three columns have been taken from a spreadsheet. The first two columns in the table below show an example of where there are no repeated values for the column Treat and all repeated values in the column Rep are missing. Using the column fill dialog with Copy down existing values over missing selected the columns can be automatically filled as show in the last 3 columns of the table.

Before			After filling columns
Treat	Rep	Year	Treat	Rep	Year
A	1	91	A	1	91
	*	90	A	1	90
	*	91	A	1	91
	2	90	A	2	90
	*	91	A	2	91
B	1	90	B	1	90
	*	91	B	1	91
	2	90	B	2	90
	*	91	B	2	91
C	1	90	C	1	90
	*	91	C	1	91
	2	90	C	2	90
	*	91	C	2	91