Learn Excel 2007 through Excel 2010 From MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis and Much More - 512 Excel Mysteries Solved
827
Learn Excel 2007 through Excel 2010 From MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis and Much More - 512 Excel Mysteries Solved
827eBook
Related collections and offers
Overview
Updating the previous edition's tips to make them compatible with Excel 2010, and featuring tips that are only available in Excel 2010, this new edition of Mr. Excel's popular software guide even incorporates suggestions sent in by readers. Each featured topic has a problem statement and description, followed by a broad strategy for solving the problem. Mr. Excel then walks readers through the specific steps to solve the issue. Alternate strategies are also provided, along with common “gotchas” that trip users up, leaving readers with not only answers to their specific dilemmas but also new and quicker ways to use formulas and spreadsheets.
Product Details
| ISBN-13: | 9781615474080 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 07/12/2011 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 827 |
| File size: | 49 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Learn Excel 2007 through 2010 from Mr Excel
512 Excel Mysteries Solved
By Bill Jelen, Scott Pierson, Mary Ellen Jelen
Holy Macro! Books
Copyright © 2011 Bill JelenAll rights reserved.
ISBN: 978-1-932802-44-3
CHAPTER 1
FIND ICONS ON THE RIBBON
Problem: The new ribbon user interface might be great for people new to Excel, but I knew the old Excel perfectly well. Why did Microsoft put pivot tables on the Insert tab instead of the Data tab, where they belong?
Strategy: The transition does not take that long, you follow a few basic rules.
Here are some simple guidelines:
• The Home tab contains the most frequently used commands. If you want to find something, start looking on the Home tab. Nearly everything from the Excel 2003 Edit and Format menus is located on the Home tab. Everything from the Excel 2003 Formatting toolbar is on the Home tab.
• The most-used commands on the Excel 2003 Insert menu are not on the Insert tab in Excel 2010! Instead, commands to insert cells, rows, columns, and worksheets are on an Insert drop-down on the Home tab. Commands to insert a function or a name are on the Formulas tab. Insert Comment is on the Review tab.
• The Pivot Tables command has been moved from the Data menu to the left side of the Insert tab.
• Most Excel 2003 File menu commands are on the Excel 2010 File tab. This tab is called the Backstage view and is discussed in detail later. A few File commands are elsewhere. For example, Save Workspace is on the View tab. Page Setup and Print Area are on the Page Layout tab. In Excel 2007, the File menu is behind the Office decoration in the top left corner of the screen.
• Everything on the old Window menu and most things from the old View menu are now on the View tab.
• Help is now the blue question mark icon at the top right of the window. Everything else that used to be on the Help menu is now under File, Help. Except Clippy. Clippy has retired.
• Items from the Data menu are generally on the Data tab, with the exception of the Pivot Tables command.
• The old Tools menu has been split among many tabs. You will find these commands spread among the Review, Formulas, Home, Data, View, and File tabs.
Microsoft does not provide a classic mode, but third party companies do. Check out the Classic Excel Menu from AddInTools.com or Toolbar Toggle.
With both of these add-ins, you can work in the Excel 2003 menu, and then switch over to the ribbon menu when you need to access new features.
SERIOUSLY, I REALLY CAN'T FIND THIS
Problem: I've looked everywhere, and I can not find certain commands.
Strategy: I will show you some strategies for finding icons, but first, here are the ones that through me for a loop:
• Help is now the blue question mark at the top right of Excel.
• Save Workspace is now on the right side of the View tab.
•Protect Sheet is now on the Review tab.
• Move or Copy Sheet is in the Format dropdown on Home.
• Insert Rows, Insert Columns is in the Insert dropdown on Home.
• The "X" to close Excel is missing from Excel 2007. It is at the bottom of the Excel 2010 File tab. In either case, you should add the Exit to the Quick Access Toolbar.
Alternate Strategy: You can find any command in the Customize dialog box. Hover over the command to see a tooltip explaining where to find the command in Excel 2010. Follow these steps to locate a command.
1. The top-left corner of Excel contains a tiny strip with icons for Save, Undo, and Redo. Right-click that strip and choose Customize Quick Access Toolbar.
2. The top left dropdown starts with Popular Commands. Open that dropdown and choose All Commands. You now have an alphabetical list of 2000+ commands.
3. Scroll through the list. When you find your command, hover. A tooltip appears showing you the ribbon tab, the group, then the command.
Gotcha: Sometimes, the tooltip will indicates that your command is in the dreaded Commands Not In The Ribbon category. You will have to add these commands to the Quick Access Toolbar in 2007/2010 or to the Ribbon in 2010.
WHERE ARE MY MACROS?
Problem: Did Microsoft abandon the macro facility? Where are the buttons to record a new macro, run a macro, and so on? How do I get to the Visual Basic Editor?
Strategy: Most of the macro icons are hidden. Due to beta tester outcry, Microsoft reluctantly added three macro options to the extreme right end of the View tab. You use the Macros dropdown to view macros, record a macro, or use relative references while recording a macro.
To access the rest of the macro functionality, you need to enable a hidden Developer ribbon tab. In Excel 2007, choose Office Icon, Excel Options, Popular, Show Developer Tab in the Ribbon. In Excel 2010, choose File, Options, Customize Ribbon. Add a checkmark next to Developer.
The Developer tab offers macro commands, buttons from the former Forms toolbar and Control Toolbox, and XML settings.
Additional Details: When you are recording a macro, instead of seeing the Stop Recording icon floating above the Excel window, you now see it in the Status Bar, next to Ready.
The same area of the status bar includes a Record Macro button when you are not recording a macro. However, because there is not a Relative References button, you cannot effectively record macros without using either the View tab or the Developer tab of the ribbon.
WHAT HAPPENED TO TOOLS, OPTIONS?
Problem: Where is the Excel 2003 Options command? Did Microsoft simplify the 13-tabbed Options dialog?
Strategy: Instead of Options, Microsoft now provides an Excel Options dialog. You access it with File, Options.
The new Excel Options dialog has 9 categories instead of the previous 13 tabs. It incorporates most settings that used to be in the Options dialog, plus Tools, AutoCorrect, Tools, Macro, Security, Tools, Add-Ins, and many options from the old Help menu.
In what seems like a confusing move, Excel took a few options from the old General tab, combined them with some new options, and moved them to the General category in the Excel Options dialog. In Excel 2007, this was called Popular.
The Advanced category in Excel 2010 offers 10 different sections. You will find that many of the former tabs from the Excel 2003 Options dialog have moved to the Advanced category in Excel 2010. Instead of flipping from tab to tab to tab in Excel 2003, you can scroll through the long list of Advanced options in Excel 2010.
The following table maps the Excel 2003 Options dialog tabs to the Excel 2010 Excel Options dialog categories:
Excel 2003 Tab Excel 2010 Category
View
Advanced (Groups 4 - 6)
Calculation
Formulas & Advanced (Group 6)
Edit
Advanced (Groups 1 and 2)
General
General and Advanced (Group 9)
Transition
Advanced (Group 10)
Custom Lists
Advanced (Group 11) or Popular in Excel 2007
Charts
Advanced (Group 4)
Color
Save
International Advanced (Group 1)
Save
Save
Error Checking Proofing
Spelling
Proofing
Security
Removed from the dialog; select File,
Save as, click the Tools button, and
choose the General category
The final four categories in the Excel Options dialog do not correspond to the Excel 2003 Options dialog:
• Customize Ribbon: For adding groups to the ribbon
• Quick Access Toolbar: For adding icons to the Quick Access toolbar
• Add-Ins: For managing add-ins (similar to Tools, Add-Ins in Excel 2003)
• Trust Center: For managing security and much more
CUSTOMIZING THE RIBBON
Problem: I want to customize the ribbon.
Strategy: Ribbon customization is not in Excel 2007. The customizations in Excel 2010 are weak compared with the customization capabilities in Excel 2003. However, they are better than the inability to customize in Excel 2007.
You might feel like the Pivot Table command belongs on the Data tab rather than on the Insert tab. You can add a new group to the Data tab to hold the pivot table icons.
First, look at the ribbon and decide where you want the new group to appear. Perhaps a good location would be between the Sort & Filter group and the Data Tools group.
Right-click anywhere on the ribbon and choose Customize the Ribbon.
The Customize dialog contains two large list boxes. You will first be working with the list box on the right side of the screen.
Expand the plus sign next to the Data entry to see the groups on the Data tab. If you want a new group to appear after the Sort & Filter group, click Sort & Filter, and then click the New Group button below the list box.
Excel adds a new group with the name of New Group (Custom). Click the Rename button below the list box.
Type a new name in the Rename dialog. Also, choose an icon. This icon will appear only when the Excel window gets small enough to force the group into a drop-down, as shown later in Figure 17.
Note: The 180 icons available in Excel 2010 are a far cry from the 4096 icons available in Excel 2003. As I pointed out at the beginning of this chapter, toolbar customization took a giant step backward after Excel 2003.
After renaming the new group in the list box on the right side, it is time to turn your attention to the list box on the left side. It starts out showing Popular Commands. Use the drop-down above the left list box to change from Popular Commands to All Commands.
Scroll down to the commands starting with Pivot. You will see a confusing array of commands. Click the first PivotTable icon, and click the Add button in the center of the screen. Click the second PivotChart icon, and then click the Add button. Click PivotTable and PivotChart Wizard, and then click the Add button.
It is sometimes difficult to figure out which icons you want. There are two icons that say PivotTable. The first icon is simply an icon. The second icon is an icon with a rightward-facing triangle on the right side of the list box. That triangle indicates that the second icon is actually a drop-down that leads to more choices. That second PivotTable drop-down icon is the icon at the bottom half of the Insert tab's Pivot Table group. It opens to enable you to choose between PivotTable and PivotChart. You might prefer to use that icon instead.
Two PivotChart icons are available. Hover over each icon to see that the first one is the PivotChart icon available on the PivotTable Tools Options tab. You will also see that the second icon is the one on the Insert tab. The first PivotChart icon will be grayed out unless you are in a pivot table. The second PivotChart icon is the one that is used to create a new pivot chart from a data set.
This figure shows the resulting group on the Data tab.
If you are wondering why you had to choose an icon back in Figure 14, it is for people who have the Excel window resized to a narrower width. If you make your Excel window narrower, the custom group will eventually get squished down to a single drop-down. Your icon will appear on that drop-down, as shown here.
Note back in Figure 10 that the Sort icon appears as a large icon with a caption and that the AZ and ZA icons appear as small icons without a caption. How can you specify that the pivot table icon should be large and the pivot chart and wizard icons should be small? You can't. At least not with the Excel interface.
If you want to start writing some XML and VBA, you can gain control over the size and images used in the ribbon. For an excellent book on this daunting task, look for RibbonX: Customizing the Office 2007 Ribbon by Robert Martin, Ken Puls and Teresa Hennig.
I find that I spend most of my time on either the Home or the Data tab. If I could combine the left side of the Home tab with the right side of the Data tab, plus pivot tables, I would probably be able to spend all my time on one tab.
This figure shows a new MrExcel tab that reuses groups from other ribbon tabs to build a new tab.
The general steps for creating a new ribbon tab are as follows:
1. Right-click the Ribbon and choose Customize the Ribbon.
2. Click New Tab at the bottom right of the dialog.
3. Click Rename and give the tab a name.
4. Use the Up and Down buttons at the right side of the dialog to move the new tab into the proper location.
5. From the left drop-down, choose Main Tabs.
6. In the left drop-down, expand an existing tab and find an existing group that you want to add to your new tab. Click that group and click Add.
7. Repeat step 6 to add additional groups.
8. You can reuse a custom group that you created previously. In the left drop-down, choose Custom Tabs and Groups. You can move the Pivot Table (Custom) tab created earlier in this chapter onto your new ribbon tab.
9. Click OK to finish customizing the ribbon tab.
GO WIDE
Problem: My ribbon looks different than my co-workers.
Strategy: Invest in a wide-screen monitor. The Office 2010 experience dramatically improves at a 1440x900 resolution.
When you reduce the size of the Excel window, Excel automatically starts consolidating ribbon options into smaller icons and then groups. The next four figures show details of the Home tab of the ribbon at different sizes.
If you are the go-to person for solving Excel problems and you are helping a co-worker over the phone without using GoToMeeting, there will be some frustration as you tell them to look for the Bad, Good, Neutral tiles and they can only see a Styles dropdown.
MINIMIZE THE RIBBON TO MAKE EXCEL FEEL A BIT MORE LIKE EXCEL 2003
Problem: The ribbon is taking up a lot of real estate at the top of my screen. It distracts me. I spend 99% of my Excel time in the grid, so I don't need to see the ribbon all the time.
Strategy: You can minimize the ribbon, reducing it to a simple line of Home, Insert, Page Layout, Formulas, and so on, as shown here.
To minimize the ribbon, you can either press Ctrl+F1 or right-click anywhere on the ribbon and then choose Minimize the Ribbon.
Additional Details: When you either click a ribbon tab with the mouse or use an Excel 2010 shortcut key, the ribbon will temporarily reappear. When you select the command from the ribbon, it will minimize again.
Double-click any ribbon tab to permanently exit minimized mode.
Alternate Strategy: Excel also offers a full screen mode. If you choose View, Full Screen, Excel will hide the ribbon, the ribbon tabs, and the Quick Access toolbar. You can press the Esc key to exit this mode.
USE A WHEEL MOUSE TO SCROLL THROUGH THE RIBBON TABS
If you point your mouse at the ribbon and scroll the wheel, you will quickly move from Home to Insert to Page Layout and so on.
WHY DO THE CHARTING RIBBON TABS KEEP DISAPPEARING?
Occasionally, new tabs will appear on the right side of the ribbon. These tabs appear when the current selection includes SmartArt graphics, charts, drawings, pictures, pivot tables, pivot charts, worksheet headers, tables, ink, or when you are in the legacy Print Preview mode.
These new tabs will stay visible as long as the object stays selected. If you click outside of your pivot table or chart, the tabs will disappear. If you are looking at an object and cannot find the tools necessary to edit the object, click the object to bring the tools back.
USE DIALOG LAUNCHERS TO ACCESS THE EXCEL 2003 DIALOG
Problem: I just want to go back to using the Excel 2003 dialogs.
Strategy: Many groups in the ribbon contain a tiny icons called dialog launchers. You can click an icon to return to the old-style dialogs. This figure shows an example of a dialog launcher.
Additional Details: It is difficult to describe the dialog launcher icon. If you enlarge the icon, you can see that it looks like the top-left corner of a square with an arrow pointing down and to the right. I am sure there is some artistic rationale why these pixels mean "take me back to the old dialog that I know and love," but I can't figure it out.
ICON, DROPDOWNS, AND HYBRIDS
Problem: The ribbon introduces several new types of controls that you've never used in Excel 2003.
In this figure, the Table and Picture icon will invoke a command. The Shapes and Screenshot icons are dropdowns that lead to a flyout menu.
However, the PivotTable icon is actually two icons. The top half will start a pivot table. The bottom half leads to a flyout. You can't really tell which icons are a hybrid of icon and dropdown until you hover over the icon with your mouse.
(Continues...)
Excerpted from Learn Excel 2007 through 2010 from Mr Excel by Bill Jelen, Scott Pierson, Mary Ellen Jelen. Copyright © 2011 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.