Excel 2016 All-in-One For Dummies (2016)

Book II

Worksheet Design

Chapter 3

Editing and Proofing Worksheets

In This Chapter

Opening workbooks for editing

Using basic cell-editing techniques

Zooming in and out on the worksheet

Freezing columns and rows on the screen

Copying and moving data entries

Finding and replacing data entries

Proofing the worksheet

Using Data Validation to circle invalid data entries in the worksheet

Finding and eliminating errors with the Text to Speech feature

Creating a spreadsheet is seldom a one-time experience. In fact, some of the spreadsheets that you create with Excel require routine changes on a regular basis, whereas others require more radical changes only once in a while. Regardless of the extent of the changes and their frequency, you can be sure that sooner or later, most of the spreadsheets you create in Excel will require editing.

In this chapter, you find out how to make simple editing changes in a worksheet by modifying the contents of a cell as well as how to do more complex editing in your worksheets. These techniques include how to use the Undo and Redo features, zoom in and out on data, move and copy data, delete data entries and insert new ones, search and replace data entries, and proof the contents of the final worksheet.

However, before you can use any of these fine editing techniques, you have to open the workbook whose contents require editing. So, with that in mind, this chapter starts out by giving you the lowdown on finding and opening workbooks in Excel.

Opening a Workbook

One of the simplest ways to open a workbook for editing in Excel is to open its folder in Windows Explorer and then double-click the workbook file icon. If you haven’t yet started Excel 2016 at the time you open the workbook, Windows automatically launches Excel at the same time that it opens the file.

Keep in mind that Excel automatically saves workbook files in your Documents folder unless you specifically select another folder or you save your workbooks online on your SharePoint site, OneDrive, or Dropbox. You can search for the Excel document you want to open from the Windows 8 Start screen (where you simply start typing the name of the file) or with the Search Programs and Files text box on the Windows 7 Start menu. If you’re using Windows 10, you can type the name of the workbook file in the Ask Me Anything text box, or you can use Cortana to locate the file by saying to her “Find …” followed by the name of the workbook file.

If you can’t remember where you saved the workbook that you need to edit (a common occurrence), you can browse for the workbook file from the Open screen or use the Search Documents text box in the Open dialog box to locate the file. See the “Finding misplaced workbooks” section later in this chapter for details.

Using the Open screen in the Backstage view

If Excel 2016 is already running and you want to open a workbook file for editing from within Excel, you can click File  ⇒  Open or press Alt+FO or Ctrl+O to launch the Open screen in the Backstage view and locate and open the file.

When Excel displays the Open screen (similar to the one shown in Figure 3-1), the program selects Recent Workbooks under Places on the left, while listing recently opened workbook files on the right. If the workbook file you want to edit is listed there, you can open the file simply by clicking its name in the list.

Figure 3-1: Use the Open screen to find and open a workbook for editing.

If the workbook file is not among those in the Recent Workbooks list, you need to select its location under Places:

· OneDrive to find a workbook saved in a folder online on your OneDrive

· This PC to find a workbook file saved in folder on a local drive, which includes your computer’s hard drive or a network drive to which you have access or even a removable storage device connected to your computer or tablet PC

Keep in mind that if your company maintains a SharePoint Team website where you save Excel workbook files and you’ve added it as a place (with the Add a Place link), its name will also appear under the Places heading on the Excel Open screen. In Figure 3-1, for example, Mind Over Media is the name of my SharePoint Team site, and I select this link when I need to open a workbook saved on the website for editing.

As soon as you select the place containing your workbook file, the Excel Open screen displays links to all the folders you’ve recently visited. (See Figure 3-2.) You can click one of these links to select its folder and display its files in the Open dialog box that is then automatically displayed. If none of the recently visited folders hold the workbook you want to open, you can select the Browse button to peruse the folders and files on the selected location in the ensuing Open dialog box.

Figure 3-2: Selecting the Browse button in the Excel Open screen to open a workbook saved on my OneDrive.

When the Open dialog box is displayed, the contents of the selected location or recently visited folder are listed in its main pane. (See Figure 3-3.) If you see the name of the workbook you want to edit in this list, click it, followed by the Open button (or double-click it) to open the workbook in Excel. To open a workbook in another folder, click its link in the left pane of the Open dialog box.

Figure 3-3: Selecting the workbook file to open for editing in the Open dialog box.

If you open a new folder and it appears empty of all files (and you know that it’s not an empty folder), this just means the folder doesn’t contain any of the types of files that Excel can open directly, such as workbooks, template files, and macro sheets. To display all the files, whether or not Excel can open them directly (meaning without some sort of conversion), click the drop-down button that says All Excel Files and then select the All Files option at the top of its drop-down menu.

Note that you can change the way folder and file icons appear in the dialog box. To do so, use the slider attached to the Change Your View drop-down list button located on the right side of the toolbar at the top of the Open dialog box. When you select Large Icons or Extra Large Icons on this slider (or anywhere in between), the Excel workbook icons actually show data in the upper-left corner of the first worksheet. This applies to all Excel 2016 workbooks saved with the Save Thumbnail check box selected and Excel 97 through 2003 workbooks saved with the Save Preview Picture check box on the Summary tab of the workbook’s Properties dialog box selected. This preview of part of the first sheet helps you quickly identify the workbook you want to open for editing or printing.

If you’re comfortable opening workbook files directly from the Open dialog box, you can completely bypass the Open screen by selecting the Don’t Show the Backstage When Opening or Saving Files check box on the Save tab of the Excel Options dialog box (Alt+FTS). Just be aware that when you select this check box, Excel 2016 not only immediately displays the Open dialog box anytime you choose the File  ⇒  Open command to open a workbook file for editing but also displays the Save As dialog box anytime you choose the File  ⇒  Save option (or any of its equivalents) to save a new workbook file. This means that you must be comfortable in using the Save As dialog box to select the place to save a new file as you are in using the Open dialog box to select the place containing the file to edit.

Using the Open dialog box

The Open dialog box that appears after you select a folder in the Excel 2016 Open screen is divided into two sections: a Places panel on the left and a Folder and File List box on the right. (See Figure 3-3.)

When you can’t find the filename you’re looking for in the list box, check to make sure that you’re looking in the right folder — because if you’re not, you’re never going to find the missing file. To tell which folder is currently open, check the Look In drop-down list box at the top of the Open dialog box.

The Directory Path list box displays the full path of the folder currently displayed in the Folder and File List box. If the folder that is currently open is not the one that has the workbook file you need to use, you then need to open the folder that does contain the file. To back up a level in the file path, click the Back button (with the left arrow) that appears to the left of the Look In list box that contains the complete pathname.

For example, say the path to the Excel 2016 folder within the Documents folder on my OneDrive is displayed in the Directory Path list box as

Greg⇒OneDrive ⇒ Documents ⇒ Excel 2016

However, I realize that the Excel workbook I want to open and edit is in the Samples folder that is also located inside the Documents folder on my OneDrive. To get back up a level to the Documents folder, I simply click the Back button one time. The Directory Path list box now contains the following pathname:

Greg ⇒ OneDrive ⇒ Documents

Below, in the center section of the Open dialog box (referred to as the Folders and Files list box), I now see the Samples folder displayed along with the Excel 2016 folder. To open the Samples folder and display the workbook files stored there in the Folders and Files list box, I simply click Samples followed by the Open button (or double-click the Samples folder icon).

If you realize the workbook file you want is on another drive entirely from the one currently displayed, use the buttons in the Places panel on the left side of the Open dialog box (normally Desktop or Documents under Quick Access, Microsoft Excel, OneDrive, This PC, and Network) to easily open any folders associated with these buttons that contain workbook files:

· Desktop: Click this to display folders and folder and file shortcuts saved directly on the Windows desktop of your computing device.

· Documents: Click this button to display folders and workbook files saved in the Personal folder on the Windows hard drive.

· Microsoft Excel: Click this button to display any Excel-related files, such as Add-ins or templates saved in the folder containing the Excel 2016 program file

· OneDrive: Click this button to display a list of folder and files online on your OneDrive.

· This PC: Click this button to display a list of local and network drives as well as devices with removable storage that are connected with your computing device.

· Network: Click this button to display a list of networked drives you’ve mapped on your computer.

Keep in mind that you can choose Preview from the Views button’s drop-down menu. Doing so displays a preview pane on the right side of the Open dialog box. This dialog box shows data in the upper-left corner of the first worksheet for all Excel 2016 workbooks saved with the Save Thumbnail check box selected and all Excel 97 through 2003 workbooks saved with the Save Preview Picture check box on the Summary tab of the workbook’s Properties dialog box selected. This preview of the first part of the initial worksheet can really help you quickly identify the workbook you want to open for editing or printing.

Opening more than one workbook at a time

If you know that you need to edit worksheets saved in multiple workbook files, you can open them all up in Excel at the same time by selecting all their files in the Folder and File List in the Open dialog box before you select the Open button.

Remember that in order to select multiple files that appear sequentially in the Open dialog box, you click the first filename and then hold down the Shift key while you click the last filename. To select files that are not listed sequentially, you need to hold down the Ctrl key while you click the various filenames.

