Section 50. Create a Range

50. Create a Range


49 About Calc Ranges


51 Fill Cells with Data

50. Create a Range

To name a range, you only need to select a range and assign a name to it. The name of the range then appears instead of the range address in the Formula bar's Name Box whenever you select that range. Calc supports the naming, renaming, and deleting of range names . Once you've named a range of cells, you no longer have to refer to that group of cells by their cell addresses.


Give your ranges meaningful names. The name Payroll05 is obviously a better name than XYZ for payroll data in the year 2005. The better you name ranges, the fewer errors you'll type in your sheets because you'll more accurately refer to cells.

Calc keeps track of your ranges and changes them as needed. If you insert a cell in the middle of a range, or even entire rows and columns somewhere inside a range, Calc reassigns the range name to the new cell range. This holds true if you delete cells from a range as well. (If you delete only cell contents, the range is unaffected.)

Select a Range

Click the anchor cell in a range you want to define. While holding down your mouse button, drag your mouse to the last cell in the range. Calc highlights the cells within the range as you drag the mouse.

Request a Range Name

Select Insert, Names, Define from the menu. (You can also press the shortcut Ctrl+F3 .) The Define Names dialog box appears. This is where you name ranges and manage them.

Type the Range Name

Type a name for your selected range in the Name text box. Do not include spaces in the range name. Click Add to add the name to your sheet. A spreadsheet can contain as many names as you need, and you can define more than one range at a time in the Define Names dialog box. After you've added the current name to your sheet, you can select another range without closing the dialog box. If you can't see the cells you want to select, drag the dialog box out of the way or click the Shrink button to temporarily shrink the dialog box so that you select what you need. When you are finished, click OK to close the Define Names dialog box. You can now use your range name in formulas.


To create a range name even more quickly, select the range and then replace the range address in the Name text box with the name you want to use. When you press Enter , the new name appears in the Name box.

Use the Range Name

Where you would otherwise use cell addressessuch as in a Sum() functionuse the range name instead. The Formula bar always displays the range name inside formulas, and the Name box displays the range name when it is selected.


AutoComplete keeps track of your range names and inserts them when you are creating a formula; press Enter to accept the suggested insert or continue typing if that's not the range name you want to include.


52 About Calc Functions explains how to use functions such as the Sum() function.

Overlap Ranges

Two or more cells can appear in different ranges. Depending on the kind of sheet you're creating, overlapping range names can be common. Multiple rows might comprise one range, whereas columns within some of those rows might define a different range. You can name any range you wish, regardless of whether part or all of that range appears in other range names.

Name as many ranges as you can because the more range names you create, the less error-prone your sheets will be. By referring to ranges by name, you are less likely to make a mistake than if you reference the cells within that range by their addresses. 2, Firefox, and Thunderbird for Windows All in One
Sams Teach Yourself 2, Firefox and Thunderbird for Windows All in One
ISBN: 0672328089
EAN: 2147483647
Year: 2005
Pages: 232
Authors: Greg Perry © 2008-2017.
If you may any questions please contact us: