Microsoft Office Excel 2003 Fast and Easy

®

™

Microsoft

Office

Excel 2003

Microsoft

Office

Excel 2003

This page intentionally left blank

Diane Koers

®

™

Microsoft

Office

Excel 2003

Microsoft

Office

Excel 2003

© 2004 by Premier Press, a division of Course Technology. All rights reserved. No part of this book may

be reproduced or transmitted in any form or by any means, electronic or mechanical, including

photocopying, recording, or by any information storage or retrieval system without written permission

from Premier Press, except for the inclusion of brief quotations in a review.

The Premier Press logo and related trade dress are trademarks of Premier Press and may not be

used without written permission.

SVP, Retail Strategic Market Group: Andy Shafran

Publisher: Stacy L. Hiquet

Senior Marketing Manager: Sarah O’Donnell

Marketing Manager: Heather Hurley

Manager of Editorial Services: Heather Talbot

Associate Marketing Manager: Kristin Eisenzopf

Project Editor: Jenny Davidson

Technical Reviewer: Greg Perry

Retail Market Coordinator: Sarah Dubois

Interior Layout: Danielle Foster

Cover Designer: Mike Tanamachi

Indexer: Sharon Shock

Proofreader: Dan Foster

Microsoft, Office, Excel, Word, and Access are either registered trademarks or trademarks of Microsoft

Corporation in the United States and/or other countries.

All other trademarks are the property of their respective owners.

Important: Premier Press cannot provide software support. Please contact the appropriate software

manufacturer’s technical support line or Web site for assistance.

Premier Press and the author have attempted throughout this book to distinguish proprietary

trademarks from descriptive terms by following the capitalization style used by the manufacturer.

Information contained in this book has been obtained by Premier Press from sources believed to be

reliable. However, because of the possibility of human or mechanical error by our sources, Premier

Press, or others, the Publisher does not guarantee the accuracy, adequacy, or completeness of any

information and is not responsible for any errors or omissions or the results obtained from use of such

information. Readers should be particularly aware of the fact that the Internet is an ever-changing

entity. Some facts may have changed since this book went to press.

ISBN: 1-59200-079-7

Library of Congress Catalog Card Number: 2003105360

Printed in the United States of America

04 05 06 07 08 BH 10 9 8 7 6 5 4 3 2 1

Premier Press, a division of Course Technology

25 Thomson Place

Boston, MA 02210

To everyone at Bosma Industries for the Blind

Serving the needs of others

I am deeply grateful to the many people at Premier Press who worked on this

book. Thank you for all the time you gave and for your assistance. While I can’t

name everyone involved, I’d like to especially thank Stacy Hiquet for the

opportunity to write this book and her confidence in me; to Greg Perry for his

wonderful technical advice; to Dan Foster and Danielle Foster for their outstanding

contributions; and a very special thank you to Jenny Davidson for all her patience

and guidance in pulling the project together and making it correct. It takes a lot of

both to work with me!

Lastly, a big hug and kiss to my husband, Vern, for his never-ending support. For

thirty-five years we’ve been a team, and it shows!

Acknowledgments Acknowledgments

DIANE KOERS owns and operates All Business Service, a software training and consulting

business formed in 1988 that services the central Indiana area. Her area of expertise

has long been in the word-processing, spreadsheet, and graphics area of computing as

well as providing training and support for Peachtree Accounting Software. Diane’s

authoring experience includes over two dozen books on topics such as PC Security,

Microsoft Windows, Office, Works, WordPerfect, Paint Shop Pro, Lotus SmartSuite,

Quicken, Money, and Peachtree Accounting, many of which have been translated into

other languages such as Dutch, Bulgarian, Spanish, and Greek. She has also developed

and written numerous training manuals for her clients.

Active in her church and civic activities, Diane enjoys spending her free time

traveling and playing with her grandsons and her two Yorkshire Terriers.

About the Author About the Author

This page intentionally left blank