After the workbook files are open in Excel, you can then switch documents by selecting their filename buttons on the Windows taskbar or by using the Flip feature (Alt+Tab) to select the workbook’s thumbnail. (See Book II, Chapter 4 for more information on working on more than one worksheet at a time.)

Finding misplaced workbooks

Everything’s hunky-dory as long as you correctly remember the folder containing the workbook file or files you need to edit. But what about those times when they seem to have mysteriously migrated and are now nowhere to be found? For those rare occasions, you simply use the Search Documents text box in the Open dialog box (see Figure 3-3) that enables you to search for missing notebooks right from within the dialog box.

To use this search feature to find a workbook, click the Search Documents text box in the upper-right corner of the Open dialog box and then begin typing search characters used in the workbook’s filename or contained in the workbook itself.

As Windows finds any matches for the characters you type, the names of the workbook files (and other Excel files such as templates and macro sheets as well) appear in the Open dialog box. As soon as the workbook you want to open is listed, you can open it by clicking its icon and filename followed by the Open button or by double-clicking it.

Using the other Open options

The drop-down menu attached to the Open button in the Open dialog box enables you to open the selected workbook file(s) in special ways. These ways include

· Open Read-Only: Opens the files you select in the Open dialog box’s list box in a read-only state, which means that you can look but you can’t touch. (Actually, you can touch; you just can’t save your changes.) To save changes in a read-only file, you must use the File  ⇒  Save As command from the Excel menu bar and give the workbook file a new filename.

· Open as Copy: Opens a copy of the files you select in the Open dialog box. Use this method of file opening as a safety net: If you mess up the copies, you always have the originals to fall back on.

· Open in Browser: Opens workbook files you save as web pages in your favorite web browser (which would normally be Microsoft Internet Explorer). Note that this command is not available unless the program identifies that the selected file or files were saved as web pages rather than plain, old Excel worksheet files.

· Open in Protected View: Opens the selected workbook in Protected View, which prevents you from making any editing changes to its worksheets until you click the Enable Editing button that appears in the red Protected View panel at the top of the worksheet area.

· Open and Repair: Attempts to repair corrupted workbook files before opening them in Excel. When you select this command, a dialog box appears, giving you a choice between attempting to repair the corrupted file or opening the recovered version, extracting the data out of the corrupted file, and placing it in a new workbook (which you can save with the Save command). Click the Repair button to attempt to recover and open the file. Click the Extract Data button if you previously tried unsuccessfully to have Excel repair the file.

· Show Previous Versions: Displays a list of various versions of the workbook file automatically saved by Excel’s AutoRecover feature and given filenames that describe when the file and under what circumstances the version was saved.

Cell Editing 101

The biggest thing to remember about basic cell editing is that you have to put the cell pointer (also known as the cell cursor) in the cell whose contents you want to modify. When modifying a cell’s contents, you can replace the entry entirely, delete characters from the entry, and/or insert new characters into the entry:

· To replace a cell’s contents, position the cell pointer in the cell and just start inputting your new entry over it. (Remember you can do this by typing from the keyboard or on a touchscreen, writing it by hand using the Windows Inking virtual keyboard.) The moment you start inputting the new entry, the first characters that are input entirely replace the existing data entry. To finish replacing the original entry, complete the new cell entry by using whatever technique you like (such as pressing an arrow key or Enter or clicking the Enter button on the Formula bar). To abort the replacement and restore the original cell entry, click the Cancel button on the Formula bar or press the Escape key on your keyboard.

· To delete characters in a cell entry, click the insertion point in the entry on the Formula bar, press F2, or double-click the mouse in the cell to get Excel into Edit mode (indicated by EDIT on the status bar). Then, use the Home, End, or ← and → keys to move the insertion point to a proper place in the entry and use the Backspace and Delete keys to remove unnecessary or incorrect characters. (Backspace deletes characters to the left of the insertion point, and Delete removes characters to the right of the insertion point.)

· To insert new characters in a cell entry, click the insertion point in the entry on the Formula bar, press F2, or double-click the mouse in the cell to get Excel into Edit mode (indicated by EDIT on the status bar). Then, use the Home, End, or ← and → keys to move the insertion point to the place in the entry where the new characters are needed and start inputting the new characters. Excel automatically inserts the new characters at the insertion point, thus pushing existing text to the right. If Excel replaces existing characters instead, you need to press the Insert key to get out of overtype mode (in which the new characters you input eat up the existing ones on the right) before you start inputting.

When you edit the contents of a cell by inserting and/or deleting characters in it, you need to remember to click the Enter button on the Formula bar or press the Enter key to complete the editing change and switch the program from Edit back to Ready mode (indicated by the reappearance of READY on the status bar). If you’re editing a cell with a simple text or number entry, you can also do this by clicking the mouse pointer in another cell to make it current. (This doesn’t work, however, when you’re editing a formula because Excel just includes the address of the cell that you click as part of the edited formula.) Also, you can’t use any of the keystrokes that normally complete a new cell entry except for the Tab and Shift+Tab keystrokes for moving to the next and previous columns in the worksheet. (All the rest, including the arrow keys, Home, and End, just move the insertion point within the cell entry.)

Undo and Redo

Excel supports multiple levels of undo that you can use to recover from potentially costly editing mistakes that would require data re-entry or extensive repair operations. The most important thing to remember about the Undo command is that it is cumulative, meaning that you may have to select it multiple times to reverse several actions that you’ve taken before you get to the one that sets your spreadsheet right again.

You can select the Undo command either by clicking the Undo button on the Quick Access toolbar or by pressing Alt+Backspace or Ctrl+Z. Excel will then reverse the effect of the last edit you made in the worksheet. For example, if you edit a cell entry and erase some of its text in error, selecting Undo restores the characters that you just erased to the entry. Likewise, if you delete a group of cells by mistake, selecting Undo restores both their contents and formatting to the worksheet.

On the Quick Access toolbar, you can click the drop-down button attached to the Undo command button to display a brief menu of the actions that you’ve recently taken in the spreadsheet. Instead of undoing one action at a time, you undo multiple actions by dragging through them in the drop-down menu. As soon as you release the mouse button, Excel then restores the spreadsheet to the state that it was in before you took all the actions that you chose from this drop-down menu.

When you make an editing change in a spreadsheet, the Undo item on the Undo button’s drop-down menu actually changes to reflect the action that you just took. For example, if you delete a group of cells by pressing the Delete key and then open the Undo button’s drop-down menu, the first item on the Undo menu appears as follows:

Clear

If you then apply new formatting to a cell selection, such as assigning a new center alignment, and then open the Undo drop-down menu, the first item on the Undo menu now appears as follows:

Center Alignment

The Undo feature works by storing a “snapshot” of the worksheet in the memory of your computer at each stage in its editing. Sometimes, if you attempt a large-scale edit in a worksheet, Excel will determine that sufficient free memory doesn’t exist to hold a snapshot of the worksheet in its current state and complete the planned editing change as well. For example, this can happen if you try to cut and paste a really large range in a big worksheet. In such a case, Excel displays an Alert dialog box that indicates a lack of enough memory and asks whether you want to continue without Undo. If you then select the Yes option, Excel completes the planned edit but without the possibility of you being able to reverse its effects with Undo. Before you take such an action, consider how much time and effort would be required to manually restore the worksheet to its previous state if you make a mistake in carrying out your editing change.

After you use the Undo feature to reverse an editing change, the Redo button on the Quick Access toolbar becomes active. The Redo command item on the Redo button’s drop-down menu has the name of the latest type of editing that you just reversed with the Undo button, such as Redo Clear when the last action you took was to restore a cell entry that you just deleted.

You use the Redo command to restore the worksheet to the condition that it was in before you last selected the Undo command. As with using the Undo button on the Quick Access toolbar, when you click the drop-down button attached to the Redo button, you can drag through a series of actions that you want repeated (assuming that you used the Undo command multiple times). You can also restore edits that you’ve undone one at a time by pressing Ctrl+Y.

You can use Undo and Redo to toggle between a Before and After view of your spreadsheet. For example, suppose that you update an entry in a cell that was used in formulas throughout a data table. As soon as you enter the new number in this cell, Excel recalculates the table and displays the new results. To once again view the original version of the table before you make this latest change, you use Undo (Ctrl+Z). After checking some values in the original table, you then restore the latest change to its numbers by selecting the Redo command (Ctrl+Y). You can then continue in this manner as long as you want, switching between Before and After versions by holding down the Ctrl key as you type Z and then type Y, alternating between Undo and then Redo.

Editing in the cell versus on the Formula bar

When doing simple editing to a cell’s contents, the question arises as to whether it’s better to edit the contents in the cell directly or edit the contents on the Formula bar. When editing short entries that fit entirely within the current column width, it really is a matter of personal choice. Some people prefer editing on the Formula bar because it’s out of the way of other cells in the same region of the worksheet. Other people prefer editing on the Formula bar because they find it easier to click the insertion point with the I-beam mouse pointer at precisely the place in the entry that needs editing. (When you press F2 to edit in the cell, Excel always positions the insertion point at the very end of the entry, and when you double-click the thick white mouse pointer in the cell, you really can’t tell exactly where you’re putting the insertion point until you finish double-clicking, at which time you see the flashing insertion point.)

