Excel 2016 All-in-One For Dummies. Harvey Greg

mouse this means click the first cell and hold down the primary mouse button as you swipe, and then release the button when the selection is made. On a touchscreen, you tap the first cell and then drag one of the selection handles (the circle that appears in the upper-left or lower-right corner of the selected cell) to make the selection.

Adjusting to the Quick Access toolbar

      When you first begin using Excel 2016, the Quick Access toolbar contains only the following three or four buttons:

      ✔ Save: Saves any changes made to the current workbook using the same filename, file format, and location.

      ✔ Undo: Undoes the last editing, formatting, or layout change you made.

      ✔ Redo: Reapplies the previous editing, formatting, or layout change that you just removed with the Undo button.

      ✔ Touch/Mouse Mode (automatically added only to Excel running on touchscreen tablets and computers): Switches between the default mouse mode and touch mode, which puts more space between tabs and their command buttons to facilitate selection with a finger or stylus.

      The Quick Access toolbar is very customizable because you can easily add any Ribbon command to it. Moreover, you’re not restricted to adding buttons for just the commands on the Ribbon; you can add any Excel command you want to the toolbar, even the obscure ones that don’t rate an appearance on any of its tabs. (See Book I, Chapter 2 for details on customizing the Quick Access toolbar.)

      By default, the Quick Access toolbar appears right above the File Menu button and Ribbon tabs. To display the toolbar beneath the Ribbon above the Formula bar, click the Customize Quick Access Toolbar button (the drop-down button to the direct right of the toolbar with a horizontal bar above a down-pointing triangle) and then select Show Below the Ribbon from its drop-down menu. Doing this helps you avoid crowding out the name of the current workbook that appears to the toolbar’s right.

Fooling around with the Formula bar

      The Formula bar displays the cell address and the contents of the current cell. The address of this cell is determined by its column letter(s) followed immediately by the row number, as in cell A1, the very first cell of each worksheet at the intersection of column A and row 1, or cell XFD1048576, the very last of each Excel 2016 worksheet at the intersection of column XFD and row 1048576. The contents of the current cell are determined by the type of entry you make there: text or numbers, if you just enter a heading or particular value, and the nuts and bolts of a formula, if you enter a calculation there.

      The Formula bar is divided into three sections:

      ✔ Name box: The leftmost section displays the address of the current cell address.

      ✔ Formula bar buttons: The second, middle section appears as a rather nondescript button displaying only an indented circle on the left (used to narrow or widen the Name box) with the Insert Function button (labeled fx) on the right until you start making or editing a cell entry. At that time, its Cancel (an X) and its Enter (a check mark) buttons appear in between them.

      ✔ Cell contents: The third white area to the immediate right of the Function Wizard button takes up the rest of the bar and expands as necessary to display really, really long cell entries that won’t fit in the normal area. This area contains a Formula Bar button on the far right that enables you to expand its display to show really long formulas that span more than a single row and then to contract the Cell contents area back to its normal single row.

      The Cell contents section of the Formula bar is really important because it always shows you the contents of the cell even when the worksheet does not. (When you’re dealing with a formula, Excel displays only the calculated result in the cell in the worksheet and not the formula by which that result is derived.) You can edit the contents of the cell in this area at any time. By the same token, when the Cell contents area is blank, you know that the cell is empty as well.

      Assigning 26 letters to 16,384 columns

      When it comes to labeling the 16,384 columns of an Excel 2016 worksheet, our alphabet with its measly 26 letters is simply not up to the task. To make up the difference, Excel first doubles the letters in the cell’s column reference so that column AA follows column Z (after which you find column AB, AC, and so on) and then triples them so that column AAA follows column ZZ (after which you get column AAB, AAC, and the like). At the end of this letter tripling, the 16,384th and last column of the worksheet ends up being XFD, so that the last cell in the 1,048,576th row has the cell address XFD1048576.

What’s up with the Worksheet area?

      The Worksheet area is where most of the Excel spreadsheet action takes place because it displays the cells in different sections of the current worksheet. Also, inside the cells is where you do all of your spreadsheet data entry and formatting, not to mention the majority of your editing.

      Keep in mind that for you to be able to enter or edit data in a cell, that cell must be current. Excel indicates that a cell is current in three ways:

      ✔ The cell cursor or pointer – the dark green border surrounding the cell’s entire perimeter – appears in the cell.

      ✔ The address of the cell appears in the Name box of the Formula bar.

      ✔ The current cell’s column letter(s) and row number are shaded (in an orange color on most monitors) in the column headings and row headings that appear at the top and left of the Worksheet area, respectively.

       Moving around the worksheet

      Each Excel worksheet contains far too many columns and rows for all of its cells to be displayed at one time. (It’s true: 17,179,869,184 cell totals equal an illegible black blob, regardless of the size of your monitor.) Excel offers many methods for moving the cell cursor around the worksheet to the cell where you want to enter new data or edit existing data:

      ✔ Click the desired cell – assuming that the cell is displayed within the section of the sheet currently visible in the Worksheet area.

      ✔ Click the Name box, type the address of the desired cell directly into this box, and then press the Enter key.

      ✔ Press Alt+HFDG, Ctrl+G or F5 to open the Go To dialog box, type the address of the desired cell into its Reference text box, and then click OK.

✔ Use the cursor keys, as shown in Table 1-1, to move the cell cursor to the desired cell.

      ✔ Use the horizontal and vertical scroll bars at the bottom and right edges of the Worksheet area to move the part of the worksheet that contains the desired cell. Then click the cell to put the cell cursor in it.

Table 1-1 Keystrokes for Moving the Cell Cursor

      Note: In the case of those keystrokes that use arrow keys, you must either use the arrows on the cursor keypad or have the Num Lock key disengaged on the numeric keypad of your keyboard.

       Keystroke shortcuts for moving the cell cursor

      Excel offers a wide variety of keystrokes for moving the cell cursor to a new cell. When you use one of these keystrokes, the program automatically scrolls a new part of the worksheet into view, if this is required to move the cell pointer. In Table 1-1, I summarize these keystrokes and how far each one moves the cell cursor from its starting position.

      The keystrokes that combine the Ctrl or End key with an arrow key (listed in Table 1-1) are among the most helpful for moving quickly from one edge to the other in large tables of cell entries. Moving from table to table in a section of the worksheet that contains many blocks of cells is also much easier.

      When you use Ctrl and an arrow key to move from edge to edge in a table or between tables in a worksheet on a physical keyboard, you hold down Ctrl while you press one of the