Читать книгу Excel 2016 All-in-One For Dummies - Харвей Грег - Страница 13

When you first open a new, blank workbook, Excel 2016 opens up a single worksheet (with the generic name, Sheet1) in a new workbook file (with the generic filename, Book1) inside a program window such as the one shown in Figure 1-2.

Figure 1-2: The Excel 2016 program window as it appears after first opening a blank workbook when both Ribbon tabs and commands are displayed.

The Excel program window containing this worksheet of the workbook is made up of the following components:

✔ File tab: When clicked, this tab opens the Backstage view, which contains a bunch of file-related options including Info, New, Open, Save, Save As, Print, Share, Export, Publish, Close, and Account, as well as Options, which enables you to change Excel’s default settings.

✔ Quick Access toolbar: You can click the Save, Undo, and Redo buttons to perform common tasks to save your work and undo and redo editing changes. You can also click the Customize Quick Access Toolbar button to the immediate right of the Redo button to open a drop-down menu containing additional common commands such as New, Open, Quick Print, and so on, as well as to customize the toolbar, change its position, and minimize the Ribbon.

✔ Ribbon: Most Excel commands are contained on the Ribbon. They are arranged into a series of tabs ranging from Home through View.

✔ Formula bar: This displays the address of the current cell along with the contents of that cell.

✔ Worksheet area: This area contains all the cells of the current worksheet identified by column headings, which use letters along the top, and row headings, which use numbers along the left edge, with tabs for selecting new worksheets. You use a horizontal scroll bar on the bottom to move left and right through the sheet and a vertical scroll bar on the right edge to move up and down through the sheet.

✔ Status bar: This bar keeps you informed of the program’s current mode and any special keys you engage, and it enables you to select a new worksheet view and to zoom in and out on the worksheet.

When using Excel 2016 on a touchscreen device, the Ribbon Display Options are automatically set to Tabs (so that associated commands appear only when you tap a tab) and the Quick Access toolbar contains a Touch/Mouse Mode button. Tap this button followed by the Touch option on its drop-down menu to spread out the tabs and their command buttons on the Ribbon. That way you have a fighting chance of correctly selecting them with your finger or stylus. On a touchscreen tablet such as the Microsoft Surface 3 tablet, an Ink Tools tab where you can modify settings for using a stylus follows the View tab.

Going behind the scenes to Excel’s Backstage view

At the top of the Excel 2016 program window, immediately below the Excel program button and the Save button on the Quick Access toolbar, you find the File menu button (the green one with “File” in white letters to the immediate left of the Home tab).

When you click the File menu button, the Excel Backstage view appears. The screen in this view contains a menu of file-related options running down a column on the left side and, depending upon which option is selected, some panels containing both at-a-glance information and further command options.

At first glance, the File menu button may appear to you like a Ribbon tab – especially in light of its rectangular shape and location immediately left of the Ribbon’s initial Home tab. Keep in mind, however, that this important file control is technically a command button that, when clicked, leads directly to a totally new, nonworksheet screen with the Backstage view. This screen has its own menu options but contains no Ribbon command buttons whatsoever.

After you click the File menu button to switch to the Backstage view, you can then select the Back button (with the left-pointing arrow) that appears above the Info menu item to return to the normal worksheet view or you can simply press the Esc key.

Getting the lowdown on the Info screen

When you choose File ⇒ Info at the top of File menu in the Backstage view, an Info screen similar to the one shown in Figure 1-3 appears.

Figure 1-3: The Excel Backstage view displaying the Info screen with permissions, distribution, version commands, and more.

On the left side of this Info screen, you find the following four command buttons:

✔ Protect Workbook to encrypt the Excel workbook file with a password, protect its contents, or verify the contents of the file with a digital signature (see Book IV, Chapters 1 and 3 for more on protecting and signing your workbooks)

✔ Inspect Workbook to inspect the document for hidden metadata (data about the file) and check the file’s accessibility for folks with disabilities and compatibility with earlier versions of Excel (see Book IV, Chapter 3 for details on using this feature)