When it comes to editing longer cell entries (that is, text entries that spill over into empty neighboring cells, and numbers that, if their digits weren’t truncated by the number format assigned, wouldn’t fit within the current cell width), you probably will want to edit their contents on the Formula bar. You can click the Formula Bar button (the carat symbol turned downward) to display the entire contents of the cell without obscuring any of the cells of the worksheet.

Get that out of here!

Sometimes you need to delete an entry that you made in a cell of the spreadsheet without replacing it with any other contents. Excel refers to this kind of deletion as clearing the cell. This is actually more correct than referring to it as “emptying” the cell because although the cell may appear empty when you delete its contents, it may still retain the formatting assigned to it, and therefore it will not truly be empty.

For this reason, clicking the Clear button (the one with the eraser icon) in the Editing group on the far right of the Home tab (or pressing Alt+HE) opens a drop-down menu with these options:

· Clear All: Use this to get rid of both the contents and the formatting assigned to the current cell selection.

· Clear Formats: Use this to get rid of just the formatting assigned to the current cell selection without getting rid of the contents.

· Clear Contents: Use this to get rid of just the contents in the current cell selection without getting rid of the formatting assigned to it. (This is the equivalent of pressing the Delete key.)

· Clear Comments: Use this to get rid of just the comments assigned to the cells in the selection without touching either the contents or the formatting.

· Clear Hyperlinks: Use this to remove hyperlinks from the cells in the selection without also removing their formatting.

· Remove Hyperlinks: Use this to remove only the hyperlink from the cells without affecting the contents or the formatting.

The Clear All option is great when you need to truly empty a cell of all formatting and contents while at the same time retaining that empty cell in the worksheet. However, what about when you need to get rid of the cell as well as all its contents? For example, suppose that you entered a column of numbers that you’ve totaled with a summing formula only to discover that midway in the list, you entered the same number twice, in one cell above the other. You don’t want to just delete the duplicate number in one of the two cells, thus leaving a single empty cell in the middle of your list of values. Having an empty cell in the middle of the list won’t skew the total, but it won’t look professional!

In this case, you want to delete both the duplicate entry and remove the newly emptied cell while at the same time pulling up the cells with the rest of the numbers in the list below along with the cell at the end that contains the formula that sums the values together. Excel offers just such a command on the Home tab in the form of the Delete button and its drop-down menu. When you choose Delete Cells from the Delete button’s drop-down menu (or press Alt+HDD), a Delete dialog box appears, similar to the one shown in Figure 3-4. This dialog box lets you choose how you want the remaining cells to be shifted when the selected cell (or cells) is removed from the worksheet.

Figure 3-4: Deleting a cell with a duplicate entry.

Keep in mind that when you use the Delete Cells option, Excel zaps everything, including the contents, formatting, and any and all attached comments. Don’t forget about the Undo button on the Quick Access toolbar or Ctrl+Z in case you ever zap something you shouldn’t have!

Figures 3-4 and 3-5 illustrate how Delete works in the example where a duplicate entry has been mistakenly entered in a column of numbers that is totaled by a summing formula. In Figure 3-4, I selected cells A5:B5, which contain duplicate entries, before clicking the Delete button’s drop-down button and then choosing Delete Cells from its drop-down menu to display the Delete dialog box.

Figure 3-5: Worksheet after deleting the cell with the duplicate entry.

As this figure shows, when the Delete dialog box opens, the Shift Cells Up option button is automatically selected. Figure 3-5 shows the same worksheet after clicking the OK button in the Delete dialog box. Notice how Excel pulled up the entries in the cells below when it deleted the duplicate in cell B5, while at the same time automatically recalculating the summing formula to reflect the total of the remaining entries.

Don’t confuse the use of the Delete key and the Delete Cells command. When you press the Delete key, Excel automatically deletes just the contents of the cells that are selected (keeping whatever formatting is used intact), leaving seemingly blank cells in the worksheet. When you choose Delete Cells from the Delete button’s drop-down menu, Excel displays the Delete dialog box, which deletes the selected cells and then shifts the remaining cells in the direction that you designate (up or to the left) to fill in what would otherwise be blank cells.

If you know that you want to use the Shift Cells Up option when deleting the current cell selection, you don’t have to bother with opening the Delete dialog box at all: Simply click the Delete button (rather than its drop-down button), and Excel instantly deletes the selection and pulls all remaining cells up.

Can I just squeeze this in here?

The Insert command button in the Editing group of the Ribbon’s Home tab is set very much like the Delete button immediately below it. You click the Insert button’s drop-down button and then its Insert Cells option (or press Alt+HII) to open an Insert dialog box, where you indicate how Excel is to deal with existing cell entries in order to accommodate the blank cells you need to squeeze in.

For example, suppose that you discover that you’ve left out three numbers from a column of summed numbers and that these values should have appeared in the middle of the column. To make this edit, position the cell cursor in the first cell of those cells whose values need to be shifted down to make room for the three missing entries and then drag the cell cursor down two rows so that you have selected the three cells with entries that you want to retain but also need to have moved down.

Figures 3-6 and 3-7 illustrate this situation. In Figure 3-6, I selected the cell range A5:B7, where cells for the six missing entries are to be inserted. I then clicked the drop-down button on the Insert button followed by Insert Cells on its drop-down menu. This action opened the Insert dialog box with the Shift Cells Right option button selected. Because I needed to have the cells in the selected range moved down to make room for the missing entries, I then simply selected the Shift Cells Down option before selecting OK.

Figure 3-6: Inserting six blank cells for missing entries in two columns of a table while shifting the existing entries down.

Figure 3-7: The worksheet table after entering the missing entries in the newly inserted blank cells.

After clicking OK in the Insert dialog box, Excel moves down the existing entries as follows:

· 2000 David Court and $550,000 previously in cells A5 and B5 to A8 and B8, respectively

· 892 Miller Lane and $385,500 previously in cells A6 and B6 to A9 and B9; respectively

· The total of $1,560,500 previously in cell B7 to B10.

This leaves the previously occupied range A5:B7 with six blank cells, where I can enter the following values:

· 34 6th Avenue and $125,000 in new blank cells A5 and B5, respectively

· 347 Oak Road and $350,000 in new blank cells A6 and B6, respectively

· 25 Smith Ranch and $285,000 in new blank cells A7 and B7, respectively

As you can see, the sum formula in the last cell in this column, cell B10, has automatically been recalculated so that the total reflects the addition of the missing values that I entered in the newly inserted cells.

If you know that you want to move existing cells down with the Shift Cells Down option when inserting new cells in the current cell selection, you don’t have to bother with opening the Insert dialog box at all: Simply click the Insert button (rather than its drop-down button), and Excel instantly inserts new cells while moving the existing ones down.

A Spreadsheet with a View

The biggest problem with editing is finding and getting to the place in the worksheet that needs modification and then keeping your place in the worksheet as you make the changes. This problem is exacerbated by the fact that you probably often work with really large spreadsheets, only a small portion of which can be displayed at any one time on your screen.

Excel provides a number of features that can help you find your way and keep your place in the spreadsheet that needs editing. Among these are its Zoom feature, which enables you to increase or decrease the magnification of the worksheet window, thus making it possible to switch from a really up-close view to a really far-away view in seconds, and its Freeze Panes feature, which enables you to keep pertinent information, such as column and row headings, on the worksheet window as you scroll other columns and rows of data into view.

“Zoom, zoom, zoom”

Excel 2016 makes it really easy to see more data in the active worksheet window with its Zoom slider feature on the Status bar in the lower-right corner of the window. The Zoom slider contains two buttons on either end: a Zoom Out button on the left side that reduces the Worksheet area’s magnification percentage by 10 percent each time you click it and a Zoom In button on the right side that increases the Worksheet area’s magnification percentage by 10 percent each time you click it. You can also quickly change the Worksheet area’s magnification percentage (and thus zoom out and in on the data) by dragging the slider’s button to the left or right.

Note that the Zoom slider button is always located in the very center of the Zoom slider, putting the Worksheet area magnification at 100% (the normal screen, depending upon your computer monitor’s screen resolution) when you first open the worksheet. As you click the Zoom Out or Zoom In button or drag the slider button, Excel keeps you informed of the current magnification percentage by displaying it to the immediate left of the Zoom Out button on the status bar. Note too, that 10% is the lowest percentage you can select by dragging the button all the way to the left on the slider, and 400% is the highest percentage you can select by dragging the button all the way to the right.

On a touchscreen device, you can use your fingers to zoom in and out on a worksheet. Simply stretch your forefinger and thumb out to zoom in on the cells and pinch them together to zoom out. As you do, the Zoom slider on the status bar shows you the currently selected zoom percentage and moves the slider right or left to follow your stretch or pinch gesture.

Although the Zoom slider is always available on the status bar in any worksheet you have open, you can change the Worksheet area’s magnification percentage by clicking the Zoom button on the Ribbon’s View tab or by pressing Alt+WQ. Doing this opens the Zoom dialog box, where you can select preset magnification percentages 200%, 100%, 75%, 50%, and 25% by clicking its option button before you click OK. In addition, you may enter any magnification percentage between a minimum of 10% and a maximum of 400% by clicking its Custom options button and entering the percentage in its text box before you click OK.

