Ms Excel - Cell References and Entries

Cell References

To refer to a specific cell we use a cell reference. This is a combination of the column heading and the row number. The cell at the top left, which is at the intersection of column A and row 1, has a cell address of A1. The cell below is A2 while the cell to the right is B1. This method of naming cells using the column letter is called the A1 method. To reference a cell on another sheet of the same workbook, we use the form Sheet2!B4 – note the exclamation mark. To reference a cell in another workbook we can use the form 'C:\My Documents\[Book2.xls]Sheet1'!$A$1. We discuss the dollar signs in a cell reference later in this chapter.

Cell Entries

A cell may contain data or a formula. A data entry may be: a number, text (sometimes called labels) or a date. Formulas begin with an equal sign (=). We discuss formulas in detail in the next unit.

You may wish to experiment by making the worksheet shown in Figure 4. Type the entries in cell A1, C1, G1 and in row 2. For example, with A1 as the active cell, type the word Text and press the R key to complete the entry. We will discuss other ways of completing a entry later. Click on C1 and enter the next piece of data.

A

B

C

D

E

F

G

1

Text

This is an example of a long text entry

123456789

2

123

1.5

1.55

1 1/2

1.23457E+14

Figure 4

A number of observations may be made about how entries are initially treated. Later we

will discuss how formatting may be used to change the appearance.

1) Text is left aligned by default. By this we mean that on a newly opened worksheet any text typed into a cell will be placed to the left within the cell. The alignment may be changed by formatting the cell.

2) By default, numbers are left aligned.

3) Text can overflow into empty adjacent cells as shown by the entry in C2. If you type anything in D2, only part of C2's entry will be visible in the cell but all of it will be visible in the Formula bar.

4) When a large number (not too large) is entered, the column automatically widens to accommodate all the digits.

5) Very large numbers (the value typed into G2 was 123456789123456) are converted to exponential (sometimes called scientific) notation. The displayed value 1.23457E+14 means the same as 1.234567 × 10¹⁴. In the next unit we discuss Excel’s precision.

6) A whole number can have no more with than 15 digits. You are unlikely to be working with numbers that large. Objects we often call numbers are really just a string of digits. For example, phone numbers and account numbers are not real numbers in that we never perform arithmetic operations on them. In these cases it is better to enter the value by first typing a single quote (it is found on the key next to R). This quote mark will not be displayed in the cell but will cause the entry to be treated as text.

7) By default, Excel does not display what it considers to be insignificant digits. Thus if you type 1.50 in B2, the value 1.5 will be displayed in the cell and in the formula bar. Again, formatting can be used to change this.

8) Fractions may be entered as in D2. Note there is a space between the 1 and the 1/2. If you wish to enter a fraction (for example, 1/2) without a whole number, you must enter it with a preceding 0 followed by a space (e.g. 0 1/2). The zero will not be displayed when the entry is completed. In all cases, the values displayed in the Formula bar will be in decimal form (e.g. 1.5, or 0.5).

9) To evaluate a number in fractional notations, enter it as a formula. Thus to display the result of 1½ + 3¾ enter = 1+1/2+3+3/4. See the next unit for more on formulas.

10) Improper fractions are converted on entry. Thus 1 4/8 will be converted to 1 1/2. In certain cases (when the denominator is 2, 4, 8, 16, 10 or 100) we may format the cell to overcome this.

11) If you enter something like 4/12 without a leading zero, Excel will take this to mean a date – either 4 December or 12 April depending on your Regional Setting. We will not be discussing dates in this supplement.

We indicated above that you need to let Excel know when you have finished entering something in a cell and recommended the use of the R key. This is not the only way. Any of the navigation keys (B, L , R , T) and T may be used. Alternatively, you may click the Tin the Formula bar – this is displayed only while a cell entry is being made or edited. Do not get into the bad habit of clicking the mouse on another cell to complete an entry. While this will work when you are entering data it will cause you grief when working with formulas.