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 |
|
| Fill to |
|
| Current cell to fill from | Specify the starting cell for the sequence. |
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.