Going Through Changes

Opening files in the Open screen

When the Open screen is first displayed in the Excel 2019 Backstage (shown in Figure 4-1) the Recent option in the Places pane on the left is selected. If the file you want to open isn’t shown in this list in the right-hand pane, you need to select one of the other Places options:

• OneDrive to open a workbook file that’s saved in the cloud in one of your folders on your Windows OneDrive (for more information on OneDrive or to open an account, visit https://onedrive.live.com/about). When you select the OneDrive option, the right-hand pane lists folders on your OneDrive that you accessed recently as well as the Browse button that enables you to locate other folders in the Open dialog box.
• This PC to open a workbook file saved locally on your computer’s hard drive or a network drive to which you have access. When you select this option, the right-hand pane lists folders on your local and network drives that you accessed recently as well as a Documents button that displays the Open dialog box giving you access to the files saved in all your local and network drives and folders.
• Add a Place to add access to a folder on your SharePoint site or OneDrive. When you select this option, the right-hand pane contains an Office 365 SharePoint and OneDrive button. Click the Office 365 SharePoint button to log into a SharePoint site for which you have a user ID and password to add its folders to the Open screen under the Computer option. Select the OneDrive option to log into your Windows Live account (for which you have a user ID and password) to add your space in the cloud to the Open screen under the OneDrive option.
• Browse to display the Open dialog box (shown in Figure 4-1) where you can select a workbook for editing from any folders saved on the local, network, or online drives available to you.

Operating the Open dialog box

After you select a folder and drive in the Excel 2019 Open screen or its Browse button or press the Ctrl+F12 shortcut from within Excel, the program displays an Open dialog box similar to the one shown in Figure 4-2. The Open dialog box is divided into panes: Navigation pane on the left, where you can select a new folder to open, and main pane on the right showing the icons for all the subfolders in the current folder, as well as the documents that Excel can open.

The folder with contents displayed in the Open dialog box is either the one designated as the Default File Location on the Save tab of the Excel Options dialog box, the folder you last opened during your current Excel work session, or the folder you selected in the Open screen of the Backstage view.

To open a workbook in another folder, select the Quick Access option in the Navigation pane (to see a list of frequently used folders in the main pane) or one of the location icons displayed in this pane to access that folder. You can display a list of folders on a particular location by clicking the Expand Folders button (the one with the triangle pointing to the right) to the left of its name.

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 appears next to the drop-down list box that currently displays All Excel Files and then click All Files on its drop-down menu.

When the icon for the workbook file you want to work with appears in the Open dialog box, you can then open it either by clicking its file icon and then clicking the Open button or, if you’re handy with the mouse, by just double-clicking the file icon.

You can use the slider attached to the Change Your View drop-down list button located in upper-right corner of the Open dialog box to change the way folder and file icons appear in the dialog box. When you select Large Icons or Extra Large Icons on this slider (or anywhere in between), the Excel workbook icons actually show a preview of the data in the upper-left corner of the first worksheet when the file is saved with the preview picture option turned on:

• To enable the preview feature when saving workbooks in Excel 2019, select the Save Thumbnail check box in the Save As dialog box before saving the file for the first time.
• To enable the preview feature when saving workbooks in Excel 97 through 2003, click the Save Preview Picture check box on the Summary tab of the workbook’s Properties dialog box (File ⇒ Properties) before saving the file for the first time.

This preview of part of the first sheet can help you quickly identify the workbook you want to open for editing or printing. To have the workbook file’s preview displayed in the Open dialog box without changing the current icon view, click the Show the Preview Pane button to the immediate right of the Change Your View drop-down button.

Changing the Recent files settings

Excel 2019 automatically keeps a running list of the last 50 files you opened in the Recent Workbooks list on the Open screen in the Backstage when the default Recent Workbooks option is selected rather than Shared With Me (which displays a list of all the Excel workbook files that coworkers or clients have shared on a OneDrive or SharePoint web site). If you want, you can have Excel fewer files in this list.

To change the number of recently opened files that appear, follow these simple steps:

1. Select File ⇒ Options ⇒ Advanced or press Alt+FTA to open the Advanced tab of the Excel Options dialog box.
2. Type a new entry (between 1 and 50) in the Show This Number of Recent Documents text box located in the Display section or use the spinner buttons to increase or decrease this number.
3. Click OK or press Enter to close the Excel Options dialog box.

If you don’t want any files displayed in the Recent Workbooks list, either on the Excel or Open screen in the Backstage view, enter 0 in the Show This Number of Recent Documents text box or select it with the spinner buttons.

Select the Quickly Access This Number of Recent Workbooks check box on the Advanced tab of the Excel Options dialog box (right below the Show This Number of Recent Workbooks option) to have Excel display the four most recently opened workbooks as menu items at the bottom of the File menu in the Backstage view. That way, you can open any of them by clicking its button even when the Open screen is not displayed in the Backstage view. If four workbook files is too many or not sufficient, you can decrease or increase the number of files shown at the bottom of the File menu by replacing 4 in the text box that appears to the right of the Quickly Access This Number of Recent Workbooks check box option.

Opening multiple workbooks

If you know that you’re going to edit more than one of the workbook files shown in the list box of the Open dialog box, you can select multiple files in the list box, and Excel will then open all of them (in the order they’re listed) when you click the Open button or press Enter.

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 aren’t 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 clicking the Task View button on the Windows 10 taskbar and then clicking the thumbnail of the workbook you want to edit or by using the Flip feature (Alt+Tab) to select the workbook’s thumbnail. You can also switch files within Excel 2019 by using the View ⇒ Switch Windows command and selecting its filename on its drop-down menu (See Chapter 7 for detailed information on working on more than one worksheet at a time.)

Find workbook files

The only problem you can encounter in opening a document from the Open dialog box is locating the filename. Everything’s hunky-dory as long as you can see the workbook filename listed in the Open dialog box or know which folder to open in order to display it. But what about those times when a file seems to migrate mysteriously and can’t be found on your computer?

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

As soon as Windows 10 finds any matches for the characters you type, the names of the workbook files (and other Excel files, such as templates and macro sheets) appear in the Open dialog box. When 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 Open file options

The drop-down button attached to the Open command button at the bottom of the Open dialog box enables you to open the selected workbook file(s) in a special way, including:

• Open Read-Only: This command 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 in the original workbook.) To save changes in a read-only file, you must use the Save As command (File ⇒ Save As or Alt+FA) and give the workbook file a new location and/or filename. (Refer to Chapter 2.)
• Open as Copy: This command opens a copy of the files you select in the Open dialog box. Use this method of opening files as a safety net: If you mess up the copies, you always have the originals to fall back on.
• Open in Browser: This command opens workbook files you save as web pages (which I describe in Chapter 12) in your favorite web browser. This command isn’t available unless the program identifies that the selected file or files were saved as web pages rather than plain old Excel workbook files.
• Open in Protected View: This command opens the workbook file in Protected View mode that keeps you from making any changes to the contents of its worksheets until you click the Enable Editing button that appears in the orange Protected View panel at the top of the screen.
• Open and Repair: This command 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 data from 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 tried unsuccessfully to have Excel repair the file.

