Excel 2003 for Starters: The Missing Manual

Introduction

• What Excel Does

• About This Book

• About MissingManuals.com

Most people don't need much convincing to use Excel, Microsoft's premier spreadsheet program. That's because Excel comes pre-installed 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 for Starters: The Missing Manual fills the void, explaining everything from basic Excel concepts to time- and frustration-saving shortcuts.

This book steps you through not only how the program works, but 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'll win you the undying adoration of your coworkers, your family, and your friends. Or at least your accountant.

Note

Although this book is written with Microsoft's latest and greatest release in mind (Excel 2003), most of the concepts, features, tricks, and tips work equally well with Excel 2002 (the version of Excel released with Office XP). If you're using Excel 2002, this book will still be useful because each chapter clearly spells out any differences you'll encounter. The Mac version of Excel is covered in Office 2004 for Macintosh: The Missing Manual.

What Excel Does

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 you organize into lists or tables. Sure, you could use a database program (like Microsoft Access) to create and work with your data, but in a lot of cases, using a spreadsheet is just plain easier.

For example, Excel is perfect for creating things like:

• Smart address books. Say you've got a bunch of address books lying around. You can type in all the names and contact information from all of your address books into an Excel spreadsheet and then use Excel's tools to sort and group the information using whatever criteria you like. For example, you can easily find all the folks who live in Portland or all the folks named Clinton.

• Household expense logs. You can create columns for each of your major expenses (groceries, clothes, rent, utilities, and so on), type in your expenses as you incur them, and then tell Excel to calculate the total for each category by day, by month, by quarter, or by year. While you're at it, you can tell Excel which expenses are taxable and which aren't. Then, at tax time, you can quick-as-a-bunny create a report for your accountant that shows all your taxable expenses.

• Business reports. Whether you own your own business or work for a large company, you can use Excel to store, analyze, and report on customer data, sales figures, inventory—virtually anything. You can create knockout charts in Excel, too, which are handy when you're trying to explain a complicated report to your boss (or your customers).

These examples just scratch the surface, of course. Resourceful spreadsheet gurus use Excel to build everything from cross-country trip itineraries to lesson plans to scientific models. Here are three quick ways to tell whether you should use Excel for any given number-crunching project:

• You already store your data in lists. Got an address book? A list of invoices, accounts payable, or serial numbers you're trying to keep track of? Lists—which you organize in Excel as rows and columns—are perfect spreadsheet fodder.

• Your data includes numbers you know you'll eventually want to add up (or take the average of, or apply a percentage to, or perform some other mathematical magic on). The raison d'être of spreadsheets in general—and Excel in particular—is that they give ordinary mortals the power to perform sophisticated mathematical calculations on a bunch of numbers, quickly and easily.

• You have data you want to sort (or otherwise organize) in a lot of different ways. Click a button, and Excel sorts your text alphabetically or your numbers from lowest to highest (or highest to lowest). But you can also tell Excel to pick out certain data: for example, all the sales figures you entered two weeks ago Tuesday, all the customers whose last names start with C, or all the dollar amounts over a buck fifty.

Excel spreadsheets look like big grids. You type your data into individual cells organized into columns (A, B, C, D, E, and so on) and rows (1, 2, 3, 4, 5, and so on).

The spreadsheet in Figure I-1 shows a table of student grades. You'll notice it's organized as a list of nine students, each of whom has two test scores and an assignment grade. Using Excel formulas, it's easy to calculate the total grade for each student. But there's no need to stop there. With a little more effort, you can calculate averages, medians, and even determine which percentile each student falls into.

Figure I-1. A typical spreadsheet is really just a table.

The bottom line is that once you enter raw information, Excel's built-in smarts can help you compute all kinds of useful figures. For example, Figure I-2 shows a sophisticated business spreadsheet that's been configured to help identify hot-selling products.

In Figure I-2, you'll notice a summary of total sales for a company. The information is grouped by where the company's customers live and is further divided according to product category. Using a summary like this, you can quickly spot the product categories that make the most money and identify which items are popular in specific cities.

Figure I-2. Sophisticated spreadsheets are not hard to create.

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 activated by the click of a button) 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 three-dimensional pie charts to more exotic scatter graphs. The spreadsheets and charts you create with Excel can be as simple or as sophisticated as you want them to be.