You can also have Excel change the magnification to suit the cell range that you’ve selected. To do this, select your cell range, click the Zoom to Selection button on the View tab, or press Alt+WG. Note that you can also do this same thing by clicking the Fit Selection option button when the Zoom dialog box is open before you click OK.

If the device you’re running Excel 2016 on is equipped with a physical mouse with a wheel in between the two mouse buttons (sometimes referred to as an IntelliMouse), you can set it up in Excel so that rolling the wheel back and forth zooms out and in on the current worksheet. To do this, click the Zoom on Roll with IntelliMouse check box in the Editing Options section of the Advanced tab of the Excel Options dialog box (File  ⇒  Options  ⇒  Advanced or Alt+FTA). After you select this check box, instead of scrolling up the rows of the worksheet, rolling the wheel forward increases the magnification (by 15% until you reach the maximum 400%). Instead of scrolling down the rows of the sheet, rolling the wheel backward decreases the magnification (by 15% until you reach the minimum 10% value).

Figures 3-8 and 3-9 illustrate how you can use the Zoom feature to first zoom out to locate a region in a large spreadsheet that needs editing and then zoom in on the region to do the editing. In Figure 3-8, I zoomed out on the Income Analysis to display all its data by selecting a 50% magnification setting. (I actually did this by dragging the Zoom slider button to the left until 50% appeared on the status bar to the left of the Zoom Out button.) At the 50% setting, I could just barely make out the headings and read the numbers in the cells. I then located the cells that needed editing and selected their cell range (J20:L25) in the worksheet.

Figure 3-8: The Income Analysis worksheet after zooming out to a 50% magnification setting.

Figure 3-9: Worksheet at 232% magnification after zooming in on the cell range J20:L25.

After selecting the range of cells to be edited, I then clicked the Zoom to Selection button on the View tab. You can see the result in Figure 3-9. As you can see on the status bar, Excel boosted the magnification from 50% up to 232% the moment I clicked the Zoom to Selection button: a comfortable size for editing these cells on even one of the smaller computer monitors.

Because Excel immediately puts the slider button at whatever point you click, you can instantly return the magnification percentage to the normal 100% after selecting any other magnification. Simply click the line at the midpoint in the Zoom slider on the status bar.

Freezing window panes

Figure 3-9 could be the poster boy for the Freeze Panes feature. Although zooming in on the range of cells that needs editing has made their data entries easy to read, it has also removed all the column and row headings that give you any clue as to what kind of data you’re looking at. If I had used the Freeze Panes command to freeze column A with the row headings and row 2 with the column headings, they would remain displayed on the screen — regardless of the magnification settings that I select or how I scroll through the cells.

To use the Freeze Panes feature in this manner, you first position the cell pointer in the cell that’s located to the immediate right of the column or columns that you want to freeze and immediately beneath the row or rows that you want to freeze before you click the Freeze Panes button on the Ribbon’s View tab followed by Freeze Panes on the button’s drop-down menu. (You can also do this by pressing Alt+WF and pressing the Enter key to choose the Freeze Pane option from the drop-down menu.)

To freeze the top row of the worksheet (assuming that it contains column headings) from anywhere in the worksheet (it doesn’t matter where the cell cursor is), choose the Freeze Top Row option from the Freeze Panes button’s drop-down menu. If you want to freeze the first column (assuming that it contains row headings) from anywhere in the worksheet, choose the Freeze First Column option from the Freeze Panes button’s drop-down menu instead.

Figures 3-10 and 3-11 illustrate how this works. Figure 3-10 shows the Income Analysis spreadsheet after freezing column A and rows 1 and 2. To do this, I positioned the cell cursor in cell B3 before choosing Freeze Panes from the Freeze Panes button’s drop-down menu. Notice the thin black line that runs down column A and across row 2, marking which column and rows of the worksheet are frozen on the display and that will now remain in view — regardless of how far you scroll to the right to new columns or scroll down to new rows.

Figure 3-10: The income worksheet after freezing column A and rows 1:2 in the worksheet display.

Figure 3-11: The income worksheet after zooming in on the cell range J20:L25 after freezing panes.

As Figure 3-11 shows, frozen panes stay on the screen even when you zoom in and out on the worksheet. For Figure 3-11, I repeated the steps I took in changing the magnification for Figures 3-8 and 3-9 (only this time with the frozen panes in place). First, I zoomed out on the Income Analysis spreadsheet by dialing the 50% magnification setting on the Zoom slider; second, I selected the range J20:L25 and then clicked the Zoom to Selection button on the View tab.

Figure 3-11 shows the result. Note that with the frozen panes in place, this time Excel only selected a 172% magnification setting instead of the original 231% setting. This lower magnification setting is worth it because of all the important information that has been added to the cell range.

When you press the Ctrl+Home shortcut key after you’ve frozen panes in a worksheet, instead of positioning the cell cursor in cell A1 as normal, Excel positions the cell cursor in the first unfrozen cell. In the example illustrated in Figure 3-10, pressing Ctrl+Home from anywhere in the worksheet puts the cell cursor in B3. From there, you can position the cell cursor in A1 either by clicking the cell or by pressing the arrow keys.

To unfreeze the panes after you’ve finished editing, click the Unfreeze Panes option on the Freeze Panes button’s drop-down menu. (This option replaces Freeze Panes at the top of the menu.)

Freeze Panes in the worksheet display have a parallel feature when printing a spreadsheet called Print Titles. When you use Print Titles in a report, the columns and rows that you define as the titles are printed at the top and to the left of all data on each page of the report. (See Book II, Chapter 5 for details.)

Saving custom views

In the course of creating and editing a worksheet, you may find that you need to modify the worksheet display many times as you work with the document. For example, you may find at some point that you need to reduce the magnification of the worksheet display to 75% magnification. At another point, you may need to return to 100% magnification and hide different columns in the worksheet. At some later point, you may have to redisplay the hidden columns and then freeze panes in the worksheet.

Excel’s Custom Views feature enables you to save any of these types of changes to the worksheet display. This way, instead of taking the time to manually set up the worksheet display that you want, you can have Excel re-create it for you simply by selecting the view. When you create a view, Excel can save any of the following settings: the current cell selection, print settings (including different page setups), column widths and row heights (including hidden columns), display settings on the Advanced tab of the Excel Options dialog box, as well as the current position and size of the document window and the window pane arrangement (including frozen panes).

To create a custom view of your worksheet, follow these steps:

1. Make all the necessary changes to the worksheet display so that the worksheet window appears exactly as you want it to appear each time you select the view. Also select all the print settings on the Page Layout tab that you want used in printing the view. (See Book II, Chapter 5 for details.)

2. Click the Custom Views command button in the Workbook Views group at the beginning of the View tab or press Alt+WC.

This action opens the Custom Views dialog box, similar to the one shown in Figure 3-12, where you add the view that you’ve just set up in the worksheet.

3. Click the Add button.

This action opens the Add View dialog box, where you type a name for your new view.

4. Enter a unique descriptive name for your view in the Name text box.

Make sure that the name you give the view reflects all its pertinent settings.

5. To include print settings and hidden columns and rows in your view, leave the Print Settings and Hidden Rows, Columns and Filter Settings check boxes selected when you click the OK button. If you don’t want to include these settings, clear the check mark from either one or both of these check boxes before you click OK.

When you click OK, Excel closes the Custom Views dialog box. When you next open this dialog box, the name of your new view appears in the Views list box.

6. Click the Close button to close the Custom Views dialog box.

Custom views are saved as part of the workbook file. To be able to use them whenever you open the spreadsheet for editing, you need to save the workbook with the new view.

7. Click the Save button on the Quick Access toolbar or press Ctrl+S to save the new view as part of the workbook file.

Figure 3-12: Adding a new view for the worksheet in the Custom Views dialog box.

After you create your views, you can display the worksheet in that view at any time while working with the spreadsheet. To display a view, follow these steps:

1. Click the Custom Views command button in the Workbook Views group on the View tab or press Alt+WC.

2. Double-click the name of the view that you want to use in displaying your worksheet in the Views list box or click the name and then click the Show button.

Always start by defining a Normal 100% view in the Custom Views dialog box that represents the standard view of the worksheet before you go about defining custom views that hide columns, freeze panes, and mess with the worksheet’s magnification. This way, you can recover from a special view (especially one that you only use in printing part of the spreadsheet but never use when editing it) simply by double-clicking Normal 100% in the Views list box of the Custom Views dialog box.

Copying and Moving Stuff Around

Moving and copying worksheet data are among the most common editing tasks that you perform when editing a typical spreadsheet. Excel offers two basic methods for moving and copying a cell selection in a worksheet: First, you can use drag-and-drop to drag the cells to a new location, or second, you can cut or copy the contents to the Clipboard and then paste them into the desired area. Moving and copying data to new areas in a spreadsheet are basically very straightforward procedures. You need to keep a few things in mind, however, when rearranging cell entries in a worksheet:

· When you move or copy a cell, Excel moves everything in the cell, including the contents, formatting, and any comment assigned to the cell. (See Book IV, Chapter 3, for information on adding comments to cells.)

· If you move or copy a cell so that it overlays an existing entry, Excel replaces the existing entry with the contents and formatting of the cell that you’re moving or copying. This means that you can replace existing data in a range without having to clear the range before moving or copying the replacement entries. It also means that you must be careful not to overlay any part of an existing range that you don’t want replaced with the relocated or copied cell entries.