Undo is Redo the second time around

After using the Undo command button on the Quick Access toolbar, Excel 2019 activates the Redo command button to its immediate right. If you delete an entry from a cell by pressing the Delete key and then click the Undo command button or press Ctrl+Z, the ScreenTip that appears when you position the mouse pointer over the Redo command button reads Redo Clear (Ctrl+Y).

When you click the Redo command button or press Ctrl+Y, Excel redoes the thing you just undid. Actually, this sounds more complicated than it is. It simply means that you use Undo/Redo to switch between the result of an action and the state of the worksheet just before that action until you decide how you want the worksheet (or until the cleaning crew turns off the lights and locks up the building).

What to do when you can’t Undo?

Just when you think it is safe to begin gutting the company’s most important workbook, I really feel that I have to tell you that (yikes!) Undo doesn’t work all the time! Although you can undo your latest erroneous cell deletion, bad move, or unwise copy, you can’t undo your latest rash save. (You know, like when you meant to choose Save As from the File menu in the Backstage view to save the edited worksheet under a different document name but chose Save and ended up saving the changes as part of the current document.)

Unfortunately, Excel doesn’t let you know when you are about to take a step from which there is no return — until it’s too late. After you’ve gone and done the un-undoable and you click the Undo button where you expect its ScreenTip to say Undo blah, blah, it now reads Can’t Undo.

