Excel 2007 Miracles Made Easy: Mr. Excel Reveals 25 Amazing Things You Can Do with the New Excel
167
Excel 2007 Miracles Made Easy: Mr. Excel Reveals 25 Amazing Things You Can Do with the New Excel
167eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473106 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 07/01/2008 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 167 |
| File size: | 7 MB |
About the Author
Read an Excerpt
Excel 2007 Miracles Made Easy
By Bill Jelen
Holy Macro! Books
Copyright © 2007 Bill JelenAll rights reserved.
ISBN: 978-1-932802-71-9
CHAPTER 1
Introducing the Ribbon
If you are a regular at MrExcel.com, you undoubtedly know and love the File – Edit – View – Insert – Format – Tools – Data – Window – Help menu bar that has been at the top of Excel for two decades.
Unfortunately, Microsoft no longer loves the menu bar. In fact, they have completely abandoned the menu bar and the toolbars in favor of something called the Ribbon.
Why would they fix something that was not broken? Well, perhaps the menu system actually was broken. Excel pros knew where to find everything on the menu, but it was pretty hopeless for a person new to Excel to ever navigate to Edit – Fill – Justify to learn that you could have Excel behave a little bit like a word processor. The chances of them finding Data – Import External Data – New Web Query to learn that their dashboards could put data from a table directly on a website were bleak.
In fact, I've heard that the Office team will visit customers and ask what new features the customer would like in Excel. Most of the time, someone asks for something that was added 10 years ago. The conclusion: There is a lot of powerful functionality in Excel that customers have not been able to discover. Instead of adding new features, Microsoft could instead make it easier for everyone to find the features that already exist. (In reality, Excel 2007 offers many fantastic new features, plus a new menu system that will help customers find previously existing features. Excel 2007 is the best new version of Excel since Excel 97.)
The ribbon is the new user interface at the top of Excel, PowerPoint, Word, and Access 2007. The ribbon is also present in the Compose Mail portion of Outlook 2007.
The ribbon is comprised of icons and words grouped into several tabs. In Excel, many of the editing icons are on the Home ribbon. Within the Home ribbon, icons are further classified into groups. In Figure 1.2, there are four icons in the Clipboard group of the Home ribbon and 11 icons in the Font group of the Home ribbon.
When I wrote Special Edition Using Excel 2007, the editorial style included the group name in the menu path. For example, the QUE book might say to "Select Home, Clipboard, Format Painter". During the course of writing the book, I started to think it was a bit strange to indicate that someone should select Clipboard. In reality, you would click on Home and then click on the Format Painter within the Clipboard group. In this book, I will say "Select Home – Format Painter".
The Most Important Choices Are Behind the Office Icon!
In the original version of Excel 2007, there was a File choice along the ribbon. For some unknown reason, Microsoft replaced the File choice with a funny looking round Office icon. This is downright confusing, because the most important commands for working with Excel are behind this icon.
Click the Office Icon and you will find most of the settings that used to be on the File menu in Excel 2003 (see Figure 1.3).
Using Dialog Box Launchers
In the lower right corner of some ribbon groups, you will see a tiny icon showing a diagonal arrow. This icon is a dialog box launcher. Click the icon to open a dialog box similar to the dialogs with which you are familiar from Excel 2003.
Making the Ribbon a Bit More Like a Menu
While 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.
Type Ctrl+F1 or right-click the ribbon and choose Minimize the Ribbon. Excel hides the ribbon as shown in Figure 1.5.
After the ribbon is hidden, you have more room to work with your document. You also can click any ribbon tab name at any time to open the ribbon temporarily. In Figure 1.6, I've selected Page Layout – Size – Legal. After clicking Legal, Excel returns the ribbon back to the view in Figure 1.5. (At least this feels more like a typical menu system.)
Tip: See Where Can I Find That on the Ribbon? on page 9 for a complete mapping from the old menu to the new ribbons. See Keyboard Shortcuts on page 19 for information on how to add buttons to the Quick Access Toolbar.
Using Context-Sensitive Ribbons
Occasionally, new tabs will appear on the right side of the ribbon. These ribbons will appear when the current selection includes SmartArt graphics, Charts, Drawings, Pictures, Pivot Tables, Pivot Charts, Worksheet headers, Tables, or Ink, or when you are in Print Preview mode.
These new ribbon tabs will stay visible as long as you are working on the selected object. When you select a cell outside of the object, Microsoft Excel 2007 will immediately put away the ribbon tabs.
Selecting from a Gallery in the Ribbon
Some ribbon elements are comprised of a gallery of many different options. In Figure 1.8, 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 More button to open the gallery and see all of the options at once (see Figure 1.9).
Using Live Preview
Many features of Excel 2007 offer a Live Preview feature. With Live Preview, you can simply hover over a choice and see a preview of that choice in the worksheet. This allows you to quickly browse many settings without actually committing to a change.
Unfortunately, it is an odd mix of features that supports Live Preview. You will have to use trial and error to identify these features.
If your computer is slow, you can turn off Live Preview. Use the Office Icon menu – Excel Options. In the Popular category, uncheck the box for Enable Live Preview.
Customizing the Ribbon
While it used to be easy to customize any toolbar, Microsoft has removed this functionality from the Excel user interface. To customize the ribbon, you now need to be able to write XML code.
Patrick Schmid has authored a COM add-in that will let you easily customize the ribbon. Patrick plans to offer a freeware and a premium version at http://pschmid.net.
Learning Where to Find Things
The most difficult part of the new Ribbon is figuring out where to find commands that you knew from Excel 2003. The next chapter, Where Can I Find That on the Ribbon?, provides a mapping to show you how to find the popular commands.
CHAPTER 2Where Can I Find That on the Ribbon?
The number one problem for someone upgrading to Excel 2007 is figuring out where to find a particular feature in the ribbon.
After upgrading, I found myself wondering, "Where would they have put pivot tables? Are they on the Data ribbon?". (No – they are on the Insert ribbon!)
This chapter is going to map, in color, where to find all of the menu items from Excel 2003. It will cover the regular menu plus the Standard and Formatting toolbars.
For the purpose of this chapter, I've color keyed the Excel 2007 ribbon tabs:
[ILLUSTRATION OMITTED]
The File Menu
Most of the commands on the legacy File menu are now on the Office Icon button (yellow squares in Figure 2.2). The Workspace functionality is on the View ribbon. Web Page Preview is no longer on the ribbon – you can add it to the Quick Access Toolbar (QAT) if it is a feature that you use. See Taming the QAT & Finding the Mini Bar on page 25 for more information on the QAT.
The Edit Menu
Nearly the entire Edit menu is on the Home ribbon. Undo & Redo are on the QAT. The Links command is now buried deep under Office Icon – Finish. For object editing, watch for context sensitive ribbon tabs to appear when you have selected the object.
View Menu
About half of the old View menu is now located on the View ribbon. The concept of toolbars and the task pane has been removed from the interface. Other commands are spread among the Insert, Review, and Page Layout ribbons.
The Insert Menu
The top selections on the Excel 2003 Insert menu have been promoted to the Home ribbon. Other commands are on the Insert, Page Layout, and Formulas ribbons.
The Format Menu
Nearly all of the commands on the Format menu now appear on the Home ribbon. One command, Format – Sheet – Background, is now on the Page Layout ribbon.
The Tools Menu
The former Tools menu commands have been broken up between several different ribbon tabs. You will find the commands on the Review, Formulas, Home, Data, and Developer tabs. The Add-Ins and Options commands are now in the Excel Options button, located on the Office Icon menu.
The Data Menu
Most of the Excel 2003 data menu is on the Excel 2007 Data ribbon, but several key elements are elsewhere. Filter & Sort appear on both the Home and Data ribbon. Pivot Tables – the most powerful feature in Excel – are, strangely, located on the Insert ribbon. The XML commands have been moved to the Developer ribbon.
The Window Menu
The entire Excel 2003 Window menu is now on the Excel 2007 View Menu.
The Help Menu
The Help command has been moved to a question mark icon at the right edge of the ribbon. The Office Assistant has been completely removed from the program. All of the remaining items on the Help menu are now in the Resources category of Excel Options.
The Standard Toolbar
The former Standard toolbar has been split between the Office icon, the Review ribbon, the Home ribbon, the QAT, and the Insert tab. Read Taming the QAT & Finding the Mini Bar on page 30 to learn how to put these icons on your Quick Access Toolbar.
The Formatting Toolbar
The entire Formatting toolbar is now on the Home ribbon.
CHAPTER 3Keyboard Shortcuts
When Excel gurus hear that Microsoft changed the menu system, they are often most concerned about all of the shortcut keys that they previously learned.
In Excel 97 – Excel 2003, most menu items had a single letter underlined. If you wanted to selected Edit – Fill – Justify from the menu, you simply had to hold down the Alt key while typing the underlined letter from each menu selection. Thus, Alt+E+I+J would allow you to quickly select the Justify command.
There are a few menu commands that I have memorized and I can type those shortcuts in my sleep.
In 80% of the cases, Excel 2007 will support your knowledge of legacy shortcut keys.
All Ctrl Key Shortcuts Continue to Work
Any Ctrl key shortcuts will continue to work. Some of the popular Ctrl shortcuts:
• Ctrl+B for Bold
• Ctrl+I for Italics
• Ctrl+U for Underline
• Ctrl+A to select All
• Ctrl+C to Copy
• Ctrl+X to Cut
• Ctrl+V to Paste
• Ctrl+Z to Undo
Most Alt Shortcuts for Edit, View, Insert, Format, Tools, and Data Will Work
When you press Alt+E, Alt+V, Alt+I, Alt+O, Alt+T, or Alt+D, Excel 2007 enters a special Office 2003 compatibility mode. A box appears in the top center of the screen showing the Office 2003 access keys that you have entered so far. When you enter enough keys to invoke a menu command in Excel 2003, the command will be invoked in Excel 2007.
In Figure 3.1, two-thirds of the keystrokes for Edit-Fill-Justify have been selected. When you strike the J, Excel will invoke the Justify command.
Caution! While this feature is good in theory, it does not work fast enough in the beta. There is a slight delay when you press Alt+E. I find that I usually have typed the I+J before the Office 2003 access key window appears. So, to use the Alt keyboard shortcuts that you've memorized from Office 97-2003, you have to type them a bit slower than normal.
Tip: Alt+F (File), Alt+W (Window), and Alt+H (Help) behave differently. Read about these keys below.
Using the Office 2007 Keyboard Shortcuts
If you are a fan of using the keyboard, you might have noticed one problem in Excel 2003. There were often menu items that did not have a keyboard shortcut. In Excel 2007, every menu item can be selected from the keyboard.
To access the new keyboard shortcuts, press and release the Alt key on the keyboard.
Excel displays a single-character keyboard shortcut for each tab of the ribbon.
If any contextual ribbon tabs are visible, they will have a two-character shortcut key.
All of the icons on the QAT are assigned a numeric keyboard shortcut. The first nine items are assigned the keys 1 through 9. Icons after that are assigned a two-character shortcut starting with a zero.
When you press F, H, N, P, M, A, R, or W, Excel displays the appropriate tab of the ribbon. The original shortcut key tips are replaced with new keytips that allow you to select any of the items in the ribbon. Figure 3.3 shows the key tips for the Data ribbon.
Some of the key tips make sense: A for Sort Ascending; D for Sort Descending; W for What If; V for Validation. Other shortcuts just seem to use the left over letters. If you type W to select What If Analysis, its dropdown provides you with three new shortcut keys from which to select items.
While the keyboard shortcuts for the QAT will constantly change, depending on how your QAT is customized, you should find that the keyboard shortcuts for the ribbon will remain constant. You can memorize that Alt+A+A will sort in ascending order. Alt+A+W+G will open the Goal Seek dialog.
Accessing the Old File and Window Menus
In Excel 2003, Alt+F would open the File menu. Alt+W opened the Window menu. In Excel 2007, these keystroke combinations do not show the Office 2003 keyboard shortcut window. Instead, Alt+F opens the Office Icon menu and Alt+W opens the View ribbon.
Many of the keyboard shortcuts in the Office Icon menu match the same shortcuts as in the File menu.
Thus, Alt+F+S was File – Save in Excel 2003, and this combination still executes a Save in Excel 2007.
Alt+F+D+A in Excel 2003 was File – Send to – Mail Recipient as Attachment. In Excel 2007, Alt+F+D still gets you to the Send menu, but now E is used to send a file as an e-mail attachment and A is used to send a file as an XPS attachment (Microsoft's new open source file format meant to compete with Adobe PDF).
Pressing Alt+W in Excel 2003 opened the Window menu. The commands on the old Window menu were New Window, Arrange, Compare Side by Side, Hide, Unhide, Split, and Freeze Panes.
Pressing Alt+W in Excel 2007 opens the View ribbon. All of the keyboard shortcuts (N, A, B, H, U, S, and F) perform identical actions in Excel 2007.
Note: In Excel 2003, Alt+W+2 would switch to the next open workbook. To do this in Excel 2007, use Ctrl+Tab.
Accessing Commands on the Excel 2003 Help Menu
Microsoft has simply abandoned the Alt+H command to access commands on the Excel 2003 Help menu.
The Office 2007 paradigm is that Alt+H takes you to the Home menu.
This is not a horrible loss, since there were not many commands on the Excel 2003 Help menu that you would have accessed using Alt+H. Alt+H+H would open Help, but the F1 key did, too, and continues to be a faster way to access Help.
Bonus Tip: Using the Keyboard to Enter Formulas
This trick is not new, but it is a faster way to enter formulas. In fact, the trick originated with Lotus 1-2-3 back in the 1980s. If you are a fan of using the keyboard, you should learn this method for entering formulas.
Say that you need to enter a formula in B8 a shown in Figure 3.8.
1. Start in cell B8. Type either an equals sign or a plus sign. If you regularly use the numeric keypad, it is easier to type a plus sign.
2. Press the up arrow three times. Excel shows a flashing cursor around cell B5. The provisional formula in B8 shows =B5.
3. Type a minus sign to continue the formula. The flashing box disappears. The focus returns to cell B8. If you want to point to cell B6, type the up arrow two times. The provisional formula now shows =B5-B6.
4. Type a plus sign.
5. Type the up arrow to move to B7.
6. Type the Enter key to accept the formula.
(Continues...)
Excerpted from Excel 2007 Miracles Made Easy by Bill Jelen. Copyright © 2007 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,
Dedication,
Acknowledgements,
Introduction,
Chapter 1 - Introducing the Ribbon,
Chapter 2 - Where Can I Find That on the Ribbon?,
Chapter 3 - Keyboard Shortcuts,
Chapter 4 - Taming the QAT & Finding the Mini Bar,
Chapter 5 - Unlocking the Big Grid,
Chapter 6 - Page Layout View,
Chapter 7 - Tables,
Chapter 8 - Data Visualizations,
Chapter 9 - Sort by Color,
Chapter 10 - Removing Duplicates,
Chapter 11 - Seeing Totals in the Status Bar,
Chapter 12 - Handling Large Blocks of Text,
Chapter 13 - Creating Business Diagrams with SmartArt,
Chapter 14 - Charting,
Chapter 15 - All Text Can Be WordArt,
Chapter 16 - Using Picture Tools,
Chapter 17 - Handling Error Formulas Using If Error Function,
Chapter 18 - New Conditional Sum Functions,
Chapter 19 - Almost New Functions,
Chapter 20 - AutoSum Tricks,
Chapter 21 - Investigating Formulas,
Chapter 22 - Formula Bar Tricks,
Chapter 23 - Back into an Answer Using Goal Seek,
Chapter 24 - Quick Translations,
Chapter 25 - Preventing Distribution of Hidden Information,
Chapter 26 - Finding Records with Filter,
Chapter 27 - Pivot Tables,
Chapter 28 - Fill Handle Tricks,
Chapter 29 - Creating and Using Custom Lists,
Chapter 30 - Joining Text,
Chapter 31 - Splitting Apart Text,
Chapter 32 - Adding Subtotals Automatically,
Chapter 33 - Using Speak Cells,
Chapter 34 - Recording a Macro,
Chapter 35 - Solving Simultaneous Equations,
Chapter 36 - Cool Uses for Excel – Solving Sudoku,
Chapter 37 - Calculating Texas Hold-Em Probabilities,
Chapter 38 - Download Cool Spreadsheets from Office Online,
Chapter 39 - Get Excel Answers from the MrExcel.com Board,
Chapter 40 - Document Themes & Cell Styles Across Microsoft Office,