PUBLISHED BY Microsoft Press A Division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright © 2004 by Curtis Frye All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. Library of Congress Cataloging-in-Publication Data Frye, Curtis, 1968- Microsoft Office Excel 2003 Step by Step / Curtis Frye. p. cm. Includes index. ISBN 0-7356-1518-7 1. Microsoft Excel (Computer file) 2. Business--Computer programs. 3. Electronic spreadsheets. I. Title. HF5548.4.M523F8 2003 005.369-dc21 2003052689 Printed and bound in the United States of America. 1 2 3 4 5 6 7 8 9 QWE 8 7 6 5 4 3 Distributed in Canada by H.B. Fenn and Company Ltd. A CIP catalogue record for this book is available from the British Library. Microsoft Press books are available through booksellers and distributors worldwide. For further informa- tion about international editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to [email protected]. FoxPro, FrontPage, IntelliMouse, Microsoft, Microsoft Press, MSN, the Office logo, Outlook, PivotChart, PivotTable, PowerPoint, Visual Basic, Visual FoxPro, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other product and company names mentioned herein may be the trademarks of their respective owners. The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. Acquisitions Editor: Alex Blanton Project Editor: Aileen Wrothwell Body Part No. X09-71434 Contents Contents What’s New in Microsoft Excel 2003 vii Getting Help ix Getting Help with This Book and Its CD-ROM ix Getting Help with Microsoft Excel 2003 ix Using the Book’s CD-ROM xi System Requirements xi Installing the Practice Files xii Using the Practice Files xii Uninstalling the Practice Files xiv Conventions and Features xv Microsoft Office Specialist Skills Standards xvii Microsoft Office Specialist Skill Standards xvii Microsoft Office Specialist Expert Skill Standards xviii Taking a Microsoft Office Specialist Certification Exam xxi About the Microsoft Office Specialist Program xxi Selecting a Microsoft Office Specialist Certification Level xxi Microsoft Office Specialist Skills Standards xxii The Exam Experience xxii Test-Taking Tips xxiv For More Information xxv Quick Reference xxvii Chapter 1 Getting to Know Excel xxvii Chapter 2 Setting Up a Workbook xxix Chapter 3 Performing Calculations on Data xxxiii Chapter 4 Changing Document Appearance xxxv Chapter 5 Focusing on Specific Data Using Filters xxxviii Chapter 6 Combining Data from Multiple Sources xl Chapter 7 Reordering and Summarizing Data xlii Chapter 8 Analyzing Alternative Data Sets xliii Chapter 9 Creating Dynamic Lists with PivotTables xlvi Chapter 10 Creating Charts xlviii Chapter 11 Printing li Chapter 12 Automating Repetitive Tasks with Macros liv Chapter 13 Working with Other Microsoft Office Programs lvii iii Contents Chapter 14 Working with Database Data lviii Chapter 15 Publishing Information on the Web lx Chapter 16 Collaborating with Colleagues lxiv 1 Getting to Know Excel 1 Introducing Excel 1 Working with an Existing Data List 3 Zeroing In on Data in a List 5 Creating a Workbook 11 Checking and Correcting Data 17 2 Setting Up a Workbook 24 Making Workbooks Easier to Work With 25 Making Data Easier to Read 31 Adding a Graphic to a Document 34 3 Performing Calculations on Data 40 Naming Groups of Data 42 Creating Formulas to Calculate Values 44 Finding and Correcting Errors in Calculations 50 4 Changing Document Appearance 56 Changing the Appearance of Data 58 Applying an Existing Format to Data 62 Making Numbers Easier to Read 64 Changing Data’s Appearance Based on Its Value 69 Making Printouts Easier to Follow 73 Positioning Data on a Printout 76 5 Focusing on Specific Data Using Filters 82 Limiting the Data That Appears on the Screen 84 Performing Calculations on Filtered Data 89 Defining a Valid Set of Values for a Range of Cells 91 6 Combining Data from Multiple Sources 96 Using a Data List as a Template for Other Lists 98 Working with More Than One Set of Data 101 iv Contents Linking to Data in Other Workbooks 107 Summarizing Multiple Sets of Data 110 Grouping Multiple Data Lists 114 7 Reordering and Summarizing Data 118 Sorting a Data List 120 Organizing Data into Levels 124 8 Analyzing Alternative Data Sets 130 Defining and Editing Alternative Data Sets 132 Defining Multiple Alternative Data Sets 135 Varying Your Data to Get a Desired Result 138 Finding Optimal Solutions with Solver 141 Analyzing Data with Descriptive Statistics 146 9 Creating Dynamic Lists with PivotTables 150 Creating Dynamic Lists with PivotTables 152 Editing PivotTables 159 Creating PivotTables from External Data 166 10 Creating Charts 172 Creating a Chart 174 Customizing Chart Labels and Numbers 180 Finding Trends in Your Data 183 Creating a Dynamic Chart Using PivotCharts 185 Creating Diagrams 190 11 Printing 196 Printing Data Lists 197 Printing Part of a Data List 205 Printing a Chart 209 12 Automating Repetitive Tasks with Macros 214 Introducing Macros 216 Creating and Modifying Macros 220 Creating a Toolbar to Hold Macros 223 Creating a Menu to Hold Macros 226 Running a Macro When a Workbook Is Opened 230 v Contents 13 Working with Other Microsoft Office Programs 232 Including an Office Document in an Excel Worksheet 234 Storing an Excel Document as Part of Another Office Document 237 Creating a Hyperlink 239 Pasting a Chart into Another Document 243 14 Working with Database Data 246 Looking Up Information in a Data List 248 Retrieving Data from a Database 251 Summarizing List Data 257 15 Publishing Information on the Web 262 Saving a Workbook for the Web 264 Publishing Worksheets on the Web 266 Publishing a PivotTable on the Web 270 Retrieving Data from the Web 272 Acquiring Web Data with Smart Tags 275 Working with Structured Data 277 Use Professional XML Data Capabilities 279 16 Collaborating with Colleagues 286 Sharing a Data List 288 Managing Comments 291 Tracking and Managing Colleagues’ Changes 293 Identifying Which Revisions to Keep 296 Protecting Workbooks and Worksheets 298 Authenticate Workbooks 303 Glossary 309 Index 313 vi What’s New in Microsoft Excel 2003 You’ll notice some changes as soon as you start Microsoft Excel 2003. The toolbars and menu bar have a new look, and there are some new task panes available on the right side of your screen. But the features that are new or greatly improved in this version of Excel go beyond just changes in appearance. Some changes won’t be apparent to you until you start using the program. To help you quickly identify features that are new or greatly enhanced with this ver- NNeeww iinn OOffffiiccee 22000033 sion, this book uses the icon in the margin whenever new features are discussed or shown. If you want to learn about only the new features of the program, you can skim through the book, completing only those topics that show this icon. The following table lists the new features that you might be interested in, as well as the chapters in which those features are discussed. To learn how to Using this new feature See Use online support tools Research tools Chapter 1, page 17 Use professional XML data XML Source task pane Chapter 15, page 283 capabilities Create a data map XML Source task pane Chapter 15, page 284 Define XML elements XML Source task pane Chapter 15, page 285 Define XML viewing options XML Source task pane Chapter 15, page 286 For more information about the Excel product, see http://www.microsoft.com/office/excel/. vii Getting Help Every effort has been made to ensure the accuracy of this book and the contents of its CD-ROM. If you do run into problems, please contact the appropriate source for help and assistance. Getting Help with This Book and Its CD-ROM If your question or issue concerns the content of this book or its companion CD-ROM, please first search the online Microsoft Knowledge Base, which provides support infor­ mation for known errors in or corrections to this book, at the following Web site: http://mspress.microsoft.com/supportsearch.asp If you do not find your answer at the online Knowledge Base, send your comments or questions to Microsoft Press Technical Support at: [email protected] Getting Help with Microsoft Excel 2003 If your question is about a Microsoft software product, including Excel, and not about the content of this Microsoft Press book, please search the Microsoft Knowledge Base at: http://support.microsoft.com/directory In the United States, Microsoft software product support issues not covered by the Microsoft Knowledge Base are addressed by Microsoft Product Support Services. The Microsoft software support options available from Microsoft Product Support Services are listed at: http://support.microsoft.com/directory Outside the United States, for support information specific to your location, please refer to the Worldwide Support menu on the Microsoft Product Support Services Web site for the site specific to your country: http://support.microsoft.com/directory ix