Chapter One
Basic Excel Usage
In 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 2 Maximizing Ribbon Efficiency 12
Tip 3 Selecting Cells Efficiently 14
Tip 4 Making "Special" Range Selections 18
Tip 5 Undoing, Redoing, and Repeating 21
Tip 6 Discovering Some Useful Shortcut Keys 24
Tip 7 Navigating Sheets in a Workbook 26
Tip 8 Resetting the Used Area of a Worksheet 28
Tip 9 Understanding Workbooks versus Windows 29
Tip 10 Customizing Your Quick Access Toolbar 31
Tip 11 Accessing the Ribbon with the Keyboard 35
Tip 12 Customizing the Default Workbook 36
Tip 13 Using Document Themes 38
Tip 14 Changing the Sheet Tab Appearance 42
Tip 15 Hiding User Interface Elements 44
Tip 16 Hiding Columns or Rows 46
Tip 17 Hiding Cell Contents 48
Tip 18 Taking Pictures of Ranges 49
Tip 19 Performing Inexact Searches 51
Tip 20 Replacing Formatting 53
Tip 21 Changing the Excel Color Scheme 55
Tip 22 Limiting the Usable Area in a Worksheet 56
Tip 23 Using an Alternative to Cell Comments 60
Tip 24 Understanding the Excel Help System 61
Tip 25 Making a Worksheet "Very Hidden" 64
Tip 26 Disabling Hyperlink Warnings 66
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. Follow these steps to find out more:
1. Choose Office [right arrow] Excel Options.
2. In the Excel Options dialog box, click the Resources tab.
3. On the Resources tab, click the About button.
Figure 1-1 shows that I''m using version 12.0, also known (officially) as Microsoft Office Excel 2007. Most people just call it Excel 2007. Notice the decimal places after the version number? That represents the build of the product. In my case, I''m using build 4518. I don''t know what the 1014 means.
So, who cares which version of Excel you use? Most of the time, nobody cares. As long as your version does what you want it to do, the version makes no difference. But if you share your workbooks with other users, the version may be very important.
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. This download gives previous versions of Office support for the new Office 2007 file formats. Therefore, you can share Excel 2007 files with users of Excel 2000, Excel 2002, and Excel 2003. Note, however, that this add-on doesn''t endow the older versions with any new features. It just allows the software to open and save the files.
If you must share a workbook with someone using a version before Excel 2007, save the file as an XLS file by choosing the Excel 97-2003 Workbook option in the Save As dialog box. Pay attention to the results of the Compatibility Checker, which appears automatically whenever you save your file in this older format. This useful dialog box, shown in Figure 1-2, identifies potential problems when your workbook is used by someone who has an Excel version earlier than Excel 2007.
You can display the Compatibility Checker dialog box at any time by choosing Office -> Prepare -> Run Compatibility Checker.
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.
The words along the top (Home, Insert, and Page Layout, for example) are known as tabs. Click a tab, and the Ribbon changes to display a new set of commands, arranged in groups. (For example, the Home tab has groups labeled Clipboard, Font, Alignment, and more).
Using the Ribbon is straightforward enough. Somewhat ironically, new users will adapt much more quickly to the Ribbon than experienced users will. Long-time Excel users will spend a considerable amount of time scratching their heads and trying to figure out where their favorite commands now live.
The following tips help you get the most out of the new Ribbon user interface:
Don''t be afraid to click on the stuff you find on the Ribbon. This advice applies especially if you''re an experienced user who is trying to adapt to the new user interface. Everything you do can be undone by clicking Undo (located on the Quick Access Toolbar, which is on the left side of the Excel title bar by default). To get more screen real estate, enter "Hide the Ribbon mode." You can hide the Ribbon by double-clicking any of the tabs. When you need to access a command, just click the tab, and the Ribbon comes back to life. The Ribbon disappears again when you finish. To leave this mode and return to normal, just double-click a tab. You can also enable and disable Hide the Ribbon mode by pressing Ctrl+F1. Access the Ribbon from the keyboard. Although the Ribbon appears to be mouse-centric, it''s quite functional from the keyboard. See Tip 11 for some keyboard access tips. Note that the appearance of the commands on the Ribbon varies, depending on the width of the Excel window. When the window is too narrow to display the entire Ribbon, the commands adapt and may seem to be missing. But the commands are still available. Figure 2-1 shows the Home tab of the Ribbon when the Excel window is at three different widths. In the first view, all controls are fully visible. When the Excel window is made narrower, some descriptive text disappears, but the icons remain. When the window is made very narrow, some groups display a single icon. However, if you click the icon, all the group commands are available to you. Take advantage of contextual tabs on the Ribbon. In some cases, the Ribbon displays new, contextual tabs. For example, when you select a chart, you see three new tabs: Design, Layout, and Format. Notice that these new tabs contain a description on the Excel title bar (when a chart is selected, the description reads Chart Tools). Selecting any of the following elements also results in the display of contextual tabs: a table, a pivot table, a drawing (a shape or WordArt), a picture (a photo or clip art), a header or footer (in Page Layout view), or a SmartArt diagram. You can, of course, continue to use all the other tabs when a contextual tab is displayed. 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). Right-click certain items in Excel to take advantage of the mini Toolbar, displayed above the shortcut menu (see Figure 2-2). This toolbar contains formatting tools that may save you a trip to the Ribbon. When I started using Excel 2007, I just ignored the mini Toolbar, but I soon realized that it''s a very handy tool. Don''t ignore the Quick Access Toolbar (QAT). This part of the new user interface is the only interface element that you (the user) can customize. Check out Tip 10 to find out how easy it is to customize it. Finally, keep in mind that Excel has additional commands that aren''t even on the Ribbon. If you come up empty-handed after performing an exhaustive Ribbon search for the old text-to-speech commands, for example, don''t despair: You need to add those commands yourself, by customizing the QAT (see Tip 10).
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.
Selecting a Range by Using the Shift and Arrow Keys
The simplest way to select a range is to press (and hold) Shift and then use the arrow keys to highlight the cells. For larger selections, you can use PgDn or PgUp while pressing Shift to move in larger increments.
You can also use the End key to quickly extend a selection to the last non-empty cell in a row or column. To select the range B3:B8 (see Figure 3-1) by using the keyboard, move the cell pointer to B3 and then press the Shift key while you press End followed by the down arrow key. Similarly, to select B3:D3, press the Shift key while you press End, followed by the right-arrow key.
Selecting the Current Region
Often, you need to select a large rectangular selection of cells - the current region. To select the entire block of cells, move the cell pointer anywhere within the range and press Ctrl+Shift+8.
New
If the active cell is within an Excel 2007 table (created by choosing Insert [right arrow] Tables [right arrow] Table), you can press Ctrl+A to select the cells (but not the header row or total row) within the table. Press Ctrl+A again to select the entire table (including the header row and total row). Press Ctrl+A a third time to select the entire worksheet.
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
Most of the time, your range selections are probably simple rectangular ranges. In some cases, you may need to make a multiple selection - a selection that includes nonadjacent cells or ranges. For example, you may want to apply formatting to cells in different areas of your worksheet. If you make a multiple selection, you can apply the formatting in one step to all selected ranges. Figure 3-2 shows an example of a multiple selection.
You can select a noncontiguous range by using either the mouse or the keyboard.
Press Ctrl as you click and drag the mouse to highlight individual cells or ranges.
From the keyboard, select a range as described previously (by using the Shift key). Then press Shift+F8 to select another range without canceling the previous range selection. Repeat this action as many times as needed.
Selecting Entire Rows
To select a single row, click a row number along the left of the worksheet. Or, select any cell in the row and press Shift+spacebar.
To select multiple adjacent rows, click and drag in the row number area. Or, select any cell in the first (or last) row, press Shift+spacebar, and use the arrow keys to extend the selection down (or up).
To select multiple nonadjacent rows, press Ctrl while you click the row numbers for the rows you want to include.
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).
To select multiple nonadjacent columns, press Ctrl while you click the column letters for the columns you want to include.
Selecting Multisheet Ranges
In addition to two-dimensional ranges on a single worksheet, ranges can extend across multiple worksheets to be three-dimensional ranges.
Figure 3-3 shows a simple example of a multisheet workbook. The workbook has four sheets, named Totals, Marketing, Operations, and Manufacturing. The sheets are laid out identically.
Assume that you want to apply the same formatting to all sheets - for example, you want to make the column headings bold with background shading. Selecting a multisheet range is the best approach. When the ranges are selected, the formatting is applied to all sheets.
In general, selecting a multisheet range is a simple two-step process:
1. Select the range in one sheet.
2. Select the worksheets to include in the range.
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.
When you finish working with the multisheet range, click any sheet tab to leave Group mode.
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. Choose Home -> Find & Select -> Go To Special to display the Go To Special dialog box, as shown in Figure 4-1. Another way to open the Go To Special dialog box is to press F5 and then click the Special button in the Go To dialog box.
After you make your choice in the Go To Special dialog box, Excel selects the qualifying subset of cells in the current selection. Usually, this results in a multiple selection. If no cells qualify, Excel lets you know with the message No cells were found.
Note
If you bring up the Go To Special dialog box when only one cell is selected, Excel bases its selection on the entire active area of the worksheet. Otherwise, the selection is based on the selected range.
(Continues...)
Excerpted from John Walkenbach''s Favorite Excel 2007 Tips & Tricksby John Walkenbach Copyright © 2007 by John Walkenbach. Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
