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
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:
$ , % ( )
Search JabSto ::

Custom Search