Moving Around

Other than using scroll bars and the Page Up and Page Down keys, there are many other ways to get around, including some unique tricks you’ll find only in Excel.

Navigating Regions

A region is a range of cell entries bounded by blank cells or column and row headings.

In In the worksheet in Regional Sales.xls, the range A3:E7 is a region, as are the ranges G3:H7, A9:E10, and G9:H10. (Strictly speaking, cell A1 is also a region because there are no adjoining cells containing entries.) For example, cell E7 is within a region, even though it’s empty.

The active area is a rectangle that encompasses all regions—that is, all the filled cells in the active worksheet.

The techniques used to navigate regions are especially helpful if you typically work with large tables of data. Getting to the bottom row of a 500-row table is easier when you don’t have to use the scroll bars.

Navigating Regions with the Keyboard

To move between the edges of regions, hold down the Ctrl key while pressing any of the arrow keys. In our example, cell A3 is the active cell; press Ctrl+Right Arrow to activate cell E3. If a blank cell is active when you press Ctrl and an arrow key, Excel moves to the first filled cell in that direction or to the last available cell in the worksheet if there are no filled cells in that direction. In Figure 5-1, for example, suppose cell F3 is active; if you press Ctrl+Right Arrow, the selection moves to cell G3.

Navigating Regions with the Mouse

When you move the mouse pointer over the edge of the active cell’s border, the pointer changes from a plus sign to an arrow. With the arrow pointer visible, you can double-click any edge of the border to change the active cell to the cell on the edge of the current region in that direction—it is the same as pressing Ctrl and an arrow key in that direction.

For example, if you double-click the bottom edge of the active cell in our example, Excel selects cell A7.

Selecting Stuff

Before you can work with a cell or range, you must select it, and when you do, it becomes active.

The reference of the active cell appears in the Name box at the left end of the formula bar.

Only one cell can be active at a time, but you can select ranges of cells. You can change the active cell in a selected range without changing your range selection.

Selecting with the Mouse

To select a range of cells, drag the mouse over the range. Alternatively, instead of dragging through all the cells you want to select, you can use a technique known as extending to indicate two diagonal corners of the range. Simply click the cell at one corner of the range you want to select, then hold Shift and click the cell at the corner of the range diagonal to the selected cell. For example, to extend the selection A1:B5 so that it becomes A1:C10, hold down the Shift key and click cell C10. When you need to select a large range, this technique is more efficient than dragging the mouse across the entire selection.

Selecting Beyond the Window Borders

It’s impossible to see an entire workbook on the screen. Knowing that, what do you do if you need to select a gigantic range of cells? You can drag the mouse pointer past the window border and wait for the automatic scrolling to get you where you need to go, but this method can be frustrating if you have trouble managing the scrolling speed and keep overshooting the target. A better method is to use the Zoom command to get a bird’s-eye view of the worksheet. Choose View, Zoom, or use the Zoom box on the Standard toolbar, and select or type the zoom percentage you want. The Zoom feature is limited to a range from 10 through 400 percent.

Selecting Columns, Rows, and Multiple Areas

Multiple-area ranges (also known as nonadjacent or noncontiguous ranges) are cell ranges that do not encompass a single rectangular area.

To select multiple area ranges with the mouse, use the Ctrl key and drag through each range you want to select. The first cell you click in the last range you select becomes the active cell.

To select an entire column or row, click the column or row heading. In other words, to select cells B1 through B65536, click the heading for column B. The first visible cell in the column becomes the active cell. To select more than one adjacent column or row at a time, drag through the column or row headings, or click the heading at one end of the range, press Shift, and then click the heading at the other end. To select nonadjacent columns or rows, hold down Ctrl and click each heading or drag through adjacent headings that you want to select.

Entering Stuff

Excel accepts two basic types of cell entries: constants and formulas. Constants fall into three main categories: numeric values, text values (also called labels or strings), and date/time values. Excel also recognizes two special types of constants called logical values and error values.

Making Entries in Cells and in the Formula Bar

To make an entry into a cell, just select the cell and start typing. As you type, the entry appears both in the formula bar and in the active cell. The flashing vertical bar in the active cell is called the insertion point.

After you finish typing, you must “lock in” the entry to store it permanently in the cell by pressing Enter. Pressing Enter normally causes the active cell to move down one row. You can change this so that when you press Enter, either the active cell doesn’t change or it moves to an adjacent cell in another direction. Choose Tools, Options, click the Edit tab, and either clear the Move Selection After Enter option or change the selection in the Direction list. You can also lock in an entry when you move the selection to a different cell by pressing Tab, Shift+Tab, Shift+Enter, or an arrow key, among other methods,

