O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Introduction

Most people don't need much convincing to use Excel, Microsoft's premier spreadsheet software. Its overwhelming popularity, especially in the business world, makes it the obvious choice for millions of number crunchers. But despite its wide use, few people know where to find Excel's most impressive features or why they'd want to use them in the first place. Excel 2010: The Missing Manual fills that void, explaining everything from basic Excel concepts to the fancy tricks of the trade.

This book not only teaches you how Excel works, but 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 smarter—Democrats 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 will win you the undying adoration of your coworkers, your family, and your friends—or at least your accountant.

Note

This book is written with Microsoft's latest and greatest release in mind: Excel 2010. This book isn't the best choice if you're using an earlier version of Excel, because Microsoft is continually changing Excel's user interface (the "look and feel" of the program). To get the right instructions, look for a previous edition of this book, such as Excel 2007: The Missing Manual or Excel 2003: The Missing Manual.

What You Can Do with Excel

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 1 shows an example.

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. And with a little more effort, you can calculate averages and medians, and determine each student's percentile. Chapter 8 looks at how to perform these calculations.

Figure 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. And with a little more effort, you can calculate averages and medians, and determine each student's percentile. Chapter 8 looks at how to perform these calculations.

Note

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:

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

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 2 shows a sophisticated spreadsheet that has been configured to help identify hot-selling product categories.

Note

Keen eyes will notice that neither of these examples (Figures Figure 1 and Figure 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 on The Tabs of the Ribbon.

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

This spreadsheet summarizes a company's total sales. The information is grouped based on where the company's customers live, and it's further divided according to product category. Summaries like these can help you spot profitable product categories and identify items popular in specific cities. This advanced example uses pivot tables, which are described in Chapter 22.

Figure 2. This spreadsheet summarizes a company's total sales. The information is grouped based on where the company's customers live, and it's further divided according to product category. Summaries like these can help you spot profitable product categories and identify items popular in specific cities. This advanced example uses pivot tables, which are described in Chapter 22.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required