Selecting and Filling a Cell Range

From NeoWiki

(Difference between revisions)

Current revision

1 Selecting and Filling a Range of Cells
- 1.1 Selecting a range
- 1.2 Filling a range
  - 1.2.1 Using the Fill Series Command
  - 1.2.2 Drag-and-Fill
2 Copying formulas
3 Using absolute references
4 Performing arithmetic operations on a cell range
- 4.1 Multiplying all the values of a range by a same number
- 4.2 Performing operations on a range by Copy/Paste Special from another range
5 Showing the formulas

Selecting and Filling a Range of Cells

Sometimes you might wish to fill an entire row or column in your spreadsheet with data â€œautomatically,â€ e.g., sequential dates or numbers. You can do this in NeoOffice using the following steps:

Selecting a range

In the â€œInput fieldâ€ in the left top of the sheet, type the name of the beginning cell and then the name of the ending cell of the desired range, separated by a colon (:), e.g. A1:A20, and press the Enter key.
You can also select the range with the mouse, or by clicking in the top-left cell in the range and shift-clicking in the bottom right cell in the range, if you do not have the cell references memorized.
You can save that range for easier future use by giving it a significant name. Open the Insert menu, then the Names sub-menu, and select the Defineâ€¦ item. Type in a name and press Add. (This name will now be visible in the Range names section in the Navigator window.)
Afterwards, it will be easy to select the range by double-clicking its name in the Range names section in the Navigator.

Filling a range

Using the Fill Series Command

Select a cell range and choose the Edit menu then the Fill sub-menu and the Seriesâ€¦ item.

Four types of series Linear, Growth, Date and AutoFill are available.

The automatic filling is done from a sort list.
- You can see all the available lists by selecting Sort Lists in the NeoOffice Calc section of the Preferences sub-menu of the NeoOffice menu.
- You can add here your personal lists by going to the NeoOffice menu and choosing Preferences. click on NeoOffice Calc and then Sort Lists.:
  - Click New
  - Enter the successive values of your list, separated with a line break. Click OK
  - The new series will be available via the Seriesâ€¦ item in the Fill sub-menu of the Edit menu by checking the case AutoFill in the Series type section and choosing the first element of the list as the Start value.

Drag-and-Fill