When you begin typing an entry, three buttons appear on the formula bar: the Cancel button, the Enter button, and the Insert Function button. When typing a formula where the entry begins with an equal sign (=), a plus sign (+), or a minus sign (–), a drop-down list of frequently used functions becomes available.

Entering Simple Numeric and Text Values

An entry that includes only numerals 0 through 9 and certain special characters, such as + – E e ( ) . , $ % and /, is a numeric value. An entry that includes almost any other character is a text value.

Using Special Characters A number of characters have special effects in Excel. Here are some guidelines for using special characters:

If you begin a numeric entry with a plus sign (+), Excel drops the plus sign.

If you begin a numeric entry with a minus sign (.), Excel interprets the entry as a negative number and retains the sign.

In a numeric entry, the characters E and e specify an exponent used in scientific notation. For example, it interprets 1E6 as 1,000,000 (1 times 10 to the sixth power), which is displayed in Excel as 1.00E+06. To enter a negative exponential number, enter a minus sign before the exponent. For example, .1E6 (1 times 10 to the negative sixth power) equals .1,000,000, and is displayed in Excel as 1.00E-06.

Excel interprets numeric constants enclosed in parentheses as negative numbers, which is a common accounting practice. For example, Excel interprets (100) as -100.

You can use decimal points and commas as you normally would. When you enter numbers that include commas as separators, however, the commas appear in the cell but not in the formula bar; this is the same as if you had applied one of Excels built-in Number formats. For example, if you enter 1,234.56, 1234.56 is displayed in the formula bar.

If you begin a numeric entry with a dollar sign, Excel assigns a Currency format to the cell. For example, if you enter $123456, Excel displays $123,456 in the cell and 123456 in the formula bar. In this case, Excel adds the comma to the worksheet display because it is part of the Currency format.

If you end a numeric entry with a percent sign (%), Excel assigns a Percentage format to the cell. For example, if you enter 23%, Excel displays 23% in the formula bar and assigns a Percentage format to the cell, which also displays 23%.

If you use a forward slash (/) in a numeric entry and the string cannot be interpreted as a date, Excel interprets the number as a fraction. For example, if you enter 11 5/8 (with a space between the number and the fraction), Excel assigns a Fraction format to the entry, meaning that the formula bar displays 11.625 and the cell displays 11 5/8.

To make sure Excel does not interpret a fraction as a date, precede the fraction with a zero and a space. For example, to prevent Excel from interpreting the fraction 1/2 as January 2, type 0 1/2.

Understanding the Difference Between Displayed Values and Underlying Values

Although you can type 32,767 characters into a cell, a numeric cell entry can maintain precision to only a maximum of 15 digits. This means that you can enter numbers longer than 15 digits into a cell, but Excel converts any digits after the fifteenth to zeros.

If you enter a number that is too long to appear in a cell, Excel converts it to scientific notation in the cell, if no other formatting has been applied. Excel adjusts the precision of the scientific notation depending on the cell width. If you enter a very large or a very small number that is longer than the formula bar, Excel displays it in the formula bar using scientific notation.

The values that appear in formatted cells are called displayed values; the values that are stored in cells and that appear in the formula bar are called underlying values. The number of digits that appear in a cell—its displayed value—depends on the width of the column and any formatting that you have applied to the cell. If you reduce the width of a column that contains a long entry, Excel might display a rounded-off version of the number, a string of pound signs (#), or scientific notation, depending on the display format you’re using.

If you see a series of pound signs (######) in a cell where you expect to see a number, simply increase the width of the cell to see the numbers again.

Troubleshooting: My formulas don’t add numbers correctly

Suppose, for example, you write a formula and Excel tells you that $2.23 plus $5.55 equals $7.79, when it should be $7.78. Check your underlying values. If you use currency formatting, numbers with more than three digits to the right of the decimal point are rounded to two decimal places. In your example, if the underlying vales are 2.234 and 5.552, the result is 7.786, which rounds out to 7.79. You can either change the decimal places, or you can use the Precision As Displayed option (Tools, Options, Calculation tab) to eliminate the problem. Be careful if you use Precision As Displayed, however, because it permanently changes all the underlying values in your worksheet to their displayed values.