✔ Manage Workbook to recover or delete draft versions saved with Excel’s AutoRecover feature (see Book II, Chapter 1 for more on using AutoRecover)

✔ Browser View Options to control what parts of the Excel workbook can be viewed and edited by users who view it online on the Web

On the right side of the Info screen, you see a list of various and sundry bits of information about the file:

✔ Properties lists the Size of the file as well as any Title, Tags, and Categories (to help identify the file when doing a search for the workbook) assigned to it. To edit or add to the Title, Tags, or Categories properties, click the appropriate text box and begin typing. To add or change additional file properties, including the Company, Comments, and Status properties, click the Properties drop-down button and then select Show Document Panel or Advanced Properties from its drop-down menu. Select Show Document Panel to open the Document panel in the regular worksheet window where you can edit properties such as Author, Title, Subject, and Keywords and to add comments. Select the Advanced Properties option to open the workbook’s Properties dialog box (with its General, Summary, Statistics, Contents, and Custom tabs) to change and review a ton of file properties.

✔ Related Dates lists the date the file was Last Modified, Created, and Printed.

✔ Related People lists the name of the workbook’s author as well as the name of the person who last modified the file. To add an author to the workbook file, click the Add an Author link that appears beneath the name of the current author. If the workbook file is new and you’ve never saved it on disk, the words “Not Saved Yet” appear after Last Modified By.

✔ The Open File Location check box appears under the Related Documents heading. Select it to open the folder containing the current workbook file, where you can find associated workbook files to work with.

✔ The Show All Properties link, when clicked, expands the list of Properties to include text fields for Comments, Template, Status, Categories, Subject, Hyperlink Base, and Company that you can edit.

Sizing up other File menu options

Immediately below the Info option at the very top of the File menu, you find the commands you commonly need for working with Excel workbook files, such as creating new workbook files as well as opening, saving, and closing files. (See Book II, Chapter 1 for more on saving and closing files and Book II, Chapter 3 for more on opening them.)

The New command immediately below Info displays a New screen, which, just like the Excel Start screen, displays a thumbnail list of all the available spreadsheet templates. (See Book II, Chapter 1 for more on creating and using workbook templates.)

Beneath the Save As command you find the Print option that, when selected, displays a Print screen. This screen contains the document’s current print settings (that you can modify) on the left side and a preview area that shows you the pages of the printed worksheet report. (See Book II, Chapter 5 for more on printing worksheets using the Print Settings panel in the Backstage view.)

Below the Print command you find the Share option, which displays a list of commands for sharing your workbook files online. Beneath this, you find an Export option used to open the Export screen, where you find options for converting your workbooks to other file types as well as controlling the browsing options when the workbook is viewed online in a web browser. (See Book IV, Chapter 4 for more about sharing workbook files online as well as converting them to other file formats.)

The new Publish option enables you to save your Excel workbooks to a folder on your OneDrive for Business account and then publish it to Microsoft’s Power BI (Business Information) stand-alone application that enables you to create visual dashboards that highlight and help explain the story behind the worksheet data.

Checking user and product information on the Account screen

Below the Close option that is used to close a workbook file (hopefully, after saving all your edits) on the File menu, you find the Account option. You can use this option to review account-related information on the Backstage Account screen. When displayed, the Account screen gives you both user and product information.

On the left side of the Account screen, your user information appears, including all the online services to which you’re currently connected. These services include social media sites such as Facebook, Twitter, and LinkedIn, as well as the more corporate services such as your OneDrive, SharePoint team site, and Office 365 account.

To add an online service to this list, click the Add a Service button at the bottom and select the service to add on the Images & Videos, Storage, and Sharing continuation menus. To manage which accounts appear on the list, highlight the name and click the Remove button to take it off the list. To manage the settings for a particular service, click the Manage button and then edit the settings online.