· When you move cells referred to in formulas in a worksheet, Excel automatically adjusts the cell references in the formulas to reflect their new locations in the worksheet.

· When you copy formulas that contain cell references, Excel automatically adjusts the cell references in the copies relative to the change in their position in the worksheet. (See Book III, Chapter 1, for details on copying formulas in a spreadsheet.)

For situations in which you need to copy only a single data entry to cells in a single row or to cells in a single column of the worksheet, keep in mind that you can use AutoFill to extend the selection left or right or up or down by dragging the Fill handle. (See Book II, Chapter 1, for information about using AutoFill to extend and copy a cell entry.)

Doing it with drag-and-drop

Drag-and-drop provides the most direct way to move or copy a range of cells in a single worksheet. To move a range, simply select the cells, position the pointer on any one of the edges of the range, and then drag the range to its new position in the worksheet and release the mouse button.

Note that it can be a real drag to use drag-and-drop to copy or move a cell selection when the first cell of the range into which the cells are being copied or moved is far away in an unseen part of the Excel worksheet area. To make it easier, set up windows in the Excel worksheet area that display both the cells that you’re moving or copying and the cells into which they’re being moved or copied. (See Book II, Chapter 4, for information on setting up windows that enable this.) Use the cut-and-paste method (as described in the later section, “Carried away with cut-and-paste”) to move and copy cell selections beyond the current worksheet when you don’t have such windows set up.

You can use drag-and-drop with a stylus on your touchscreen device if it’s so equipped. If you’re using your finger, however, you must tap the cell selection and then use the Cut or Copy option followed by the Paste option on the mini-toolbar that appears (see “Carried away with cut-and-paste,” later in this chapter).

When you use drag-and-drop with a stylus, Excel displays a pop-up menu when you release the rectangular outline indicating where the range is to be moved or copied in a new area of the worksheet by removing the stylus’ pointer from the touchscreen. You then select between the Move Here and Copy Here options on this pop-up menu to complete the move or copy operation.

Moving cells with drag-and-drop

The only thing that you need to be mindful of when using drag-and-drop is that you must position the mouse (or stylus) pointer on one of the edges of the cell range and wait until the pointer’s cursor changes shape from a thick white cross to an outlined arrowhead pointing to the center of a black cross, before you begin dragging the range to its new position in the worksheet. Also, when positioning the pointer on an edge of the range, avoid the lower-right corner because locating the pointer there transforms it into the Fill handle (a simple black cross) used by the AutoFill feature to extend the cell range rather than move the range.

As you drag a cell range using drag-and-drop, Excel displays only the outline of the range with a ScreenTip that keeps you informed of its new cell or range address. After you’ve positioned the outline of the selected range so that the outline surrounds the appropriate cells in a new area of the worksheet, simply release the mouse button or your finger or stylus from the touchscreen. Excel moves the selected cells (including the entries, formatting, and comments) to this area.

If the outline of the cell selection that you’re dropping encloses any cells with existing data entries, Excel displays an Alert dialog box asking whether you want to replace the contents of the destination cells. If you click OK in this dialog box, the overlaid data entries are completely zapped when they’re replaced by the incoming entries.

Copying cells with drag-and-drop

You can use drag-and-drop to copy cell ranges as well as to move them. To modify drag-and-drop so that the feature copies the selected cells rather than relocating them, hold down the Ctrl key when you position the mouse pointer on one of the edges of the selected range. Excel indicates that drag-and-drop is ready to copy rather than move the cell selection by changing the mouse pointer to an outline pointer with a small plus sign in the upper-right. When the pointer assumes this shape, you simply drag the outline of the selected cell range to the desired position and release both the Ctrl key and mouse button.

Carried away with cut-and-paste

Given the convenience of using drag-and-drop, you may still prefer to use the more traditional cut-and-paste method when moving or copying cells in a worksheet. This is especially true when running Excel 2016 on a touchscreen device that doesn’t have access to a mouse. Cut-and-paste uses the Clipboard (a special area of memory shared by all Windows programs), which provides a temporary storage area for the data in your cell selection until you paste the selection into its new position in the worksheet.

To move a cell selection, click the Cut command button (the one with the scissors icon) in the Clipboard group at the beginning of the Ribbon’s Home tab (or press the shortcuts, Alt+HX, Ctrl+X, or Shift+Delete). To copy the selection, click the Copy command button (with the two sheets of paper side by side immediately beneath the Cut button) on the Home tab (or press the shortcuts, Alt+HC, Ctrl+C, or Ctrl+Insert).

When you cut or copy a selection to the Clipboard, Excel displays a marquee around the cell selection (sometimes called marching ants), and the following message appears on the status bar:

Select destination and press ENTER or choose Paste

To complete the move or copy operation, simply select the first cell in the range where you want the relocated or copied selection to appear and then press the Enter key, click the Paste button on the Home tab, or press the shortcuts Alt+HV, Ctrl+V, or Shift+Insert. Excel then completes the move or copy operation, pasting the range as required, starting with the active cell. When selecting the first cell of a paste range, be sure that you have sufficient blank cells below and to the right of the active cell so that the range you’re pasting doesn’t overlay any existing data that you don’t want Excel to replace.

Unlike when moving and copying a cell selection with drag-and-drop, the cut-and-paste method doesn’t warn you when it’s about to replace existing cell entries in cells that are overlaid by the incoming cell range — it just goes ahead and replaces them with nary a beep or an alert! If you find that you moved the selection to the wrong area or replaced cells in error, immediately click the Undo button on the Quick Access toolbar or press Ctrl+Z to restore the range to its previous position in the worksheet.

“Paste it again, Sam”

When you complete a copy operation with cut-and-paste by clicking the Paste button in the Clipboard group at the beginning of the Ribbon’s Home tab instead of pressing the Enter key, Excel copies the selected cell range to the paste area in the worksheet without removing the marquee from the original range. You can continue to paste the selection to other areas in the worksheet without having to open the Clipboard task pane to recopy the cell range to the Clipboard. If you don’t need to paste the cell range in any other place in the worksheet, you can press Enter to complete the copy operation. If you don’t need to make further copies after using the Paste command, you can remove the marquee from the original selection simply by pressing the Escape or the Enter key.

Also, when you paste a cell selection that you’ve copied to the Clipboard (this doesn’t apply when pasting cells that you’ve cut to the Clipboard), Excel displays the Paste Options button in the lower-right corner of the cell selection (marked with the word Ctrl). When you position the mouse pointer over this Paste Options button (or press the Ctrl key), a palette of buttons divided into three sections (Paste, Paste Values, and Other Paste Options) appears as shown in Figure 3-13.

Figure 3-13: The Paste Option button’s palette with the option buttons that commonly appear after pasting a cell selection that’s been copied to the Clipboard.

The buttons in these three sections of the Paste Options palette offer you the following choices for refining your paste operation. The seven buttons that appear in the Paste section include

· Paste (P): Excel pastes everything in the cell selection (text, values, formulas, and cell formatting).

· Formulas (F): Excel pastes all the text, numbers, and formulas in the current cell selection without their formatting.

· Formulas & Number Formatting (O): Excel pastes the number formats assigned to the copied values along with their formulas.

· Keep Source Formatting (K): Excel copies the formatting from the original cells and pastes this into the destination cells (along with the copied entries).

· No Borders (B): Excel pastes everything in the cell selection without copying any borders applied to its cell range.

· Keep Source Column Widths (W): Excel makes the width of the columns in the destination range the same as those in the source range when it copies their cell entries.

· Transpose (T): Excel changes the orientation of the pasted entries. For example, if the original cell entries run down the rows of a single column of the worksheet, the transposed pasted entries will run across the columns of a single row.

The three buttons that appear in the Paste Values section of the Paste Options palette include

· Values (V): Excel pastes only the calculated results of any formulas in the source cell range.

· Values & Number Formatting (A): Excel pastes the calculated results of any formulas along with all the formatting assigned to the labels, values, and formulas in the source cell range into the destination range. This means that all the labels and values in the destination range appear formatted just like the source range even though all the original formulas are lost and only the calculated values are retained.

· Values & Source Formatting (E): Excel pastes the calculated results of all formulas along with formatting assigned to source cell range.

The four buttons that may appear in the Other Paste Options section of the Paste Options palette include

· Formatting (R): Excel pastes only the formatting (and not the entries) copied from the source cell range to the destination range.

· Paste Link (N): Excel creates linking formulas in the destination range so that any changes that you make to the entries in cells in the source range are immediately brought forward and reflected in the corresponding cells of the destination range.

· Picture (U): Excel pastes only the pictures in the copied cell selection.

· Linked Picture (I): Excel pastes a link to the pictures in the copied cell selection.

Taking it out of the Clipboard task pane

Excel puts the contents of all cell selections that you copy and paste (using the Copy and Paste command buttons or their keyboard equivalents) into the Office Clipboard. In fact, as you edit your spreadsheet in this manner, the Clipboard stores the contents of up to the last 24 copied-and-pasted cell selections (before replacing them with new copied-and-pasted selections). Up to that time, you can examine the contents of the Clipboard and even paste your cell selections in other places in your spreadsheet or in documents open in other programs that you’re running. (See Book IV, Chapter 4 for information about pasting Excel data from the Clipboard into other applications.)

