Bill Jelen is the principal of MrExcel.com and the author of Learn Excel from Mr. Excel and Guerilla Data Analysis Using Microsoft Excel. He lives in Uniontown, Ohio.

Excel 2007 Miracles Made Easy

By Bill Jelen

Holy Macro! Books

Copyright © 2007 Bill Jelen
All rights reserved.
ISBN: 978-1-932802-25-2

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,

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...