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.
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.
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.
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.
Despite the many improvements in software over the years, one feature hasn’t improved a bit: Microsoft’s documentation. In fact, with Office 2003, 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, these screens are 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 is to serve as the manual that should have accompanied Excel 2003 and Excel 2002. In these pages, you’ll find step-by-step instructions and tips for using Excel’s most popular features, including a few you may not even be aware of.
This book is based on Excel: The Missing Manual (O’Reilly). That book is a truly complete reference for the Excel 2002 and 2003, 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: The Missing Manual can be your trusted guide.
This book is divided into two sections, each containing several chapters.
Section One: 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.
Section Two: 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 jump-start the spreadsheet creation process by taking advantage of prebuilt, reusable spreadsheet templates. And you’ll learn about the wide range of different chart types available and when it makes sense to use each one.
At the end of this book, you’ll find an appendix that helps you use Excel’s sometimes useful but often infuriating online help.
Throughout this book, you’ll find sentences like this one: “Open the My Computer → C: → Windows folder.” That’s shorthand for a much longer instruction that directs you to open three nested folders in sequence, like this: “On your hard drive, there’s an icon called My Computer. Open that. Inside My Computer, there’s a folder for your C: drive. Open that. Inside your C: drive is your Windows folder. Open that.”
Similarly, this kind of arrow shorthand helps to simplify the business of choosing commands in menus, such as File → New → Window, as shown in Figure I-3. You’ll also see this arrow notation used to indicate which tab or pane of a dialog box you’re supposed to click: “Choose Tools → Options → General,” for example.
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 http://www.missingmanuals.com and click the link for this book to hit a page where you can download a zip file that includes the examples, organized by chapter.
At http://www.missingmanuals.com, you’ll find news, articles, and updates to the books in the Missing Manual and Power Hound 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 into your own copy of the book, if you like.
In the meantime, we’d love to hear your own suggestions for new books in the Missing Manual and Power Hound lines. 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 it’s 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.