Excel 2007 for Starters: 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 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, inventoryvirtually 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? Listswhich you organize in Excel as rows and columnsare 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 generaland Excel in particularis 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.

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.

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.