This updated edition of Excel 2007 Miracles Made Easy features the amazing new upgrades offered in Excel 2010 and prepares users to transition to it. Covering only the revised components in Excel 2010, this manual shows how to navigate the ribbon interface and leverage the new business-intelligence tools within. With information on everything from using pivot tables to calculating Texas Hold 'Em probabilities, this indispensable reference features instructions for utilizing all of the new capabilities of Excel 2010.
This updated edition of Excel 2007 Miracles Made Easy features the amazing new upgrades offered in Excel 2010 and prepares users to transition to it. Covering only the revised components in Excel 2010, this manual shows how to navigate the ribbon interface and leverage the new business-intelligence tools within. With information on everything from using pivot tables to calculating Texas Hold 'Em probabilities, this indispensable reference features instructions for utilizing all of the new capabilities of Excel 2010.
eBookSecond Edition, Second edition (Second Edition, Second edition)
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
This updated edition of Excel 2007 Miracles Made Easy features the amazing new upgrades offered in Excel 2010 and prepares users to transition to it. Covering only the revised components in Excel 2010, this manual shows how to navigate the ribbon interface and leverage the new business-intelligence tools within. With information on everything from using pivot tables to calculating Texas Hold 'Em probabilities, this indispensable reference features instructions for utilizing all of the new capabilities of Excel 2010.
Product Details
| ISBN-13: | 9781615473007 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 09/15/2010 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 234 |
| File size: | 9 MB |
About the Author
Bill Jelen is an Excel expert at www.mrexcel.com, a regular on TechTV Canada's Call for Help, and a video podcaster who is regularly listed in the iTunes top 50. He is the author of Excel Gurus Gone Wild, Pivot Table Data Crunching, and 14 titles in the Excel for Professionals series. He lives in Akron, Ohio.
Read an Excerpt
Rev Up to Excel 2010
Upgraders Guide to Excel 2010
By Bill Jelen
Holy Macro! Books
Copyright © 2010 Bill JelenAll rights reserved.
ISBN: 978-1-61547-300-7
CHAPTER 1
INTRODUCING THE RIBBON
By far, the biggest change when you upgrade from Excel 2003 to Excel 2010 is trying to find commands on the ribbon. Rather than the familiar File, Edit, View, Insert, Format, Tools, Data, Window, Help menu bar, you are now presented with large icons and words such as File, Home, Insert, Page Layout, Formulas, Data, Review, and View.
Where to Find Commands in Excel 2010
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 in Chapter 4. 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.
• 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.
• 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. Table 1.1 shows the break out of individual commands.
Ribbon Components
The ribbon is the new interface at the top of Excel 2010. It consists of icons and words grouped into several tabs. Within each tab, icons are further classified into groups. In Figure 1.1, there are 4 icons in the Clipboard group of the Home tab and 10 icons in the Font group.
Dialog Box Launchers
In the lower-right corner of some groups, you will see a tiny icon showing a diagonal arrow. You can see this in the lower -right corner of Figure 1.1. This icon is a dialog box launcher. Click the icon to open a dialog box similar to the dialogs you are familiar with from Excel 2003.
Icons That Really Contain a Drop-Down
A lot of large icons look like a single icon until you hover over the icon. You will then see that the top half of the icon is a Paste icon, for example, and the bottom half of the icon is a drop-down with more choices related to the icon.
Gallery Icons Fit Many Choices into a Tiny Space
Some ribbon elements consist of a gallery of many different options. In Figure 1.4, the Chart Layouts gallery shows three thumbnails at a time.
You can use the Up- and Down-Arrow button to scroll through three thumbnails. Or, click the third arrow to open the gallery and see all the options simultaneously (Figure 1.5)
Making the Ribbon a Bit More Like a Menu
Although there is nothing you can do to bring back the legacy Excel menu and toolbars, you can make the ribbon behave a bit more like a menu.
Press Ctrl+F1 or right-click the ribbon and choose Minimize the Ribbon. When you do so, Excel hides the ribbon, as shown in Figure 1.6.
After the ribbon is hidden, you have more room to work with your document. You can also later click any ribbon tab name to open the ribbon temporarily. After you choose a command, Excel returns the ribbon back to the view in Figure 1.6. At least this feels more like a typical menu system.
Context-Sensitive Ribbons
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.
Different Screen Sizes Show Different Icons
If you frequently try to help coworkers over the phone, you want to be aware that the ribbon looks different at different sizes. If you have a massively wide monitor, you will see more icons than someone working on an older laptop.
To demonstrate, Figure 1.7 shows the right side of the Home tab. You see 11 drop-down icons. If you were looking at this screen, you might tell a coworker to open the drop-down next to the word Fill.
In Figure 1.8, the Excel window is less wide. The Insert, Delete, and Format icons are now arranged vertically, and words are missing next to the words AutoSum, Fill, and Clear.
At an even smaller screen width, the Insert, Delete, and Format icons are combined into a single drop-down called Cells. You have to open the Cells drop-down to see the Insert icon.
Eventually, if the Excel window gets small enough, Microsoft assumes that you cannot possibly be working in the worksheet and the ribbon disappears completely.
Keeping Some Commands Always Visible on the QAT
In Excel 97-2003, you always had quick access to icons for bold, italic, cut, paste, align right, decrease decimal, sort ascending, print preview, Chart Wizard, and more.
With Excel 2007, these icons are spread across seven ribbon tabs, so the odds are that you will not always have access to the various icons that you might need. Microsoft addresses this problem with the Quick Access Toolbar (QAT).
The QAT is a toolbar that is always visible near the ribbon. It appears on the left side of the screen, above the File tab. Although the QAT initially contains three icons (Save, Undo, and Redo), you can customize the QAT to hold all your favorite icons. One set of customizations can apply to all workbooks opened on the computer, and you can define a second set of icons to open for each specific workbook.
When you open the drop-down menu at the right side of the QAT, Microsoft suggests some popular icons that you might want to have available all the time. In Figure 1.12, selecting Open Recent File adds that icon to the end of the toolbar.
In reality, you can add any of hundreds of commands to the QAT. Right-click any command and choose Add to Quick Access Toolbar, as shown in Figure 1.13.
If you find yourself using the QAT frequently, move it below the ribbon, as shown in Figure 1.13, by using the final menu item in Figure 1.12.
You can customize the QAT in even more ways, too, as you'll read about in Chapter 2.
Finding the Elusive Mini Toolbar
If you have used Outlook 2003, you might be familiar with toolbars that fade in. When you receive a new Outlook message, a notifier box starts to appear in the lower-right corner of the screen. This notifier shows the subject line, the first words in the message, and icons to immediately delete the mail or to open the e-mail. If you are busy working on a document and ignore the notifier, it slowly fades away. However, if you move the mouse toward the notifier, it solidifies so that you have time to click the Delete button to eliminate the mail if it is junk.
The Mini Toolbar uses similar technology. I think it will appear much more frequently in Word and PowerPoint than it will appear in Excel. It is possible to use Excel 40 hours a week and never see the Mini Toolbar appear.
Note: During the beta period, Microsoft has variously called this feature a Mini Bar, Mini Toolbar, and a Floaty. Although I prefer visiting the Mini Bar, it looks like Mini Toolbar will win out in the final version. When I write books for Que, the editorial guidelines there state that the T in toolbar is never capitalized. Although I generally agree with this, to me, Mini Toolbar is just a bad replacement for Mini Bar, and so I will capitalize the T. After all, you wouldn't drive a Mini cooper automobile, would you?
The Mini Toolbar is elusive in Excel for two reasons. First, it is relatively hard to select characters within a cell in Excel. It is easier in Excel charts or SmartArt graphics to select characters, but it is relatively rare to select just a few characters inside of a cell. Second, on many computers, the Mini Toolbar initially appears in a completely invisible state! If you don't move the mouse pointer toward the completely invisible Mini Toolbar, it will never appear.
In Figure 1.14, the Mini Toolbar is just starting to appear (as we select characters in a chart title and move up and to the right). The toolbar is so light, it is difficult to guess if it will even show up as this book goes through the printing press. The Mini Toolbar starts just above the e in the word Title.
If you then hold the mouse still, the Mini Toolbar will remain in its nearly invisible state. If you move the mouse left or down, the Mini Toolbar will become completely invisible.
However, if you move the mouse right or up by a few pixels, the Mini Toolbar sharpens completely into view.
The theory behind the Mini Toolbar is that a leading reason for selecting text is that you might be planning on formatting the text. The Mini Toolbar puts 17 popular formatting commands at your mousetip. You can do a fair amount of formatting without ever having to visit the ribbon or the QAT.
Although the Mini Toolbar contains only 17 icons, a few of those icons lead to drop-down menus with significant variations. In Figure 1.16, the paint bucket icon opens to reveal flyout menus for patterns and gradients.
Mini Toolbar Versions
The figures shown previously in this chapter represent the Mini Toolbar when you are formatting a chart title. If you cause the Mini Toolbar to appear when you are editing text within a cell, some buttons do not apply.
Quadruple-click any nonblank cell and move the mouse pointer up and to the right. An abbreviated version of the Mini Toolbar with seven icons will appear. It doesn't make sense to change the indentation of just a few characters in a cell, so Excel produces this version of the Mini Toolbar, shown in Figure 1.17.
If you right-click any cell, the full Mini Toolbar appears above the cell. This is by far an easier way to invoke the toolbar than by selecting characters within the cell. In this version of the Mini Toolbar, the Center Across Selection icon replaces the Send Forward icon.
Customizing the Mini Toolbar
Although you can customize the ribbon and QAT, you still cannot add icons to the Mini Toolbar in Excel 2010.
When Does the Mini Toolbar Completely Disappear?
As discussed previously, if you move toward the Mini Toolbar, it solidifies. If you move away from the Mini Toolbar, it disappears. You can move your mouse from 7 o'clock to 2 o'clock and cause the Mini Toolbar to fade in and out of view.
However, once your mouse strays a certain distance from the selection, the Mini Toolbar disappears and will not reappear until you reselect the text.
This distance is based on pixels. In general, though, if you move approximately 10 rows away from the selection in a spreadsheet with the default font and zoom, you will have hit the limit and the Mini Toolbar will permanently disappear.
I can see many situations where you would move away from the selection (for example, to respond to an incoming e-mail notification). After you've moved too far away from the selection, you will either have to reselect the text or use the formatting icons in the Home ribbon.
Permanently Disabling the Mini Toolbar
The Mini Toolbar is fairly elusive in Excel and will rarely get in your way. However, if you want to permanently disable the feature, you can do so.
From the File menu, choose Options. The first setting in the Popular category is Show Mini Toolbar on Selection. Uncheck the box shown in Figure 1.19 to disable the Mini Toolbar.
Surely There Is a Classic Mode
I've been told that when Bill Gates saw the ribbon, he asked whether there would be a Classic mode. The Office user interface team convinced Bill that the ribbon would be too different and that there could not be a Classic mode. Bill Gates bought that argument.
Okay, so the largest software company in the world cannot figure out how to do a Classic mode, but a lone programmer named Lin Jie is offering Classic mode for $15.95. Figure 1.20 shows the familiar File, Edit, View menus along with the Standard and Formatting toolbars. To buy the add-in, visit www.mrexcel.com/classicexcelmenu.html
Next Steps
Chapter 2 shows you how to customize the ribbon and QAT.
CHAPTER 2CUSTOMIZING THE RIBBON AND QUICK ACCESS TOOLBAR
Presumably, you are reading this book because you are upgrading from Excel 97-2003 to Excel 2010. You are going to be horribly disappointed with the limited customizations that you can do to the ribbon and Quick Access Toolbar (QAT). During the Excel 2007 era, it was impossible to customize the ribbon using the Excel interface, so the limited tools in Excel 2010 actually seem like a great improvement over Excel 2003.
Adding a New Group to an Existing Tab
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 2.8.
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 dataset.
Figure 2.7 shows the resulting group on the Data tab.
If you are wondering why you had to choose an icon back in Figure 2.5, 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 in Figure 2.8.
(Continues...)
Excerpted from Rev Up to Excel 2010 by Bill Jelen. Copyright © 2010 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
New Paste Options ShortcutsIntroducing the Ribbon
Where Can I Find That on the Ribbon?
Customizing the Ribbon
Backstage View
Keyboard Shortcuts
Taming the QAT & Finding the Mini Bar
Unlocking the Big Grid
Page Layout View
Tables
Data Visualizations
Sparklines
Sort by Color
Removing Duplicates
Seeing Totals in the Status Bar
Handling Large Blocks of Text
Creating Business Diagrams with SmartArt
Charting
All Text Can Be WordArt
Using Picture Tools
Handling Error Formulas Using IfError Function
New Conditional Sum Functions
New Statistical Functions
Almost New Functions
AutoSum Tricks
Investigating Formulas
Formula Bar Tricks
Back into an Answer Using Goal Seek
Quick Translations
Preventing Distribution of Hidden Information
Finding Records with Filter
Pivot Tables & Slicers
Fill HandleTricks
Creating and Using Custom Lists
Joining Text
Splitting Apart Text
Adding Subtotals Automatically
Using Speak Cells
Recording a Macro
Solving Simultaneous Equations
Cool Uses for Excel – Solving Sudoku
Calculating Texas Hold-Em Probabilities
Download Cool Spreadsheets from Office Online
Get Excel Answers from the MrExcel.com Board
Document Themes & Cell Styles Across Microsoft Office