eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473229 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 09/28/2005 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 836 |
| File size: | 30 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Learn Excel from Mr Excel
277 Excel Mysteries Solved
By Bill Jelen, Linda DeLonais, Paragon Prepress Editorial Team
Holy Macro! Books
Copyright © 2005 Bill JelenAll rights reserved.
ISBN: 978-1-932802-19-1
CHAPTER 1
SHOW FULL MENUS ALL THE TIME
Problem: You are trying to learn Excel. Microsoft only shows you the most common menu choices under each menu.
Fig. 1 shows a menu from one computer on my desk.
Fig. 2 is a screenshot from another computer on my desk.
A screenshot of the data menu of the third computer on my desk is seen here in Fig. 3.
This is maddening. How can you learn that there is a Sort option under the Data menu if Excel will not show you all of the options? Furthermore, Microsoft customizes the menu on the basis of items you use regularly.
First, this is insane; you cannot learn to regularly use something if you don't know it is there. Second, when you go to a new computer, one of the options that you think should be there may not show up. It is very difficult to learn Excel when the menus are not consistent across computers.
Strategy: Turn off Adaptive Menus. This will show you the complete menu every time that you access that menu. Follow these steps:
1. Go to the Tools menu, as shown in Fig. 4.
2.If the Customize option is not shown, select the double-down arrow at the bottom of the list in order to expand the menu.
3. Select Customize ... from the Tools menu, as shown in Fig. 5.
4. As you will see in Fig. 6, there are three tabs across the top of the Customize dialog. Choose the Options tab and then choose the box for Always Show Full Menus.
Additional Details: If you are in the process of learning Excel, it also helps to choose the Show Standard and Formatting Toolbars on Two Rows option. This will enable you to see all of the icons on the important Standard and Formatting toolbars. In Excel 2000, this setting appears as Standard and Formatting Toolbars Share One Row. In that version, you will want to uncheck the option.
Gotcha: Changing this setting in Excel will also affect Word and other Office products. There is no way to have Excel show full menus while Word shows the abbreviated menus.
Summary: While learning Excel, use the Tools — Customize feature to show the complete list of commands on each menu.
Commands Discussed: Tools — Customize
CHAPTER 2SHOW FULL TOOLBARS ALL THE TIME
Problem: You are trying to learn Excel. By default, Microsoft only shows you the most common menu choices from Excel's two most important toolbars: Standard and Formatting.
The Standard toolbar has icons for a New Workbook, Saving, Printing, Print Preview, Spell Check, Cut, Copy, Paste, Undo, and Redo. These are all fairly important items.
The Formatting toolbar has icons for Font, Font Size, Bold, Italic, Underline, Left Align, Center, Right Align, Number, Bullets, Indent, Out-dent, Borders, Cell Color, and Font Color. These are also all fairly important items.
The complete Standard toolbar can be seen here in Fig. 7. Note that my toolbar has some extra icons from various add-ins that I use.
Although this is the complete toolbar, most default installations will only show a subset of the icons on the toolbar. An example of the abbreviated toolbar follows in Fig. 8.
In order to use any of the other icons on the Standard toolbar, you have to choose the double-right arrow on the toolbar, as shown in Fig. 9.
The complete Formatting toolbar follows in Fig. 10.
Most users with a default installation will only see a portion of these icons and will have to use the double-right arrow on the toolbar to see all of the icons.
Microsoft does this in order to show you more rows of data in the spreadsheet. This may have been a problem in the days of VGA monitors. However, with today's high screen resolutions, you can afford to have the toolbars displayed on two rows and to see all of the available options at a glance.
Strategy: Display the toolbars on two rows. From the Tools menu, select Customize. On the Options tab, select the option for Show Standard and Formatting toolbars on two rows, as shown in Fig. 11.
Gotcha: In Excel 2000 and earlier, this option was called Display Standard and Formatting Toolbars on One Row.
Summary: While learning Excel, use the Tools — Customize feature to show the complete version of Standard and Formatting toolbars.
Commands Discussed: Tools — Customize
CHAPTER 3ADD A CLOSE BUTTON TO THE STANDARD TOOLBAR
Problem: Your efficiency with Excel would increase if you had a one-click option for closing workbooks.
Strategy: Customize the Standard toolbar to include a Close option near the Save option. Follow these steps.
1. From the menu, select View — Toolbars — Customize. The Customize option is the final choice in the list of toolbars. This will display the Customize dialog box. While the Customize box is displayed, the toolbars are in a special state. You can take any toolbar button and drag it to a new location, to a new toolbar, or even drag it off the toolbar to delete it. Also, there are hundreds of new icons that you can add to your toolbars.
2. From the Customize dialog box, choose the Commands tab, as shown in Fig. 12.
3. Choose a Category in the left listbox. From the right listbox, you can scroll through dozens of buttons in that category.
4. The Close button is located in the File category. Choose File from the left listbox, as shown in Fig. 13.
5. Click on Close in the right listbox. Drag the item towards a toolbar. When you are on the toolbar, choose the desired location. Excel will display an I-Beam at the location where the button will be added, as shown in Fig. 14.
6. When the I-Beam is in the proper location, release the mouse button. The button is added to the toolbar, as shown in Fig. 15.
7. Choose OK to close the Customize dialog. The Close button will remain on your Standard toolbar in this and all future Excel sessions until you remove it.
Summary: Experiment with adding new buttons so that any task that you perform regularly has a one-click option.
Commands Discussed: View — Toolbars — Customize
CHAPTER 4CLOSE ALL OPEN WORKBOOKS
Problem: You have 22 Excel workbooks open. You want to keep Excel open but close all of the workbooks. Doing File — Close 22 times can get monotonous.
Strategy: Hold down the Shift key on the keyboard before selecting the File menu. This will allow you to see an alternate File menu with Close All in place of Close, as shown in Fig. 16.
Summary: Hold down the Shift key to access the timesaving Close All command.
Commands Discussed: File — Close All
CHAPTER 5DOUBLE THE VALUE OF THE RECENTLY USED FILE LIST
Problem: You routinely open the same six workbooks. The File menu shows only the last four workbooks that you opened or saved. It sure would be nice if it showed at least the last six workbooks.
Strategy: Good news! You can increase the Recently Used File List (located at the bottom of the File menu) from four to nine workbooks. Go to Tools — Options — General. Use the spin button to dial the Recently Used File List from 4 to 9, as shown in Fig. 17.
Gotcha: Immediately after changing the value, your list may only show four files. You have to open and close additional files to expand the list, as shown in Fig. 18.
Summary: To have the File menu show more than the last four workbooks used, use Tools — Options — General and select the number of recently used files for it to show.
Commands Discussed: Tools — Options — General — Recently Used File List
CHAPTER 6REMEMBER WORKBOOKS TO OPEN USING A WORKSPACE
Problem: You need to open seven files in order to prepare a weekly report. It is tedious to open all seven workbooks.
Strategy: Open the seven workbook files and save them as a workspace. Follow these steps.
1. Open the workbooks and then select File — Save Workspace, as shown in Fig. 19.
2. Give the workspace file a name, such as MyFiles. As shown in Fig. 20, note that the file saved will have an .xlw extension instead of.xls.
3. You can now close Excel and open MyFiles.xlw. All seven workbooks will open at once.
Gotcha: This workspace does not actually store the seven files, but only points to them. If you were to move one of the seven files to a new folder using Windows Explorer, Excel would not be able to open that file.
Gotcha: Excel does not remember the arrangement of the workbooks in the workspace. Although your original workbooks may have been maximized, Excel might open them in a tiled arrangement. Choose the Maximize icon at the top of one workbook to return all workbooks to their original maximized state. This problem was worse in Excel 97, where Excel would frequently forget the zoom and frozen panes in the workspace.
Summary: To reduce the time in opening more than one workbook, use File — Save Workspace when opening workbooks, and then name the workspace.
Commands Discussed: File — Save Workspace
CHAPTER 7AUTOMATICALLY MOVE THE CELL POINTER IN A DIRECTION AFTER ENTERING A NUMBER
Problem: If you type a number and then press a direction arrow key, Excel will enter the number and move the cell pointer in the direction of the arrow key. However, if you are using the numeric keypad, it is much more convenient to use the Enter key on the numeric keypad rather than the arrow keys. By default, Excel will move the cell pointer down one cell when you type Enter. When entering data in a worksheet such as the one shown in Fig. 21, is there a way to have Excel automatically move the cell pointer to the next cell to the right after each entry?
Strategy: Go to Tools — Options. On the Edit tab, go to the Move Selection after Enter Direction dropdown. Choose Right, as shown in Fig. 22.
Result: The cursor will automatically move one cell to the right every time you press the Enter key.
Summary: You can have the cell pointer move in any direction after using Enter by changing this setting on the Edit tab of the Options menu.
Commands Discussed: Tools — Options — Edit
CHAPTER 8HOW TO SEE HEADINGS AS YOU SCROLL AROUND A REPORT
Problem: You have a spreadsheet with headings at the top, as shown in Fig. 23. You want to be able to scroll through the data and always see the headings.
Strategy: Use the Freeze Panes command on the Window menu. It is not obvious, but in order to make the Freeze Panes command work, you must place the cell pointer in the correct location before using the command.
Look at the spreadsheet shown in Fig. 23. It would be really handy to have rows 1 through 3 always visible while you scroll. Here's how to do that.
1. Place the cell pointer in cell A4, as shown in Fig. 24, before you select the Freeze Panes command. The Freeze Panes command will freeze all visible rows above the cell pointer and all visible columns to the left of the cell pointer. By placing the cell pointer in column A, you will not freeze any columns, only the rows.
2. With the cell pointer in cell A4, select Window — Freeze Panes. A solid horizontal line will be drawn between rows 3 and 4. As you scroll down past row 30, you will always be able to see the heading rows, as shown in Fig. 25.
Additional Details: To turn off this feature, go to the Window menu and select Unfreeze Panes. This menu item is only visible after you have frozen the panes.
If you want to maximize the number of rows visible, it is possible to freeze only row 3.
1. First, unfreeze the panes using Window — Unfreeze Panes. Place the cell pointer in the last visible row in the window. Hit the Down Arrow twice to force rows 1 and 2 to scroll above the window. Row 3 is now the first visible row, as shown in Fig. 26.
2. Place the cell pointer in cell A4 and invoke the Window — Freeze Panes command. You will now be able to scroll with only row 3 frozen at the top of the window, as shown in Fig. 27.
Summary: Use the Window — Freeze Panes command to keep certain rows visible at the top of the window as you scroll through the data.
Commands Discussed: Window — Freeze Panes; Window — Unfreeze Panes
Cross Reference: How to Print Titles at the Top of Each Page;
CHAPTER 9HOW TO SEE HEADINGS AND ROW LABELS AS YOU SCROLL AROUND A REPORT
Problem: As shown in Fig. 28, you have a widespreadsheet. There are headings at the top of the spreadsheet and there are several columns of labels at the left side of the spreadsheet. You also have monthly sales figures that extend far to the right. You need to be able to scroll through the sales figures while always seeing both the headings at the top and the labels at the left of the spreadsheet.
Strategy: Use the Freeze Panes command on the Window menu. Remember, to make the Freeze Panes command work, you must place the cell pointer in the correct location before using the command.
In the spreadsheet shown in Fig. 28, you might want row 5 and columns A through F visible at all times. You would then be able to scroll through the monthly figures, while always being able to see the customer information in the left columns and the month name information in row 5.
1. First, you want to scroll the worksheet so that cell A5 is in the upper left visible corner. You could try to do this with the scrollbars or the arrows, but this method will always work:
• Scroll A5 out of view by hitting PgDn a few times and the Tab key a few times.
• Hit the F5 key to bring up the Go To dialog.
• Enter A5 in the Reference box and hit Enter. Cell A5 will now be the first visible cell in the window.
2. Place the cell pointer in cell G6. Select Window — Freeze Panes. You will see a solid line between columns F and G and between rows 5 and
Result: As shown in Fig. 29, you can scrollthrough any numeric data while being able to look at the row and column headings.
Gotcha: In Fig. 29, rows 1 through 4 cannot be accessed. You will have to unfreeze the panes to change those rows.
Gotcha: If you have many columns frozen at the left of the report and then make those columns wider, it is possible that the entire window will be filled with the frozen columns. You then have a situation where the arrow keys will move the cell pointer to other cells that you cannot see. The Address Bar and the Formula Bar will show you the active cell, but you cannot see it. You need to either make the frozen columns less wide, or use the Window — Unfreeze Panes command to unfreeze the panes.
Summary: Use the Window — Freeze Panes command to keep certain rows and columns visible at the top of the window as you scroll through the data. It is critical that you place the cell pointer in the first cell that is not to be frozen before invoking the command.
Commands Discussed: Window — Freeze Panes; Edit — Go To
Cross Reference: How to See Headings as You Scroll Around a Report
CHAPTER 10HOW TO PRINT TITLES AT THE TOP OF EACH PAGE
Problem: The report shown in Fig. 30 has 90rows of data. You want to have the title rows print at the top of each printed page.
Strategy: Printing options are controlled on the fourth tab of the Page Setup dialog box. In this case, you want rows 1 through 5 to print at the top of each page.
1. Select Page Setup from the File menu. The dialog box offers four tabs. Select the fourth tab, called Sheet. See Fig. 31.
2. Enter 1:5 in the box called Rows to Repeat at Top, as shown in Fig. 31, in order to have rows 1 through 5 repeated at the top of each printed page. Select Print Preview to ensure that the results are what you desire.
Alternate Solution: Rather than typing 1:5 in the text box, you could click the reference icon on the right side of the box, as shown in Fig. 32.
This will shrink the Page Setup dialog box to just the Rows to Repeat at Top dialog. You can now use the mouse to select rows 1 through 5. Choose the icon at the right side of the text box to return to the Page Setup dialog box, as shown in Fig. 33.
Alternate Solution: If the rows you desire to repeat at the top are visible behind the dialog box, as shown in Fig. 34, use the mouse to highlight them while the cursor is in the Rows to repeat at top textbox.
Summary: Use the Page Setup command on the File menu to have titles and headings appear at the top of every page.
Cross Reference: Another way to have text appear at the top or bottom of every page is to use the Header/Footer command on the Page Setup dialog. See How to Print Page Numbers at the Bottom of Each Page on Page 19.
Commands Discussed: File — Page Setup
CHAPTER 11PRINT A LETTER AT THE TOP OF PAGE 1 AND REPEAT HEADINGS AT THE TOP OF EACH SUBSEQUENT PAGE
Problem: As shown in Fig. 35, you are sending out a worksheet that contains instructions, followed by a lengthy report. You would like the headings to appear at the top of each page after the first page. You don't want the headings to appear at the top of the letter on page one.
Strategy: Printing options are controlled on the fourth tab of the Page Setup dialog box. If you specify that a row in the middle of the print range should be repeated at the top of the pages, it will not begin repeating until the next page.
(Continues...)
Excerpted from Learn Excel from Mr Excel by Bill Jelen, Linda DeLonais, Paragon Prepress Editorial Team. Copyright © 2005 Bill Jelen. Excerpted by permission of Holy Macro! Books.
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.
Table of Contents
Contents
Title Page,Copyright Page,
ABOUT THE AUTHOR,
ACKNOWLEDGMENTS,
DEDICATION,
FOREWORD,
PART 1 - THE EXCEL ENVIRONMENT,
PART 2 - CALCULATING WITH EXCEL,
PART 3 - WRANGLING DATA,
PART 4 - MAKING THINGS LOOK GOOD,
AFTERWORD,