As you probably already know, you can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you’d expect: The down arrow moves the active cell down one row, the right arrow moves it one column to the right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.)

The Num Lock key on your keyboard controls how the keys on the numeric keypad behave. When Num Lock is on, Excel displays Num Lock in the status bar, and the keys on your numeric keypad generate numbers. Most keyboards have a separate set of navigational (arrow) keys located to the left of the numeric keypad. The state of the Num Lock key doesn’t affect these keys.

Table 1.2 summarizes all the worksheet movement keys available in Excel.

To change the active cell by using the mouse, click another cell; it becomes the active cell. If the cell that you want to activate isn’t visible in the workbook window, you can use the scrollbars to scroll the window in any direction. To scroll one cell, click either of the arrows on the scrollbar. To scroll by a complete screen, click either side of the scrollbar’s scroll box. You also can drag the scroll box for faster scrolling.

Press Ctrl while you use the mouse wheel to zoom the worksheet. If you prefer to use the mouse wheel to zoom the worksheet without pressing Ctrl, choose Office Excel Options and select the Advanced section. Place a check mark next to the Zoom On Roll With Intellimouse check box.

Using the scrollbars or scrolling with your mouse doesn’t change the active cell. It simply scrolls the worksheet. To change the active cell, you must click a new cell after scrolling.

The commands available in the Ribbon vary, depending upon which tab is selected. The Ribbon is arranged into groups of related commands. Here’s a quick overview of Excel’s tabs.

The appearance of the commands on the ribbon varies, depending on the width of Excel window. When the window is too narrow to display everything, the commands adapt and may seem to be missing. But the commands are still available. Figure 1.3 shows the Home tab of the Ribbon with all controls fully visible. Figure 1.4 shows the Ribbon when Excel’s window is made more narrow. Notice that some of the descriptive text is gone, but the icons remain. Figure 1.5 shows the extreme case when the window is made very narrow. Some groups display a single icon. However, if you click the icon, all the group commands are available to you.

Figure 1.3. The Home tab of the Ribbon.

Figure 1.4. The Home tab when Excel’s window is made narrower.

Figure 1.5. The Home tab when Excel’s window is made very narrow.

In addition to the standard tabs, Excel 2007 also includes contextual tabs. Whenever an object (such as a chart, a table, or a SmartArt diagram) is selected, specific tools for working with that object are made available in the Ribbon.

Figure 1.6 shows the contextual tab that appears when a chart is selected. In this case, it has three contextual tabs: Design, Layout, and Format. Notice that the contextual tabs contain a description (Chart Tools) in Excel’s title bar. When contextual tabs appear, you can, of course, continue to use all the other tabs.

Figure 1.6. When you select an object, contextual tabs contain tools for working with that object.

When you hover your mouse pointer over a Ribbon command, you’ll see a pop-up box that contains the command’s name, and a brief description. For the most part, the commands in the Ribbon work just as you would expect them to. You do encounter several different styles of commands on the Ribbon:

Some of the Ribbon groups contain a small icon on the right side, known as a dialog launcher. For example, if you examine the Home Alignment group, you see this icon (refer to Figure 1.8). Click it, and Excel displays the Format Cells dialog box, with the Alignment tab pre-selected. The dialog launchers generally provide options that aren’t available in the Ribbon.

Figure 1.8. Some of the Ribbon groups contain a small icon on the right side, known as a dialog launcher.

At first glance, you may think that the Ribbon is completely mouse-centric. After all, none of the commands have the traditional underline letter to indicate the Alt+keystrokes. But in fact, the Ribbon is very keyboard friendly. The trick is to press the Alt key to display the pop-up keytips. Each Ribbon control has a letter (or series of letters) that you type to issue the command.

Figure 1.9 shows how the Home tab looks after I hit the Alt key to display the keytips. If you press one of the keytips, the screen then displays more keytips. For example, to use the keyboard to align the cell contents to the left, press Alt, followed by H (for Home) and then AL (for Align Left). If you’re a keyboard fan (like me), it takes just a few times before you memorize the keystrokes required for commands that you use frequently.

