Editing Formulas:

You edit formulas the same way you edit text entries. To delete characters in a formula, drag through the characters in the cell or the formula bar and press Backspace or Delete. To replace a character, highlight it and type its replacement. To replace a reference, highlight it and then click the new cell you want the formula to use. A relative reference is entered automatically. You can also insert additional cell references in a formula. For example, to insert a reference to cell B1 in the formula =A1+A3, simply move the insertion point between A1 and the plus sign and either type +B1 or type a plus sign and click cell B1. 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, the formula is adjusted accordingly. For example, if you save Book2 as Sales.xls, the remote reference formula =[Book2]Sheet2!$A$2 changes to =[Sales.xls]Sheet2!$A$2. And if you rename Sheet2 of Sales.xls to February, the reference changes to =[Sales.xls]February!$A$2. If the referenced workbook is closed, the full path to the folder where the workbook is stored appears in the reference, as shown in the example ='C:\Work\[Sales.xls]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, open the closed workbook and click cells with the mouse to enter references so that Excel inserts them in the correct syntax for you.

Using Numeric Text in Formulas:

The 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 contains only the following characters:

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

In addition, you can use the / character in fractions. You can also use the following five number-formatting characters:

$ , % ( )

You must enclose numeric text strings in quotation marks. For example, if you type the formula =$1234+$123, Excel displays an error message stating that Excel found an error in the formula you entered. (The error message also offers to correct the error for you by removing the dollar signs.) But the formula ="$1234"+"$123" produces the result 1357 (ignoring the dollar signs). When Excel performs the addition, it automatically translates numeric text entries into numeric values.

About Text Values:

The term “text values” refers to any entry that is neither a number nor a numeric text value; the entry is treated as text only. You manipulate text values in the same way that you manipulate numeric values. For example, if cell A1 contains the text ABCDE and you type the formula =A1 in cell A10, cell A10 displays ABCDE.

You can use the & (ampersand) operator to concatenate, or join, several text values.

Extending the preceding example, if cell A2 contains the text FGHIJ and you type the formula =A1&A2 in cell A3, cell A3 displays ABCDEFGHIJ. To include a space between the two strings, change the formula to =A1&" "&A2. This formula uses two concatenation operators and a literal string, or string constant (a space enclosed in quotation marks).

You can use the & operator to concatenate strings of numeric values as well. For example, if cell A3 contains the numeric value 123 and cell A4 contains the numeric value 456, the formula =A3&A4 produces the string 123456. This string is left aligned in the cell because it’s considered a text value. (Remember, you can use numeric text values to perform any mathematical operation as long as the numeric string contains only the numeric characters listed on the previous page.)

Finally, you can use the & operator to concatenate a text value and a numeric value. For example, if cell A1 contains the text ABCDE and cell A3 contains the numeric value 123, the formula =A1&A3 produces the string ABCDE123. You can find the sample file used in this example as Concatenation.xls.