Introduction .............................................................................. xix

PART I BUILDING A BASIC WORKSHEET ............................................... 1

Chapter 1 Exploring the Excel Screen................................................................ 3

Chapter 2 Creating a Simple Worksheet .......................................................... 21

Chapter 3 Editing a Worksheet ........................................................................ 39

Chapter 4 Making the Worksheet Look Good ................................................. 61

Chapter 5 Selecting Printing Options .............................................................. 89

PART II WORKING WITH FORMULAS AND

LARGER WORKSHEETS ........................................................... 103

Chapter 6 Working with Formulas .................................................................. 105

Chapter 7 Using Excel Functions.................................................................... 119

Chapter 8 Working with Larger Workbooks .................................................. 147

Chapter 9 Managing Larger Worksheets ....................................................... 159

PART III ADDING VISUAL INTEREST..................................................... 179

Chapter 10 Working with Art Images ............................................................... 181

Chapter 11 Creating Charts from Excel Data .................................................. 199

Chapter 12 Using the Drawing Toolbar ............................................................ 217

Contents at a Glance Contents at a Glance

PART IV ANALYZING DATA ................................................................... 233

Chapter 13 Working with Data ......................................................................... 235

Chapter 14 Discovering Tools for Speed and Quality ...................................... 255

Chapter 15 Customizing Excel ......................................................................... 273

PART V INTEGRATING EXCEL TECHNOLOGY ..................................... 287

Chapter 16 Integrating Excel with Word.......................................................... 289

Chapter 17 Using Excel with Access ................................................................ 305

Chapter 18 Collaborating with Others ............................................................. 321

Appendix A Excel Shortcut Keys ....................................................................... 351

Glossary ................................................................................... 359

Index ........................................................................................ 365

x CONTENTS AT A GLANCE

Introduction ............................................................. xix

PART I

BUILDING A BASIC WORKSHEET ............................. 1

Chapter 1 Exploring the Excel Screen .........................................3

Starting Excel ..................................................................................... 4

Exploring the Worksheet Screen ....................................................... 4

Working with the Task Pane .......................................................... 6

Working with Menus ..................................................................... 8

Using Toolbars ............................................................................. 11

Moving around the Worksheet Screen............................................ 16

Using the Mouse ......................................................................... 17

Using the Keyboard..................................................................... 18

Accessing the Go To Command .................................................. 18

Using Excel Help.............................................................................. 19

Chapter 2 Creating a Simple Worksheet ...................................21

Entering Data................................................................................... 22

Entering Labels............................................................................ 22

Entering Values............................................................................ 24

Entering Dates............................................................................. 25

Saving a Worksheet File .................................................................. 27

Saving a File the First Time ......................................................... 27

Resaving a Worksheet ................................................................. 29

Closing a Worksheet........................................................................ 30

Contents Contents

xii CONTENTS

Enabling AutoRecover ..................................................................... 31

Opening an Existing File ................................................................. 33

Displaying the Open Dialog Box................................................. 33

Opening a Recently Used Worksheet ......................................... 35

E-Mailing a Worksheet..................................................................... 35

Starting a New Worksheet .............................................................. 37

Chapter 3 Editing a Worksheet .................................................39

Editing Data ..................................................................................... 40

Replacing the Contents of a Cell ................................................ 40

Editing the Contents of a Cell..................................................... 41

Clearing Cell Contents ................................................................ 42

Learning Selection Techniques ........................................................ 42

Inserting Areas................................................................................. 43

Inserting Columns ....................................................................... 44

Inserting Rows ............................................................................. 45

Inserting Cells .............................................................................. 46

Deleting Areas ................................................................................. 47

Deleting Columns ........................................................................ 47

Deleting Rows ............................................................................. 48

Deleting Cells .............................................................................. 48

Using Undo and Redo...................................................................... 49

Undoing the Previous Step ......................................................... 50

Redoing the Previous Step .......................................................... 50

