Excel Data Analysis: Your Visual Blueprint for Creating and Analyzing Data, Charts and PivotTables

308 Pages·2003·12.19 MB·English

by  Jinjer Simon

537547 Cover 2/25/03 10:15 AM Page 1
Perfect bind Trim:8 X 9Bleed:.25”4-color process plus Pantone 2945 cv Matte layflat
Data analysis tools on
CD-ROM!
•Trial versions of FinOptions XL,
Welcome to the only guidebook series that takes a visual Analyse-It,Sigma XL,4TOPS
Excel Data Analysis
Data Analysis,and XLSTAT Pro
approach to professional-level computer topics.Open the book
•UNISTAT demo version
and you’ll discover step-by-step screen shots that demonstrate •Macro codes E
over 100 key Excel data analysis techniques,including: •A searchable e-version of the
x
book
• Creating PivotTables and PivotCharts • Manipulating data within a chart
c
• Importing and linking data • Editing external database tables
e
• Compiling data from various sources • Using an Excel function in a macro
• Filtering a list • Building PivotTable formulas l

• Creating an amortization table • Using Analysis ToolPak Add-ins
D
a
t
a

A
n
a
l
y
s
i
s
“Apply It” and “Extra” High-resolution screen Succinct explanations Two-page lessons Your visual blueprint for analyzing
™
sidebars highlight shots demonstrate walk you through break big topics into Simon
useful tips each task step by step bite-sized modules
data, charts, and PivotTables
ISBN 0-7645-3754-7
Data analysis tools on CD-ROM!
*85555-IGFJJa $26.99 USA ,!7IA7G4-fdhfeh!:p;m;Q;t;T
$39.99 CAN • FinOptions XL,Analyse-It,Sigma XL,
£18.95 UK
and other trial software
Category: www.wiley.com/compbooks
Spreadsheets • Plus macro codes,an e-version of the
System requirements: Windows 98 or higher. book,and more
See the What’s on the CD-ROM Appendix for details and complete system requirements.