Figure 1.9. Pressing Alt displays the keytips.

After you press Alt, you can also use the left and right arrow keys to scroll through the tabs. When you reach the proper tab, press down arrow to enter the Ribbon. Then use left and right arrow keys to scroll through the Ribbon commands. When you reach the command you need, press Enter to execute it. This method isn’t as efficient as using the keytips, but it’s a quick way to take a look at the commands available.

Navigating dialog boxes is generally very easy—you simply click the control you want to activate.

Although dialog boxes were designed with mouse users in mind, you can also use the keyboard. Every dialog box control has text associated with it, and this text always has one underlined letter (a hot key or accelerator key). You can access the control from the keyboard by pressing the Alt key and then the underlined letter. You also can use Tab to cycle through all the controls on a dialog box. Shift+Tab cycles through the controls in reverse order.

Many of Excel’s dialog boxes are “tabbed” dialog boxes. A tabbed dialog box includes notebook-like tabs, each of which is associated with a different panel.

When you click a tab, the dialog box changes to display a new panel containing a new set of controls. The Format Cells dialog box is a good example. This dialog box is shown in Figure 1.13; it has six tabs, which makes it functionally equivalent to six different dialog boxes.

Figure 1.13. Use the dialog box tabs to select different functional areas in the dialog box.

Tabbed dialog boxes are quite convenient because you can make several changes in a single dialog box. After you make all your setting changes, click OK or press Enter.

Excel 2007 introduced a new style of modeless tabbed dialog box in which the tabs are on the left, rather than across the top. An example is shown in Figure 1.14. To select a tab using the keyboard, use the up and down arrow keys and then press Tab to access the controls.

Figure 1.14. A “new style” tabbed dialog box with tabs on the left.

Start Excel and make sure that you have an empty workbook displayed. To create a new, blank workbook, press Ctrl+N.

The sales projection will consist of two columns of information. Column A will contain the month names, and column B will store the projected sales numbers. You start by entering some descriptive titles into the worksheet. Here’s how to begin:

In this step, you enter the month names in column A.

Your worksheet should resemble the one shown in Figure 1.16.

Figure 1.16. Your worksheet, after entering the column headings and month names.

Next, you provide the sales projection numbers in column B. Assume that January’s sales are projected to be $50,000, and that sales will increase by 3.5 percent in each of the subsequent months.

At this point, your worksheet should resemble the one shown in Figure 1.17. Keep in mind that, except for cell B2, the values in column B are calculated with formulas. To demonstrate, try changing the projected sales value for the initial month, January (in cell B2). You’ll find that the formulas recalculate and return different values. But these formulas all depend on the initial value in cell B2.

Figure 1.17. Your worksheet, after creating the formulas.

The values in the worksheet are difficult to read because they aren’t formatted. In this step, you apply a number format to make the numbers easier to read and more consistent in appearance:

At this point, you have a functional worksheet—but it could use some help in the appearance department. Converting this range to an “official” (and attractive) Excel table is a snap:

The worksheet displays the monthly projected sales, but what about the total sales for the year? Because this range is a table, it’s simple:

How about a chart that shows the projected sales for each month?

Figure 1.19 shows the worksheet after creating the chart. Your chart may look different, depending on the chart layout or style you selected.

Figure 1.19. The table and chart.

Printing your worksheet is very easy (assuming that you have a printer attached and that it works properly).

Until now, everything that you’ve done has occurred in your computer’s memory. If the power should fail, all may be lost—unless Excel’s AutoRecover feature happened to kick in. It’s time to save your work to a file on your hard drive.

If you’ve followed along, you may have realized that creating this workbook was not at all difficult. But, of course, you’ve barely scratched the surface. The remainder of this book will cover these tasks (and many, many more) in much greater detail.