Use the Office Background drop-down list box that appears between your user information and the Connected Services list on the Account screen to change the pattern that appears in the background of the title bar of all your Office 2016 programs. By default, Office 2016 uses a Clouds pattern. You can change the background by selecting a new pattern from the Office Background drop-down menu on the Excel Account screen or have no pattern displayed by selecting None from the menu. Below this option, you see the Office Theme selection (Colorful by default) that sets the overall color pattern you use. Just be aware that any change you make here affects the title areas of all the Office 2016 programs you run on your device (not just the Excel 2016 program window).

On the right side of the Account screen, you find the Product information. Here you can see the activation status of your Office programs as well as review the version number of Excel that is installed on your device. Because many Office 365 licenses allow up to five installations of Office 2016 on different devices (desktop computer, laptop, Windows tablet, and smartphone, for example), you can select the Show Additional Licensing Information link and then click the Manage Account link that appears to go online. There, you can check how many Office installations you still have available and, if need be, manage the devices on which Office 2016 is activated.

Ripping through the Ribbon

The Ribbon (shown in Figure 1-4) groups related commands together with the goal of showing you all the most commonly used options needed to perform a particular Excel task.

Figure 1-4: Excel’s Ribbon consists of a series of tabs containing command buttons arranged into different groups.

The Ribbon is made up of the following components:

✔ Tabs: Excel’s main tasks are brought together and display all the commands commonly needed to perform that core task.

✔ Groups: Related command buttons can be organized into subtasks normally performed as part of the tab’s larger core task.

✔ Command buttons: Within each group you find command buttons that you can select to perform a particular action or to open a gallery. Note that many command buttons on certain tabs of the Excel Ribbon are organized into mini-toolbars with related settings.

✔ Dialog Box launcher: This button is located in the lower-right corner of certain groups and opens a dialog box containing a bunch of additional options you can select.

To get more of the Worksheet area displayed in the program window, you can minimize the Ribbon so that only its tabs are displayed. (In fact, this Tabs display option is the default setting for Excel 2016 running on a touchscreen device, such as the Microsoft Surface tablet.)

You can minimize the Ribbon by doing any of the following:

✔ Click the Collapse the Ribbon button (the button with the caret symbol in the lower-right corner of the Excel Ribbon).

✔ Double-click a Ribbon tab.

✔ Press Ctrl+F1.

✔ Click the Shows Tabs item on the Ribbon Display Options button’s drop-down menu.

To redisplay the entire Ribbon and keep all the command buttons on the selected tab displayed in the program window, click the tab and then select the Pin the Ribbon button (the one with the push-pin icon that replaces the Unpin the Ribbon button). You can also do this by double-clicking one of the tabs or pressing Ctrl+F1 a second time, or even by selecting the Show Tabs and Commands item on the drop-down menu that appears when you click or tap the Ribbon Display Options button.

When you work in Excel with the Ribbon minimized, the Ribbon expands each time you select one of its tabs to show its command buttons, but that tab stays open only until you select one of its command buttons. The moment you select a command button, Excel immediately minimizes the Ribbon again so that only the tabs display.

Note, however, that when Excel expands a tab on the collapsed Ribbon, the Ribbon tab overlaps the top of the worksheet, obscuring the header with the column letters as well as the first couple of rows of the worksheet itself. This setup can make it a little harder to work when the Ribbon commands you’re selecting pertain to data in these first rows of the worksheet. For example, if you’re centering a title entered in cell A1 across several columns to the right with the Merge & Center command button on the Home tab, you can’t see the result of selecting the button until you once again minimize the Ribbon by selecting a visible cell in the worksheet. If you then decide you don’t like the results or want to further refine the title’s formatting, you need to redisplay the Home tab of the Ribbon once again, which obscures the cells in the top two rows all over again! (The workaround for this is to do most of your formatting with the commands on the mini-bar that appears when you right-click a cell selection so that you don’t have to open the minimized Ribbon at all. See the section on formatting cells with the mini-bar in Book II, Chapter 2 for details.)

