Navigating with your keyboard

Not surprisingly, 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 the way the keys on the numeric keypad behave. When Num Lock is on, the keys on your numeric keypad generate numbers. Many keyboards have a separate set of navigation (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.

Navigating with your mouse

To change the active cell by using the mouse, just click another cell and 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. To scroll faster, drag the scroll box or right-click anywhere on the scrollbar for a menu of shortcuts.

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 File ➪ 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.

Ribbon tabs

The commands available on 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:

• Home  You'll probably spend most of your time with the Home tab selected. This tab contains the basic Clipboard commands, formatting commands, style commands, commands to insert and delete rows or columns, plus an assortment of worksheet editing commands.
• Insert  Select this tab when you need to insert something into a worksheet—a table, a diagram, a chart, a symbol, and so on.
• Page Layout  This tab contains commands that affect the overall appearance of your worksheet, including some settings that deal with printing.
• Formulas  Use this tab to insert a formula, name a cell or a range, access the formula auditing tools, or control the way Excel performs calculations.
• Data  Excel's data-related commands are on this tab, including data validation and sorting commands.
• Review  This tab contains tools to check spelling, translate words, add comments, or protect sheets.
• View  The View tab contains commands that control various aspects of how a sheet is viewed. Some commands on this tab are also available in the status bar.
• Developer  This tab isn't visible by default. It contains commands that are useful for programmers. To display the Developer tab, choose File ➪ Options and then select Customize Ribbon. In the Customize the Ribbon section on the right, make sure that Main Tabs is selected in the drop-down control and place a check mark next to Developer.
• Help  This tab provides ways to get help, make suggestions, and access other aspects of Microsoft's community.
• Add-Ins  This tab is visible only if you loaded an older workbook or add-in that customizes the menu or toolbars. Because the old menus and toolbars were replaced by the Ribbon, these user interface customizations appear on the Add-Ins tab.

The preceding list contains the standard Ribbon tabs. Excel may display additional Ribbon tabs based on what's selected or resulting from add-ins that are installed.

The appearance of the commands on the Ribbon varies, depending on the width of the Excel window. When the Excel window is too narrow to display everything, the commands adapt; some of them might 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 narrower. 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. Most groups display a single icon; however, if you click the icon, all the group commands are available to you.

Contextual tabs

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

Figure 1.6 shows the contextual tabs that appear when a chart is selected. In this case, it has two contextual tabs: Chart Design and Format. When contextual tabs appear, you can, of course, continue to use all the other tabs.

Types of commands on the Ribbon

When you hover your mouse pointer over a Ribbon command, you'll see a ScreenTip that contains the command's name and a brief description. For the most part, the commands on the Ribbon work just as you would expect. You'll find several different styles of commands on the Ribbon.

• Simple buttons  Click the button, and it does its thing. An example of a simple button is the Increase Font Size button in the Font group of the Home tab. Some buttons perform the action immediately; others display a dialog box so that you can enter additional information. Button controls may or may not be accompanied by a descriptive label.
• Toggle buttons  A toggle button is clickable and conveys some type of information by displaying two different colors. An example is the Bold button in the Font group of the Home tab. If the active cell isn't bold, the Bold button displays in its normal color. If the active cell is already bold, the Bold button displays a different background color. If you click the Bold button, it toggles the Bold attribute for the selection.
• Simple drop-downs  If the Ribbon command has a small down arrow, the command is a drop-down menu. Click it, and additional commands appear below it. An example is the Conditional Formatting command in the Styles group of the Home tab. When you click this control, you see several options related to conditional formatting. Style galleries, like the Data Types gallery on the Data tab, also have drop-down arrows to reveal more styles if they are available.
• Split buttons  A split button control combines a one-click button with a drop-down. If you click the button part, the command is executed. If you click the drop-down part (a down arrow), you choose from a list of related commands. An example of a split button is the Merge & Center command in the Alignment group of the Home tab (see Figure 1.7). Clicking the left part of this control merges and centers text in the selected cells. If you click the arrow part of the control (on the right), you get a list of commands related to merging cells.
• Check boxes  A check box control turns something on or off. An example is the Gridlines control in the Show group of the View tab. When the Gridlines check box is checked, the sheet displays gridlines. When the control isn't checked, the gridlines don't appear.
• Spin buttons  Excel's Ribbon has only one spin button control: the Scale to Fit group of the Page Layout tab. Click the top part of the spin button to increase the value; click the bottom part of the spin button to decrease the value.

Some of the Ribbon groups contain a small icon in the bottom-right corner, known as a dialog box launcher. For example, if you examine the groups on the Home tab, you find dialog box launchers for the Clipboard, Font, Alignment, and Number groups—but not the Styles, Cells, and Editing groups. Click the icon, and Excel displays a dialog box or task pane. The dialog box launchers often provide options that aren't available on the Ribbon.

Accessing the Ribbon by using your keyboard

At first glance, you may think that the Ribbon is completely mouse centric. After all, the commands don't display the traditional underlined 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.8 shows how the Home tab looks after you press the Alt key to display the keytips and then the H key to display the keytips for the Home tab. 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).

Nobody will memorize all of these keys, but if you're a keyboard fan, it takes just a few times before you memorize the keystrokes required for commands that you use frequently.

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 the down arrow to enter the Ribbon. Then use left- and right-arrow keys to scroll through the Ribbon commands. If you hold down the Ctrl key while in the Ribbon, the left and right arrows will jump to the first control in the previous or next group, respectively. 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

Navigating dialog boxes is generally easy—you simply click the control that 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 (called a hot key or an accelerator key). You can access the control from the keyboard by pressing Alt and then the underlined letter. You can also press Tab to cycle through all the controls on a dialog box. Pressing Shift+Tab cycles through the controls in reverse order.

Using tabbed dialog boxes

Several Excel dialog boxes are “tabbed” dialog boxes; that is, they include notebook-like tabs, each of which is associated with a different panel.

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

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.

Getting started on your worksheet

Start Excel and make sure you have an empty workbook displayed. To create a new, blank workbook, press Ctrl+N (the shortcut key for File ➪ New ➪ Blank Workbook). Enter some sales projections in the new workbook.

The sales projections 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:

1. Select cell A1 (the upper-left cell in the worksheet) by using the navigation (arrow) keys, if necessary. The Name box displays the cell's address.
2. Type Month into cell A1 and press Enter. Depending on your setup, either Excel moves the selection to a different cell or the pointer remains in cell A1.
3. Select cell B1, type Projected Sales, and press Enter. The text extends beyond the cell width, but don't worry about that for now.

Filling in the month names

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

1. Select cell A2 and type Jan (an abbreviation for January). At this point, you can enter the other month name abbreviations manually, or you can let Excel do some of the work by taking advantage of the AutoFill feature.
2. Make sure that cell A2 is selected. Notice that the active cell is displayed with a heavy outline. At the bottom-right corner of the outline, you'll see a small square known as the fill handle. Move your mouse pointer over the fill handle, click, and drag down until you've highlighted from cell A2 down to cell A13.
3. Release the mouse button, and Excel automatically fills in the month names.

Your worksheet should resemble the one shown in Figure 1.14.

Entering the sales data

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 subsequent month.

1. Select cell B2 and type 50000, the projected sales for January. You could type a dollar sign and comma to make the number more legible, but you do the number formatting a bit later.
2. To enter a formula to calculate the projected sales for February, move to cell B3 and type the following:

   =B2*103.5%

   When you press Enter, the cell displays 51750. The formula returns the contents of cell B2, multiplied by 103.5%. In other words, February sales are projected to be 103.5% of the January sales—a 3.5% increase.

3. The projected sales for subsequent months use a similar formula, but rather than retype the formula for each cell in column B, take advantage of the AutoFill feature. Make sure that cell B3 is selected. Click the cell's fill handle, drag down to cell B13, and release the mouse button.

At this point, your worksheet should resemble the one shown in Figure 1.15. 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. All these formulas depend on the initial value in cell B2.

Formatting the numbers

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.

1. Select the numbers by clicking cell B2 and dragging down to cell B13. Don't drag the fill handle this time, though, because you're selecting cells, not filling a range.
2. Access the Ribbon and choose Home. In the Number group, click the drop-down Number Format control (it initially displays General), and select Currency from the list. The numbers now display with a currency symbol and two decimal places. That's much better, but the decimal places aren't necessary for this type of projection.
3. Make sure that the range B2:B13 is selected, choose Home ➪ Number, and click the Decrease Decimal button. One of the decimal places disappears. Click that button a second time and the values are displayed with no decimal places.

Making your worksheet look a bit fancier

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.

1. Activate any cell within the range A1:B13.
2. Choose Insert ➪ Tables ➪ Table. Excel displays the Create Table dialog box to make sure that it guessed the range properly.
3. Click OK to close the Create Table dialog box. Excel applies its default table formatting and displays its Table Design contextual tab.

Your worksheet should look like Figure 1.16.

If you don't like the default table style, just select another one from the Table Design ➪ Table Styles group. Notice that you can get a preview of different table styles by moving your mouse over the Ribbon. When you find one you like, click it, and the style will be applied to your table.

Summing the values

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

1. Activate any cell in the table.
2. Choose Table Design ➪ Table Style Options ➪ Total Row. Excel automatically adds a new row to the bottom of your table, including a formula that calculates the total of the Projected Sales column.
3. If you'd prefer to see a different summary formula (for example, average), click cell B14 and choose a different summary formula from the drop-down list.

Creating a chart

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

1. Activate any cell in the table.
2. Choose Insert ➪ Charts ➪ Recommended Charts. Excel displays some suggested chart type options.
3. In the Insert Chart dialog box, click the second recommended chart (a column chart), and click OK. Excel inserts the chart in the center of the window. To move the chart to another location, click its border and drag it.
4. Click the chart and choose a style using the Chart Design ➪ Chart Styles options.

Figure 1.17 shows the worksheet with a column chart. Your chart may look different, depending on the chart style you selected.

Printing your worksheet

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

1. Make sure that the chart isn't selected. If a chart is selected, the chart will print on a page by itself. To deselect the chart, just press Esc or click any cell.
2. To make use of Excel's handy Page Layout view, click the Page Layout button on the right side of the status bar. Excel displays the worksheet page by page so that you can easily see how your printed output will look. In Page Layout view, you can tell immediately whether the chart is too wide to fit on one page. If the chart is too wide, click and drag a corner of the chart to resize it or just move the chart below the table of numbers. Click the Normal button to return to the default view.
3. When you're ready to print, choose File ➪ Print. At this point, you can change some print settings. For example, you can choose to print in landscape rather than portrait orientation. Make the change, and you see the result in the preview window.
4. When you're satisfied, click the large Print button in the upper-left corner. The page is printed, and you're returned to your workbook.

Saving your workbook

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.

1. Click the Save button on the Quick Access Toolbar. (This button looks like an old-fashioned floppy disk, popular in the previous century.) Because the workbook hasn't been saved yet and still has its default name, Excel responds with a Save this file dialog box that lets you choose the location for the workbook file. The Choose a Location drop-down lists some recently used locations, or you can click More options to see the Save as Backstage screen. From there, you can click Browse to navigate to any location on your computer.
2. Click Browse. Excel displays the Save As dialog box.
3. In the File Name field, enter a name (such as Monthly Sales Projection). If you like, you can specify a different location.
4. Click Save or press Enter. Excel saves the workbook as a file. The workbook remains open so that you can work with it some more.

If you've followed along, you probably have realized that creating this workbook was not difficult. But, of course, you've barely scratched the surface of Excel. The remainder of this book covers these tasks (and many, many more) in much greater detail.