01 537547 FM.qxd 3/4/03 11:43 AM Page i
Excel Data Analysis
Your visual blueprint™ for creating and analyzing
data, charts, and PivotTables
by Jinjer Simon
TM
From
®
&
01 537547 FM.qxd 3/4/03 11:43 AM Page ii
Excel Data Analysis: Your visual blueprint™for creating and
analyzing data, charts, and PivotTables
Published by FOR PURPOSES OF ILLUSTRATING THE CONCEPTS AND TECHNIQUES
Wiley Publishing, Inc. DESCRIBED IN THIS BOOK, THE AUTHOR HAS CREATED VARIOUS
909 Third Avenue NAMES, COMPANY NAMES, MAILING, E-MAIL AND INTERNET
New York, NY 10022 ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR INFORMATION,
ALL OF WHICH ARE FICTITIOUS. ANY RESEMBLANCE OF THESE
Published simultaneously in Canada FICTITIOUS NAMES, ADDRESSES, PHONE AND FAX NUMBERS AND
SIMILAR INFORMATION TO ANY ACTUAL PERSON, COMPANY AND/OR
Copyright © 2003 by Wiley Publishing, Inc, Indianapolis, Indiana
ORGANIZATION IS UNINTENTIONAL AND PURELY COINCIDENTAL.
Certain designs and text Copyright © 1992-2003 maranGraphics, Inc., used with
maranGraphics' permission.
Important Numbers
maranGraphics, Inc.
5755 Coopers Avenue For U.S. corporate orders, please call maranGraphics at 800-469-6616 or fax
Mississauga, Ontario, Canada 905-890-9434.
L4Z 1R9 For general information on our other products and services or to obtain
technical support please contact our Customer Care Department within the
No part of this publication may be reproduced, stored in a retrieval system or
U.S. at 800-762-2974, outside the U.S. at 317-572-3993 or fax [phone].
transmitted in any form or by any means, electronic, mechanical, photocopying,
recording, scanning or otherwise, except as permitted under Sections 107 or 108 of
Permissions
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 maranGraphics
to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923,
[phone], fax [phone]. Requests to the Publisher for permission Certain text and Illustrations by maranGraphics, Inc., used with
should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 maranGraphics' permission.
Crosspoint Blvd, Indianapolis, IN 46256, [phone], fax [phone], Microsoft
E-mail: [email protected].
Microsoft Excel screen shots reprinted by permission from Microsoft Corporation.
Library of Congress Control Number: 2003101786
ISBN: 0-7645-3754-7
No place in Scotland is filled with as much
Manufactured in the United States of America history, legend, and lore as Edinburgh Castle,
one of the highlights of a visit to this little
10 9 8 7 6 5 4 3 2 1 country. It's
believed the
1V/SX/QT/QT/IN
ancient city grew
up on the seat of
a dead volcano,
Trademark Acknowledgments
Castle Rock.
Its walls shelter
Wiley, the Wiley Publishing logo, Visual, the Visual logo, Simplified, Master St. Margaret's
VISUALLY, Teach Yourself VISUALLY, Visual Blueprint, In an Instant, Chapel, the
Read Less - Learn More and related trade dress are trademarks or registered oldest structure
trademarks of Wiley Publishing, Inc. in the United States and other countries and in Edinburgh. You
may not be used without written permission. The maranGraphics logo is a can visit the State Apartments, particularly
trademark or registered trademark of maranGraphics, Inc. All other trademarks are Queen Mary's Bedroom, where Mary Queen of
the property of their respective owners. Wiley Publishing, Inc. and maranGraphics, Scots gave birth to James VI of Scotland (later
Inc. are not associated with any product or vendor mentioned in this book. James I of England). Explore Scotland's intriguing
past inFrommer's Scotland, 7th Edition, available
wherever books are sold or at Frommers.com.
is a trademark of
Wiley Publishing, Inc.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHOR HAVE
USED THEIR BEST EFFORTS IN PREPARING THIS BOOK, THEY MAKE NO REPRESENTATIONS
OORF WTAHRIRSA NBTOIEOSK W ITAHN DR ESSPPEECCTI FTIOC ATHLLEY A CDCIUSRCALCAYIM O R ACNOYM PLIMETPELNIEEDSS OWFA TRHREA NCOTINETSE NOTFS U.S. Corporate Sales U.S. Trade Sales
MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE
CREATED OR EXTENDED BY SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE Contact maranGraphics Contact Wiley
ASIDTVUIACTEI OANN. DY OSUT RSAHTEOGUILEDS CCOONNSTUAILNTE DW IHTHER EAI NP RMOAFYE SNSIOOTN ABLE WSUHIETRAEB LAEP PFROORP RYIOATUER. at [phone] or at [phone] or
NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY fax [phone]. fax [phone].
OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL,
CONSEQUENTIAL, OR OTHER DAMAGES.
01 537547 FM.qxd 3/4/03 11:43 AM Page iii
Excel Data Analysis
01 537547 FM.qxd 3/4/03 11:43 AM Page iv
maranGraphics is a family-run business
located near Toronto, Canada.
At maranGraphics, we believe in producing great rebuild every screen shot to provide the best clarity
computer books — one book at a time. possible, making our screen shots the most precise and
easiest to read in the industry. We strive for perfection,
maranGraphics has been producing high-technology
and believe that the time spent handcrafting each
products for over 25 years, which enables us to offer the
element results in the best computer books money
computer book community a unique communication
canbuy.
process.
Thank you for purchasing this book. We hope you
Our computer books use an integrated communication
enjoy it!
process, which is very different from the approach used
in other computer books. Each spread is, in essence, a Sincerely,
flow chart — the text and screen shots are totally
incorporated into the layout of the spread. Introductory Robert Maran
text and helpful tips complete the learning experience.
President
maranGraphics' approach encourages the left and right maranGraphics
sides of the brain to work together — resulting in faster [email protected]
orientation and greater memory retention. www.maran.com
Above all, we are very proud of the handcrafted nature
of our books. Our carefully-chosen writers are experts
in their fields, and spend countless hours researching
and organizing the content for each topic. Our artists
01 537547 FM.qxd 3/4/03 11:43 AM Page v
CREDITS
Project Editor Book Design
Maureen Spears maranGraphics®
Acquisitions Editor Production Coordinator
JenDorsey Dale White
Project Development Manager Screen Artists
Lindsay Sandman Jill A. Proll
Copy Editor Cover Illustration
Jill Mazurczyk David E. Gregory
Technical Editors Proofreader
Kerwin McKenzie Vicki Broyles
Allen Wyatt
Barbara A. Prillaman Quality Control
David Faust
Editorial Manager
Rev Mengle Indexer
TECHBOOKS Production Services
Permissions Editor
Laura Moss Special Help
Barbara A. Prillaman
Media Development Specialist
Megan Decraene Vice President and Executive
Group Publisher
Manufacturing Richard Swadley
Allan Conley
Linda Cook Vice President and
Paul Gilchrist ExecutivePublisher
Jennifer Guynn BobIpsen
Layout Vice President and Publisher
Beth Brooks Barry Pruett
Sean Decker
LeAndra Johnson
Composition Director
Kristin McMullan
Debbie Stailey
Heather Pope
01 537547 FM.qxd 3/4/03 11:43 AM Page vi
ABOUT THE AUTHOR
Jinjer Simonhas been actively involved in the computer industry for the past 18 years. Her involvement
in the industry has included programming, providing software technical support, training end-users,
developing written and online user documentation, creating software tutorials, and developing Web
sites. She is the author of several computer books, including Excel Programming: Your visual blueprint
for creating interactive spreadsheets, Windows CE For Dummies,and Windows CE 2 For Dummies.
01 537547 FM.qxd 3/4/03 11:43 AM Page vii
AUTHOR'S ACKNOWLEDGMENTS
As an author, it is my responsibility to recognize each of the individuals that contributed to the
completion of this book. Although my responsibility is to produce the content for the book, many
others are responsible for getting this book pulled together.
I would like to recognize the efforts of everyone at Wiley Publishing for all the hard work on this
project. First off, my acquisitions editor, Jen Dorsey got the project off and running. Again I had the
opportunity to work with Maureen Spears, my project editor, on a third book. Helping Maureen out
were Jill Mazurczyk, the copy editor, and Rev Mengle, editorial manager. I would also like to thank the
graphics and production departments for the great job they did pulling everything together.
I would also like to thank my technical editors, Allen Wyatt and Kerwin McKenzie. They provided some
great input on the content of the book. With her statistical knowledge, Barb Prillaman also helped with
Chapter 11.
I want to thank my agent, Neil Salkind at Studio B, for working out the kinks in the project. Finally, I
want to thank my husband Richard, and children, Alex and Ashley, for their patience while I completed
this project.
01 537547 FM.qxd 3/4/03 11:43 AM Page viii
TABLE OF CONTENTS
HOW TO USE THIS BOOK
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .XII
1) GETTING STARTED WITH EXCEL
Excel Data Analysis Options ..............................................................................................2
Excel Data Types ..................................................................................................................4
Locate a Value in a Worksheet ..........................................................................................6
Select a Range of Cells ........................................................................................................7
Name a Range ......................................................................................................................8
Create Label Ranges ..........................................................................................................10
Modify Named Ranges ......................................................................................................12
Copy and Paste a Range of Cells ......................................................................................14
Create a Custom Number Format ..................................................................................16
Apply AutoFormat to a Worksheet ..................................................................................18
Create a Named Style ......................................................................................................20
Create a Custom Template ..............................................................................................22
Protect Worksheets ............................................................................................................24
2) ORGANIZE WORKSHEET DATA
Create a List ........................................................................................................................26
Add a Series to a List ........................................................................................................28
Sort a List ............................................................................................................................30
Create a Custom Sort ........................................................................................................32
Consolidate Data ..............................................................................................................36
Outline Your Data ..............................................................................................................40
3) EVALUATE WORKSHEET DATA
Apply Conditional Formatting ..........................................................................................42
Summarize Data with Subtotals ......................................................................................46
Filter a List ..........................................................................................................................48
Create a Custom Filter ......................................................................................................50
Create an Advanced Filter ................................................................................................52
Create Scenarios ................................................................................................................54
Validate Data ......................................................................................................................56
viii