To open the Clipboard task pane on the left side of the Excel program window, click the Dialog Box launcher in the Clipboard group on the Ribbon’s Home tab (the button in the lower-right corner of the Clipboard group with an arrow pointing downward at a diagonal 45-degree angle).

When the Clipboard task pane is displayed, it shows all the individual copied-and-pasted items that have been placed there (up to a maximum of 24). While this pane is open, Excel also places there all selections that you cut or copy in the worksheet, even those that you paste by pressing the Enter key as well as those you don’t paste elsewhere.

If you want Excel to place all selections that you cut and copy in the worksheet into the Office Clipboard even when the Clipboard task pane is not open, click the Collect Without Showing Office Clipboard item on the Options button’s drop-down menu at the bottom of the Clipboard pane.

To paste an item on the Clipboard into a cell of one of your worksheets, click the cell and then position the mouse pointer over the item in the Clipboard task pane. When the item’s pop-up button appears, click it and then choose Paste from the pop-up menu, shown in Figure 3-13.

If you’re doing a lot of cut-and-paste work in a spreadsheet using the Clipboard, you can have Excel automatically display the Clipboard task pane as you do the editing. Simply open the Clipboard task pane, click the Options button at the very bottom, and then choose the Show Office Clipboard Automatically option from its pop-up menu to select this setting. When this setting is selected, Excel automatically opens the Clipboard task pane if you put more than two items in the Clipboard during your work session. To have Excel display the Clipboard task pane when you press Ctrl+C twice in a row (Ctrl+CC), choose the Show Clipboard When Ctrl+C Pressed Twice option from this menu.

Inserting rather than replacing copied cells

When you use cut-and-paste to move or copy a cell selection, you can have Excel paste the data into the worksheet without replacing existing entries in overlaid cells by choosing the Insert Cut Cells or Insert Copied Cells from the Insert button’s drop-down menu (depending on whether you cut or copied the cells to the Clipboard) on the Ribbon’s Home tab instead of clicking the normal Paste command button. Excel then displays the Insert Paste dialog box, where you can choose between a Shift Cells Right and a Shift Cells Down option button. Select Shift Cells Right to have existing cells moved to columns on the right to make room for the moved or copied cells. Select Shift Cells Down to have the existing cells moved to lower rows to make room for them.

If you want to shift existing cells down to make room for the ones you’ve cut or copied to the Clipboard, you can simply click the Insert button on the Home tab rather than bothering to click the Insert Cut Cells or Insert Copied Cells option on the button’s drop-down menu.

Pasting just the good parts with Paste Special

Normally, when you paste worksheet data from the Clipboard, Excel pastes all the information (entries, formatting, and comments) from the cell selection into the designated paste area, thus replacing any existing entries in the cells that are overlaid. You can, however, use the options on the Paste button’s drop-down menu or use the options in the Paste Special dialog box (by choosing Paste Special from this drop-down menu or pressing Alt+HVS) to control what information is pasted into the paste range.

If you open the Paste Special dialog box (see Figure 3-14), you also have access to options that perform simple mathematical computations (Add, Subtract, Multiply, and Divide) between the number of cell entries that overlay each other. (See Table 3-1.)

Figure 3-14: The paste options in the Paste Special dialog box give you control over how a cell selection on the Clipboard is pasted into your worksheet.

Table 3-1 The Paste Special Dialog Box Options

The options in the Paste Special dialog box are divided into two areas: Paste and Operation. The Paste option buttons (some of which duplicate the options on the drop-down menu on the Ribbon and the Paste Option’s palette in the worksheet) enable you to specify which components of the copied cell selection you want copied; see Table 3-1 for a list of options.

The Operation option buttons in the Paste Special dialog box enable you to specify which mathematical operation, if any, should be performed between the overlaying values in copy and paste ranges. Select the Skip Blanks check box when you don’t want Excel to replace existing entries in the paste range with overlaying blank cells in the copy range.

The Transpose option, which appears on the Paste button’s drop-down menu and the Paste Options button (also duplicated by the Transpose check box in the Paste Special dialog box), is particularly helpful when you have a row of column headings that you want to convert into a column of row headings or when you have a column of row headings that you want to convert into a row of column headings. You can also use this option to pivot an entire table of data so that the data that runs across the rows now runs down the columns, and vice versa.

Figure 3-15 illustrates just such a situation. Here, I selected the production schedule table (including the column headings) in the cell range A2:J6, clicked the Copy button on the Home tab of the Ribbon, and then moved the cell cursor to cell A8. After that, I chose the Transpose option from the Paste button’s drop-down menu. Excel’s Live Preview feature then shows how this transposition would appear in the cell range A8:E17 in Figure 3-15.

Figure 3-15: Transposing a copy of the production schedule table so that dates now form the row headings and the part numbers now form the column headings.

In the transposed table, the original row headings are now the column headings just as the original column headings are now the row headings. Note, too, that in transposing the table, Excel retained the formulas that total the units produced each month, although now they appear in the last column of the table instead of the last row.

To convert a cell range that contains formulas to its calculated values (as though you had input them as numbers), select the cell range, click the Copy button on the Home tab, and then choose the Paste Values option from the Paste button’s drop-down menu without moving the cell cursor. This causes Excel to paste the calculated values on top of the formulas that created them, thus zapping the overlaid formulas and leaving you with only the computed values!

Find and Replace This Disgrace!

No discussion of spreadsheet editing would be complete without including the Find and Replace features in Excel. You can use the Find feature to quickly locate each and every occurrence of a specific string (a series of characters) in a worksheet. You can use the Replace feature to have Excel actually update the cells that it finds with new text or numbers.

Both the Find and the Replace features share the same dialog box (aptly called the Find and Replace dialog box). If you only want to find a cell’s particular contents, you just use the options on the Find tab. (The Find tab is automatically selected when you open the Find and Replace dialog box by clicking the Find option on the Find & Select button’s drop-down menu on the Home tab of the Ribbon, or when you press Alt+HFDF or simply Ctrl+F.) If you want to update the contents of some or all of the cells that you find, use the options on the Replace tab (which is automatically selected when you open the Find and Replace dialog box by choosing the Replace option from the Find & Select button’s drop-down menu, or when you press Alt+HFDR or simply Ctrl+H).

The Find and Replace tabs in the Find and Replace dialog box contain a bunch of search options that you can use in finding and replacing stuff in your spreadsheet. The only problem is that these options are hidden when you first open the Find and Replace dialog box. To expand the Find and Replace dialog box to display the extra search options on the Find and Replace tab, click the Options button.

Finding stuff

To use the Find command to locate information in your worksheet, follow these steps:

1. To search the entire worksheet, select a single cell. To restrict the search to a specific cell range or nonadjacent selection, select all the cells to be searched.

2. Choose the Find option from the Find & Select button’s drop-down menu on the Ribbon’s Home tab or press Ctrl+F.

Excel opens the Find and Replace dialog box with the Find tab selected.

3. Type the search string that you want to locate in the Find What combo box.

When entering the search string, you can use the question mark (?) or asterisk (*) wildcards to stand in for any characters that you’re unsure of. Use the question mark to stand for a single character, as in Sm?th, which will match either Smith or Smyth. Use the asterisk to stand formultiple characters as in 9*1, which will locate 91, 94901, or even 9553 1st Street. To search with the asterisk as a wildcard character, precede the character with a tilde (~), as in ~*2.5, to locate formulas that are multiplied by the number 2.5. (The asterisk is the multiplication operator in Excel.)

If the cell holding the search string that you’re looking for is formatted in a particular way, you can narrow the search by specifying what formatting to search for.

4. Click the Options button and then click the Format drop-down button to specify the formatting to search for in addition to your search string. Click the Format button to select the formatting from the Find Format dialog box or click Choose Format from Cell to select the formatting directly from a cell in the worksheet.

When you click the Format button, Excel opens a Find Format dialog box with the same tabs and options as the standard Format Cells dialog box. You then select the formatting that you want to search for in this dialog box and click OK.

When you choose the Choose Format from Cell item from the Format button’s drop-down menu, the Find and Replace dialog box temporarily disappears until you click the cell in the worksheet that contains the formatting that you want to search for with the thick, white-cross mouse pointer with eyedropper icon.

Note that when using the Find feature to locate a search string, by default, Excel searches only the current worksheet for your search string. If you want Excel to search all the cells of all worksheets in the workbook, you need to follow Step 5.

5. Choose the Workbook option from the Within drop-down menu to have Excel search all worksheets in the workbook.

If the Within drop-down list box doesn’t appear at the bottom of your Find and Replace dialog box, click the Options button to expand it and add the Within, Search, and Look In drop-down lists along with the Match Case and Match Entire Cell Contents check boxes.

By default, Excel searches across the rows in the worksheet or current selection (that is, to the right and then down from the active cell). If you want to have the program search down the columns and then across the rows, you need to follow Step 6.

6. Choose the By Columns option from the Search drop-down menu to have Excel search down the columns (that is, down and then to the right from the active cell).

