Microsoft Office Excel 2003

382 Pages·2004·12.949 MB·English

by  Curtis Frye

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 [phone]. 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