One exception to this rule is when the program gives you advance warning (which you should heed). When you choose a command that is normally possible but because you’re low on memory or the change will affect so much of the worksheet, or both, Excel knows that it can’t undo the change if it goes through with it, the program displays an alert box telling you that there isn’t enough memory to undo this action and asking whether you want to go ahead anyway. If you click the Yes button and complete the edit, just realize that you do so without any possibility of pardon. If you find out, too late, that you deleted a row of essential formulas (that you forgot about because you couldn’t see them), you can’t bring them back with Undo. In such a case, you would have to close the file (File ⇒ Close) and NOT save your changes.

Copies, drag-and-drop style

What if you want to copy rather than drag and drop a cell range? Suppose that you need to start a new table in rows farther down the worksheet, and you want to copy the cell range with the formatted title and column headings for the new table. To copy the formatted title range in the sample worksheet, follow these steps:

1. Select the cell range.

   In the case of Figures 4-3 and 4-4, that’s cell range A1:E2.

2. Hold the Ctrl key down while you position the mouse pointer on an edge of the selection (that is, the expanded cell cursor).

   The pointer changes from a thick, shaded cross to an outlined arrowhead with a + (plus sign) to the right of it with the drag-and-drop ScreenTip beside it. The plus sign next to the pointer is your signal that drag and drop will copy the selection rather than move it.

3. Drag the cell-selection outline to the place where you want the copy to appear and release the mouse button.

If, when using drag and drop to move or copy cells, you position the outline of the selection so that it overlaps any part of cells that already contain entries, Excel displays an alert box that asks whether you want to replace the contents of the destination cells. To avoid replacing existing entries and to abort the entire drag-and-drop mission, click the Cancel button in this alert box. To go ahead and exterminate the little darlings, click OK or press Enter.

Insertions courtesy of drag and drop

Like the Klingons of Star Trek fame, spreadsheets, such as Excel, never take prisoners. When you place or move a new entry into an occupied cell, the new entry completely replaces the old as though the old entry never existed in that cell.

To insert the cell range you’re moving or copying within a populated region of the worksheet without wiping out existing entries, hold down the Shift key while you drag the selection. (If you’re copying, you have to get ambitious and hold down both the Shift and Ctrl keys at the same time!)

With the Shift key depressed while you drag, instead of a rectangular outline of the cell range, you get an I-beam shape that shows where the selection will be inserted if you release the mouse button along with the address of the cell range (as a kind of Insertion ScreenTip). When you move the I-beam shape, notice that it wants to attach itself to the column and row borders while you move it. After you position the I-beam at the column or row border where you want to insert the cell range, release the mouse button. Excel inserts the cell range, moving the existing entries to neighboring blank cells (out of harm’s way).

When inserting cells with drag and drop, it may be helpful to think of the I-beam shape as a pry bar that pulls apart the columns or rows along the axis of the I. Also, sometimes after moving a range to a new place in the worksheet, instead of the data appearing, you see only #######s in the cells. (Excel 2019 doesn’t automatically widen the new columns for the incoming data as it does when formatting the data.) Remember that the way to get rid of the #######s in the cells is by widening those troublesome columns enough to display all the data-plus-formatting; and the easiest way to do this kind of widening is by double-clicking the right border of the column.

Relatively speaking

Figure 4-6 shows the worksheet after the formula in a cell is copied to the cell range C12:E12 and cell B12 is active. Notice how Excel handles the copying of formulas. The original formula in cell B12 is as follows:

=SUM(B3:B11)

When the original formula is copied to cell C12, Excel changes the formula slightly so that it looks like this:

=SUM(C3:C11)

Excel adjusts the column reference, changing it from B to C, because I copied from left to right across the rows.

When you copy a formula to a cell range that extends down the rows, Excel adjusts the row numbers in the copied formulas rather than the column letters to suit the position of each copy. For example, cell E3 in the Mother Goose Enterprises – 2019 Sales worksheet contains the following formula:

=SUM(B3:D3)

When you copy this formula to cell E4, Excel changes the copy of the formula to the following:

