Replication is a means of copying cells, usually number cells. It is particularly useful for copying formulae as it can update a formula for each cell into which it is copied.
In the following spreadsheet:
A | B | C | D | |
---|---|---|---|---|
1 | Price ex VAT | VAT | Price inc VAT | Total inc postage |
2 | ||||
3 | £100.00 | £17.50 | ||
4 | £200.00 | |||
5 | £300.00 | |||
6 | £400.00 | |||
7 | £500.00 | |||
8 | £600.00 | |||
9 | ||||
10 | Postage charge | £10.00 |
Cell B3 contains the formula A3 * 0.175. (Note: this example was written in 1991 when the VAT rate was 17.5%) If you want to work out 17.5% of the remaining values in cells A4 to A8, you don’t have to laboriously type formulae into cells B4 to B8. Instead, you can do the following:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Blocks | Replicate Down | ![]() |
BRD |
Your spreadsheet will now appear as follows:
A | B | C | D | |
---|---|---|---|---|
1 | Price ex VAT | VAT | Price inc VAT | Total inc postage |
2 | ||||
3 | £100.00 | £17.50 | ||
4 | £200.00 | £35.00 | ||
5 | £300.00 | £52.50 | ||
6 | £400.00 | £70.00 | ||
7 | £500.00 | £87.50 | ||
8 | £600.00 | £105.00 | ||
9 | ||||
10 | Postage charge | £10.00 |
The formula in cell B3 has been copied into cells B4 to B8, but the cell reference in the formula has been updated for each of the cells in column B. Cell B4, for example, contains the formula A4 * 0.175. Cell B5 contains the formula A5 * 0.175.
To get the totals, all you have to do is the following:
The spreadsheet will appear as follows:
A | B | C | D | |
---|---|---|---|---|
1 | Price ex VAT | VAT | Price inc VAT | Total inc postage |
2 | ||||
3 | £100.00 | £17.50 | £117.50 | |
4 | £200.00 | £35.00 | £235.00 | |
5 | £300.00 | £52.50 | £352.50 | |
6 | £400.00 | £70.00 | £470.00 | |
7 | £500.00 | £87.50 | £587.50 | |
8 | £600.00 | £105.00 | £705.00 | |
9 | ||||
10 | Postage charge | £10.00 |
The formula in cell C3 has been copied into cells C4 to C8, but the cell reference in the formula has been updated for each of the cells in column C. Cell C4, for example, contains the formula A4+B4. Cell C5 contains the formula A5+B5.
There may be situations in which you don’t want the cell reference(s) in the formula to be updated in the way described above. In the example above, you may want to add 10.00 to each total as the extra price for postage, for example. You would do this as follows:
Your spreadsheet will look like this:
A | B | C | D | |
---|---|---|---|---|
1 | Price ex VAT | VAT | Price inc VAT | Total inc postage |
2 | ||||
3 | £100.00 | £17.50 | £117.50 | £127.50 |
4 | £200.00 | £35.00 | £235.00 | £245.00 |
5 | £300.00 | £52.50 | £352.50 | £362.50 |
6 | £400.00 | £70.00 | £470.00 | £480.00 |
7 | £500.00 | £87.50 | £587.50 | £597.50 |
8 | £600.00 | £105.00 | £705.00 | £715.00 |
9 | ||||
10 | Postage charge | £10.00 |
This time, the formula in cell D3 has been copied into cells D4 to D8. However, the cell reference B10 has not been updated because it has been ‘fixed’ by the $ characters. The other cell reference in the formula in cell D3, C3, has been updated since it is not fixed by $ characters. Therefore, cell D4 contains the formula C4+$B$10, cell D5 contains the formula C5+$B$10, and so on.
Note that all references, including those fixed by $ characters, are updated correctly when you insert or delete rows or columns.
You need to put a $ character before each element of the cell reference you want to fix. Sometimes, you may only want to fix one element. For example, you may want the row references to be updated when copied but not the column references.
Consider the following document:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | France | Germany | Spain | England | |
2 | |||||
3 | Cars | - | road | sea | road |
4 | TVs | road | air | road | road |
5 | Fridges | sea | road | - | road |
6 | Post | air | air | air | air |
7 | Spares | sea | road | sea | road |
8 | |||||
9 | Totals | ||||
10 | |||||
11 | air | 1 | 2 | 1 | 1 |
12 | sea | 2 | 0 | 2 | 0 |
13 | road | 1 | 3 | 1 | 4 |
Cell B11 contains the formula
DCOUNTA(B$3B$7,B$3B$7=$A11)
DCOUNTA counts the number of cells in the range for which the condition is true. B$3B$7 is the range of cells which the DCOUNTA function is to act upon. B$3B$7=$A11 is the condition it has to satisfy. A11 contains the word ‘air’ so PipeDream counts all the cells in the range B3B7 which contain ‘air’. This gives the required result 1.
If you now mark cells B11 to E11 and give the Replicate Right command:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Blocks | Replicate Right | ![]() |
BRR |
PipeDream will count the occurrences of ‘air’ in the ranges C3C7, D3D7 and E3E7. When replicating, $ fixes any column or row it precedes. In this case, therefore, cell C11 contains the formula DCOUNTA(C$3C$7,C$3C$7=$A11), cell D11 contains the formula DCOUNTA (D$3D$7,D$3D$7=$A11), and so on. The references to column B have been updated, but the row references (3 and 7) have not been updated as they are fixed with $ characters.
Now mark cells B11 to E13 and give the Replicate Down command. Since there is no $ character, the row reference (11) is updated for each new row. Therefore, on row 12, the total occurrences of cells matching cell A12 (sea) are returned and on row 13, the total occurrences of cells matching cell A13 (road) are returned.
You can see that replication, together with us of the $ character, can dramatically increase the speed and accuracy with which you construct a spreadsheet.
In PipeDream, as in other spreadsheets, the term replication is generally used to refer to copying number cells. The differences between Copy Block and replication are as follows:
With the Replicate command, you can replicate a single cell to another position in the document. You can replicate a single cell to a range of cells. Or you can replicate a range of cells to another range of cells.
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Blocks | Replicate | Adj-![]() |
BRE |
The contents of the first cell will appear replicated in the specified cell.
The first and last cells of the range you have marked will already appear on the Range to copy from input field. If you had not marked the range, you could type in the required cell references at this stage. The cell references of the cell at which you want the copy of the range to start will appear in the Range to copy to input field since the caret is in the cell, so all you need to do is to click the button or press Return↵. If you had not positioned the caret in the right cell, you could type it in at this stage.
The range will be replicated at the specified position.
For information on | See |
---|---|
Copying, moving or deleting blocks | Block operations |