Part AL I MA TE RI Basic Excel Usage n this part, you’ll find tips and tricks covering some of the fundamental uses of Excel, from selecting cells and navigating sheets in a workbook to hiding rows and columns, as well as working with the Quick Access Toolbar and changing Excel’s color scheme.
Tips and Where to Find Them Tip 1 Understanding Excel Versions 9 Tip 13 Using Document Themes 38 Tip 2 Maximizing Ribbon Efficiency 12 Tip 3 Selecting Cells Efficiently 14 Tip 14 Changing the Sheet Tab Appearance 42 Tip 4 Making “Special” Range Selections 18 Undoing, Redoing, and Repeating 21 Discovering Some Useful Shortcut Keys 24 Tip 5 Tip 6 Tip 7 Tip 8 Tip 9 Navigating Sheets in a Workbook Resetting the Used Area of a Worksheet Understanding Workbooks versus Windows Tip 15 Hiding U
Tip 1: Understanding Excel Versions 9 Understanding Excel Versions If you’re reading this book, you should be using Excel 2007 — which is radically different from all other Excel versions. I’ve found that most users don’t even know which version of Excel they use. Here’s how to find out your Excel version, plus additional information. If Excel has a menu titled Help, click it and then choose About. If Excel does not have a menu titled Help, then you’re using Excel 2007.
10 Tip 1: Understanding Excel Versions Suppose that you use Excel 2007 and you give a co-worker (who uses Excel 2000) a copy of a workbook. If you happened to use a feature that was introduced in Excel 2002, Excel 2003, or Excel 2007, your co-worker may not be able to work with your file in the way you intended. In fact, if you saved the file in one of the new Excel 2007 file formats, she may not even be able to open the file. NOTE Microsoft provides the free add-on Office 2007 Compatibility Pack.
Tip 1: Understanding Excel Versions 11 Released Comments 10 2001 Known as Excel 2002 (or Excel XP), this version is part of Office XP. It has a long list of new features, but most of them are of little value to the majority of users. Perhaps this version’s most significant feature is its ability to recover your work when Excel crashes.
12 Tip 2: Maximizing Ribbon Efficiency Maximizing Ribbon Efficiency When you first fired up Excel 2007, you probably noticed that the commands at the top of the window are different — very different — from other versions. Since the beginning of time, all Windows programs have had a similar user interface that consists of menus and toolbars. The Office 2007 designers went out on a limb and came up with a radically different user interface: the Ribbon.
Tip 2: Maximizing Ribbon Efficiency 13 Part I Figure 2-1: The look of the Ribbon varies, depending on the width of the Excel window. • Right-click just about anything on-screen to get a context-sensitive shortcut menu. It’s one element of the old user interface that’s still in place. And, if you’re trying to decrease your mouse dependence, you can also press Shift+F10 to display the shortcut menu for the selected item (cell, range, or chart element, for example).
14 Tip 3: Selecting Cells Efficiently Selecting Cells Efficiently Many Excel users think that the only way to select a range of cells is to drag over the cells with the mouse. Although selecting cells with a mouse works, it’s rarely the most efficient way to accomplish the task. The answer, of course, is to use your keyboard to select ranges.
Tip 3: Selecting Cells Efficiently 15 Selecting a Range by Shift+Clicking When you’re selecting a very large range, using the mouse may be the most efficient method — but dragging is not required. Select the upper-left cell in the range. Then scroll to the lower-right corner of the range, press Shift, and click the lower-right cell. Selecting Noncontiguous Ranges Figure 3-2: A multiple selection that consists of noncontiguous ranges.
16 Tip 3: Selecting Cells Efficiently Selecting Entire Columns To select a single column, click a column letter along the top of the worksheet. Or, select any cell in the column and press Ctrl+spacebar. To select multiple adjacent columns, click and drag in the column letter section. Or, select any cell in the first (or last) column, press Ctrl+spacebar, and use the arrow keys to extend the selection to the right (or left).
Tip 3: Selecting Cells Efficiently 17 NOTE To select a group of contiguous worksheets, press Shift and click the sheet tab of the last worksheet that you want to include in the selection. To select individual worksheets, press Ctrl and click the sheet tab of each worksheet that you want to select. When you make the selection, the sheet tabs of the selected sheets appear with a white background, and Excel displays [Group] on the title bar.
18 Tip 4: Making “Special” Range Selections Making “Special” Range Selections As you use Excel, you’ll probably wonder how you can locate specific types of cells in your worksheets. For example, wouldn’t it be handy to be able to locate every cell that contains a formula, or perhaps all cells whose values depend on the current cell? Excel provides an easy way to locate these and many other special types of cells. The key to many types of special selections is the Go To Special dialog box.
Tip 4: Making “Special” Range Selections 19 TABLE 4-1 GO TO SPECIAL OPTIONS What It Selects Comments Only the cells that contain cell comments. Constants All non-empty cells that don’t contain formulas. This option is useful if you have a model set up and you want to clear out all input cells and enter new values. The formulas remain intact. Use the check boxes under the Formulas option to choose which cells to include. Formulas Cells that contain formulas.
20 Tip 4: Making “Special” Range Selections NOTE When you select an option in the Go To Special dialog box, be sure to note which suboptions become available. For example, when you select the Constants option, the Formulas suboptions become available to help you further refine the results. Likewise, the Dependents suboptions also apply to Precedents, and the Data Validation suboptions also apply to Conditional formats.
Tip 5: Undoing, Redoing, and Repeating 21 Undoing, Redoing, and Repeating This tip describes three procedures that every Excel user needs to understand. These procedures help you recover from mistakes and improve your editing efficiency. Undoing Choose Undo after issuing a command in error and it’s as though you never issued the command. You can reverse the effects of the last 100 commands that you executed by selecting Undo multiple times. Some actions, however, can’t be reversed.
22 Tip 5: Undoing, Redoing, and Repeating NOTE Keep in mind that the effect of executing a macro cannot be undone. In fact, running a macro wipes out all the Undo information. This serious weakness in Excel isn’t present in Microsoft Word. However, at least Excel 2007 fixed another Undo-related problem. In previous versions, saving your workbook destroyed the Undo information. Fortunately, that’s no longer the case.
Tip 5: Undoing, Redoing, and Repeating 23 NOTE Unfortunately, repeating an operation is unreliable in Excel 2007 — at least in the initial release. In fact, it’s seriously broken. In some situations, you may find that pressing Ctrl+Y has no effect (when it should have an effect). At other times, you may find that pressing Ctrl+Y repeats the command that you issued before the previous command.
24 Tip 6: Discovering Some Useful Shortcut Keys Discovering Some Useful Shortcut Keys Excel has no shortage of shortcut keys. Your productivity is sure to increase if you take the time to learn the shortcut keys for commands that you use frequently. In the following table, I list some of the most useful shortcut keys. This list is certainly not an exhaustive one — it describes just the commands that I find most useful.
Tip 6: Discovering Some Useful Shortcut Keys Shortcut What You Can Do with It Ctrl+V Paste a copied or cut item in the selected cell Ctrl+X Cut the selected cells Ctrl+Y Repeat the last repeatable command Ctrl+Z Undo the last action 25 Part I
26 Tip 7: Navigating Sheets in a Workbook Navigating Sheets in a Workbook As you know, a single workbook can contain multiple worksheets. The sheet tabs at the bottom of the Excel window identify the worksheets. All Excel users know that they can activate a different sheet by clicking its sheet tab. If the tab for the sheet you want isn’t visible, you can use the tab scroll controls to the left of the first sheet tab to scroll the tabs left or right (see Figure 7-1).
Tip 7: Navigating Sheets in a Workbook 27 In many cases, the most efficient way to activate a different sheet is to avoid the mouse and use the keyboard. Surprisingly, many users don’t know about two useful keyboard commands: • Ctrl+PgDn: Activates the next sheet • Ctrl+PgUp: Activates the previous sheet I use these keys 90 percent of the time — but then again, I avoid using a mouse whenever I can.
28 Tip 8: Resetting the Used Area of a Worksheet Resetting the Used Area of a Worksheet When you press Ctrl+End, Excel activates the lower-right cell in the used area of the worksheet. In some cases, you find that the lower-right cell in the worksheet is an empty cell — not the real last cell. In other words, Excel sometimes loses track of the used area of your worksheet. For example, you may find that a workbook’s file size seems much too large.
Tip 9: Understanding Workbooks versus Windows 29 Understanding Workbooks versus Windows One of the most common questions asked in the Excel newsgroups is “Why is Excel displaying two copies of my workbook?” Then the Excel users go on to describe the symptoms: The filename is followed by a colon and a number (for example, budget.xlsx:2). Most people who ask this question in the newsgroups have probably issued the New Window command accidentally.
30 Tip 9: Understanding Workbooks versus Windows Figure 9-1: Displaying two windows for a workbook lets you view the cells and their formulas.
Tip 10: Customizing Your Quick Access Toolbar 31 Customizing the Quick Access Toolbar In previous versions of Excel, you can fairly easily modify the user interface. You can create custom toolbars that contain frequently used commands, and you can even remove menu items that you never use. In earlier versions of Excel, you can also display any number of toolbars and move them wherever you like. For users who have moved up to Excel 2007, those days are over.
32 Tip 10: Customizing Your Quick Access Toolbar As far as I can tell, there is no limit to the number of commands that you can add to your QAT. But regardless of the number of icons, the QAT always displays a single line of icons. If the number of icons exceeds the Excel window width, it displays an additional icon at the end: More Controls. Click the More Controls icon, and the hidden QAT icons appear in a pop-up window.
Tip 10: Customizing Your Quick Access Toolbar 33 Part I Figure 10-2: Use the Customization tab in the Excel Options dialog box to customize the QAT.
34 Tip 10: Customizing Your Quick Access Toolbar Only you can decide which commands to put on your QAT. In general, if you find that you use a particular command frequently, it should probably be on your QAT. For example, when I work on a chart, I like to use the Chart Elements control to make it easy to select chart elements. That control is located in the Chart Tools ➪ Format tab and the Chart Tools ➪ Layout tab, but not in the Chart Tools ➪ Design tab.
Tip 11: Accessing the Ribbon From Your Keyboard 35 Accessing the Ribbon from Your Keyboard At first glance, you may think that the Ribbon is completely mouse-centric. After all, none of the commands has the traditional underlined letter to indicate the Alt+keystrokes. In fact, the Ribbon is very keyboard friendly. The trick is to press the Alt key (or slash key) to display the pop-up keytips: Each Ribbon control has a letter (or series of letters) that you type to issue the command.
36 Tip 12: Customizing the Default Workbook Customizing the Default Workbook When you create a new Excel workbook, you get a standard default workbook. What if you don’t like that workbook? For example, you may prefer a workbook with only one worksheet. Or, maybe you don’t like the default font or font size. Perhaps you prefer to have the gridlines hidden in your worksheets. Or, maybe you have a standard header that you always use on printed pages.
Tip 12: Customizing the Default Workbook 37 NOTE If you’re using Windows XP, the XLStart folder may be located in either of these directories: C:\Documents and Settings\\Application Data\ Microsoft\Excel\XLStart C:\Program Files\Microsoft Office\Office12\XLStart If you’re using Windows Vista, the directory is: C:\Users\\AppData\Roaming\Microsoft\Excel\XLStart If you ever need to bypass your new default workbook and start with one of the normal Excel default workbooks, choose Office ➪
38 Tip 13: Using Document Themes Using Document Themes Over the years, I’ve seen hundreds of Excel workbooks that were created by others. A significant percentage of these workbooks have one thing in common: They are ugly! In an effort to help users create more professional-looking documents, the Office 2007 designers incorporated the concept of Office document themes. Using themes is an easy (and almost foolproof) way to specify the colors and fonts and a variety of graphical effects in a document.
Tip 13: Using Document Themes 39 Figure 13-2 shows the same worksheet after applying a different document theme. The different theme changes the fonts, colors (which may not be apparent in the figure), and graphical effects for the SmartArt diagram. Part I Figure 13-2: The worksheet, after applying a different theme. Applying a Theme Figure 13-3 shows the theme choices that appear when you choose Page ➪ Layout ➪ Themes. This display is a “live preview” display.
40 Tip 13: Using Document Themes Figure 13-3: The built-in Excel theme choices. Each theme uses two fonts (one for headers and one for the body), and in some cases, these two fonts are the same. If none of the theme choices is suitable, choose Page Layout ➪ Themes ➪ Font ➪ Create New Theme Fonts to specify the two fonts you prefer (see Figure 13-4). When you use the Home ➪ Fonts ➪ Font control, the two fonts from the current theme are listed first in the drop-down list.
Tip 13: Using Document Themes 41 Use the Page Layout ➪ Themes ➪ Colors control to select a different set of colors. And, if you’re so inclined, you can even create a custom set of colors by choosing Page Layout ➪ Themes ➪ Colors ➪ Create Theme Colors. This command displays the dialog box shown in Figure 13-5. Note that each theme consists of 12 colors. Four of the colors are for text and backgrounds, six are for accents, and two are for hyperlinks.
42 Tip 14: Changing the Sheet Tab Appearance Changing the Sheet Tab Appearance Many users don’t realize it, but they can change the appearance of the sheet tabs displayed in a workbook. This tip describes how to change the size of the text and the color of the sheet tabs. Changing the Sheet Tab Color If your workbook has many sheets, you may find it helpful to color-code the sheet tabs.
Tip 14: Changing the Sheet Tab Appearance 43 Part I Figure 14-1: Use the Advanced Appearance dialog box to change the text size in the Excel sheet tabs.
44 Tip 15: Hiding User Interface Elements Hiding User Interface Elements Excel has various options that enable you to hide quite a few elements in order to customize your workspace. In some cases, you can change the display options in more than one place. For each of the elements listed in this section, I show you the easiest Hide method possible.
Tip 15: Hiding User Interface Elements 45 From the Excel Options Dialog Box You change the following settings from the Advanced tab in the Excel Options dialog box. To display this dialog box, choose Office ➪ Excel Options. Then click the Advanced tab. Windows in Taskbar: This option is in the Display section of the Advanced tab in the Excel Options dialog box. When the Show All Windows in the Taskbar option is turned on, each workbook appears as a separate icon on the Windows taskbar.
46 Tip 16: Hiding Columns or Rows Hiding Columns or Rows If you have data in a column or row that you don’t want to see, you can hide the column or row. Doing this is often useful if you have formulas that provide intermediate calculations and you don’t want them to appear in a report. Or, you may just want to hide unused rows and columns so that you can focus only on the used area of the sheet. NOTE Formulas that refer to data in hidden rows or columns continue to function normally.
Tip 16: Hiding Columns or Rows • 47 Select a range that consists of cells to the left and to the right of the hidden columns, and press Ctrl+Shift+0 (that’s a zero). To unhide one or more hidden rows, use any of these methods: Select a range that consists of cells above and below the hidden rows. Then choose Home ➪ Cells ➪ Format ➪ Hide & Unhide ➪ Unhide Rows. • Select entire rows above and below the hidden rows, and then right-click and choose Unhide from the shortcut menu.
48 Tip 17: Hiding Cell Contents Hiding Cell Contents Excel doesn’t provide a direct way to hide the contents of cells (without hiding entire rows and columns), but you can fake it in a few ways: • Use a special custom number format. Select the cell or cells to be hidden, press Ctrl+1 and click the Number tab in the Format Cells dialog box. Select Custom from the Category list, and then, in the Type field, enter ;;; (three semicolons). • Make the font color the same as the background color.
Tip 18: Taking Pictures of Ranges 49 Taking Pictures of Ranges Excel 2007 makes it easy to convert a range of cells into a picture. The picture can either be a “dead” image (it doesn’t change if the original range changes) or a “live” picture (which reflects changes in the original range). The range can even contain objects, such as charts.
50 Tip 18: Taking Pictures of Ranges Creating a Live Image of a Range To create an image that’s linked to the original range of cells, select the cells and press Ctrl+C to copy the range to the Clipboard. Then choose Home ➪ Clipboard ➪ Paste ➪ As Picture ➪ Paste Picture Link. Excel pastes a picture of the original range, and the picture is linked — if you make changes to the original, those changes are shown in the linked picture.
Tip 19: Performing Inexact Searches 51 Performing Inexact Searches If you have a large worksheet with lots of data, locating what you’re looking for can be difficult. The Excel Find and Replace dialog box is a useful tool for locating information, and it has a few features that many users overlook. Figure 19-1: The Find and Replace dialog box, with the Find tab selected. In many cases, you want to locate “approximate” text. For example, you may be trying to find data for a customer named Stephen R.
52 Tip 19: Performing Inexact Searches NOTE To search for a question mark or an asterisk, precede the character with a tilde character (~). For example, the following search string finds the text *NONE*: ~*NONE~* If you need to search for the tilde character, use two tildes. If your searches don’t seem to be working correctly, double-check these three options (which sometimes have a way of changing on their own): • Match Case: If this check box is selected, the case of the text must match exactly.
Tip 20: Replacing Formatting 53 Replacing Formatting A useful, but often overlooked, Excel feature is the ability to search for (and replace) cell formatting. For example, if you have cells that use the 14-point Calibri font, it’s a simple matter to change the formatting in all those cells to something else. To change the formatting by searching and replacing, follow these steps: 1.
54 Tip 20: Replacing Formatting NOTE If you use the Choose Format from Cell option in Step 4, you may find that not all occurrences of the formatting are replaced — usually because one or more aspects of the formatting do not match. For example, if you click on a cell that has General number formatting, it doesn’t replace cells that have Date number formatting.
Tip 21: Changing the Excel Color Scheme 55 Changing the Excel Color Scheme One of the new features in Office 2007 is the ability to change the color scheme of the applications. You do this in the Excel Options dialog box. Choose Office ➪ Excel Options, and then click the Popular tab. Use the Color Scheme drop-down list to select your color choice: Blue, Sliver, or Black (see Figure 21-1). When you change the color scheme, your choice affects all other Microsoft Office 2007 applications.
56 Tip 22: Limiting the Usable Area in a Worksheet Limiting the Usable Area in a Worksheet Have you ever wanted to restrict access to a certain range within a worksheet? For example, you may want to set up a worksheet so that only cells in a particular range can be activated or modified. This tip describes two ways to accomplish this task: by using the ScrollArea property and by using worksheet protection.
Tip 22: Limiting the Usable Area in a Worksheet 57 Part I Figure 22-2: Use the Properties window to control some properties of the worksheet. After performing these steps, you find that you cannot activate any cell outside the specified range. Also, some commands no longer work. For example, you cannot select entire rows and columns. Note that the scroll area is limited to a single contiguous range of cells. There’s a problem: The ScrollArea property isn’t persistent.
58 Tip 22: Limiting the Usable Area in a Worksheet CAUTION This method is by no means a foolproof way to prevent users from accessing parts of a workbook. Nothing can prevent a savvy user from using the Properties window to delete the contents of the ScrollArea field. Or, when the workbook is open, the user can choose to disable macros for the workbook. Another way to bypass the Workbook_Open macro is to press Shift while the file opens.
Tip 22: Limiting the Usable Area in a Worksheet 59 After you perform these steps, only the unlocked cells (those you selected in Step 1) are accessible. CAUTION Worksheet passwords are not at all secure. In fact, it’s a trivial matter to crack such a password. Therefore, worksheet protection is more of a convenience feature than a security feature.
60 Tip 23: Using an Alternative to Cell Comments Using an Alternative to Cell Comments As you probably know, you can attach a comment to any cell by using the Review ➪ Comments ➪ New Comment button (or, by right-clicking the cell and choosing Insert Comment from the shortcut menu). Use Review ➪ Comments ➪ Show All Comments to toggle the display of comments. The Excel Options dialog box has additional comment viewing options, found in the Display section of the Advanced tab.
Tip 24: Understanding the Excel Help System 61 Understanding the Excel Help System With every new release of Office, it seems that Microsoft revamps the Help system. Office 2007 is no exception. The new Help system (officially known as the Help Viewer) is radically different from the previous one, and in fact uses completely different technology. A new aspect of Office 2007 is its Supertip feature.
62 Tip 24: Understanding the Excel Help System Figure 24-2: Specifying where to search for help. • When you’re connected to Office Online, you can identify the URL for the Help topic and send it to someone else. To do so, right-click the current Help topic and choose Properties. The Properties dialog box displays the URL of the Help topic. Select the URL text with your mouse, and press Ctrl+C to copy it. The URL can be opened with any Web browser.
Tip 24: Understanding the Excel Help System 63 Part I Figure 24-3: Configuring the Help window toolbar. • If you’re a VBA programmer, you can write a macro to display a particular help topic. To do so, you need to know the topic ID. Locate the Help topic that you want to display, and then right-click and choose Copy Topic ID. For example, the topic ID for the topic Overview of Formulas is HP10081865.
64 Tip 25: Making a Worksheet “Very Hidden” Making a Worksheet “Very Hidden” You probably already know how to hide a worksheet: Just right-click the sheet tab and choose Hide Sheet from the shortcut menu. And, of course, it’s just as easy to unhide a sheet: Right-click any sheet tab and choose Unhide from the shortcut menu. (You see a list of all hidden sheets.) To make it more difficult for the casual user to unhide a hidden sheet, make the worksheet “very hidden.” Here’s how to do it: 1.
Tip 25: Making a Worksheet “Very Hidden” 65 Is the sheet is hidden forever? Nope. To make the very hidden sheet visible again, you use a simple VBA macro. The macro listed here unhides Sheet2 of the active workbook (change the sheet name as appropriate): Sub UnhideSheet() Worksheets(“Sheet2”).Visible = True End Sub Making a worksheet very hidden is not a security feature. Anyone who really wants to know what resides on a very hidden sheet can easily find out by using the UnhideSheet macro.
66 Tip 26: Disabling Hyperlink Warnings Disabling Hyperlink Warnings As you probably know, Excel lets you insert a hyperlink into a cell. A hyperlink can activate a different sheet or a different workbook, or it can execute an external program. For example, if a cell contains a hyperlink to an MP3 music file, clicking the hyperlink plays the song on the default music player. Hyperlinks are great — until you start using them.
Tip 26: Disabling Hyperlink Warnings 67 10. In the Edit DWORD Value dialog box, click the Decimal option, and type 1 in the Value data field. 11. Click OK to enter the value. Figure 26-2 shows the Registry Editor after I made the change. Part I Figure 26-2: The Registry Editor, with a new setting that disables the hyperlink warning message. After performing these steps, restart Excel. The new setting takes effect. By the way, this change affects all Microsoft Office 2007 applications.
68 Tip 26: Disabling Hyperlink Warnings 6. Remove the check mark from the Confirm Open After Download option, and click OK. 7. Click Close to close the Folder Options dialog box. You may need to repeat these steps for other file types. Eventually, Excel will be an environment free of hyperlink warnings.