=SUM(B4:D4)

Excel adjusts the row reference to keep current with the new row 4 position. Because Excel adjusts the cell references in copies of a formula relative to the direction of the copying, the cell references are known as relative cell references.

Some things are absolutes!

All new formulas you create naturally contain relative cell references unless you say otherwise. Because most copies you make of formulas require adjustments of their cell references, you rarely have to give this arrangement a second thought. Then, every once in a while, you come across an exception that calls for limiting when and how cell references are adjusted in copies.

One of the most common of these exceptions is when you want to compare a range of different values with a single value. This happens most often when you want to compute what percentage each part is to the total. For example, in the Mother Goose Enterprises – 2019 Sales worksheet, you encounter this situation in creating and copying a formula that calculates what percentage each monthly total (in the cell range B14:D14) is of the quarterly total in cell E12.

Suppose that you want to enter these formulas in row 14 of the Mother Goose Enterprises – 2019 Sales worksheet, starting in cell B14. The formula in cell B14 for calculating the percentage of the January-sales-to-first-quarter-total is very straightforward:

=B12/E12

This formula divides the January sales total in cell B12 by the quarterly total in E12 (what could be easier?). Look, however, at what would happen if you dragged the fill handle one cell to the right to copy this formula to cell C14:

=C12/F12

The adjustment of the first cell reference from B12 to C12 is just what the doctor ordered. However, the adjustment of the second cell reference from E12 to F12 is a disaster. Not only do you not calculate what percentage the February sales in cell C12 are of the first quarter sales in E12, but you also end up with one of those horrible #DIV/0! error things in cell C14.

To stop Excel from adjusting a cell reference in a formula in any copies you make, convert the cell reference from relative to absolute. You do this by pressing the function key F4, after you put Excel in Edit mode (F2). Excel indicates that you make the cell reference absolute by placing dollar signs in front of the column letter and row number. For example, in Figure 4-7, cell B14 contains the correct formula to copy to the cell range C14:D14:

=B12/$E$12

Look at the worksheet after this formula is copied to the range C14:D14 with the fill handle and cell C14 is selected (see Figure 4-8). Notice that the Formula bar shows that this cell contains the following formula:

=C12/$E$12

Because E12 was changed to $E$12 in the original formula, all the copies have this same absolute (non-changing) reference.

If you goof up and copy a formula where one or more of the cell references should have been absolute but you left them all relative, edit the original formula as follows:

1. Double-click the cell with the formula or press F2 to edit it.
2. Position the insertion point somewhere on the reference you want to convert to absolute.
3. Press F4.
4. When you finish editing, click the Enter button on the Formula bar and then copy the formula to the messed-up cell range with the fill handle.

Be sure to press F4 only once to change a cell reference to completely absolute as I describe earlier. If you press the F4 function key a second time, you end up with a so-called mixed reference, where only the row part is absolute and the column part is relative (as in E$12). If you then press F4 again, Excel comes up with another type of mixed reference, where the column part is absolute and the row part is relative (as in $E12). If you go on and press F4 yet again, Excel changes the cell reference back to completely relative (as in E12). After you’re back where you started, you can continue to use F4 to cycle through this same set of cell reference changes all over again.

If you’re using Excel 2019 on a device without access to a physical keyboard with function keys (such as a touchscreen tablet), the only way to convert cell addresses in your formulas from relative to absolute or some form of mixed address is to open the Touch keyboard and use it add the dollar signs before the column letter and/or row number in the appropriate cell address on the Formula bar.

Paste it again, Sam …

An advantage to copying a selection with the Copy and Paste commands and the Clipboard is that you can paste the information multiple times. Just make sure that, instead of pressing Enter to complete the first copy operation, you click the Paste button on the Home tab of the Ribbon or press Ctrl+V.

When you use the Paste command to complete a copy operation, Excel copies the selection to the range you designate without removing the marquee from the original selection. This is your signal that you can select another destination range (in either the same or a different document).

After you select the first cell of the next range where you want the selection copied, choose the Paste command again. You can continue in this manner, pasting the same selection to your heart’s content. When you make the last copy, press Enter instead of clicking the Paste command button or pressing Ctrl+V. If you forget and select Paste, get rid of the marquee around the original cell range by pressing the Esc key.

