15,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
- Veröffentlichungsjahr: 2011
Every time you turn around, you run into Excel. It's on yourPC at work. It's on your PC at home. You get Excel files fromyour boss. Wouldn't you like to understand this powerfulMicrosoft Office spreadsheet program, once and for all? Now, youcan crunch financial data, add sparkle to presentations, convertstatic lists of numbers into impressive charts, and discover whatall the shouting's about regarding databases, formulas, andcells. You may even decide that getting organized with a goodspreadsheet is downright useful and fun! Flip open Excel 2003 For Dummies, and you'llquickly start getting the basics of Excel in plain English. Writtenfor the rest of us, this down-to-earth book gently shows you howto: * Create a spreadsheet from scratch * Apply the basics of formatting cells * Take on database forms--even add records--andprevail * Get organized and stay that way * Save worksheets as Web pages for your company intranet In a clear and easy-to-understand style, veteran softwaretrainer and technology writer Greg Harvey explains the basics ofworksheets and workbooks, how to enter data and workwith formulas, and how to print your masterpieces. Whenyou're feeling very bold, he'll have you addingcomments and pictures, saving files with security protection, andlearning to zip between multiple worksheets in a workbook withease. And there's much more: * Clip and save the Top Ten Beginner Basics of Excel 2003 * Pay heed to the Top Ten Commandments of Excel 2003 * Impress your colleagues by creating a company org chart * Re-open those documents and add or edit new data withaplomb * Move between these sheets without trouble * Decipher and take charge of helpful tools and commands such asSort, Filter, Format Cells, and PivotTable You'll finally be able to stop pestering the Excel expertsin your office. Become your own expert with the friendly anddown-to-earth practical instruction you'll find in Excel2003 For Dummies.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 585
Ähnliche
Excel 2003 For Dummies
by Greg Harvey
Excel 2003 For Dummies®
Published by Wiley Publishing, Inc. 111 River Street Hoboken, NJ 07030 www.wiley.com
Copyright © 2003 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8700. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, e-mail: perm [email protected].
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.
For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.
Library of Congress Control Number: 2003101884
ISBN: 0-7645-3756-3
Manufactured in the United States of America
10 9 8 7 6 5
1B/RX/QU/QW
About the Author
Greg Harvey is a product of the great American Midwest, born in the Chicagoland area in 1949 (thus his saying “I’m only as old as China” — Red China, that is) in the dark ages of the Cold War, before the age of McDonald’s, MTV, and, certainly, personal computers. On the shores of Lake Michigan, he learned his letters and numbers and showed great promise in the world of academia (quickly achieving Red Bird reading status after being put back as a Yellow Bird due to an unforeseen bout of chicken pox at the start of the school year). After earning many gold stars along with a few red, he graduated from Roosevelt School (named for Teddy, not that socialist Delano) in 1963.
During his stint at Thornridge High School in the perfectly boring Chicago suburb of Dolton, Illinois (named for Tom Dolton, the gunslinger?), he found great solace in Motown music (thanks, Phil!) and the drama department (to this day, he can recite every line from the play Auntie Mame, verbatim). Bored with what passed for academic studies, he went through high school in three years. Looking back on these formative years, Greg was sure thankful for the great tunes and Auntie’s philosophy, “Life’s a banquet, kid, and some poor suckers are starving.”
In 1966 (ah, the Sixties), he entered the University of Illinois at Urbana, Illinois, where he was greatly influenced by such deep philosophers as Abbie Hoffman and Mahatma Gandhi. In the summer of 1968, he purchased his first pair of handmade sandals (from Glen, a hippie sandal maker who’d just returned from the Summer of Love in San Francisco).
During his college years, he became quite political. He holds the distinction of being one of a handful of men and women to attend the “camp-out” protest against women’s dorm curfews (back then, not only were dorms not sexually integrated, but also, women were locked up at 11 p.m. on weeknights, 1 a.m. on weekends) and the last one to leave after all the others went back to their dorms. During his subsequent college years, he became a regular at the Red Herring coffeehouse, the veritable den of SDS activity on campus.
In addition to antiwar protests, Greg attended various and sundry classes in the Liberal Arts (such as they were in the last half of the 20th century). In the end, he took a major in Classical Studies (ancient Greek and Latin) and a split minor in American History and French (Greg showed a facility for foreign language, probably stemming from the fact that he’s always had a big mouth). In the course of his classical studies, he was introduced to his first computer-based training, learning basic Latin with a CAI program called what else but PLATO!
At the beginning of 1971 (January 12, in fact), Greg migrated west from Chicago to San Francisco (with flowers in his hair). Deciding it was high time to get a skill so that he could find a real job, he enrolled in the Drafting and Design program at Laney College in Oakland. After that, he spent nine years working over a hot drafting table, drawing (by hand, mind you) orthographic and perspective plans for various and sundry engineering projects. During his last engineering gig, he worked with a proprietary CAD software package developed by Bechtel Engineering that not only generated the drawings, but also kept track of the materials actually needed to create the stuff.
In 1981, following his engineering career, Greg went back to school at San Francisco State University, this time to earn his secondary teaching credential. Upon completion of his teacher training, he bought one of the very first IBM personal computers (with 16K and a single 160K floppy disk!) to help with lesson preparation and student bookkeeping. He still vividly remembers poring over the premiere issue of PC World magazine for every piece of information that could teach him how to make peace with his blankety, blankety personal computer.
Instead of landing a teaching job at the high school or community college (because there weren’t any at the time), Greg got a job with a small software outfit, ITM, that was creating an online database of software information (well ahead of its time). As part of his duties, Greg reviewed new software programs (like Microsoft Word 1.0 and Lotus 1-2-3 Release 1) and wrote articles for business users.
After being laid off from this job right after the Christmas party in 1983 (the first of several layoffs from high-tech startups), Greg wrote his first computer book on word processing software for Hayden Books (as a result of a proposal he helped to write while still employed full-time at ITM). After that, Greg worked in various software evaluation and training jobs. After a few more high-tech software testing and evaluation jobs in Silicon Valley, Greg turned to software training to get, as he put it, “the perspective of the poor schmo at the end of the terminal.” During the next three years, Greg trained a whole plethora of software programs to business users of all skill levels for several major independent software-training companies in the San Francisco Bay Area.
In the fall of 1986, he hooked up with Sybex, a local computer book publisher, for which he wrote his second computer training book, Mastering SuperCalc. And the rest, as they say, is history. To date, Greg is the author of more than 30 books on using computer software, with the titles created under the For Dummies aegis for Hungry Minds, Inc., being among his all-time favorites.
In mid-1993, Greg started a new multimedia publishing venture called Mind over Media. As a multimedia developer, he hopes to enliven his future computer books by making them into true interactive learning experiences that will vastly enrich and improve the training of users of all skill levels. You can send him e-mail at [email protected] and visit his Web site at www.mindovermedia.com.
In 1999, Greg began graduate school at the California Institute of Integral Studies (CIIS) in San Francisco. In the summer of 2000, he received his Master’s degree in Philosophy and Religion in the area of Asian and Comparative Studies. In the fall of that year, he entered the Ph.D. program at CIIS, where he is now concentrating on the study of the classical Chinese and Tibetan languages and the study of Chinese philosophy and religion.
Dedication
An Erucolindo melindonya
Author’s Acknowledgments
Let me take this opportunity to thank all the people, both at Wiley Publishing, Inc., and at Mind over Media, Inc., whose dedication and talent combined to get this book out and into your hands in such great shape.
At Wiley Publishing, Inc., I want to thank Tiffany Franklin for her help in getting this project under way; Christine Berman as project editor; and Diana Conover as copy editor for making sure that the project stayed on course and made it into production so that all the talented folks on the production team could create this great final product.
At Mind over Media, I want to thank Christopher Aiken for his review of the updated manuscript and invaluable input and suggestions on how to present and handle new features.
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development
Project Editor: Christine Berman
Acquisitions Editor: Tiffany Franklin
Copy Editor: Diana R. Conover
Technical Editor: Tom Farrington
Editorial Manager: Leah Cameron
Media Development Manager: Laura VanWinkle
Media Development Supervisor: Richard Graves
Editorial Assistant: Amanda Foxworth
Cartoons: Rich Tennant (www.the5thwave.com)
Production
Project Coordinator: Kristie Rees
Layout and Graphics: Joyce Haughey, LeAndra Hosier,Stephanie D. Jumper, Michael Kruzil, Lynsey Osborn, Jacque Schneider, Melanee Wolven
Proofreaders: Carl W. Pierce, Robert Springer, TECHBOOKS Production Services
Indexer: Maro Riofrancos
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Contents
Title
Introduction
About This Book
How to Use This Book
What You Can Safely Ignore
Foolish Assumptions
How This Book Is Organized
Conventions Used in This Book
Where to Go from Here
Part I : Getting In on the Ground Floor
Chapter 1: What Is All This Stuff?
What in the World Would I Do with Excel?
Getting the Darn Thing Started
Mousing Around
So What Do All These Buttons Do?
You Gotta Get Me out of This Cell!
Ordering Directly from the Menus
Digging Those Dialog Boxes
Ogling the Online Help
When It’s Time to Make Your Exit
Chapter 2: Creating a Spreadsheet from Scratch
So What Ya Gonna Put in That New Workbook of Yours?
Doing the Data-Entry Thing
It Takes All Types
Fixing Up Those Data Entry Flub-Ups
Taking the Drudgery out of Data Entry
How to Make Your Formulas Function Even Better
Making Sure That the Data Is Safe and Sound
Document Recovery to the Rescue
Part II : Editing Without Tears
Chapter 3: Making It All Look Pretty
Choosing a Select Group of Cells
Trimming Your Tables with AutoFormat
Festooning Your Cells with the Formatting Toolbar
Using the Format Cells Dialog Box
Calibrating Columns
Futzing with the Fonts
Altering the Alignment
Fooling Around with the Format Painter
Chapter 4: Going Through Changes
Opening the Darned Thing Up for Editing
Much Ado about Undo
Doing the Old Drag-and-Drop Thing
Formulas on AutoFill
Let’s Be Clear about Deleting Stuff
Kindly Step Aside . . .
Stamping Out Your Spelling Errors
Stamping Out Errors with Text to Speech
Chapter 5: Printing the Masterpiece
Starting the Show with Print Preview
The Page Stops Here . . .
My Page Was Set Up!
When Ya Gonna Give Your Page a Break?
Letting Your Formulas All Hang Out
Part III : Getting Organized and Staying That Way
Chapter 6: Oh, What a Tangled Worksheet We Weave!
Zeroing In with Zoom
Splitting the Difference
Fixed Headings Courtesy of Freeze Panes
Electronic Sticky Notes
The Cell Name Game
Seek and Ye Shall Find . . .
You Can Be Replaced!
Do Your Research
You Can Be So Calculating
Putting on the Protection
Chapter 7: Maintaining Multiple Worksheets
Juggling Worksheets
Don’t Short-Sheet Me!
Opening Windows on Your Worksheets
Comparing Two Worksheets Side by Side
Passing Sheets in the Night
To Sum Up . . .
Part IV : Life Beyond the Spreadsheet
Chapter 8: The Simple Art of Making Charts
Conjuring Up Charts with the Chart Wizard
Picture This!
Working with WordArt
Ordering Up Organization Charts
One on Top of the Other . . .
Nixing the Graphics
Printing Charts Only
Chapter 9: How to Face a Database
Designing the Data Form
Sorting It All Out
You AutoFilter the Database to See the Records You Want
Chapter 10: Of Hyperlinks and Web Pages
Adding Hyperlinks to a Worksheet
Spreadsheets on the Web?
Sending Worksheets via E-Mail
Part V : The Part of Tens
Chapter 11: Top Ten Features in Excel 2003
Chapter 12: Top Ten Beginner Basics
Chapter 13: The Ten Commandments of Excel 2003
Introduction
W elcome to Excel 2003 For Dummies, the definitive work on Excel 2003 for those of you who have no intention of ever becoming a spreadsheet guru. In this book, you’ll find all the information that you need to keep your head above water as you accomplish the everyday tasks that people do with Excel. The intention of this book is to keep things simple and not bore you with a lot of technical details that you neither need nor care anything about. As much as possible, in this book I attempt to cut to the chase by telling you in plain terms just what it is that you need to do to accomplish a task using Excel.
Excel 2003 For Dummies covers all the fundamental techniques that you need to know in order to create, edit, format, and print your own worksheets. In addition to showing you around the worksheet, this book also exposes you to the basics of charting, creating databases, and converting spreadsheets into Web pages. Keep in mind, though, that this book just touches on the easiest ways to get a few things done with these features — I make no attempt to cover charting, databases, or worksheet Web pages in a definitive way. This book concentrates mainly on spreadsheets because spreadsheets are what most people need to create with Excel.
About This Book
This book isn’t meant to be read cover to cover. Although its chapters are loosely organized in a logical order (progressing as you might when studying Excel in a classroom situation), each topic covered in a chapter is really meant to stand on its own.
Each discussion of a topic briefly addresses the question of what a particular feature is good for before launching into how to use it. In Excel, as with most other sophisticated programs, you usually have more than one way to do a task. For the sake of your sanity, I have purposely limited the choices by usually giving you only the most efficient ways to do a particular task. Later on, if you’re so tempted, you can experiment with alternative ways of doing a task. For now, just concentrate on performing the task as I describe.
As much as possible, I’ve tried to make it unnecessary for you to remember anything covered in another section of the book. From time to time, however, you will come across a cross-reference to another section or chapter in the book. For the most part, such cross-references are meant to help you get more complete information on a subject, should you have the time and interest. If you have neither, no problem; just ignore the cross-references as if they never existed.
How to Use This Book
This book is like a reference in which you start out by looking up the topic you need information about (either in the Table of Contents or the Index), and then you refer directly to the section of interest. I explain most topics conversationally (as though you were sitting in the back of a classroom where you can safely nap). Sometimes, however, my regiment-commander mentality takes over, and I list the steps you need to take to accomplish a particular task in a particular section.
What You Can Safely Ignore
When you come across a section that contains the steps you take to get something done, you can safely ignore all text accompanying the steps (the text that isn’t in bold) if you have neither the time nor the inclination to wade through more material.
Whenever possible, I have also tried to separate background or footnote-type information from the essential facts by exiling this kind of junk to a sidebar (look for blocks of text on a gray background). These sections are often flagged with icons that let you know what type of information you will encounter there. You can easily disregard text marked this way. (I’ll scoop you on the icons I use in this book a little later.)
Foolish Assumptions
I’m going to make only one assumption about you (let’s see how close I get): You have access to a PC (at least some of the time) that has Windows XP or Windows 2000 and Excel 2003 installed on it. However, having said that, I make no assumption that you’ve ever launched Excel, let alone done anything with it.
This book is written expressly for users of Excel 2003. If you have a previous version of Excel for Windows (like Excel 2002) running under a previous version of Windows (like 98 or ME [Millennium Edition]), please put this book down and instead pick up a copy of Excel 2002 For Dummies, published by Wiley Publishing, Inc.
If you happen to be using Excel 2002 for Windows (either because you just haven’t seen the need for upgrading yet, you’re just too cheap to purchase the upgrade, or after installing Windows XP, you simply don’t have enough disk space left for Excel 2003), you can use this book for the most part to figure out Excel 2002 for Windows. Just keep in mind that are some features such as the new Home and Research Task panes and Side by Side Compare With that simply don’t exist in Excel 2002 and others such as the Help and File Search features that work somewhat differently in Excel 2002 from the way they’re described in this edition.
How This Book Is Organized
This book is organized in five parts (which gives you a chance to see at least five of those great Rich Tennant cartoons!). Each part contains two or more chapters (to keep the editors happy) that more or less go together (to keep you happy). Each chapter is further divided into loosely related sections that cover the basics of the topic at hand. You should not, however, get too hung up about following along with the structure of the book; ultimately, it doesn’t matter at all if you find out how to edit the worksheet before you learn how to format it, or if you figure out printing before you learn editing. The important thing is that you find the information — and understand it when you find it — when you need to perform a particular task.
In case you’re interested, a synopsis of what you find in each part follows.
Part I: Getting In on the Ground Floor
As the name implies, in this part I cover such fundamentals as how to start the program, identify the parts of the screen, enter information in the worksheet, save a document, and so on. If you’re starting with absolutely no background in using spreadsheets, you definitely want to glance at the information in Chapter 1 to discover what this program is good for before you move on to how to create new worksheets in Chapter 2.
Part II: Editing Without Tears
In this part, I show how to edit spreadsheets to make them look good, as well as how to make major editing changes to them without courting disas-ter. Peruse Chapter 3 when you need information on formatting the data to improve the way it appears in the worksheet. See Chapter 4 for rearranging, deleting, or inserting new information in the worksheet. And read Chapter 5 for the skinny on printing out your finished product.
Part III: Getting Organized and Staying That Way
Here I give you all kinds of information on how to stay on top of the data that you’ve entered into your spreadsheets. Chapter 6 is full of good ideas on how to keep track of and organize the data in a single worksheet. Chapter 7 gives you the ins and outs of working with data in different worksheets in the same workbook and gives you information on transferring data between the sheets of different workbooks.
Part IV: Life Beyond the Spreadsheet
In Part IV, I explore some of the other aspects of Excel besides the spreadsheet. In Chapter 8, you find out just how ridiculously easy it is to create a chart using the data in a worksheet. In Chapter 9, you discover just how useful Excel’s database capabilities can be when you have to track and organize a large amount of information. In Chapter 10, you find out about adding hyperlinks to jump to new places in a worksheet, to new documents, and even to Web pages, as well as how to convert worksheets into both static and dynamic (interactive) Web pages for your company’s Web site(s).
Part V: The Part of Tens
As is the tradition in these For Dummies books, the last part contains lists of the top ten most useful and useless facts, tips, and suggestions.
Conventions Used in This Book
The following information gives you the lowdown on how things look in this book — publishers call these items the book’s conventions (no campaigning, flag-waving, name-calling, or finger-pointing is involved, however).
Keyboard and mouse
Excel 2003 is a sophisticated program with lots of fancy boxes, plenty of bars, and more menus than you can count. In Chapter 1, I explain all about these features and how to use them.
Although you use the mouse and keyboard shortcut keys to move your way in, out, and around the Excel worksheet, you do have to take some time to enter the data so that you can eventually mouse around with it. Therefore, this book occasionally encourages you to type something specific into a specific cell in the worksheet. Of course, you can always choose not to follow the instructions. When I tell you to enter a specific function, the part you should type generally appears in bold type. For example, =SUM(A2:B2) means that you should type exactly what you see: an equal sign, the word SUM, a left parenthesis, the text A2:B2 (complete with a colon between the letter-number combos), and a right parenthesis. You then, of course, have to press Enter to make the entry stick.
When Excel isn’t talking to you by popping up message boxes, it displays highly informative messages in the status bar at the bottom of the screen. This book renders messages that you see on-screen like this:
=SUM(A2:B2)
Occasionally I may ask you to press a key combination in order to perform a certain task. Key combinations are written like this: Ctrl+S. That plus sign in between means that you should hold down both the Ctrl key and the S key at the same time before releasing them. This (sometimes cruel) type of finger aerobics may take some practice.
When you need to wade through one or more menus to get to the selection you want, I sometimes (though not often, mind you) use command arrows to lead you from the initial menu, to the submenu, and so on, to the command you ultimately want. For example, if you need to first open the File menu to get to the Open command, I may write that instruction like this: Choose File⇒ Open.
Finally, if you’re really observant, you may notice a discrepancy between the capitalization of the names of dialog box options (such as headings, radio buttons, and check boxes) as they appear in the book and how they actually appear in Excel on your computer screen. Microsoft has this habit of capitalizing only the first letter of the names of long dialog box options. For example, on the Sheet tab of the Page Setup dialog box, you’ll see a check box option called
Row and column headings
In Chapter 5 where I discuss the use of this option, this heading appears in the text as
Row and Column Headings
I intentionally use this convention of capitalizing the initial letters of all the main words of a dialog box option to help you differentiate the name of the option from the rest of the text describing its use.
Special icons
The following icons are strategically placed in the margins to point out stuff you may or may not want to read.
This icon alerts you to nerdy discussions that you may well want to skip (or read when no one else is around).
This icon alerts you to shortcuts or other valuable hints related to the topic at hand.
This icon alerts you to information to keep in mind if you want to meet with a modicum of success.
This icon alerts you to information to keep in mind if you want to avert complete disaster.
Where to Go from Here
If you’ve never worked with a computer spreadsheet, I suggest that, right after getting your chuckles with the cartoons, you go first to Chapter 1 and find out what you’re dealing with. If you’re already familiar with the ins and outs of electronic spreadsheets but don’t know anything about creating worksheets with Excel, jump into Chapter 2, where you find out how to get started entering data and formulas. Then, as specific needs arise (such as, “How do I copy a formula?” or “How do I print just a particular section of my worksheet?”), you can go to the Table of Contents or the Index to find the appropriate section and go right to that section for answers.
Part I
Getting In on the Ground Floor
In this part . . .
O ne look at the Excel 2003 screen (with all its boxes, buttons, and tabs), and you realize how much stuff is going on here. This is no doubt because of the addition of the Windows taskbar to the (already rather over-designed) Excel 2003 screen and then throwing in the Microsoft Office Shortcut Bar to boot! Well, not to worry: In Chapter 1, I break down the parts of the Excel 2003 screen and make some sense out of the rash of icons, buttons, and boxes that you’re going to be facing day after day after day.
Of course, it’s not enough to just sit back and have someone like me explain what’s what on the screen. To get any good out of Excel, you’ve got to start learning how to use all these bells and whistles (or buttons and boxes, in this case). That’s where Chapter 2 comes in, giving you the lowdown on how to use some of the screen’s more prominent buttons and boxes to get your spreadsheet data entered. From this humble beginning, it’s a quick trip to total screen mastery.
Chapter 1
What Is All This Stuff?
In This Chapter
Deciding how you can use Excel 2003
Looking at cell basics
Starting Excel 2003 from the Start menu or with a desktop shortcut
Making sense of the Excel 2003 screen
Getting the lowdown on the Excel 2003 toolbars
Surfing an Excel 2003 workbook
Selecting commands from the pull-down menus
Selecting commands from the shortcut menus
Getting some help from the Answer Wizard
Getting the heck out of Excel 2003
J ust because electronic spreadsheets like Excel 2003 have become almost as commonplace on today’s personal computers as word processors and games doesn’t mean that they’re either well understood or well used. In fact, I encounter scads of users, even those who are reasonably well versed in the art of writing and editing in Microsoft Word, who have little or no idea of what they could or should do with Excel.
This lack of awareness is really a shame — especially in this day and age when Office 11 seems to be the only software found on the majority of machines (probably because, together, Windows XP or 2000 and Office 11 hog so much hard drive space that no room is left to install anybody else’s software). If you’re one of the folks who has Office 11 on your computer but doesn’t know a spreadsheet from a bedsheet, this means that Excel 2003 is just sitting there, taking up a lot of space. Well, it’s high time to change all that.
What in the World Would I Do with Excel?
Excel is a great organizer for all types of data, be they numeric, textual, or otherwise. Because the program has loads of built-in calculating capabilities, most people turn to Excel when they need to set up financial spreadsheets. These spreadsheets tend to be filled to the gills with formulas for computing stuff, such as total sales, net profits and losses, growth percentages, and those sorts of things.
Also popular are Excel’s charting capabilities that enable you to create all types of charts and graphs from the numbers that you crunch in your financial worksheets. Excel makes it really easy to turn columns and rows of boring, black-and-white numbers into colorful and snappy charts and graphs. You can then use these charts to add some pizzazz to written reports (like those created with Word 2003) or to punch up overheads used in formal business presentations (like those created with Microsoft PowerPoint).
Now, even if your job doesn’t involve creating worksheets with a lot of fancy-Dan financial calculations or lah-di-dah charts, you probably have plenty of things for which you could and should be using Excel. For instance, you may have to keep lists of information or maybe even put together tables of information for your job. Excel is a great list keeper (even though we tend to refer to such lists as data lists or databases in Excel) and one heck of a table maker. Therefore, you can use Excel anytime that you need to keep track of products that you sell, clients who you service, employees who you oversee, or you name it.
Little boxes, little boxes . . .
There’s a really good reason why Excel is such a whiz at doing financial calculations by formula and keeping lists and tables of information orga-nized. Look at any blank Excel worksheet (the one in Figure 1-1 will do fine) and just what do you see? Boxes, lots of little boxes, that’s what! These little boxes (you can find millions of them in each worksheet that you encounter) are called cells in spreadsheet jargon. And each piece of information (such as a name, address, monthly sales figure, or even your Aunt Sally’s birth date) goes into its own box (cell) in the worksheet that you’re building.
If you’re used to word processing, this idea of entering different types of information in little, bitty cells can be somewhat strange to get used to. If you’re thinking in word-processing terms, you need to think of the process of building an Excel worksheet as being more like setting up a table of information in a Word document rather than writing a letter or report.
Figure 1-1: Little boxes, little boxes, and they all look just the same!
Send it to my cell address
As you can see in Figure 1-1, the Excel worksheet contains a frame used to label the columns and rows. Columns are given letters of the alphabet, and the rows are numbered. The columns and rows must be labeled because the Excel worksheet is humongous. (Figure 1-1 shows only a tiny part of the total worksheet.) The column and row labels act like street signs in a city — they can help you identify your current location, even if they don’t prevent you from becoming lost.
Why spreadsheet programs produce nothing but worksheets
Spreadsheet programs like Excel 2003 refer to their electronic sheets as worksheets rather than spreadsheets. And, although it is perfectly acceptable (even preferable) to call one of its electronic sheets a worksheet, you never, never refer to Excel as a worksheet program — it’s always called a spreadsheet program. So you can think of Excel as a spreadsheet program that produces worksheets, but not as a worksheet program that produces spreadsheets. (On the other hand, I often refer to worksheets as spreadsheets in this book — and so can you.)
Cells: The building blocks of all worksheets
The cells in an Excel worksheet are formed by the intersection of the column and row grid. Technically, such an arrangement is known as an array. An array keeps track of different pieces of information stored in it by referring to its row position and its column position (something you see more clearly when I discuss the R1C1 cell referencing system in the sidebar, “Cell A1, also known as Cell R1C1,” elsewhere in this chapter). To display your worksheet data in its grid and tabular format, Excel just reads from the row and column position associated with the data that you enter there.
As shown in Figure 1-2, Excel constantly shows you your current position in the worksheet in three different ways:
Look at the current cell reference box of Figure 1-2, where it reads G9 (upper-left corner). This box, called the Name box, resides on the Formula bar, which sits atop the worksheet display. Here, Excel lists the current cell location by its column (G) and row (9) refer- ence. (Read more about the so-called A1 cell reference system in the sidebar elsewhere in this chapter, “Cell A1, also known as Cell R1C1.”)
In the worksheet itself, the cell pointer (refer to Figure 1-2), shown by a heavy border, appears in the cell that’s currently selected.
In the frame of the worksheet, the letter of the column and the number of the row containing the cell pointer are shaded in a distinct golden color.
You wonder why Excel makes such a big deal about telling you which cell is current in the worksheet? That’s a good question, and the answer is important:
In the worksheet, you can enter or edit information in only the cell that’s current.
The repercussions of this seemingly innocuous little statement are enormous. It means that if you’re paying more attention to what you need to enter in your spreadsheet than to which cell is current, you can end up replacing something you’ve already entered. It also means that you’ll never be able to edit a particular cell entry if you haven’t first selected the cell to make it current.
Figure 1-2: Excel shows you where you are in the worksheet.
So just how many cells are we talking about here?
I’m not exaggerating when I say that each worksheet contains millions of cells, any of which can be filled with information. Each worksheet has 256 columns, of which only the first 9 or 12 (letters A through I or L) are normally visible in a new worksheet, and 65,536 rows, of which only the first 15 to 25 are normally visible in a new worksheet. If you multiply 256 by 65,536, you come up with a total of 16,777,216 empty cells in each worksheet you use! (That’s over 16 million of those suckers!)
And as if that weren’t enough, each new workbook that you start comes equipped with three of these worksheets, each with its own 16,777,216 blank cells. This gives you a grand total of 50,331,648 cells at your disposal in any one Excel file that you happen to have open. And should that number prove to be too few (yeah, right!), you can add more worksheets (each with its 16,777,216 cells) to the workbook.
Cell A1, also known as cell R1C1
The A1 cell reference system is a holdover from the VisiCalc days. (VisiCalc was the granddaddy of spreadsheet programs for personal computers.) In addition to the A1 system, Excel supports a much older, technically more correct system of cell references: the R1C1 cell reference system. In this system, both the columns and rows in the worksheet are numbered, and the row number precedes the column number. For example, in this system, cell A1 is called R1C1 (row 1, column 1); cell A2 is R2C1 (row 2, column 1); and cell B1 is R1C2 (row 1, column 2). To switch to the R1C1 system, choose Tools⇒ Options on the Excel menu bar and then click the General tab followed by the R1C1 Reference Style check box before clicking OK.
Assigning 26 letters to 256 columns
When it comes to labeling the 256 columns in a worksheet, our alphabet — with its measly 26 letters — is not up to the task. To make up the difference, Excel doubles up the cell letters in the column reference so that column AA immediately follows column Z. This is followed by column AB, AC, and so on to AZ. After column AZ, you find column BA, and then BB, BC, and so on. According to this system for doubling the column letters, the 256th (and last) column of the worksheet is column IV. This, in turn, gives the very last cell of any worksheet the cell reference IV65536.
What you should know about Excel at this point
Remember the following things about Excel:
Each Excel file is referred to as a workbook.
Each new workbook that you open contains three blank worksheets.
• Each of these three blank worksheets contains a whole bunch of cells into which you enter your data.
• Each cell in each of these three worksheets has its own cell address made up of the letter(s) of its column and the number of its row.
More worksheet size trivia
If you were to produce the entire worksheet grid on paper, you would need a sheet that was approximately 21 feet wide by 1,365 feet long! On a 14-inch computer screen at a screen resolution of 800 by 600 pixels, you can normally see no more than between 10 and 12 complete columns and between 20 and 25 complete rows of the entire worksheet. With columns being about 1 inch wide and rows about 1/4 inch high, 10 columns represent a scant 3.9 percent of the total width of the worksheet, while 20 rows fill only about 0.03 percent of its total length. This should give you some idea of how little of the total worksheet is visible on the screen as well as just how much area is available.
U All spreadsheet information is stored in the individual cells of the worksheet. You can, however, enter information into only the cell that is current (that is, selected with the cell pointer).
U Excel indicates which of the over 16 million cells in the worksheet is the current (active) one by displaying its cell reference on the formula bar and displaying the cell pointer in the worksheet itself. (Refer to Figure 1-2.)
U The system for referencing cells in a worksheet — the A1 cell reference system — combines the column letter(s) with the row number.
What you still need to know about Excel
You could easily get the mistaken idea that a spreadsheet program like Excel is little more than a quirky word processor with a gridlock that forces you to enter your information in tiny, individual cells instead of offering you the spaciousness of full pages.
Well, I’m here to say that Bill Gates didn’t become a billionaire several times over by selling a quirky word processor. (All you Microsoft Word users out there, please hold your tongues!) The big difference between the cell of a worksheet and the pages of a word processor is that each cell offers com-puting power along with text-editing and formatting capabilities. This computing power takes the form of formulas that you create in various cells of the worksheet.
Quite unlike a paper spreadsheet, which contains only values computed somewhere else, an electronic worksheet can store both the formulas and the computed values returned by these formulas. Even better, your formulas can use values stored in other cells of the worksheet, and, as I explain in Chapter 2, Excel automatically updates the computed answer returned by such a formula anytime that you change these values in the worksheet.
Excel’s computational capabilities, combined with its editing and formatting capabilities, make the program perfect for generating any kind of document that uses textual and numeric entries and requires you to perform calculations on those values. Because you can make your formulas dynamic — so that their calculations are automatically updated when you change referenced values stored in other cells of the worksheet — you will find it much easier to keep the calculated values in a worksheet document both current and correct.
Getting the Darn Thing Started
If you’re at all familiar with using Windows XP or 2000, you won’t be shocked to find out that you have about a zillion ways to get Excel up and running after the program’s been installed on your hard drive. (Okay, only about half a dozen, and I’m going to talk about almost all of them.) Suffice it to say at this point that all the various and sundry methods for starting Excel require that you have Windows XP or Windows 2000 installed on your personal computer. (Excel 2003 won’t run under any older Windows versions, such as Windows 95 and 98.) After that, you have only to turn on the computer before you can use any of the following methods to get Excel 2003 started.
Starting Excel 2003 from the Windows Start menu
The most common way to launch Excel is by selecting the program from the Windows Start menu just as you can do to start any program installed on your computer. To start Excel 2003 from the Start menu, follow these simple steps:
1. Click the Start button on the Windows taskbar to open the Windows Start menu.
2. Highlight All Programs at the top of the Start menu.
3. Click the Microsoft Excel 2003 option on the Programs menu.
As soon as you complete these steps, Windows opens Excel 2003. As the program loads, you see the opening screen for Microsoft Excel 2003. When Excel finishes loading, you are presented with a screen like the one shown in Figure 1-4, containing a new workbook in which you can begin working.
After launching Excel from the All Programs submenu, Windows goes ahead and adds Microsoft Excel to the left panel of the Windows Start menu. This means that the next time you need to launch Excel, all you have to do is click the Start button on the Windows taskbar and then click Microsoft Excel on the left side of the Start menu.
Starting Excel 2003 with a desktop shortcut
If you use Excel all the time like I do, you won’t want to have to deal with the Start menu each time you need to launch the program. Instead, you can create an Excel desktop shortcut that enables you to start the program simply by double-clicking its icon. If you find that is too much trouble, you can add the desktop shortcut to the Quick Launch toolbar on the Windows taskbar. By doing that, you make it possible to launch the program simply by clicking the Excel button on the Quick Launch toolbar.
To create the Excel desktop shortcut, follow these steps:
1. Click the Start button on the Windows taskbar.
The Start menu opens where you can click the Search item.
2. Click Search in the lower-right corner of the Start menu.
The Search Results dialog box appears.
3. Click the All Files and Folders link in the panel on the left side of the Search Results dialog box.
The Search Companion pane appears on the left side of the Search Results dialog box.
4. Type excel.exe in the All or Part of the File Name text box.
Excel.exe is the name of the executable program file that runs Excel. After finding this file on your hard disk, you can create a desktop shortcut from it that launches the program.
5. Click the Search button.
Windows now searches your hard disk for the Excel program file. After locating this file, its name appears on the right side of the Search Results dialog box. When this filename appears, you can click the Stop button in the left panel to halt the search
6. Right-click the file icon for the excel.exe file and then highlight Send To on the pop-up menu and click Desktop (Create Shortcut) on its continuation menu.
A shortcut named Shortcut to excel.exe appears to your desktop.
7. Click the Close button in the upper-right corner of the Search Results dialog box.
After closing the Search Results dialog box, you should see the icon named Shortcut to excel.exe on the desktop. You should probably rename the shortcut to something a little more friendly, such as Excel 2003.
8. Right-click the Shortcut to excel.exe icon and then click Rename on the pop-up menu.
9. Replace the current name by typing a new shortcut name, such as Excel 2003 and then click anywhere on the desktop.
After creating an Excel desktop shortcut on the desktop, from then on, you can launch Excel by double-clicking the shortcut icon.
If you want to be able to launch Excel by clicking a single button, drag the icon for your Excel desktop shortcut to the Quick Launch toolbar to the immediate right of the Start button at the beginning of the Windows taskbar. When you position the icon on this toolbar, Windows indicates where the new Excel button will appear by drawing a black, vertical I-beam in front of or between the existing buttons on this bar. As soon as you release the mouse button, Windows adds an Excel 2003 button to the Quick Launch toolbar that enables you to launch the program by a single-click of its icon.
Figure 1-3 shows my Windows desktop after creating an Excel 2003 desktop shortcut and adding this shortcut as a button to the Quick Launch toolbar. Note that Windows does not remove the desktop shortcut at the time you add it to the Quick Launch toolbar. That way, you have a choice between using the desktop shortcut (with a double-click) or using the Excel 2003 button on the Quick Launch toolbar (with a single-click).
Mousing Around
Although most of Excel’s capabilities are accessible from the keyboard, in most cases the mouse is the most efficient way to select a command or perform a particular procedure. For that reason alone, if you need to use Excel regularly in getting your work done, it is well worth your time to master the program’s various mouse techniques.
Minding your mouse manners
Windows programs, such as Excel, use four basic mouse techniques to select and manipulate various objects in the program and workbook windows:
Clicking an object to select it: Positioning the pointer on something and then pressing and immediately releasing the primary mouse button (the left button unless, as a leftie, you’ve switched the buttons around).
Right-clicking an object to display its shortcut menu: Positioning the pointer on something and then pressing and immediately releasing the secondary mouse button (the right button unless, as a leftie, you’ve switched the buttons around).
Double-clicking an object to open it: Positioning the pointer on something and then pressing and immediately releasing the primary mouse button rapidly twice in a row.
Dragging an object to move or copy it: Positioning the pointer on something and then pressing and holding down the primary mouse button as you move the mouse in the direction you wish to drag the object. When you have positioned the object in the desired location on the screen, you then release the primary mouse button to place it.
When clicking an object to select it, you must make sure that the tip of the mouse pointer is touching the object that you want to select before you click. To avoid moving the pointer slightly before you click, grasp the sides of the mouse between your thumb (on one side) and your ring and little finger (on the other side), and then click the primary button with your index finger. If you run out of room on your desktop for moving the mouse, just pick up the mouse and reposition it on the desk (which does not move the pointer).
Figure 1-3: To start Excel, click the Excel button on the Quick Launch toolbar or double-click its desktop shortcut.
When a single click is just as good as a double
Keep in mind that when using Windows XP or 2000, you have the ability to modify the way that you open icons on the Windows desktop on the General tab of the Folder Options dialog box (opened by choosing Tools⇒Folder Options on menu bar of a window, such as My Documents and My Computer). If you use a single-click to Open an Item (Point to Select), you can open programs, such as Excel 2003, along with any of its folders and files on the desktop and in the My Computer and Explorer windows by single-clicking their icons. If this is how your computer is set up, your double-clicking days are pretty much over and done!
Getting your mouse pointer in shape
The shape of the mouse pointer is anything but static in the Excel program. As you move the mouse pointer to different parts of the Excel screen, the pointer changes shape to indicate a change in function.
Don’t confuse the mouse pointer with the cell pointer. The mouse pointer changes shape as you move it around the screen. The cell pointer always maintains its shape as an outline around the current cell or cell selection (whereupon it expands to include all the selected cells). The mouse pointer responds to any movement of your mouse on the desk and always moves independently of the cell pointer. You can use the mouse pointer to reposition the cell pointer, however. You do this by positioning the thick, white cross pointer in the cell that you want to hold the cell pointer and then clicking the primary mouse button.
So What Do All These Buttons Do?
In Figure 1-4, I identify the different parts of the Excel program window that appear when you first start the program (assuming that you haven’t selected an existing workbook to open at the same time the program starts). As you can see, the Excel window, upon opening, is chock-full of all kinds of useful, though potentially confusing, stuff!
Turning on to the title bar
The first bar in the Excel window is called the title bar because it shows you the name of the program that is running in the window (Microsoft Excel). When the workbook window is full size (as it is in Figure 1-4), the name of the workbook file follows Microsoft Excel, as in
Microsoft Excel Enterprise Edition - Book1
To the left of the program and filename on the title bar, you see the Excel icon (it appears as a green italic L crossed to form an X inside a box). When you click this icon, the program Control menu opens with all the commands that enable you to size and move the Excel program window. If you choose the Close command (the large X in the upper-right corner) on this menu (or press the shortcut keys, Alt+F4), you exit from Excel and are returned to the desktop.
Figure 1-4: The Excel program window contains a veritable smorgas-bord of buttons, toolbars, and task panes.
The buttons on the right side of the title bar are sizing buttons. Click the Minimize button (the one with the underscore), and the Excel window shrinks down to a button on the Windows taskbar. Click the Restore Down button (the one with the image of two smaller, tiled windows), and the Excel window assumes a somewhat smaller size on the desktop, and the Restore Down button changes to the Maximize button (the one with a single, full-size window), which you can use to restore the window to its original full size. If you click the Close button (the one with an X), you exit Excel (just as if you choose Close on the Control menu or press Alt+F4).
Messing around with the menu bar
The second bar in the Excel window is the menu bar. This bar contains the Excel pull-down menus, File through Help. You use these menus to select the Excel commands that you need to select when creating or editing your worksheets. (Jump ahead to the section “Ordering Directly from the Menus” for more information on how to select commands.)
To the left of the pull-down menus, you see an Excel file icon. When you click this icon, the file Control menu (much like the program Control menu) opens, showing all the commands that enable you to size and move the Excel workbook window (which fits within the Excel program window). Choose the Close command on this menu (or press the shortcut keys, Ctrl+W or Ctrl+F4), and you close the current Excel workbook without exiting the Excel program.
To the right of the pull-down menus, you see the Ask a Question drop-down text box. You can use this box to ask the Excel Answer Wizard any question you have about using Excel 2003. When you enter a new question in the Ask a Question box, Excel displays a list of possibly relevant help topics beneath the text box. Clicking one of these topics automatically opens the Excel Help window (see “Ogling the Online Help,” later in this chapter, for details).
The sizing buttons on the right side of the menu bar do the same thing to the current workbook window as the sizing buttons on the title bar do to the Excel program window. Click the Minimize Window button, and the Excel workbook window shrinks down to a tiny workbook title bar at the bottom of the workbook area. Click the Restore Window button, and the current workbook window assumes a somewhat smaller size on the workbook area. The Excel workbook icon, filename, and file-sizing buttons move to the title bar of this somewhat smaller workbook window; and the Restore Window button changes to the Maximize button, which you can use to restore the window to its original full size. If you click the Close button (the one with the X), you close the current workbook file (just as if you choose Close on the file Control menu or press Ctrl+W or Ctrl+F4).
Excel 2003 automatically adds a button to the Windows taskbar for each workbook file that you open in Excel 2003. This nifty new feature makes it super-easy to switch between workbooks. When you minimize the Excel program with the program’s Minimize Window button, a button displaying the name of the current workbook is added to the taskbar.
Scrutinizing the Standard and Formatting toolbars
The third bar in Excel 2003 stacks the two most popular Excel toolbars, Standard and Formatting, side by side. These two toolbars contain buttons (also known as tools) for doing the most common tasks that you perform in Excel. Tools on the Standard toolbar include those for doing really basic file-type stuff such as creating, saving, opening, and printing workbooks. Tools on the Formatting toolbar include those for manipulating appearance-type stuff, such as selecting a new font and font size and adding effects such as boldface,underlining, and italics to worksheet text.
To identify the function of any of the tools on these two (or any other) toolbars, simply position the arrowhead mouse pointer over the button until a tiny text box (called a ScreenTip) appears below the mouse pointer. To have Excel execute the command associated with a particular tool, simply click the button under the mouse pointer.
Stack ’em up
If you prefer to have immediate access to all the buttons on the Standard and Formatting toolbars at all times, you can do so easily by stacking the two toolbars one on top of the other, rather than side by side. (This is the way that these two toolbars appeared in all three previous versions of Excel for Windows). To make this change, right-click somewhere on the menu bar or third bar with the two toolbars, and then select the Customize command at the very bottom of the shortcut menu that pops up. Doing this opens the Customize dialog box from which you click the Options tab. Click the first check box option on the Options tab labeled Show Standard and Formatting Toolbars On Two Rows to add a check mark. I promise: You’ll never have need for the Toolbar Options button again.
Because the Standard and Formatting toolbars each contain a whole bunch of tools, not all the buttons in either toolbar can be displayed together on the third bar of the Excel screen. This is why the last tool on each of these two toolbars, the Toolbar Options button (indicated by a downward-pointing black triangle), has the continuation symbol (>>) over it. The presence of this symbol immediately tells you that the toolbar is truncated in some way and that not all of its buttons are displayed.
When you click the Toolbar Options button, Excel displays a palette with the additional tools that don’t fit on the toolbar when it’s stacked side-by-side with another toolbar. At the bottom of the palette of additional tools, you find two commands:
Show Buttons on Two Rows: Click this to display the Standard and Formatting toolbars on separate rows.
Add or Remove Buttons: This command comes with a pop-up button that enables you to customize which buttons appear on either the Standard or Formatting toolbars.
When you select the Add or Remove Buttons item on the Toolbar Options pop-up menu, Excel displays another pop-up menu from which you can select the Standard or Formatting toolbar. After selecting Standard or Formatting from this submenu, yet another pop-up menu appears, this one showing all the buttons associated with the particular toolbar (Standard or Formatting) that you selected. All the buttons currently displayed on the particular toolbar have check marks in front of them. To add buttons to the toolbar from this menu, click the tool in question to precede it with a check mark. To temporarily remove one of the buttons on the toolbar, click the button to remove its check mark. (For detailed information on customizing the buttons on these and other Excel toolbars, see Chapter 12.)
In Table 1-1, you can see the name and function of each tool normally found on the Standard toolbar when you first install Excel 2003. In Table 1-2, I list the name and function of each tool usually found on the Formatting toolbar. Rest assured: You will come to know each one intimately as your experience with Excel grows.
