It is possible to copy the contents of one cell to other cells in an instant using the Fill function – or to create an increasing or decreasing number series across several cells starting from a certain cell content.
There are two ways to use this function:
Filling with the mouse
The easiest way to fill cells automatically is via the mouse. Proceed as follows:
| 1. | Enter the initial value in a cell. |
| 2. | Click on that cell (to make it the currently selected cell). |
| 3. | With your mouse, point to the small rectangle in the bottom-right corner of the cell frame (marked in red color in the figure below): |
| 4. | Drag this rectangle, while holding the mouse button down, to cover the desired range of cells. Release the mouse button when the target position has been reached. |
All cells in the selection you created have now been automatically filled.
What the cells are filled with depends on the content of the initial cell:
▪If the initial cell contains text, PlanMaker fills all cells with this text.
▪If the initial cell contains a number (e.g., 42), PlanMaker fills successive cells with incrementing values (in this case, 43, 44, 45, etc.).
| Tip: If you do not want PlanMaker to fill the cells with a series of numbers but, instead, duplicate the value 42 in all cells, press and hold down the Ctrl key, while dragging with the mouse. |
To control the fill function precisely, press and hold down the Alt key while dragging. A dialog box (described below) will appear, providing full control over how the cells are filled.
Filling via buttons/dialog
To fill cells automatically via buttons/dialog box, proceed as follows:
| 1. | Enter the initial value in a cell. |
| 2. | Select the cell with this initial value and all cells you want to be filled. |
| 3. | Choose the ribbon command Home | group Contents | Fill  . |
| 4. | In the dropdown menu, specify the fill direction (starting from the cell with the initial value in the direction of the selected cells): Down, right, up or left. |
| As soon as you have selected a direction, the corresponding cells are filled with the initial value (copied as value and format, not incremented). |
| 5. | If you want to make further settings (for example, increment values instead of copying them), choose the Series entry in the dropdown menu to open the dialog box.. Here you can define exactly how the cells should be filled (see below). |
| As soon as you confirm with OK in the dialog box, the selected cells are filled according to your settings. |
In the dialog box...
...the following settings can be configured:
▪Series in
| Select here if you want to fill in the direction of the rows (right) or in the direction of the columns (down). |
| Note: If you have selected only one row or only one column, this option is omitted and PlanMaker automatically defaults to rows or columns. |
▪Source
| Enable the Contents option if you want the cells to be filled with values. |
| Enable the Format option if you also want the formatting of the source cell to be continued in the new cells. |
| By default, both options are enabled. |
▪Action
| This option is equivalent to pressing the Ctrl key during filling with the mouse. It lets you determine whether to copy values or to increment them from cell to cell: |
| If, for example, the initial cell contains the number 42 and you select Copy, all cells will be filled with the value 42. |
| If you select Increment, successive cells will be filled with a series of numbers (in this case, 43, 44, 45, etc.). |
| The numerical increment can be specified using the other options in this dialog box: |
▪Series type
| Lets you specify how the series of values is continued: |
| Arithmetic series with automatic increment. Each next value will be increased by an increment The increment is determined by PlanMaker automatically. |
| If, for example, you continue two cells containing the values 10 and 20, the next cells will be filled with 30, 40, 50, etc. (see also tips and tricks in the next section). |
| Here, each next value will be increased by the increment. |
| If, for example, the initial cell contains the value 1 and you specify an increment of 2, the next cells will be filled with 3, 5, 7, 9, 11, etc. |
| Here, each next value will be multiplied by the increment. |
| If, for example, the initial cell contains the value 1 and you specify an increment of 2, the next cells will be filled with 2, 4, 8, 16, 32, etc. |
| Here, each next value in the chosen Date unit (see below) will be increased by the specified increment. This type of series only works correctly if the initial cell contains a date. |
| If, for example, the initial cell contains the date 1/1/2018, the Increment is set to 1 and the Date unit is set to "Months", the new cells will be filled with 2/1/2018, 3/1/2018, 4/1/2018, etc. |
▪Date unit
| This option is only available if the Series type is set to "Date". |
| It allows you to specify the date unit of the increment: |
| Workdays: increment by days, excluding Saturdays and Sundays. For example, if the initial value is a Friday, the next value would be the Monday (not Saturday). |
| Months: increment by months |
| Years: increment by years |
▪Increment
| Lets you specify the amount by which each value will increase from cell to cell. For examples, see above. |
| Positive or negative increment values can be used. |
| This option is not available if the Series type is set to "Auto", because PlanMaker automatically determines the increment in this case. |
▪Increment each cell separately
| When this option is enabled, the specified increment will be added to each of the currently selected cells individually. |
| Example: Let's assume you have selected three cells containing the values 1, 2,and 3, and try to fill the cells below them. |
| When this option is disabled, the sequence from the source cells is simply continued and the cells are filled as follows: |
| 4, 5, 6, 7, 8, 9, 10, 11, 12, etc. |
| When you enable this option, the cells are filled as follows: |
| 2, 3, 4, then 3, 4, 5, then 4, 5, 6, etc. |
▪Edit lists button
| You can also create your own lists for the Fill function. |
| If, for example, you create a list with the content "Red", "Green" and "Blue", and later start a fill operation from a cell that contains the text "Red", PlanMaker will continue the series and fill with Green, Blue, Red, Green, Blue, etc. |
Tips and tricks for automatic filling
The many options that are available for the Fill function make its application seem more difficult than it actually is. This section offers some tips and tricks to help you effectively use this very useful feature.
Tip: The quickest way to fill a range of cells is to drag the small rectangle in the bottom-right corner of a cell with the mouse in the appropriate direction. PlanMaker then sets the series type to "Auto". This means it will automatically figure which values to use to fill the cells (see examples).
▪Filling with text
| When filling with text, you do not need to worry about any options, because text can only be copied anyway. |
| Then, drag the small rectangle in the bottom-right corner of the cell over the cells you want to fill. |
▪Filling with numbers
| If you use the mouse to fill cells with numbers, PlanMaker will automatically determine the increment. |
| If, for example, you drag the small rectangle from a cell with the value 42, the other cells will be filled with the values 43, 44, 45, etc. |
| If the initial cell value is 10, and the next cell value is 20, and you select both cells and drag the small rectangle down, PlanMaker automatically recognizes how to continue this series and fills the other cells with 30, 40, 50, etc. |
▪Filling with formulas
| If you use the mouse to fill cells with formulas, PlanMaker automatically attempts to update the formulas in the new cells with incremented cell addresses. |
| For example, the formula =A1*2 in the initial cell will be updated to =A2*2, =A3*2, etc., in the other cells. |
▪Filling with dates
| Filling with dates is identical to filling with numbers: |
| Enter the starting date in the initial cell and drag the small rectangle over the desired cells. PlanMaker will add one day in every next cell. |
| If desired, you can use the ribbon command Home | group Contents | Fill | Series to specify via the dialog box whether to increment by days, months or years. |
▪Filling with structures
| As you have already learned, it is possible to select multiple initial cells before starting the filling operation. But what if the selected cells have no automatically recognizable increment or completely different values? |
| PlanMaker will attempt to find a pattern in these values. If, for example, you enter the values 1, 2, text, PlanMaker will continue the series with 3, 4, text; 5, 6, text; 7, 8, text; etc. |
| If the initial cells contain just text, PlanMaker will simply repeat the text. For example, the series Red, Green, Blue will be repeated as Red, Green, Blue, Red, Green, Blue, etc. |
▪Filling with custom lists
| With the ribbon command Home | group Contents | Fill | Series you can create your own lists for the Fill function using the Edit lists button in the opened dialog box. |
| If, for example, you create a list with the content "Red", "Green" and "Blue", and later start a fill operation from a cell that contains the text "Red", PlanMaker will continue the series and fill with Green, Blue, Red, Green, Blue, etc. |
| Note: If desired, you can also add a separate Edit lists button for direct access to a ribbon tab. For more information on this and all other information on creating your own lists, see Editing lists for automatic filling. |