Keeping tabs on the Excel Ribbon

The very first time you launch Excel 2016 and open a new workbook, the Ribbon contains the following seven tabs, proceeding from left to right:

✔ Home: Use this tab when creating, formatting, and editing a spreadsheet. This tab is arranged into the Clipboard, Font, Alignment, Number, Styles, Cells, and Editing groups.

✔ Insert: Use this tab when adding particular elements (including graphics, pivot tables, charts, hyperlinks, and headers and footers) to a spreadsheet. This tab is arranged into the Tables, Illustrations, Apps, Charts, Reports, Sparklines, Filter, Links, Text, and Symbol groups.

✔ Page Layout: Use this tab when preparing a spreadsheet for printing or reordering graphics on the sheet. This tab is arranged into the Themes, Page Setup, Scale to Fit, Sheet Options, and Arrange groups.

✔ Formulas: Use this tab when adding formulas and functions to a spreadsheet or checking a worksheet for formula errors. This tab is arranged into the Function Library, Defined Names, Formula Auditing, and Calculation groups. Note that this tab also contains a Solutions group when you activate certain add-in programs, such as Conditional Sum and Euro Currency Tools – see Book I, Chapter 2 for more on Excel add-ins.

✔ Data: Use this tab when importing, querying, outlining, and subtotaling the data placed into a worksheet’s data list. This tab is arranged into the Get External Data, Connections, Sort & Filter, Data Tools, and Outline groups. Note that this tab also contains an Analysis group if you activate add-ins, such as the Analysis Toolpak and Solver Add-In – see Book I, Chapter 2 for more on Excel add-ins.

✔ Review: Use this tab when proofing, protecting, and marking up a spreadsheet for review by others. This tab is arranged into the Proofing, Language, Comments, and Changes groups. Note that this tab also contains an Ink group with a sole Start Inking button if you’re running Excel on a Windows tablet or smartphone or on a laptop or desktop computer that’s equipped with some sort of electronic input tablet.

✔ View: Use this tab when changing the display of the Worksheet area and the data it contains. This tab is arranged into the Workbook Views, Show, Zoom, Window, and Macros groups.

Although these seven tabs are the standard ones on the Ribbon, they are not the only tabs that can appear in this area. Excel displays contextual tools with their own tab or tabs as long as you’re working on a particular object selected in the worksheet, such as a graphic image you’ve added or a chart or pivot table you’ve created. The name of the contextual tools for the selected object appears immediately above the tab or tabs associated with the tools.

The moment you deselect the object (usually by clicking somewhere on the sheet outside of its boundaries), the contextual tool for that object and all of its tabs immediately disappears from the Ribbon, leaving only the regular tabs – Home, Insert, Page Layout, Formulas, Data, Review, and View – displayed.

Adding the Developer tab to the Ribbon

If you do a lot of work with macros (see Book VIII, Chapter 1) and XML files in Excel, you should add the Developer tab to the Ribbon. This tab contains all the command buttons normally needed to create, play, and edit macros as well as to import and map XML files. To add the Developer tab to the Excel Ribbon, follow these steps:

1. Click the File menu button followed by the Options item in the Backstage view (Alt+FT).

The Excel Options dialog box opens in the worksheet view.

2. Select the Customize Ribbon option in the Excel Options dialog box and then click the Developer check box under Main Tabs in the Customize the Ribbon list box on the right. Click OK to finish.

Selecting with mouse and keyboard

Because Excel 2016 runs on many different types of devices, the most efficient means of selecting Ribbon commands depends not only on the device on which you’re running the program, but on the way that device is equipped as well.

For example, when I use Excel 2016 on my Microsoft 3 Surface tablet with its touch cover (equipped with both keyboard and touchpad) connected, I select commands from the Excel Ribbon more or less the same way I do when running Excel on my Windows desktop computer equipped with a standalone physical keyboard and mouse or on my Windows 10 laptop computer with its built-in physical keyboard and track pad.

