If you think about it, you’ll realize that the only reason why you would ever need to change a reference is if you plan to copy the formula. Figure 3-1 demonstrates why this is so. The formula in cell C3 is

=$B3*C$2

Figure 3-1. An example of using nonrelative references in a formula.

This formula calculates the area for various lengths (listed in column B) and widths (listed in row 3). After the formula is entered, it can then be copied down to C7 and across to F7. Because the formula uses absolute references to row 2 and column B and relative references for other rows and columns, each copied formula produces the correct result. If the formula used only relative references, copying the formula would cause all the references to adjust and thus produce incorrect results.

Normally, Excel uses what’s known as A1 notation: Each cell address consists of a column letter and a row number. However, Excel also supports R1C1 notation. In this system, cell A1 is referred to as cell R1C1, cell A2 as R2C1, and so on.

To change to R1C1 notation, access the Formulas tab of the Excel Options dialog box. Place a check mark next to R1C1 Reference Style. After you do so, you’ll notice that the column letters all change to numbers. All the cell and range references in your formulas are also adjusted.

Table 3-1 presents some examples of formulas that use standard notation and R1C1 notation. The formula is assumed to be in cell B1 (also known as R1C2).

If you find R1C1 notation confusing, you’re not alone. R1C1 notation isn’t too bad when you’re dealing with absolute references. But when relative references are involved, the brackets can be very confusing.

The numbers in brackets refer to the relative position of the references. For example, R[–5]C[–3] specifies the cell that’s five rows above and three columns to the left. On the other hand, R[5]C[3] references the cell that’s five rows below and three columns to the right. If the brackets are omitted, the notation specifies the same row or column. For example, R[5]C refers to the cell five rows below in the same column.

Although you probably won’t use R1C1 notation as your standard system, it does have at least one good use. Using R1C1 notation makes it very easy to spot an erroneous formula. When you copy a formula, every copied formula is exactly the same in R1C1 notation. This is true regardless of the types of cell references that you use (relative, absolute, or mixed). Therefore, you can switch to R1C1 notation and check your copied formulas. If one looks different from its surrounding formulas, there’s a good chance that it might be incorrect.

In addition, if you write VBA code to create worksheet formulas, you might find it easier to create the formulas by using R1C1 notation.

When a formula refers to other cells, the references need not be on the same sheet as the formula. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Here’s an example of a formula that uses a cell reference in a different worksheet (Sheet2):

=Sheet2!A1+1

You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name (in square brackets), the worksheet name, and an exclamation point. Here’s an example:

=[Budget.xlsx]Sheet1!A1

If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example:

='[Budget For 2008.xlsx]Sheet1'!A1

Referencing Data in a Table

Excel 2007 supports a special type of range that has been designated as a table (using the Insert Tables Table command). Tables add a few new twists to formulas.

When you enter a formula into a cell in a table, Excel automatically copies the formula to all of the other cells in the column — but only if the column was empty. This is known as a calculated column. If you add a new row to the table, the calculated column formula is entered automatically for the new row. Most of the time, this is exactly what you want. If you don’t like the idea of Excel entering formulas for you, use the SmartTag to turn this feature off. The SmartTag appears after Excel enters the calculated column formula.

Excel 2007 also supports “structured referencing” for referring to cells within a table. The table in the accompanying figure is named Table1.

You can create formulas that refer to cells within the table by using the column headers. In some cases, this may make your formulas easier to understand. But the real advantage is that your formulas will continue to be valid if rows are added or removed from the table. For example, these are all valid formulas:

=SUM(Table1[Washington])
=Table1[[#Totals],[California]]
=Table1[[#Headers],[California]]
=SUM(Table1[[#This Row],[California]:[Washington]])

The last formula, which uses [#This Row], is valid only if it’s in a cell in one of the rows occupied by the table.

If the linked workbook is closed, you must add the complete path to the workbook reference. Here’s an example:

='C:BudgetingExcel Files[Budget For 2008.xlsx]Sheet1'!A1

Although you can enter link formulas directly, you can also create the reference by using normal pointing methods. To do so, the source file must be open. When you do so, Excel creates absolute cell references. If you plan to copy the formula to other cells, make the references relative.

Working with links can be tricky. For example, if you choose the Office Save As command to make a backup copy of the source workbook, you automatically change the link formulas to refer to the new file (not usually what you want to do). Another way to mess up your links is to rename the source workbook when the dependent workbook is not open.

Excel provides several ways to name a cell or range:

Figure 3-2. Excel makes it easy to create names that use descriptive text in your worksheet.

Using names is especially important if you write VBA code that uses cell or range references. The reason? VBA does not automatically update its references if you move a cell or range that’s referred to in a VBA statement. For example, if your VBA code writes a value to Range(“C4”), the data will be written to the wrong cell if the user inserts a new row above or a new column to the left of cell C4. Using a reference to a named cell, such as Range(“InterestRate”), avoids these potential problems.

When you create a name for a cell or a range, Excel doesn’t automatically use the name in place of existing references in your formulas. For example, assume that you have the following formula in cell F10:

=A1-A2

If you define the names Income for A1 and Expenses for A2, Excel will not automatically change your formula to

=Income-Expenses

However, it’s fairly easy to replace cell or range references with their corresponding names. Start by selecting the range that contains the formulas that you want to modify. Then choose the Formulas Defined Names Name a Range Apply Names. In the Apply Names dialog box, select the names that you want to apply and then click OK. Excel replaces the range references with the names in the selected cells.

Note

Unfortunately, there is no way to automatically unapply names. In other words, if a formula uses a name, you can’t convert the name to an actual cell or range reference. Even worse, if you delete a name that is used in a formula, the formula does not revert to the cell or range address — it simply returns a #NAME? error.

My Power Utility Pak add-in (available at a discount by using the coupon in the back of the book) includes a utility that scans all formulas in a selection and automatically replaces names with their cell addresses.

Sub DeleteHiddenNames()
    Dim n As Name
    Dim Count As Integer
    For Each n In ActiveWorkbook.Names
        If Not n.Visible Then
            n.Delete
            Count = Count + 1
        End If
    Next n
    MsgBox Count & " hidden names were deleted."
End Sub

Excel has a special operator called the intersection operator that comes into play when you’re dealing with ranges. This operator is a space character. Using names with the intersection operator makes it very easy to create meaningful formulas. For this example, refer to Figure 3-2. If you enter the following formula into a cell

=Qtr_2 South

the result is 732 — the intersection of the Qtr2 range and the South range.

Excel lets you name complete rows and columns. In the preceding example, the name Qtr1 is assigned to the range B3:B6. Alternatively, Qtr1 could be assigned to all of column B, Qtr2 to column C, and so on. You also can do the same horizontally so that North refers to row 3, South to row 4, and so on.

The intersection operator works exactly as before, but now you can add more regions or quarters without having to change the existing names.

When naming columns and rows, make sure that you don’t store any extraneous information in named rows or columns. For example, remember that if you insert a value in cell C7, it is included in the Qtr1 range.

A named cell or range normally has a workbook-level scope. In other words, you can use the name in any worksheet in the workbook.

Another option is to create names that have a worksheet-level scope. To create a worksheet-level name, define the name by preceding it with the worksheet name followed by an exclamation point: for example, Sheet1!Sales. If the name is used on the sheet in which it is designed, you can omit the sheet qualifier when you reference the name. You can, however, reference a worksheet-level name on a different sheet if you precede the name with the sheet qualifier.

The Name Manager dialog box (Formulas Defined Names Name Manager) makes it easy to identify names by their scope (see Figure 3-3). Note that you can sort the names within this dialog box. For example, click the Scope column header, and the names are sorted by scope.

Figure 3-3. The Name Manager displays the scope for each defined name.

Virtually every experienced Excel user knows how to create cell and range names (although not all Excel users actually do so). But most Excel users do not know that you can use names to refer to values that don’t appear in your worksheet — that is, constants.

Suppose that many formulas in your worksheet need to use a particular interest rate value. One approach is to type the interest rate into a cell and give that cell a name, such as InterestRate. After doing so, you can use that name in your formulas, like this:

=InterestRate*A3

An alternative is to call up the New Name dialog box (Formulas Defined Names Define Name) and enter the interest rate directly into the Refers To box (see Figure 3-4). Then you can use the name in your formulas just as if the value were stored in a cell. If the interest rate changes, just change the definition for InterestRate, and Excel updates all the cells that contain this name.

Figure 3-4. Excel lets you name constants that don’t appear in worksheet cells.

Tip

This technique also works for text. For example, you can define the name IWC to stand for International Widget Corporation. Then you can enter =IWC into a cell, and the cell displays the full name.

In addition to naming cells, ranges, and constants, you can also create named formulas. To do so, enter a formula directly into the Refers To field in the New Name dialog box.

Note

This is a very important point: The formula that you enter uses cell references relative to the active cell at the time that you create the named formula.

Figure 3-5 shows a formula (=A1^B1) entered directly in the Refers To box in the New Name dialog box. In this case, the active cell is C1, so the formula refers to the two cells to its left. (Notice that the cell references are relative.) After this name is defined, entering =Power into a cell raises the value two cells to the left to the power represented by the cell directly to the left. For example, if B10 contains 3 and C10 contains 4, entering the following formula into cell D10 returns a value of 81 (3 to the 4th power):

Figure 3-5. You can name a formula that doesn’t appear in any worksheet cell.

=Power

When you display the New Name dialog box after creating the named formula, the Refers To box displays a formula that is relative to the current active cell. For example, if cell D32 is the active cell, the Refers To box displays

=Sheet1!B32^Sheet1!C32

Notice that Excel appends the worksheet name to the cell references used in your formula. This, of course, will cause the named formula to produce incorrect results if you use it on a worksheet other than the one in which it was defined. If you would like to use this named formula on a sheet other than Sheet1, you need to remove the sheet references from the formula (but keep the exclamation points). For example:

=!A1^!B1

After you understand the concept, you might discover some new uses for named formulas. One distinct advantage is apparent if you need to modify the formula. You can just change the formula one time rather than edit each occurrence of the formula.

CD-ROM

The companion CD-ROM contains a workbook with several examples of named formulas. The workbook is called named formulas.xlsx.

Tip

When you’re working in the New Name dialog box, the Refers To field is normally in “point mode,” which makes it easy to enter a range reference by clicking in the worksheet. Press F2 to toggle between point mode and normal editing mode, which allows you to use the arrow keys to edit the formula.

In addition to providing names for cells and ranges, you can give more meaningful names to objects such as pivot tables and shapes. This can make it easier to refer to such objects, especially when you refer to them in your VBA code.

To change the name of a nonrange object, use the Name box, which is located to the left of the formula bar. Just select the object, type the new name in the Name box, and then press Enter.

Note

If you simply click elsewhere in your workbook after typing the name in the Name box, the name won’t stick. You must press Enter.

For some reason, Excel 2007 does not allow you to use the Name box to rename a chart. You must use Chart Tools Layout Properties Chart Name.

Excel’s array formulas enable you to perform individual operations on each cell in a range in much the same way that a programming language’s looping feature enables you to work with elements of an array. If you’ve never used array formulas before, this section will get your feet wet with a hands-on example.

Figure 3-6 shows a worksheet with text in A1:A5. The goal of this exercise is to create a single formula that returns the sum of the total number of characters in the range. Without the single formula requirement, you would write a formula with the LEN function, copy it down the column, and then use the SUM function to add the results of the intermediate formulas.

Figure 3-6. Cell B1 contains an array formula that returns the total number of characters contained in range A1:A5. Notice the brackets in the formula bar.

To demonstrate how an array formula can occupy more than one cell, create the worksheet shown in the figure and then try this:

The preceding steps enter a single array formula into five cells. Enter a SUM formula that adds the values in B1:B5, and you’ll see that the total number of characters in A1:A5 is 29.

Here’s the key point: It’s not necessary to actually display those five array elements. Rather, Excel can store the array in memory. Knowing this, you can type the following single array formula in any blank cell (Remember: Don’t type the curly brackets, and make sure that you enter it by pressing Ctrl+Shift+Enter):

{=SUM(LEN(A1:A5))}

This formula essentially creates a five-element array (in memory) that consists of the length of each string in A1:A5. The SUM function uses this array as its argument, and the formula returns 29.

Figure 3-7 shows a worksheet set up to display a calendar for any month (change the month, and the calendar updates). Believe it or not, the calendar is created with a single array formula that occupies 42 cells.

Figure 3-7. A single multicell array formula is all it takes to make a calendar for any month in any year.

The array formula, entered in the range B5:H10, is:

{=IF(MONTH(DATE(YEAR(B3),MONTH(B3),1))<>MONTH(DATE(YEAR(B3),
MONTH(B3),1)-(WEEKDAY(DATE(YEAR(B3),MONTH(B3),1))-1)
+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)," ",
DATE(YEAR(B3),MONTH(B3),1)-(WEEKDAY(DATE(YEAR(B3),
MONTH(B3),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

CD-ROM

The companion CD-ROM contains a workbook with the calendar example, as well as several additional array formula examples. The file is named array formula examples.xlsx.

The advantages of using array formulas rather than single-cell formulas include the following:

A few disadvantages of using array formulas are the following:

Table 3-3 contains formulas that demonstrate a variety of counting techniques.

Table 3-4 shows a number of formula examples that demonstrate a variety of summing techniques.

Other ways to count or sum cells that meet certain criteria are:

For more information, consult the Help system.

When working with times, you simply enter the time into a cell in a recognized format. Excel’s system for representing dates as individual values is extended to include decimals that represent portions or fractions of days. In other words, Excel perceives all time with the same system whether that time is a particular day, a certain hour, or a specific second. For example, the date serial number for August 15, 2007, is 39309. Noon (halfway through the day) is represented internally as 39309.5. Again, you normally don’t have to be concerned with these fractional serial numbers.

Because dates and times are stored as serial numbers, it stands to reason that you can add and subtract dates and times. For example, you can enter a formula to calculate the number of days between two dates. If cells A1 and A2 both contain dates, the following formula returns the number of intervening days:

=A2-A1

Tip

When performing calculations with time, things get a bit trickier. When you enter a time without an associated date, the date is assumed to be January 0, 1900 (date serial number 0). This is not a problem — unless your calculation produces a negative time value. When this happens, Excel displays an error (displayed as #########). The solution? Switch to the 1904 date system. Display the Excel Options dialog box, click the Advanced tab, and then enable the Use 1904 Date System check box. Be aware that switching to the 1904 date system can cause problems with dates already entered in your file or dates in workbooks that are linked to your file.

Tip

In some cases, you may need to use time values to represent duration, rather than a point in time. For example, you may need to sum the number of hours worked in a week. When you add time values, you can’t display more than 24 hours. For each 24-hour period, Excel simply adds another day to the total. The solution is to change the number formatting to use square brackets around the hour part of the format. The following number format, for example, displays more than 24 hours:

[hh]:mm

The world, of course, didn’t begin on January 1, 1900. People who work with historical information when using Excel often need to work with dates before January 1, 1900. Unfortunately, the only way to work with pre-1900 dates is to enter the date into a cell as text. For example, you can enter the following into a cell, and Excel won’t complain:

July 4, 1776

You can’t, however, perform any manipulation on dates that are actually text. For example, you can’t change its formatting, you can’t determine which day of the week this date occurred on, and you can’t calculate the date that occurs seven days later. (See Figure 3-9.)

Figure 3-9. The Extended Date Functions add-in lets you work with pre-1900 dates.