By default, Excel locates the search string in the contents of each cell as entered on the Formula bar. This means that if you’re looking for a cell that contains 1,250 and the spreadsheet contains the formula =750+500, whose calculated value as displayed in the cell is 1,250, Excel won’t consider this cell to be a match because in searching the Formula bar, it finds =750+500 instead of 1,250.

To have Excel search the contents of each cell (and thus, consider a cell that displays your value to be a match even when its contents on the Formula bar don’t contain the search string), you need to change the Look In setting from Formulas to Values. If you want Excel to search for the search string in the comments you’ve added to cells, you need to change the Look In setting to Comments.

7. Choose Values from the Look In drop-down menu to have Excel locate the search string in the contents of each cell as it’s displayed in the worksheet. Choose Comments from this drop-down menu instead to have Excel locate the search string in the comments that you’ve added to cells.

Note that when you select Comments to search the comments you’ve added to the spreadsheet, you can’t specify any formatting to search for because the Format button in the Find and Replace dialog box becomes grayed out.

By default, Excel ignores case differences between the search string and the content of the cells being searched so that Assets, ASSETS, and assets all match the search string, Assets. To find only exact matches, follow Step 8.

8. Select the Match Case check box to find occurrences of the search string when it matches the case that you entered.

By default, Excel considers any occurrence of the search string to be a match — even when it occurs as part of another part of the cell entry. So when the search string is 25, cells containing 25, 15.25, 25 Main Street, and 250,000 are all considered matches. To find only complete occurrences of your search string in a cell, follow Step 9.

9. Select the Match Entire Cell Contents check box to find occurrences of the search string only when it’s the entire cell entry.

After you’ve entered the search string and search options as you want them, you’re ready to start searching the spreadsheet.

10. Click the Find All button to find all occurrences of the search string. Click the Find Next button to find just the first occurrence of the search string.

When you click Find All, Excel lists all the cells containing the search string in a list box at the bottom of the Find and Replace dialog box, as shown in Figure 3-16. You can then have Excel select the cell with a particular occurrence by clicking its link in this list box. You may have to drag the Find and Replace dialog box out of the way to see the selected cell.

When you click Find Next, Excel selects the next cell in the spreadsheet (using the designated search direction). To find subsequent occurrences of the search string, you need to continue to click Find Next until you reach the cell that you’re looking for. Again, you may have to drag the Find and Replace dialog box out of the way to see the cell that Excel has located and selected in the worksheet.

11. After you finish searching the spreadsheet for the search string, click the Close button.

Figure 3-16: Finding a value in a worksheet by using the options on the Find tab.

Note that Excel retains your search string and search option conditions even after closing the Find and Replace dialog box. To repeat a search, just press Ctrl+F and then click Find All or Find Next. You can also reinstate a search string that you used earlier in your work session by choosing it from the Find What drop-down menu.

Finding and replacing stuff

The Find feature is sufficient if all you want to do is locate an occurrence of a search string in your worksheet. Many times, however, you will also want to change some or all of the cells that match the search string. For those situations, you use the Replace feature to locate the search string and replace it with some other string.

To search and replace information in your worksheet, follow these steps:

1. To search and replace the entire worksheet, select a single cell. To restrict the search and replace operation to a specific cell, range, or nonadjacent selection, select all the cells to be searched.

2. Choose the Replace option from the Find & Select button’s drop-down menu on the Ribbon’s Home tab or press Ctrl+H.

Excel opens the Find and Replace dialog box with the Replace tab selected (similar to the one shown in Figure 3-17). Note that if the Find and Replace dialog box is already open from choosing the Find option from the Find & Select button’s drop-down menu or pressing Ctrl+F, all you have to do is click the Replace tab.

3. Type the search string that you want to locate in the Find What combo box and specify any formatting to be searched by using its Format button.

Refer to the previous steps on finding a search string for details on specifying the search string in the Find What combo box and specifying the formatting to be searched for.

4. Type the replacement string in the Replace With combo box.

Enter this string exactly as you want it to appear in the cells of the worksheet. Use uppercase letters where uppercase is to appear, lowercase letters where lowercase is to appear, and the question mark and asterisk only where they are to appear. (They don’t act as wildcard characters in a replacement string.)

5. Click the Options button and then click the Format drop-down button and choose Format to select the formatting to be added to your replacement string from the Find Format dialog box. Or click Choose Format from Cell and select the formatting directly from a cell in the worksheet.

When you click the Format item, Excel opens a Find Format dialog box with the same tabs and options as the standard Format Cells dialog box. You may then select the formatting that you want the replacement string to have in this dialog box and then click OK.

When you choose the Choose Format from Cell item on the Format button drop-down menu, the Find and Replace dialog box temporarily disappears until you click the cell in the worksheet that contains the formatting that you want the replacement string to have with the thick, white-cross mouse pointer with eyedropper icon.

6. Make any necessary changes to the Within, Search, Look In, Match Case, and Match Entire Cell Contents options for the search string.

These options work just as they do on the Find tab. If these options aren’t displayed on the Replace tab of your Find and Replace dialog box, click its Options button to expand the dialog box.

7. Click the Find Next button to locate the first occurrence of the search string. Then, click the Replace button to replace the first occurrence with the replacement string or click the Find Next button again to skip this occurrence.

Using the Find Next and Replace buttons to search and replace on a case-by-case basis is by far the safest way to use the Find and Replace feature. If you’re certain (really certain) that you won’t mess up anything by replacing all occurrences throughout the spreadsheet, you can click the Replace All button to have Excel make the replacements globally without stopping to show you which cells are updated.

8. When you finish replacing entries on a case-by-case basis, click the Close button.

This action abandons the Find and Replace operation and closes the Find and Replace dialog box. When you globally replace the search string, Excel automatically closes the Find and Replace dialog box after replacing the last search string match.

Figure 3-17: Updating dates in a worksheet using the Find and Replace feature.

Remember that you can click the Undo button on the Quick Access toolbar or press Ctrl+Z to restore any replacements that you made in error.

Spell Checking Heaven

You can use Excel’s Spell Check feature to catch all the spelling mistakes that AutoCorrect lets slip through. To spell check your spreadsheet, click the Spelling button at the beginning of the Ribbon’s Review tab or press Alt+RS or, simply, F7.

When you spell check a spreadsheet, Excel looks up each word in the Excel Dictionary. If the word is not found (as is often the case with less-common last names, abbreviations, acronyms, and technical terms), Excel selects the cell with the unknown spelling and then displays a Spelling dialog box showing the unknown word in the Not in Dictionary text box with suggested correct spellings shown in a Suggestions list box below, which is similar to the one shown in Figure 3-18.

Figure 3-18: Spell checking a worksheet with the Spelling dialog box.

You can then take any of the following actions to take care of the unknown word:

· Click one of the words in the Suggestions list box and then click the Change button to have Excel replace the unknown word with the selected suggestion and continue spell checking the rest of the spreadsheet.

· Click one of the words in the Suggestions list box and then click the Change All button to have Excel replace all occurrences of the unknown word with the selected suggestion throughout the entire spreadsheet and then continue spell checking.

· Click the Ignore Once button to let the misspelling slide just this once and continue spell checking the rest of the spreadsheet.

· Click the Ignore All button to ignore all occurrences of the unknown word in the spreadsheet and continue spell checking.

· Click the Add to Dictionary button to add the unknown word to a custom dictionary so that Excel will know the word the next time you spell- check the worksheet.

· Click the AutoCorrect button to have Excel add the unknown word to the AutoCorrect list with the selected suggestion as its automatic replacement.

Keep in mind that Excel checks the spelling of the cells only in the current worksheet (and not all the sheets in the workbook). If you want Excel to spell check another worksheet, you need to click its sheet tab to make it active and then click the Spelling button on the Review tab (or press F7). If you want to spell check just a portion of the worksheet, select the range or nonadjacent cell selection before you start the spell check.

When Excel finishes checking the current worksheet or cell selection, the program displays an alert dialog box that indicates that the spell checking has been completed.

Changing the spelling options

When you use the Spell Check feature, you can change certain spelling options to better suit the spreadsheet that you’re checking. To change the spelling options, click the Options button at the bottom of the Spelling dialog box. This action opens the Proofing tab of the Excel Options dialog box with the following options in the When Correcting Spelling in Microsoft Office Programs section:

· Ignore Words in UPPERCASE: Remove the check mark from the check box so that Excel marks acronyms and other words entered in all uppercase letters as misspellings.

· Ignore Words That Contain Numbers: Remove the check mark from the check box so that Excel marks words such as B52 that contain letters and numbers as misspellings.

· Ignore Internet and File Addresses: Remove the check mark from the check box so that Excel marks web URL addresses such as www.dummies.com and file pathnames such as c:\documents\finance as misspellings.

· Flag Repeated Words: Remove the check mark so that Excel no longer marks repeated words such as Bora Bora as misspellings.

· Enforce Accented Uppercase in French: Add a check mark so that Excel marks uppercase French words that don’t have the proper accent marks as misspellings.

· Suggest from Main Dictionary Only: Have Excel use only the main dictionary when doing a spell check (thus, ignoring all words that you’ve added to a custom dictionary).

· Custom Dictionaries: Open the Custom Dictionaries dialog box where you can edit the words in a custom dictionary or add a new custom dictionary to be used in spell checking. (See “Adding words to the custom dictionary” that follows.)

