Naming Spreadsheet Areas

Naming Spreadsheet Areas

Calc lets you name sections of your spreadsheet to make those areas more meaningful. For example, you can select a column of first quarter sales numbers and name that area "Q1Sales".

This is a useful feature for a couple of reasons. When you name areas in Calc, you can jump to those areas quickly in the spreadsheet, either from the Navigator window or from the drop-down list in the formula bar, as shown in Figure 22-11. You can also include names in formulas rather than cell ranges, making formulas more meaningful. The following example illustrates this:

=SUM(B6:B9) can be written as =SUM(Q1Sales)

Figure 22-11. Jumping to named areas of a spreadsheet


You can name ranges individually, or select multiple rows and columns and create multiple named areas automatically with one procedure.

Naming a Single Region

  1. Select the region you want to name.

  2. Choose Insert > Names > Define.

  3. In the Define Names window, type the name of the named area in the top box, as shown in Figure 22-12.

    Figure 22-12. Defining a named region


  4. Click OK.

If you want to name more than one region, you can keep the Define Names window open by clicking Add instead of OK in step 3. You can then enter another name, click the Shrink button to select a new region in the spreadsheet, click Shrink again, and click Add.

Naming Multiple Regions Automatically

You can select a big area of a spreadsheet that contains multiple regions you'd like to name, then use this procedure to create multiple named areas out of the big area. When you use this procedure, Calc creates names for regions based on column and/or row headings in the selected area. An example is shown in Figure 22-13, followed by steps.

  1. Select the area containing all the regions you want to name.

  2. Choose Insert > Names > Create.

  3. In the Create Names window, select the naming options you want. See Figure 22-13.

  4. Click OK.

Figure 22-13. Creating multiple named regions with one procedure


Deleting Area Names

You can delete the name assignments you've given to areas. Deleting names doesn't affect the data areas they were assigned to. However, if you delete the names of areas you've used in formulas (such as =sum(Q1) ), the formulas will no longer work. Also, if you delete a name that was used to create a hyperlink to the area by dragging from Navigator, the hyperlink will no longer work.

  1. Choose Insert > Names > Define.

  2. In the Define Names window, select a name you want to delete, and click the Delete button.

  3. Click Yes in the confirmation window.

  4. Click OK.


Pointing to Cell References and Errors

Cells can sometimes contain references to a lot of other cells , especially when you use more complex formulas and functions. Also, a single cell can be referenced in a lot of other cells. Calc has a couple of great tools that draw arrows to or from a selected cell, pointing to the other cells that the current cell references, called precedents , or the cells that reference the current cell, called dependents .

Tracing precedents and dependents are great for troubleshooting, because they show you exactly which cells are used in a calculation, making it easier to spot incorrect cell references.

Calc also includes an error tracing tool, which points to the cells causing an error in a particular cell.

  1. Select the cell you want to trace.

  2. Choose Tools > Detective > (Trace Precedents, Trace Dependents, or Trace Error).

Arrows appear, pointing to the relevant references, as illustrated in Figure 22-14.

Figure 22-14. Tracing precedents, dependents, and errors


You can remove the arrows by choosing Tools > Detective, and selecting the relevant remove item. If you're going to use traces on a regular basis, consider assigning a shortcut key removing traces. See Assigning Shortcut Keys to Menu Items on page 104. Select the Options category, then select the Remove All Traces command.