Microsoft Office Tutorials and References

In Depth Information

**Editing formulas**

Editing formulas

You edit formulas the same way you edit text entries: click in the cell or formula bar, click or

drag to select characters, press Backspace or Delete or start typing. To replace a cell

reference, highlight it and click the new cell you want the formula to use; Excel enters a relative

reference automatically. You can also just click to place the insertion point where in the

formula you want to insert a reference. To include cell B1 in the formula =A1+A3, place the

insertion point between A1 and the plus sign, type another plus sign, and then click cell B1.

Excel inserts the reference and the formula becomes =A1+B1+A3.

Understanding reference syntax

So far, we have used the default worksheet and workbook names for the examples in this

book. When you save a workbook, you must give it a permanent name. If you create a

formula first and then save the workbook with a new name, Excel adjusts the formula

accordingly. For example, if you save Book2 as
Sales.xlsx
, Excel changes the remote reference

formula =[Book2]Sheet2!$A$2 to =[Sales.xlsx]Sheet2!$A$2. And if you rename Sheet2 of

Sales.xlsx to
February
, Excel changes the reference to =[Sales.xlsx]February!$A$2. If the

referenced workbook is closed, Excel displays the full path to the folder where the workbook is

stored in the reference, as shown in the example ='C:\Work\[Sales.xlsx]February'!$A$2.

In the preceding example, note that apostrophes surround the workbook and worksheet

portion of the reference. Excel adds the apostrophes around the path when you close the

workbook. If you type a new reference to a closed workbook, however, you must add the

apostrophes yourself. To avoid typing errors, it is best to work with the linked workbooks

open and click cells to enter references so that Excel inserts them in the correct syntax for

you.

Using numeric text in formulas

The seemingly oxymoronic term
numeric text
refers to an entry that is not strictly numbers

but includes both numbers and a few specific text characters. You can perform

mathematical operations on numeric text values as long as the numeric string uses only the following

characters in very specific ways:

0 1 2 3 4 5 6 7 8 9 . + – E e

In addition, you can use the forward slash (/) character in fractions. You can also use the

following five number-formatting characters:

$ , % ( )