However, when I run Excel 2016 on my Surface 3 tablet when the touch cover is not connected, I’m limited to selecting Ribbon commands directly on the touchscreen with my finger or stylus.

The most direct method for selecting Ribbon commands equipped with a physical keyboard and mouse is to click the tab that contains the command button you want and then click that button in its group. For example, to insert an online image into your spreadsheet, you click the Insert tab and then click the Illustrations button followed by the Online Pictures button to open the Insert Pictures dialog box.

The easiest method for selecting commands on the Ribbon – if you know your keyboard at all well – is to press the keyboard’s Alt key and then type the letter of the hot key that appears on the tab you want to select. Excel then displays all the command button hot keys next to their buttons, along with the hot keys for the Dialog Box launchers in any group on that tab. (See Figure 1-5.) To select a command button or Dialog Box launcher, simply type its hot key letter.

Figure 1-5: When you select a Ribbon tab by pressing Alt plus the hot key assigned to that tab, Excel displays the hot keys for its command buttons.

If you know the old Excel shortcut keys from versions prior to Excel 2007, you can still use them. For example, instead of going through the rigmarole of pressing Alt+HCC to copy a cell selection to the Windows Clipboard and then Alt+HVP to paste it elsewhere in the sheet, you can still press Ctrl+C to copy the selection and then press Ctrl+V when you’re ready to paste it.

Selecting Ribbon commands by touch

When selecting Ribbon commands on a touchscreen device without access to a physical keyboard and mouse or touchpad, you are limited to selecting commands directly by touch.

Before trying to select Excel Ribbon commands by touch, however, you definitely want to turn on touch mode in Excel 2016. You do this by tapping the Touch/Mouse Mode button at the end of the Quick Access toolbar followed by the Touch option on its drop-down menu. When you do this, Excel spreads out the command buttons on the Ribbon tabs by putting more space around them, making it more likely that you’ll actually select the command button you’re tapping with your finger (or even a more slender stylus) instead of the one right next to it. (This is a particular problem with the command buttons in the Font group on the Home tab that enable you to add different attributes to cell entries such as bold, italic, or underlining: They’re so close together when touch mode is not on that they’re almost impossible to correctly select by touch.)

What “click and drag” means on your device

Given all the different choices for selecting stuff in Excel, you need to be aware of a few click-and-drag conventions used throughout this book:

✔ When I say “click something” (a command button, cell, or whatever), this means click the primary mouse button (the left one unless you change it) on a physical mouse or tap the object directly with your finger or stylus.

✔ When I say “double-click something,” this means click the primary button twice in rapid succession on a physical mouse or double-tap the object with your finger or stylus.

✔ When I say “right-click,” this means click with the secondary button (the right button unless you change it) on a physical mouse or tap the object and keep your finger or stylus on the touchscreen until the context menu, pop-up gallery, or whatever appears.

✔ When I say “drag through a cell selection,” with a physical 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 four arrow keys (indicated by the + 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.

✔ Mode: This button indicates the current state of the Excel program (READY, ENTER, EDIT, and so on).

✔ AutoCalculate: This indicator displays the AVERAGE, COUNT, and SUM of all the numerical entries in the current cell selection.

✔ Layout: This selector enables you to select between three layouts for the Worksheet area: Normal, the default view that shows only the worksheet cells with the column and row headings; Page Layout view, which adds rulers and page margins and shows page breaks for the worksheet; and Page Break Preview, which enables you to adjust the paging of a report.

✔ Zoom: The Zoom slider enables you to zoom in and out on the cells in the Worksheet area by dragging the slider to the right or left, respectively.

When you begin recording your first macro in Excel 2016 (see Book VIII, Chapter 1 for details), a Stop Recording button (with a square icon) appears on the status bar to the immediate right of the Mode indicator. When you finish recording this macro, the Stop Recording button on the status bar immediately changes into a Record Macro button (using an icon with a dot on a tiny worksheet) that you can thereafter use to record any or all your future macros.