· French Modes or Spanish Modes: Choose between the traditional or more modern spellings of French or Spanish words, respectively.

· Dictionary Language: Specify the language and country of the dictionary to use when spell checking the worksheet.

Adding words to the custom dictionary

You use the Add to Dictionary button in the Spelling dialog box to add unknown words to a custom dictionary. By default, Excel adds words to a custom dictionary file named RoamingCustom.dic. This file is located in the UProof folder, which is located within the Microsoft folder inside the Application Data folder. The Application Data folder is either inside the Windows User folder on your C: drive or, if you’re on a network, this file may be located in your username folder inside the Profiles folder that lies within the Windows folder on your C: drive.

If you want, you can create other custom dictionaries to use when spell checking your worksheets. To create a new custom dictionary, follow these steps:

1. Click the Custom Dictionaries button in the When Correcting Spelling in Microsoft Office Programs section of the Proofing tab.

Excel opens the Custom Dictionaries dialog box, where you can create a new custom dictionary to use.

2. Click the New button in the Custom Dictionaries dialog box.

Excel opens the Create Custom Dictionary dialog box.

3. Type the name for your new custom dictionary and then click the Save button.

After the Create Custom Dictionary dialog box closes, the name of the custom dictionary you created appears underneath CUSTOM.DIC (Default) in the Dictionary List box.

4. (Optional) To restrict the language of a custom dictionary, select the language from the Dictionary Language drop-down list after clicking the dictionary’s name in the Dictionary List box to select it.

5. To make the new custom dictionary the default dictionary into which new words are saved, click the dictionary’s name in the Dictionary List box to select it and then click the Change Default button.

6. Click OK to close the Custom Dictionaries dialog box and then click OK again to close the Excel Options dialog box.

Excel returns you to the Spelling dialog box.

7. Click the Add to Dictionary button to add the unknown word to the new default custom dictionary and then continue spell checking your spreadsheet.

Note that Excel continues to add all unknown words to your new custom dictionary until you change the default back to the original custom dictionary (or to another custom one that you’ve created). To change back and start adding unknown words to the original custom dictionary, select the RoamingCustom.dic file in the Custom Dictionaries dialog box and then click the Change Default button.

You can directly edit the words that you add to your custom dictionary. Click the Custom Dictionaries button on the Proofing tab of the Excel Options dialog box (File  ⇒  Options  ⇒  Proofing or Alt+FTP) and then click the Edit Word List command button. Excel then opens a dialog box with the default dictionary’s name that contains a Word(s) text box where you can enter new words to add to the custom dictionary and a Dictionary list box below that lists all the words added to the dictionary in alphabetical order. To add a new word to the dictionary, type it in the Word(s) text box (carefully, you don’t want to add a misspelling to the dictionary) and then click the Add button. To remove a word, click it in the Dictionary list box and then click the Delete button.

Looking Up and Translating Stuff

In addition to the very useful Spelling button (discussed in the previous section), the Proofing, Insights, and Language groups on the Review tab contains three other command buttons that can come in handy from time to time:

· Thesaurus opens the Research pane with the Thesaurus option selected. Here you can look up synonyms for a particular term that you enter into the Search For text box or for the contents of the current cell automatically entered into this text box.

· Smart Lookup opens the Insights task pane where you can find a definition and more online information about the entry that’s in the active cell when you click it.

· Translate opens the Research pane with the Translation option selected. Here you can look up a translation for a particular term that you enter into the Search For text box or for the contents of the current cell automatically entered into this text box in the language listed in the To drop-down list box.

When the Insights Task pane is first opened by clicking the Smart Lookup button, the Explore tab is selected, enabling you to explore the term that’s in the active cell using Wikipedia, Bing Image search, and a general web search. If you want a dictionary definition for the entry that’s in the current cell, click the Define tab at the top of the Insights task pane.

Marking Invalid Data

In addition to using the Data Validation feature to restrict what kind of data can be entered into cell ranges of a worksheet as described in Book II, Chapter 1, you can use it to mark all the data (by circling their cells) that are outside of expected or allowable parameters.

To use the Data Validation feature in this way, you follow this general procedure:

· Select the cell range(s) in the worksheet that need to be validated and marked.

· Open the Data Validation dialog box by clicking the Data Validation button on the Data tab of the Ribbon or by pressing Alt+AVV, and then use its options to set up the validation criteria that determine which values in the selected cell range are out of bounds. (See Book II, Chapter 1for details.)

· Choose the Circle Invalid Data option from the Data Validation button’s drop-down menu on the Data tab of the Ribbon.

Figure 3-19 shows an example of how you might use Data Validation to mark entries that are below a certain threshold. In this case, I set it up for Excel to mark all subtotal monthly sales cells entries in the range D4:D15 in the 2016 Sales worksheet that are above $50,000 by drawing a red circle around their cells.

Figure 3-19: Using Data Validation to mark unexpected entries (monthly sales above $50K) in a data table.

To set this up in the 2016 Sales worksheet, I followed these three steps:

· Selected the cell range (D4:D15) with monthly sales data for the year.

· Opened the Data Validation dialog box (Alt+AVV) and then on the Settings tab selected Decimal in the Allow drop-down list and Greater Than in the Data drop-down list, and entered 50000 in the Minimum text box before clicking OK.

· Chose the Circle Invalid Data option from the Data Validation button’s drop-down menu on the Data tab. (You can also press Alt+AVI.)

To remove the circles from the cells marked as invalid, choose the Clear Validation Circles option from the Data Validation button’s drop-down menu or press Alt+AVR. To clear the validation settings from the cells, select the range and then open the Data Validation dialog box and click its Clear All button before you click OK.

Eliminating Errors with Text to Speech

Find and Replace is a great tool for eliminating errors that you’ve flagged in the worksheet. Likewise, the Spell Check feature is great for eliminating input errors that result from typos. Unfortunately, neither of these features can help you to identify data input errors that result from actions, such as mistyping the entry (without misspelling it) or transposing one entry with another.

The only way that you can flag and then correct these errors is by checking and verifying the accuracy of each and every data entry in the worksheet. Usually, you do this by checking the columns and rows of data in a spreadsheet against the original documents from which you generated the spreadsheet. Excel’s Text to Speech feature can help in this checking by reading aloud each entry that’s been made in a selected range of cells or data table within the worksheet. As the data entries are read aloud, you can then verify their accuracy against documents used in the original data entry.

The Text to Speech translation feature requires no prior training or special microphones: All that’s required is a pair of speakers or headphones connected to your computer.

Unfortunately, the various Text to Speech command buttons are not available from any of the tabs on the Ribbon. The only way to access them is by adding their command buttons either as custom buttons on the Quick Access toolbar or to a custom tab on the Ribbon. Figure 3-20 shows the Quick Access toolbar on my copy of Excel 2016 after I’ve added the Speak Cells, Speak Cells - Stop Speaking Cells, and Speak Cells on Enter buttons needed to check cell entries by having them read aloud.

Figure 3-20: Quick Access toolbar with three Text to Speech custom buttons added.

To add the Speak Cells, Speak Cells - Stop Speaking Cells, and Speak Cells on Enter command buttons to the Quick Access Toolbar, follow these steps:

1. Click the Customize Quick Access Toolbar drop-down button and then choose the More Commands option from its drop-down menu.

Doing this opens the Quick Access Toolbar tab of the Excel Options dialog box.

2. Choose Commands Not in the Ribbon from the Choose Commands From drop-down menu.

The Text to Speech command buttons include Speak Cells, Speak Cells - Stop Speaking Cells, Speak Cells by Columns, Speak Cells by Rows, and Speaks Cells on Enter.

3. Click the Speak Cells button in the Choose Commands From list box on the left and then click the Add button to add it to the bottom of the Customize Quick Access Toolbar list box on the right.

4. Repeat the process outlined in Step 3, this time adding the Speak Cells - Stop Speaking Cells and Speak Cells on Enter buttons.

If you want to reposition the Text to Speech buttons on the Quick Access toolbar, select each button in the Customize Quick Access Toolbar list box and then move it left on the bar by clicking the Move Up button or right by clicking Move Down. If you want to set off the Text to Speech buttons as a separate group on the Quick Access toolbar, add a <Separator> icon ahead of the Speak Cells command button (and following the Speak Cells on Enter button if you have buttons not related to the Text to Speech function that follow on the Quick Access toolbar).

5. Click the OK button to close the Excel Options dialog box.

After adding the Text to Speech buttons to the Quick Access toolbar, you can use them to corroborate spreadsheet entries and catch those hard-to-spot errors as follows:

1. Select the cells in the worksheet whose contents you want read aloud by Text to Speech.

If you want to check a table of data, simply position the cell cursor in the first cell, and Excel will then automatically select the entire table when you click the Speak Cells button.

2. Click the Speak Cells button to have your Windows device begin reading back the entries in the selected cells.

The Text to Speech feature reads the contents of each cell in the cell selection by first reading down each column and then across the rows.

3. To pause the Text to Speech feature when you locate a discrepancy between what you’re reading and what you’re hearing, click the Speak Cells - Stop Speaking Cells button.

Keep in mind that you can click the Speak Cells on Enter button to have your computer speak each new entry that you make as you complete it by pressing the Enter key. Excel also moves the cell cursor down one row.