Undoing a Series of Actions ........................................................ 51

Moving Data Around ....................................................................... 51

Cutting and Pasting Data ............................................................ 51

Using Drag and Drop to Move Cells ........................................... 53

Copying Data ................................................................................... 55

Using the Office Clipboard .............................................................. 56

Using the Fill Feature....................................................................... 58

Chapter 4 Making the Worksheet Look Good ...........................61

Using AutoFormat ........................................................................... 62

Formatting Numbers ....................................................................... 64

Formatting Numbers with the Toolbar ....................................... 64

Formatting Numbers through the Menu .................................... 68

Formatting with Fonts ..................................................................... 71

CONTENTS xiii

Selecting a Typeface.................................................................... 71

Selecting a Font Size ................................................................... 72

Adding Bold, Underline, or Italics ............................................... 73

Working with Font Colors ........................................................... 73

Setting Font Options Using the Format Dialog Box .................. 74

Adjusting Column Width ................................................................. 75

Adjusting Row Height...................................................................... 77

Setting Worksheet Alignment ......................................................... 78

Adjusting Cell Alignment ............................................................ 79

Setting Vertical Alignment .......................................................... 80

Centering Headings .................................................................... 82

Adding Cell Borders ........................................................................ 83

Adding Background Color ............................................................... 84

Using Cell Patterns .......................................................................... 85

Copying Cell Formatting ................................................................. 87

Chapter 5 Selecting Printing Options ........................................89

Setting Up the Pages....................................................................... 90

Setting Up Margins ..................................................................... 90

Setting Page Orientation and Size.............................................. 91

Setting Other Printing Options ................................................... 92

Creating a Header or Footer ...................................................... 94

Selecting a Print Area ...................................................................... 97

Printing a Worksheet ....................................................................... 98

Using Print Preview ..................................................................... 98

Printing the Worksheet ............................................................. 100

Part I Review Questions ................................................................ 102

PART II

WORKING WITH FORMULAS AND

LARGER WORKSHEETS ........................................ 103

Chapter 6 Working with Formulas ...........................................105

Creating Formulas ......................................................................... 106

Creating a Simple Formula........................................................ 106

Creating a Compound Formula ................................................ 108

Editing Formulas............................................................................ 109

xiv CONTENTS

Copying Formulas.......................................................................... 110

Copying Using the Fill Feature ................................................. 111

Copying with Copy and Paste................................................... 112

Creating an Absolute Reference in a Formula .............................. 114

Viewing Formulas .......................................................................... 116

Understanding Common Formula Error Messages ...................... 117

Chapter 7 Using Excel Functions .............................................119

Understanding Function Syntax .................................................... 120

Creating a Total with the SUM Function ....................................... 120

Entering a SUM Function .......................................................... 121

Using the AutoSum Button ....................................................... 124

Using Other Functions ................................................................... 125

Calculating with Mathematical Functions ................................. 126

Analyzing with Statistical Functions .......................................... 128

Using Date Functions ................................................................ 133

Figuring with Financial Functions.............................................. 137

Understanding Logical Functions.............................................. 139

Getting Help with Excel Functions................................................ 142

Chapter 8 Working with Larger Workbooks ............................147

Moving from Worksheet to Worksheet ......................................... 148

Inserting Worksheets ..................................................................... 149

Naming Worksheets ...................................................................... 150

Hiding a Worksheet ....................................................................... 151

Deleting Worksheets ..................................................................... 153

Creating a Reference to Another Worksheet................................ 154

Creating Links to Other Workbooks ............................................. 156

Chapter 9 Managing Larger Worksheets .................................159

Modifying Worksheet Views .......................................................... 160

Hiding Rows and Columns ........................................................ 160

Splitting a Window .................................................................... 163

Freezing Panes .......................................................................... 165

Using Zoom ............................................................................... 167

Working with Ranges ..................................................................... 168

Creating Range Names ............................................................. 168