Keeping pace with Paste Options

Right after you click the Paste button on the Home tab of the Ribbon or press Ctrl+V to paste cell entries that you copy (not cut) to the Clipboard, Excel displays a Paste Options button with the label, (Ctrl), to its immediate right at the end of the pasted range. When you click this drop-down button or press the Ctrl key, a palette similar to the one shown in Figure 4-9 appears with three groups of buttons (Paste, Paste Values, and Other Paste Options).

You can use these paste options to control or restrict the type of content and formatting that’s included in the pasted cell range. The paste options (complete with the hot key sequences you can type to select them) on the Paste Options palette include:

• Paste (P): Excel pastes all the stuff in the cell selection (formulas, formatting, you name it).
• 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 all the stuff 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 cells’ 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.
• 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 any formulas along with all formatting assigned to the source cell range.
• 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.

The options that appear on the Paste Options palette are context sensitive. This means that the particular paste options available on the palette depend directly upon the type of cell entries previously copied to the Office Clipboard. Additionally, you can access this same palette of paste options by clicking the drop-down button that appears directly beneath the Paste button on the Ribbon instead of clicking the Paste Options button that appears at the end of the pasted range in the worksheet or pressing the Ctrl key on your keyboard.

Paste it from the Clipboard task pane

The Office Clipboard can store multiple cuts and copies from any program running under Windows (not just Excel). In Excel, this means that you can continue to paste stuff from the Clipboard into a workbook even after finishing a move or copy operation (even when you do so by pressing the Enter key rather than using the Paste command).

To open the Office Clipboard in its own task pane to the immediate left of the Worksheet area (see Figure 4-10), click the Dialog Box launcher in the lower-right corner of the Clipboard group on the Ribbon’s Home tab.

To paste an item from the Clipboard into a worksheet, click the cell in the worksheet where you want the item to be copied before you click item in the Clipboard task pane. If the Clipboard item is composed of a series of text or values, Excel will paste the into the cells of the worksheet starting at the current cell. If the item is a graphic, Excel will pasted in the vicinity of the current cell (see Chapter 10 for details on working with graphics in your worksheets).

You can paste all the items stored in the Office Clipboard into the current worksheet by clicking the Paste All button at the top of the Clipboard task pane. To clear the Office Clipboard of all the current items, click the Clear All button. To delete only a particular item from the Office Clipboard, position the mouse pointer over the item in the Clipboard task pane until its drop-down button appears. Click this drop-down button, and then select Delete from the pop-up menu (refer to Figure 4-10).

To have the Clipboard task pane appear automatically after making two cuts or copies to the Clipboard in an Excel workbook, click the Show Office Clipboard Automatically option on the task pane’s Options button menu. To open the Clipboard task pane in the Excel program window by pressing Ctrl+CC, click Show Office Clipboard When Ctrl+C Pressed Twice on the task pane’s Options button. Pressing Ctrl+CC only opens the task pane. You still have to click the Close button on the Office Clipboard to close the task pane.

So what’s so special about Paste Special?

Normally, unless you fool around with the Paste Options (see the section “Keeping pace with Paste Options,” earlier in this chapter), Excel copies all the information in the range of cells you selected: formatting, as well the formulas, text, and other values you enter. You can use the Paste Special command to specify which entries and formatting to use in the current paste operation. Many of the Paste Special options are also available on the Paste Options palette.

To paste particular parts of a cell selection while discarding others, click the drop-down button that appears at the bottom of the Paste command button on the Ribbon’s Home tab. Then, click Paste Special on its drop-down menu to open the Paste Special dialog box, shown in Figure 4-11.

The options in the Paste Special dialog box include:

