Filling cells automatically

You can automatically fill a whole range of cells with values. You might use this, for example, when you set up a spreadsheet in which you want a column for every year from 1984 to the present. Column A would be 1984, column B would be 1985, and so on.

There are two types of fill: Normal and Series. In normal fill, the same value is put in each selected cell. In series fill, a different value (calculated in a specified increment from the starting value) is put in each cell.

To automatically fill in a range with a particular value, using Normal Fill, follow these steps:

  1. First enter the value in a cell.

  2. Next, select the range you want to fill (click and drag the mouse across and/or down the spreadsheet to select a range, making sure the first cell in the selected range is the cell containing the value).

  3. Click on the Fill button image\fill.gif or select the Fill command from the Edit menu. The "Fill" dialog is displayed.

  4. Click on OK (note that the fact that the Series box is left unselected indicates that this is a normal fill).

  5. The range will be filled with the value of the active cell. With normal fill, the fill is performed in one direction at a time. If the range spreads over an area greater than a single row or column, then you need to repeat the operation, specifying a different fill direction, to fill the whole area. For instance, you might first fill an area down and then right. (See below for details)

To automatically fill in a range with an incremented value, using Series Fill, follow these steps:

  1. First select the range you want to fill.

  2. Next, select the Fill command from the Edit menu or click on the Fill button on the toolbar.

  3. In the "Fill" dialog select the Series checkbox by clicking on it. This activates the Series box.

  4. In the Start box enter the value from which you want to start.

  5. In the Step box enter the step-size by which you want the value to be incremented. (Note that Ability recognizes various incremental lists, including numbers, text that ends with a number, and the lists, such as days of the week, contained in Custom Lists (under Options from the Tools menu)). If you leave the Step box empty, Spreadsheet will automatically use an increment of 1.

  6. The range will be filled incrementally according to the direction selected in the Direction box. If the range stretches over rows and columns the fill will proceed in the same direction throughout the range. (See below for details)

  7. When all is set, click on the OK button. The selected cells will be filled.

Direction of Fill

With either fill type, if the selected range spreads over an area greater than a single row or column, then you can choose in which direction you would like the fill to work.

Left

Fills the cells to the left of the active cell with its value

Right

Fills the cells to the right of the active cell with its value

Up

Fills the cells above the active cell with its value

Down

Fills the cells below the active cell with its value

Note: Fill will work with formulas as well as values. Filling with a formula works in the same way as copying a formula: each formula is propagated throughout the range. The formula should be entered in the active cell, as for normal fill, before selecting the range to be filled (see Copying a formula for more details).

Example 1

To fill the area A1..D10 with a particular value, say the number 1, do the following:

Enter 1 in A1 and then select the range A1..D10. Select Fill from the Edit menu or click on it on the toolbar. The "Fill" dialog will be displayed. Leave the Down button switched on and then select OK. The first column in the range will be filled. Next, select Fill again and this time switch on the Right option by clicking on it. The rest of the range will be filled.

Example 2

To fill the range A1..D10 with an incremented series of invoice numbers, where the first one is ABC45, the step is 2 and the range is to be filled along the rows, do the following:

Select the range to be filled. Select Fill from the Edit menu or by clicking on the toolbar. The "Fill" dialog will be displayed. Click on the Series checkbox to activate it. In the Start box put ABC45. In the Step box put 2. Select the Right option. Select OK. The range will be filled along the rows in increments of 2, from ABC45 to ABC123.