What You Can Do with Excel
Excel’s New Face
Excel’s New Features
About This Book
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 for Starters: The Missing Manual fills that void, explaining everything from basic Excel concepts to time- and frustration-saving shortcuts.
This book teaches you not only how the program works, but it helps you steer clear of obscure options that aren’t worth the trouble to learn. Meanwhile, you’ll learn how to home in on the hidden gems that’ll win you the undying adoration of your coworkers, family, and friends—or 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 for Starters: The Missing Manual. The Mac version of Excel is covered in Office 2004 for Macintosh: The Missing Manual.
Excel 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.
Figure I-1. This spreadsheet lists nine students, each of whom has two test scores and an assignment grade. Using Excel formulas, it’s easy to calculate the final grade for each student.
Some common spreadsheets include:
Business documents like financial statements, invoices, expense reports, and earnings statements.
Personal documents like weekly budgets, catalogs of your Star Wars action figures, exercise logs, and shopping lists.
Scientific data like experimental observations, models, and medical charts.
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.
Note
Keen eyes will notice that Figure I-1 doesn’t 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.2.
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 check your spelling. 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. Excel can be as simple or as sophisticated as you want it to be.
Although 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 easy—or not—a 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.
Everything you’ll ever want to do in Excel—from picking a fancy background color to prepping your spreadsheet for printing—is 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-2).
Tip
Wondering what each tab holds? You’ll take a tab tour in Chapter 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 tool—it’s also a great way to explore Excel.
Figure I-2. When you launch Excel you start at the Home tab, but here’s what happens when you click the Page Layout tab. Now, you have a slew of options for tasks like adjusting paper size and making a decent printout. The buttons in a tab are grouped into smaller boxes for clearer organization.
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-3).
If 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.
Figure I-3. Top: A large Excel window gives you plenty of room to play. The ribbon uses the space effectively, making the most important buttons bigger. Bottom: When you shrink the Excel window, the ribbon rearranges its buttons and makes some smaller (by shrinking the button’s icon or leaving out the title). Shrink small enough, and you might run out of space for a section altogether. In that case, you get a single button (like the Number, Styles, and Cells sections in this example) for an entire section. Click this button and the missing commands appear in a drop-down panel.
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:
Pick the ribbon tab you want.
Choose a command in that tab.
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 (Figure I-4). Once you hit a key to pick a tab, letters appear over every button in that tab. You can then press the corresponding key to trigger the command (Figure I-5).
Figure I-4. When you press Alt, Excel helps you out with KeyTips next to every tab. If you follow up with M (for the Formulas tab), you’ll see letters next to every command in that tab, as shown in Figure I-5.
Figure I-5. You can now follow up with F to trigger the Insert Function button, U to get to the Auto-Sum feature, and so on. Don’t bother trying to match letters with tab or button names—there are so many features packed into the ribbon that in many cases the letters don’t mean anything at all.
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.
Figure I-6. By pressing Alt+E, you’ve triggered the “imaginary” Edit menu from Excel 2003, and earlier versions. You can’t actually see it (because in Excel 2007 this menu doesn’t exist). However, the tooltip lets you know that Excel is paying attention. You can now complete your action by pressing the next key for the menu command you’re nostalgic for.
There’s still one small part of the traditional Excel menu system left in Excel 2007—sort 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-7).
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-8.
Figure I-7. The Office menu is bigger and easier to read than a traditional menu. It also has a list of the documents you used recently on the right side. (You’ll learn about this handy feature in Section 1.5.1.)
Keen 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 (Figure I-9). It holds a series of tiny icons, like the toolbars in older versions of Excel. 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 you 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.)
The slick new ribbon is Excel’s most dramatic change, but it’s not the only new feature in Excel 2007. Other hot additions include:
Fewer limits. Excel worksheets can now be bigger, formulas can be more complex, and cells can hold way more text. Although 99.87 percent of Excel fans never ran into any of these limits in previous versions, it’s nice to know that the Excel engine continues to get more powerful.
Faster speeds. One of the newest pieces of computing hardware is a dual core CPU. (The CPU is the brain of any computer.) A dual core CPU can perform two tasks at once, but it performs best with software that knows how to take advantage of the way it works. Excel 2007 knows all about dual core CPUs, which means intense calculations are even faster on these computers.
Better-looking charts. Excel charts have always been intelligent, but they’ve never made good eye candy. Excel 2007 shakes things up with a whole new graphics engine that lets you add fantastic looking charts to your spreadsheets.
Formula AutoComplete. The latest in a whole bunch of auto-do-something features, formula AutoComplete just might be the most helpful innovation yet. It prompts you with possible values when you type in complex formulas.
Tables. When Microsoft created Excel 2003, they added a wildly popular list feature that helped people manage lists of information. In Excel 2007, lists morph into tables and get even more powerful.
Save-as-PDF. A PDF file is Adobe’s popular electronic document format that lets you share your work with other people, without losing any of your formatting (and without letting them change any of your numbers). Due to legal headaches, this feature didn’t quite make it into the Excel 2007 installation, but it’s available as a free download from Microsoft. Chapter 1 has the details.
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.
Despite 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 Excel’s most popular features, including those you may not even know exist.
Note
This book is based on Excel 2007: The Missing Manual (O’Reilly). That book is a truly complete reference for Excel 2007, covering every feature, including geeky stuff like XML, VBA, ERROR.TYPE( ) functions, and other things you’ll probably never encounter—or even want to. But if you get really deep into Excel and want to learn more, Excel 2007: The Missing Manual can be your trusted guide.
This book is divided into two parts, each containing several chapters.
Part 1: Worksheet Basics. In this part, you’ll get acquainted with Excel’s interface and learn the basic techniques for creating spreadsheets and entering and organizing data. You’ll also learn how to format your work to make it more presentable and how to create slick printouts.
Part 2: Worksheet Power. This part introduces you to Excel’s most important feature—formulas. You’ll learn how to perform calculations and create formulas using Excel’s built-in functions. You’ll also learn how to search, sort, and filter large amounts of information using tables. And to top things off, you’ll learn about the wide range of different chart types available and when it makes sense to use each one.
Throughout this book, you’ll find sentences like this one: “Choose Insert → Illustrations → Picture.” This a shorthand way of telling you how to find a feature in the Excel ribbon. It translates to the following instructions: “Click the Insert tab of the toolbar. On the tab, look for the Illustrations section. In the Illustrations box, click the Picture button.” Figure I-10 shows the button you want.
Figure I-10. In this book, arrow notations help to simplify ribbon commands. For example, “Choose Insert → Illustrations → Picture” leads to the highlighted button shown here.
Note
As you saw back in Figure I-3, 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.
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-11).
Figure I-11. Excel doesn’t bother to show these three tabs unless you’re working on a chart, because it’s frustrating to look at a bunch of buttons you can’t use. This sort of tab, which appears only when needed, is called a contextual tab.
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-11). 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 can’t mistake the Chart Tools | Design tab for a Design tab in some other group of contextual tabs.
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-12.
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.
Figure I-12. Excel gives you several options for pasting text from the clipboard. Click the top part of the Paste button to perform a plain-vanilla paste (with all the standard settings), or click the bottom part to see the menu of choices shown here.
Note
Be on the lookout for drop-down arrows in the ribbon—they’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.
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. There’s 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-13.
Figure I-13. As you can see here, the Clipboard, Font, Alignment, and Number sections all have dialog box launchers. The Styles, Cells, and Editing sections don’t.
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.
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).
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 then—without moving the cursor at all—press 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.
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.
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.
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.
Get Excel 2007 for Starters: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