• All to paste all the stuff in the cell selection (formulas, formatting, you name it).
• Formulas to paste all the text, numbers, and formulas in the current cell selection without their formatting.
• Values to convert formulas in the current cell selection to their calculated values.
• Formats to paste only the formatting from the current cell selection, leaving the cell entries in the dust.
• Comments to paste only the notes that you attach to their cells (kind of like electronic self-stick notes — see Chapter 6 for details).
• Validation to paste only the data validation rules into the cell range that you set up with the Data Validation command (which enables you to set what value or range of values is allowed in a particular cell or cell range).
• All Using Source Theme to paste all the information plus the cell styles applied to the cells.
• All Except Borders to paste all the stuff in the cell selection without copying any borders you use there.
• Column Widths to apply the column widths of the cells copied to the Clipboard to the columns where the cells are pasted.
• Formulas and Number Formats to include the number formats assigned to the pasted values and formulas.
• Values and Number Formats to convert formulas to their calculated values and include the number formats you assign to all the pasted values.
• All Merging Conditional Formats to paste Conditional Formatting into the cell range.
• None to prevent Excel from performing any mathematical operation between the data entries you cut or copy to the Clipboard and the data entries in the cell range where you paste.
• Add to add the data you cut or copy to the Clipboard and the data entries in the cell range where you paste.
• Subtract to subtract the data you cut or copy to the Clipboard from the data entries in the cell range where you paste.
• Multiply to multiply the data you cut or copy to the Clipboard by the data entries in the cell range where you paste.
• Divide to divide the data you cut or copy to the Clipboard by the data entries in the cell range where you paste.
• Skip Blanks check box when you want Excel to paste everywhere except for any empty cells in the incoming range. In other words, a blank cell cannot overwrite your current cell entries.
• Transpose check box when you want Excel to change the orientation of the pasted entries. For example, if the original cells’ 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.
• Paste Link button when you’re copying cell entries and you want to establish a link between copies you’re pasting and the original entries. That way, changes to the original cells automatically update in the pasted copies.

Sounding the all clear!

To get rid of just the contents of a cell selection rather than delete the cells and their contents, select the range of cells to clear and then simply press the Delete key.

If you want to get rid of more than just the contents of a cell selection, click the Clear button (the one with the eraser) in the Editing group on the Ribbon’s Home tab and then click one of the following options on its drop-down menu:

• Clear All: Gets rid of all formatting and notes, as well as entries in the cell selection (Alt+HEA)
• Clear Formats: Deletes only the formatting from the cell selection without touching anything else (Alt+HEF)
• Clear Contents: Deletes only the entries in the cell selection just like pressing the Delete key (Alt+HEC)
• Clear Comments: Removes the notes in the cell selection but leaves everything else behind (Alt+HEM)
• Clear Hyperlinks: Removes the active hyperlinks (see Chapter 12) in the cell selection but leaves its descriptive text (Alt+HEL)
• Remove Hyperlinks: Removes the active hyperlinks in the cell selection along with all the formatting (Alt+HER)

Get these cells outta here!

To delete the cell selection rather than just clear out its contents, select the cell range, click the drop-down button attached to the Delete command button in the Cells group of the Home tab, and then click Delete Cells on the drop-down menu (or press Alt+HDD). The Delete dialog box opens, showing options for filling in the gaps created when the cells currently selected are blotted out of existence:

• Shift Cells Left: This default option moves entries from neighboring columns on the right to the left to fill in gaps created when you delete the cell selection by clicking OK or pressing Enter.
• Shift Cells Up: Select this to move entries up from neighboring rows below.
• Entire Row: Select this to remove all the rows in the current cell selection.
• Entire Columns: Select this to delete all the columns in the current cell selection.

If you know that you want to shift the remaining cells to the left after deleting the cells in the current selection, you can simply click the Delete command button on the Home tab of the Ribbon. This is the same thing as opening the Delete dialog box and then clicking OK when the default Shift Cells Left button is selected.

To delete an entire column or row from the worksheet, you can select the column or row on the Workbook window frame, right-click the selection, and then click Delete from the column’s or row’s shortcut menu.

You can also delete entire columns and rows selected in the worksheet by clicking the drop-down button attached to the Delete command button on the Ribbon’s Home tab and then clicking the Delete Sheet Columns (Alt+HDC) option or the Delete Sheet Rows option (Alt+HDR) on the drop-down menu.

Deleting entire columns and rows from a worksheet is risky business unless you are sure that the columns and rows in question contain nothing of value. Remember, when you delete an entire row from the worksheet, you delete all information from column A through XFD in that row (and you can see only a very few columns in this row). Likewise, when you delete an entire column from the worksheet, you delete all information from row 1 through 1,048,576 in that column.