Introduction
| Most people don't need much convincing to use Excel, Microsoft's premier spreadsheet software. In fact, the program comes preinstalled on a lot of computers, making it the obvious choice for millions of number crunchers. Despite its wide use, however, few people know where to find Excel's most impressive features or why they'd want to use them in the first place. Excel 2007: The Missing Manual fills that void, explaining everything from basic Excel concepts to fancy tricks of the trade. This book teaches you not only how the program works, it also shows you how to use Excel's tools to answer real-world questions like "How many workdays are there between today and my vacation?", "How much money do I need in the bank right now to retire a millionaire?", and "Statistically speaking, who's smarterDemocrats or Republicans?" Best of all, you'll steer clear of obscure options that aren't worth the trouble to learn, while homing in on the hidden gems that'll win you the undying adoration of your coworkers, your family, and your friendsor at least your accountant . Note: This book is written with Microsoft's latest and greatest release in mind: Excel 2007. This book won't help you if you're using an earlier version of Excel, because Microsoft has dramatically changed Excel's user interface (the "look and feel" of the program). However, if you're an unredeemed Excel 2003 or Excel 2002 fanatic, you can get help from the previous edition of this book, which is simply named Excel 2003: The Missing Manual . The Mac version of Excel is covered in Office 2004 for Macintosh: The Missing Manual .
What You Can Do with ExcelExcel and Word are the two powerhouses of the Microsoft Office family. While Word lets you create and edit documents, Excel specializes in letting you create, edit, and analyze data that's organized into lists or tables. This grid-like arrangement of information is called a spreadsheet . Figure I-1 shows an example.
Tip: Excel shines when it comes to numerical data, but the program doesn't limit you to calculations. While it has the computing muscle to analyze stacks of numbers, it's equally useful for keeping track of the DVDs in your personal movie collection. Some common spreadsheets include:
These examples just scratch the surface. Resourceful spreadsheet gurus use Excel to build everything from cross-country trip itineraries to logs of every Kevin Bacon movie they've ever seen. Of course, Excel really shines in its ability to help you analyze a spreadsheet's data. For example, once you've entered a list of household expenses, you can start crunching numbers with Excel's slick formula tools. Before long you'll have totals, subtotals, monthly averages, a complete breakdown of cost by category, and maybe even some predictions for the future: Excel can help track your investments and tell you how long until you'll have saved enough to buy that weekend house in Vegas. The bottom line is that once you enter raw information, Excel's built-in smarts can help compute all kinds of useful figures. Figure I-2 shows a sophisticated spreadsheet that's been configured to help identify hot-selling product categories.
Note: Keen eyes will notice that neither of these examples (Figure I-1 and Figure I-2) include the omnipresent Excel ribbon, which usually sits atop the window, stacked with buttons . That's because it's been collapsed neatly out of the way to let you focus on the spreadsheet. You'll learn how to use this trick yourself in Section 1.3.1. Excel's not just a math wizard. If you want to add a little life to your data, you can inject color , apply exotic fonts, and even create macros (automated sequences of steps) to help speed up repetitive formatting or editing chores. And if you're bleary-eyed from staring at rows and rows of spreadsheet numbers, you can use Excel's many chart-making tools to build everything from 3-D pie charts to more exotic scatter graphs. (See Chapter 17 to learn about all of Excel's chart types.) Excel can be as simple or as sophisticated as you want it to be. Excel's New FaceAlthough Microsoft's reluctant to admit it, most of Excel's core features were completed nearly 10 years ago. So what has Microsoft been doing ever since? The answer, at least in part, is spending millions of dollars on usability tests, which are aimed at figuring out how easyor nota program is to use. In a typical usability test, Microsoft gathers a group of spreadsheet novices, watches them fumble around with the latest version of Excel, and then tweaks the program to make it more intuitive. After producing Excel 2003, Microsoft finally decided that minor tune-ups couldn't fix Excel's overly complex, button-heavy toolbars . So they decided to start over. The result is a radically redesigned user interface that actually makes sense. The centerpiece of this redesign is the super-toolbar called the ribbon . The RibbonEverything you'll ever want to do in Excelfrom picking a fancy background color to pulling information out of a databaseis packed into the ribbon. To accommodate all these buttons without becoming an over-stuffed turkey , the ribbon uses tabs . Excel starts out with seven tabs in the ribbon. When you click one of these tabs, you see a whole new collection of buttons (Figure I-3). Tip: Wondering what each tab holds? You'll take a tab tour in Chapter 1 in Section 1.3.1. The ribbon is the best thing to hit the Excel scene in years. The ribbon makes it easier to find features and remember where they are, because each feature is grouped into a logically related tab. Even better, once you find the button you need you can often find other, associated commands by looking at the section where the button is placed. In other words, the ribbon isn't just a convenient toolit's also a great way to explore Excel.
The ribbon is full of craftsmanship-like detail. For example, when you hover over a button, you don't see a paltry two- or three-word description in a yellow box. Instead, you see a friendly pop-up box with a complete mini-description and a shortcut that lets you trigger this command from the keyboard. Another nice detail is the way the way you can jump through the tabs at high velocity by positioning the mouse pointer over the ribbon and rolling the scroll wheel (if your mouse has a scroll wheel). And you're sure to notice the way the ribbon rearranges itself to fit the available space in the Excel window (see Figure I-4). Using the Ribbon with the KeyboardIf you're an unredeemed keyboard lover, you'll be happy to hear that you can trigger ribbon commands with the keyboard. The trick is using keyboard accelerators , a series of keystrokes that starts with the Alt key (the same key you used to use to get to a menu). When using a keyboard accelerator, you don't hold down all the keys at the same time. (As you'll soon see, some of these keystrokes contain so many letters that you'd be playing Finger Twister if you tried holding them all down simultaneously .) Instead, you hit the keys one after the other. The trick to using keyboard accelerators is to understand that once you hit the Alt key, there are two things you do, in this order:
Before you can trigger a specific command, you must select the correct tab (even if it's already displayed). Every accelerator requires at least two key presses after you hit the Alt key. You need even more if you need to dig through a submenu. By now, this whole process probably seems hopelessly impractical . Are you really expected to memorize dozens of different accelerator key combinations?
Fortunately, Excel is ready to help you out with a new feature called KeyTips. Here's how it works. Once you press the Alt key, letters magically appear over every tab in the ribbon. Once you hit a key to pick a tab, letters appear over every button in that tab (Figure I-5). You can then press the corresponding key to trigger the command (Figure I-6).
In some cases, a command might have two letters, in which case you need to press both keys, one after the other. (For example, the Find & Select button on the Home tab has the letters FD. To trigger it, press Alt, then H, then F, and then D.) Tip: You can back out of KeyTips mode without triggering a command at any time by pressing the Alt key again. There are other shortcut keys that don't use the ribbon. These are key combinations that start with the Ctrl key. For example, Ctrl+C copies highlighted text and Ctrl+S saves your work. Usually, you find out about a shortcut key by hovering over a command with the mouse. For example, hover over the Paste button in the ribbon's Home tab, and you see a tooltip that tells you its timesaving shortcut key is Ctrl+V. And if you've worked with a previous version of Excel, you'll find that Excel 2007 keeps most of the same shortcut keys. The Office MenuThere's still one small part of the traditional Excel menu system left in Excel 2007sort of. The traditional File menu that lets you open , save, and print files has been transformed into the Office menu . You get there using the Office button, which is the big round logo in the top-left corner of the window (Figure I-8). The Office menu is generally used for three things:
There's one menu quirk that takes a bit of getting used to. Some menu commands hide submenus that have more commands. Take for instance the Print command. From the Office menu, you can choose Print to fire off a quick printout of your work. But if you click the right-pointing arrow at the edge of the Print command (or if you hover over it for a moment), you see a submenu with more options, as shown in Figure I-9.
The Quick Access ToolbarKeen eyes will have noticed the tiny bit of screen real estate that sits on the right side of the Office button, just above the ribbon. It holds a series of tiny icons, like the toolbars in older versions of Excel (Figure I-10). This is the Quick Access toolbar (or QAT to Excel nerds).
If the Quick Access toolbar was nothing but a specialized shortcut for three commands, it wouldn't be worth the bother. However, the nifty thing about the Quick Access toolbar is that you can customize it. In other words, you can remove commands you don't use and add your own favorites. The Appendix shows how. Microsoft has deliberately kept the Quick Access toolbar very small. It's designed to provide a carefully controlled outlet for those customization urges. Even if you go wild stocking the Quick Access toolbar with your own commands, the rest of the ribbon remains unchanged. (And that means a coworker or spouse can still use your computer without suffering a migraine.) Excel's New FeaturesThe slick new ribbon is Excel's most dramatic change, but it's not the only new feature in Excel 2007. Other hot additions include:
Of course, this list is by no means complete. Excel 2007 is chock-full of refinements, tweaks, and tune-ups that make it easier to use than any previous version. You'll learn all the best tricks throughout this book. And if you've used a previous version of Excel, look for the "Nostalgia Corner" boxes, which tell how things have changed. About This BookDespite the many improvements in software over the years, one feature hasn't improved a bit: Microsoft's documentation. In fact, with Office 2007, you get no printed user guide at all. To learn about the thousands of features included in this software collection, Microsoft expects you to read the online help. Occasionally, the online help is actually helpful, like when you're looking for a quick description explaining a mysterious new function. On the other hand, if you're trying to learn how to, say, create an attractive chart, you'll find nothing better than terse and occasionally cryptic instructions. The purpose of this book, then, is to serve as the manual that should have accompanied Excel 2007. In these pages, you'll find step-by-step instructions and tips for using almost every Excel feature, including those you may not even know exist. About the OutlineThis book is divided into eight parts , each containing several chapters.
About |
|
Note: As you saw back in Figure I-4, the ribbon adapts itself to different screen sizes. Depending on the size of your Excel window, it's possible that the button you need to click won't include any text. Instead, it shows up as a small icon. In this situation, you can hover over the mystery button to see its name before deciding whether to click it.
Contextual tabs
There are some tabs that only appear in the ribbon when you're working on specific tasks. For example, when you create a chart, a Chart Tools section appears with three new tabs (see Figure I-12).
|
When dealing with contextual tabs, the instructions in this book always include the title of the tab section (it's Chart Tools in Figure I-12). Here's an example: "Choose Chart Tools Design 
Type Change Chart Type." Notice that the first part of this instruction includes the tab section title (Chart Tools) and the tab name (Design), separated by the character. That way, you cant mistake the Chart Tools Design tab for a Design tab in some other group of contextual tabs.
Drop-down buttons
From time-to-time you'll encounter buttons in the ribbon that have short menus attached to them. Depending on the button, this menu might appear as soon as you click the button, or it might appear only if you click the button's drop-down arrow, as shown in Figure I-13.
|
When dealing with this sort of button, the last step of the instructions in this book tells you what to choose from the drop-down menu. For example, say you're directed to "Home Clipboard Paste Paste Special." That tells you to select the Home tab, look for the Clipboard section, click the drop-down part of the Paste button (to reveal the menu with extra options), and then choose Paste Special from the menu.
Note: Be on the lookout for drop-down arrows in the ribbonthey're tricky at first. You need to click the arrow part of the button to see the full list of options. When you click the other part of the button, you don't see the list. Instead, Excel fires off the standard command (the one Excel thinks is the most common choice) or the command you used most recently.
Dialog box launchers
As powerful as the ribbon is, you can't do everything using the buttons it provides. Sometimes you need to use a good ol' fashioned dialog box. (A dialog box is a term used in the Windows world to describe a small window with a limited number of options. Usually, dialog boxes are designed for one task and they aren't resizable, although software companies like Microsoft break these rules all the time.)
There are two ways to get to a dialog box in Excel 2007. First, some ribbon buttons take you there straight away. For example, if you choose Home Clipboard Paste Paste Special, you always get a dialog box. Theres no way around it.
The second way to get to a dialog box is through something called a dialog box launcher , which is just a nerdified name for the tiny square-with-arrow icon that sometimes appears in the bottom-right corner of a section of the ribbon. The easiest way to learn how to spot a dialog box launcher is to look at Figure I-14.
|
When you click a dialog box launcher, the related dialog box appears. For example, click the dialog box launcher for the Font section and you get a full Font dialog box that lets you scroll through all the typefaces on your computer, choose the size and color, and so on.
In this book, there's no special code word that tells you to use a dialog box launcher. Instead, you'll see an instruction like this: "To see more font options, look at the Home Font section and click the dialog box launcher (the small icon in the bottom-right corner)." Now that you know what a dialog box launcher is, that makes perfect sense.
Ordinary menus
As you've already seen, the ribbon has taken the spotlight from traditional toolbars and menus. However, there are a couple of cases where you'll still use the familiar Windows menu. One example is when you use the Visual Basic editor (in Chapter 28). In this case, the arrows refer to menu levels. For example the instruction "Choose File Save means "Click the File menu heading. Then, on the File menu, click the Save command."
Another example is when you use the Office menu. Instructions for the Office menu look something like this: "Choose Office button Save." That translates to "Click the Office button in the top-left corner of the window to show the Office menu. Next, choose Save from the menu."
About Shortcut Keys
Every time you take your hand off the keyboard to move the mouse, you lose a few microseconds. That's why many experienced computer fans use keystroke combinations instead of toolbars and menus wherever possible. Ctrl+S, for example, is a keyboard shortcut that saves your current work in Excel (and most other programs).
When you see a shortcut like Ctrl+S in this book, it's telling you to hold down the Ctrl key, and, while it's down, press the letter S, and then release both keys. Similarly, the finger-tangling shortcut Ctrl+Alt+S means hold down Ctrl, and then press and hold Alt, and then press S (so that all three keys are held down at once).
About Clicking
This book gives you three kinds of instructions that require you to use your computer's mouse or laptop's trackpad. To click means to point the arrow cursor at something on the screen, and thenwithout moving the cursor at allpress and release the clicker button on the mouse (or laptop trackpad). To double-click , of course, means to click twice in rapid succession, again without moving the cursor at all. And to drag means to move the cursor while pressing the button continuously.
Examples
As you read this book, you'll see a number of examples that demonstrate Excel features and techniques for building good spreadsheets. Many of these examples are available as Excel workbook files in a separate download. Just surf to www.missingmanuals.com, click the link for this book, and then click the "Missing CD" to visit a page where you can download a Zip file that includes the examples, organized by chapter.
About MissingManuals.com
At www.missingmanuals.com, you'll find news, articles, and updates to the books in the Missing Manual series.
But the Web site also offers corrections and updates to this book (to see them, click the book's title, and then click Errata). In fact, you're invited and encouraged to submit such corrections and updates yourself. In an effort to keep the book as up-to-date and accurate as possible, each time we print more copies of this book, we'll make any confirmed corrections you've suggested. We'll also note such changes on the Web site, so that you can mark important corrections in your own copy of the book.
In the meantime, we'd love to hear your own suggestions for new books in the Missing Manual series. There's a place for that on the Web site, too, as well as a place to sign up for free email notification of new titles in the series.
Safari Enabled
When you see a Safari Enabled icon on the cover of your favorite technology book, that means the book is available online through the O'Reilly Network Safari Bookshelf.
Safari offers a solution that's better than e-books. It's a virtual library that lets you easily search thousands of top tech books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.
![Excel 2007[c] The Missing Manual](/icons/blank_book.jpg "Excel 2007[c] The Missing Manual"){kind=icon}