You'll notice a little black square in the bottom-right corner of the current cell. This is a drag-and-fill tool; click-and-drag the square along the row/column you wish to fill, from the 'source' cell to the last cell you want filled, and then release the corner. The tool behaves differently depending on what's in the current cell (or cells - note that this also works with blocks of cells, though only in one direction at a time):
- Text - copies the same text into all the target cells
- Number - adds 1 to each suceeding cell, useful for filling a column/row quickly with 1-50 (disconcertingly. it also does this with non-integer cell values, so you can get the sequence e.g. -1.1, -0.1, 0.9, 1.9!)
- Formula - equivalent to copying/pasting the formula (see Copying Formulas below), i.e. the cell references get updated for each cell filled, unless the cell reference in the formula is absolute, i.e. $B$1 will stay $B$1 all the time, but B1 will become B2, B3 etc. (or C1, D1 if you're filling a row)

Copying formulas

The Fill function allows you to apply a recurrent formula to a cell range.

Example :

Select the range A1:A20, enter 1 in A, choose the Edit menu then the Fill sub-menu, click Series.
Set up the Type to Linear, the Start value to 1, the End value to 20 and the Increment to 1.
Click OK.

The series of the first 20 integers appears in the selected range.

Enter 1 in the cell B1.
Select the cell B2 and enter in the formula line, in the top of the page : "=B1+A2" (without quotes). Press Enter. The sum 3 appears in the cell B2.
Select the range B2:B20, choose the Edit then the Fill sub-menu and the Down item. Now every cell of the selected range contains the sum of the successive integers and the cell B20 contains the sum of the first 20 integers, i.e. 210.

Using absolute references

You will find the definition of absolute and relative references in NeoOffice help: go to the Help menu, then NeoOffice Help. In the drop-down menu select NeoOffice Calc, click on Find and enter References in the Search field. Select Adresses and References, Absolute and Relative.

Example of absolute references:

Let us assume that you want to enter the value (A1 + B1 + C1)*n, in the cell D1, where n is a number that you want be able to change if needed, then copy that formula for all the cells of the range A1:C5. To do so, proceed as follows:

Enter a number (the value of n) in E1
Click in the cell D1 and type in the formula bar:

= SUM(A1:C1)*$E$1

Press Return
Click in the cell D1 and fill the range D1:D5 by using drag/and/fill. The cell D2 shows the value (A2+B2+C2)*$E$1. Without using absolute references, D2 would show (A2+B2+C2)*E2.

You get the corresponding results in the cells D3 to D5.

Performing arithmetic operations on a cell range

You can use the Paste Special function to perform arithmetic operations on the values of a cell range. Examples follow.

Multiplying all the values of a range by a same number

Enter the multiplier, for example 2.5, in an empty cell
Copy that number Cmd-C
Select the cell range to be modified
Choose the Edit menu then the Paste Specialâ€¦ sub-menu
In the dialogue that opens, in the Operations section check Multiply. Click OK

==>All the numbers contained in the selected cells are replaced with their product by 2.5
N.B. The selected cell range must form a matrix.

Performing operations on a range by Copy/Paste Special from another range

Let us assume that you want to use the range C1:D2 to perform operations on the range A1:B2

Select and copy the range C1:D2
Select the range to modify, A1:B2
Choose the Edit menu then the Paste Specialâ€¦ sub-menu
In the dialogue that opens, in the Operations section check Multiply. Click OK

==>The range A1:B2 contains now the products A1*C1, A2*C2, B1*D1 and B2*D2.

Showing the formulas

If in the View section of NeoOffice Calc of the Preferences sub-menu of the NeoOffice menu, you check the Value highlighting option, the cells which contain values will be displayed in blue, and those which contain formulas in green. If you choose that option, you'll not be able to change the character color in those cells.

This article in other languages: FranÃ§ais Italiano

Retrieved from "https://neowiki.neooffice.org/index.php/Selecting_and_Filling_a_Cell_Range"

Categories: NeoOffice | Tips and Hints

 ==Selecting and Filling a Range of Cells==
 ===Filling a range===
+====Using the Fill Series Command====
 * Select a cell range and choose the {{menu|Edit}} menu then the {{menu|Fill}} sub-menu and the {{menu|Seriesâ€¦}} item.
 Four types of series Linear, Growth, Date and AutoFill are available.
-* The automatic filling is done from a sort list. You can see all the available lists by selecting  {{prefName|Sort Lists}} in the  {{section|NeoOffice Calc}} section of the {{menu| Preferences}} sub-menu of the {{menu|NeoOffice}} menu.<br>
+* The automatic filling is done from a sort list.
-You can add here your personal lists:
+**You can see all the available lists by selecting  {{prefName|Sort Lists}} in the  {{section|NeoOffice Calc}} section of the {{menu|Preferences}} sub-menu of the {{menu|NeoOffice}} menu.<br>
-* Click {{button|New}}
+**You can add here your personal lists by going to the {{Menu|NeoOffice}} menu and choosing {{Menu|Preferences}}. click on {{section|NeoOffice Calc}} and then {{prefName|Sort Lists}}.:
-** Enter the successive values of your list, separated with a line break. Click {{button|OK}}
+*** Click {{button|New}}
-** That series will be available via the {{menu|Seriesâ€¦}} item in the {{menu|Fill}} sub-menu of the {{menu|Edit}} menu by checking the case {{prefName|AutoFill}} in the {{section|Series type}} section and choosing the first element of the list as the {{section|Start value}}.
+*** Enter the successive values of your list, separated with a line break. Click {{button|OK}}
-* Drag-and-Fill - you'll notice a little black square in the bottom-right corner of the current cell. This is a drag-and-fill tool; click-and-drag the square along the row/column you wish to fill, from the 'source' cell to the last cell you want filled, and then release the corner. The tool behaves differently depending on what's in the current cell:
+*** The new series will be available via the {{menu|Seriesâ€¦}} item in the {{menu|Fill}} sub-menu of the {{menu|Edit}} menu by checking the case {{prefName|AutoFill}} in the {{section|Series type}} section and choosing the first element of the list as the {{section|Start value}}.
+====Drag-and-Fill====
+* You'll notice a little black square in the bottom-right corner of the current cell. This is a drag-and-fill tool; click-and-drag the square along the row/column you wish to fill, from the 'source' cell to the last cell you want filled, and then release the corner. The tool behaves differently depending on what's in the current cell (or cells - note that this also works with blocks of cells, though only in one direction at a time):
 ** Text - copies the same text into all the target cells
-** Number - adds 1 to each suceeding cell, useful for e.g. filling a column quickly with 1-50 (disconcertingly. it also does this with non-integer cell values, so you can get the sequence e.g. -1.1, -0.1, 0.9, 1.9!)
+** Number - adds 1 to each suceeding cell, useful for filling a column/row quickly with 1-50 (disconcertingly. it also does this with non-integer cell values, so you can get the sequence e.g. -1.1, -0.1, 0.9, 1.9!)
-** Formula - equivalent to copying/pasting the formula, i.e. the cell references get updated for each cell filled, unless the cell reference in the formula is absolute, i.e. $B$1 will stay $B$1 all the time, but B1 will become B2, B3 etc. (or C1, D1 if you're filling a row)
+** Formula - equivalent to copying/pasting the formula (see Copying Formulas below), i.e. the cell references get updated for each cell filled, unless the cell reference in the formula is absolute, i.e. $B$1 will stay $B$1 all the time, but B1 will become B2, B3 etc. (or C1, D1 if you're filling a row)
 ==Copying  formulas==
 * Select the cell B2 and enter in the formula line, in the top of the page : "=B1+A2" (without quotes). Press {{key|Enter}}. The sum 3 appears in the cell B2.
 *Select the range B2:B20, choose the {{menu|Edit}} then the {{menu|Fill}} sub-menu and the {{menu|Down}} item. Now every cell of the selected  range contains the sum of the successive integers  and the cell B20 contains the sum of the first 20 integers, i.e. 210.
+==Using absolute references==
+You will find the definition of ''absolute and relative references'' in NeoOffice help: go to the {{menu|Help}} menu, then {{menu|NeoOffice Help}}.  In the drop-down menu select {{menu|NeoOffice Calc}}, click on {{section|Find}} and enter ''References'' in the Search field. Select {{prefName|Adresses and References, Absolute and Relative}}.
+:Example of absolute references:<br>
+Let us assume  that you want to enter the value  (A1 + B1 + C1)*n, in the cell D1, where ''n'' is a number that you want be able to change if needed, then copy that formula for all the cells of the range A1:C5. To do so, proceed as follows:
+*Enter a number (the value of ''n'') in E1
+*Click in the cell D1 and type in the formula bar:
+:= SUM(A1:C1)*$E$1
+*Press {{key|Return}}
+*Click in the cell D1 and fill the range D1:D5 by using drag/and/fill. The cell D2 shows the value (A2+B2+C2)*$E$1. Without using absolute references,  D2 would show (A2+B2+C2)*E2.<br>
+You get the corresponding results in the cells D3 to D5.
+==Performing arithmetic operations on a cell range==
+You can use the  ''Paste Special'' function to perform arithmetic operations on the values of a cell range.
+Examples follow.
+===Multiplying all the values of a range by a same number===
+* Enter the multiplier, for example 2.5, in an empty cell
+*Copy that number {{key|Cmd-C}}
+*Select the cell range to be modified
+*Choose the {{menu|Edit}} menu then the {{menu|Paste Specialâ€¦}} sub-menu
+*In the dialogue that opens, in the {{section|Operations}} section check {{prefName|Multiply}}. Click {{button|OK}}
+==>All the numbers contained in the selected cells are replaced with their product by 2.5<br>
+'''N.B.''' The selected cell range must form a matrix.
+===Performing operations on a range by ''Copy/Paste Special'' from another range===
+Let us assume that you want to use the range C1:D2 to perform operations on the range A1:B2
+*Select and copy the range C1:D2
+*Select the range to modify, A1:B2
+*Choose the {{menu|Edit}} menu then the {{menu|Paste Specialâ€¦}} sub-menu
+*In the dialogue that opens,  in the {{section|Operations}} section check {{prefName|Multiply}}. Click {{button|OK}}
+==>The range A1:B2 contains now the products A1*C1, A2*C2, B1*D1 and B2*D2.
 ==Showing the formulas==