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

+ symbol in keystrokes, such as Ctrl+→). On the Touch keyboard, you tap Ctrl and then tap the appropriate arrow key to accomplish the same thing.

      When you use End and an arrow-key alternative, you must press and then release the End key before you press the arrow key (indicated by the comma in keystrokes, such as End, →). Pressing and releasing the End key causes the END MODE indicator to appear onscreen in the status bar. This is your sign that Excel is ready for you to press one of the four arrow keys.

      Because you can keep the Ctrl key depressed as you press the different arrow keys that you need to use, the Ctrl-plus-arrow key method provides a more fluid method for navigating blocks of cells on a physical keyboard than the End-then-arrow key method. On the Touch keyboard, there is essentially no difference in technique.

      You can use the Scroll Lock key to “freeze” the position of the cell pointer in the worksheet so that you can scroll new areas of the worksheet in view with keystrokes such as PgUp (Page Up) and PgDn (Page Down) without changing the cell pointer’s original position (in essence, making these keystrokes work in the same manner as the scroll bars).

      After engaging Scroll Lock (often abbreviated ScrLk), when you scroll the worksheet with the keyboard, Excel does not select a new cell while it brings a new section of the worksheet into view. To “unfreeze” the cell pointer when scrolling the worksheet via the keyboard, you just press the Scroll Lock key again.

       Tips on using the scroll bars

      To understand how scrolling works in Excel, imagine the worksheet is a humongous papyrus scroll attached to rollers on the left and right. To bring into view a new section of a papyrus worksheet that is hidden on the right, you crank the left roller until the section with the cells that you want to see appears. Likewise, to scroll into view a new section of the worksheet that is hidden on the left, you crank the right roller until that section of cells appears.

      You can use the horizontal scroll bar at the bottom of the Worksheet area to scroll back and forth through the columns of a worksheet. Likewise, you can use the vertical scroll bar to scroll up and down through its rows. To scroll one column or a row at a time in a particular direction, click the appropriate scroll arrow at the ends of the scroll bar. To jump immediately back to the originally displayed area of the worksheet after scrolling through single columns or rows in this fashion, simply click the darker area in the scroll bar that now appears in front of or after the scroll bar.

      You can resize the horizontal scroll bar, making it wider or narrower, by dragging the button that appears to the immediate left of its left scroll arrow. When working in a workbook that contains a whole bunch of worksheets, in widening the horizontal scroll bar you can end up hiding the display of the workbook’s later sheet tabs.

      To scroll very quickly through columns or rows of the worksheet, hold down the Shift key and then drag the mouse pointer in the appropriate direction within the scroll bar until the columns or rows that you want to see appear on the screen in the Worksheet area. When you hold down the Shift key as you scroll, the scroll button within the scroll bar becomes really narrow, and a ScreenTip appears next to the scroll bar, keeping you informed of the letter(s) of the columns or the numbers of the rows that you’re currently whizzing through.

      If your mouse has a wheel, you can use it to scroll directly through the columns and rows of the worksheet without using the horizontal or vertical scroll bars. Simply position the white-cross mouse pointer in the center of the Worksheet area and then hold down the wheel button of the mouse. When the mouse pointer changes to a four-point arrow, drag the mouse pointer in the appropriate direction (left and right to scroll through columns or up and down to scroll through rows) until the desired column or row comes into view in the Worksheet area.

       On a touchscreen, you scroll the worksheet by swiping the screen with your finger. (Don’t use your stylus because pressing it in the worksheet area only results in selecting the cell you touch). You swipe upward to scroll worksheet rows down and swipe down to scroll the rows up. Likewise, you swipe left to scroll columns right and swipe right to scroll columns left.

       The only disadvantage to using the scroll bars to move around is that the scroll bars bring only new sections of the worksheet into view – they don’t actually change the position of the cell cursor. If you want to start making entries in the cells in a new area of the worksheet, you still have to remember to select the cell (by clicking it) or the group of cells (by dragging through them) where you want the data to appear before you begin entering the data.

      One good reason for adding extra sheets to a workbook

      You may wonder why on earth anyone would ever need more than three worksheets, given just how many cells each individual sheet contains. The simple truth is that it’s all about how you choose to structure a particular spreadsheet rather than running out of places to put the data. For example, say you need to create a workbook that contains budgets for all the various departments in your corporation; you may decide to devote an individual worksheet to each department (with the actual budget spreadsheet tables laid out in the same manner on each sheet) rather than placing all the tables in different sections of the same sheet. Using this kind of one-sheet-per-budget layout makes it much easier for you to find each budget, print each one as a separate page of a report, and, if ever necessary, consolidate their data in a separate summary worksheet.

       Surfing the sheets in a workbook

      Each new workbook you open in Excel 2016 contains a single blank worksheet, aptly named Sheet1, with 16,384 columns and 1,048,576 rows (giving you a truly staggering total of 51,539,607,552 blank cells!). Should you still need more worksheets in your workbook, you can add them simply by clicking the New Sheet button (the circle with the plus sign in it) that appears to the immediate right of Sheet1 tab.

      On the left side of the bottom of the Worksheet area, the Sheet tab scroll buttons appear, followed by the actual tabs for the worksheets in your workbook and the New Sheet button. To activate a worksheet for editing, you select it by clicking its sheet tab. Excel lets you know what sheet is active by displaying the sheet name on its tab in green, boldface type as well as underlining the tab and making the tab appear to be connected to the current worksheet above.

       Don’t forget the Ctrl+Page Down and Ctrl+Page Up shortcut keys for selecting the next and previous sheets, respectively, in your workbook. You can also click the Next Sheet and Previous Sheet button marked by the ellipsis (…). The Next Sheet button is the one with the ellipsis on the right side of the sheet tabs immediately left of the New Sheet button. The Previous Sheet button is the one with ellipsis on the left side of the sheet tabs to the immediate left of the first visible sheet tab.

      If your workbook contains too many sheets for all their tabs to be displayed at the bottom of the Worksheet area, use the Sheet tab scroll buttons to bring new tabs into view (so that you can then click them to activate them). You click the Next Scroll button (the one with the triangle pointing right) to scroll the next hidden sheet tab into view on the right and the Previous Scroll button (the one with the triangle pointing left) to scroll the next hidden sheet into view on the left. You Ctrl+click the Next Scroll button to scroll the last sheet into view and Ctrl+click the Previous Scroll button to scroll the first sheet into view.

       Right-click either Sheet tab scroll button to display the Activate dialog box listing the names of all the worksheets in the workbook in their order from first to last. Then, to scroll to and select a worksheet, double-click its name or click the name followed by the OK button.

Taking a tour of the status bar

The status bar is the last component at the very bottom of the Excel program window. (See Figure 1-6.) The status bar contains the following areas:

       Figure 1-6: The Excel 2016 status bar.

      ✔