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

306 Pages·2007·31.12 MB·English

by  Denise Etheridge

01_132296 ffirs.qxp 5/24/07 12:39 PM Page iv
Excel® 2007 Data
Analysis
Your visual blueprint™ for creating and
analyzing data, charts, and PivotTables
by Denise Etheridge
01_132296 ffirs.qxp 5/24/07 12:39 PM Page ii
Excel®2007 Data Analysis: Your visual blueprint™for FOR PURPOSES OF ILLUSTRATING THE CONCEPTS AND
creating and analyzing data, charts, and PivotTables TECHNIQUES DESCRIBED IN THIS BOOK, THE AUTHOR HAS
CREATED VARIOUS NAMES, COMPANY NAMES, MAILING, E-MAIL
Published by AND INTERNET ADDRESSES, PHONE AND FAX NUMBERS AND
Wiley Publishing, Inc. SIMILAR INFORMATION, ALL OF WHICH ARE FICTITIOUS. ANY
111 River Street RESEMBLANCE OF THESE FICTITIOUS NAMES, ADDRESSES, PHONE
Hoboken, NJ 07030-5774 AND FAX NUMBERS AND SIMILAR INFORMATION TO ANY ACTUAL
Published simultaneously in Canada PERSON, COMPANY AND/OR ORGANIZATION IS UNINTENTIONAL
AND PURELY COINCIDENTAL.
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
No part of this publication may be reproduced, stored in a retrieval
Contact Us
system or transmitted in any form or by any means, electronic,
mechanical, photocopying, recording, scanning or otherwise, except as
For general information on our other products and services please
permitted under Sections 107 or 108 of the 1976 United States Copyright
contact our Customer Care Department within the U.S. at
Act, without either the prior written permission of the Publisher, or
800-762-2974, outside the U.S. at 317-572-3993, or fax [phone].
authorization through payment of the appropriate per-copy fee to the
Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, For technical support please visit www.wiley.com/techsupport.
978-750-8400, fax [phone]. 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 [phone], or online at www.wiley.com/go/
permissions.
Library of Congress Control Number: 2007925985
ISBN: 978-0-470-13229-6
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Trademark Acknowledgments
Wiley, the Wiley Publishing logo, Visual, the Visual logo, Visual Blueprint,
Read Less - Learn More, and related trade dress are trademarks or
registered trademarks of John Wiley & Sons, Inc. and/or its affiliates.
Microsoft and Excel are registered trademarks of Microsoft Corporation in
the United States and/or other countries. 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.
The Metropolitan Cathedral of Brasilia
A vision of architect Oscar Niemeyer, the breathtaking Metropolitan
Cathedral of Brasilia reflects the innovative architecture and design
that characterize this young and original city. Sweeping skyward,
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER the Cathedral’s sixteen gracefully curved columns symbolize
AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES
handslifted in prayer, while the unique bell tower replicates a
WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE
candelabra. Begun in 1959, the Cathedral was
CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL
designated aHistoric Monument in 1967,
WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF
FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE thereby accessing public funds for its
CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. completion. Itwas dedicated in 1970.
THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE Learn more about Brasilia’s unusual
SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE history and architecture in
UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN Frommer’s Brazil, available
RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL wherever books are sold or
SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE atwww.frommers.com.
SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE
SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE
LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN
ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A
CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION Sales
DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES
Contact Wiley
THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE
OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD at [phone]
BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY or [phone].
HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS
WRITTEN AND WHEN IT IS READ.
01_132296 ffirs.qxp 5/24/07 12:39 PM Page iii
P F V B …
RAISE OR ISUAL OOKS
“ This is absolutely the best computer-related book “I am an avid fan of your Visual books. If I need to
I have ever bought. Thank you so much for this learn anything, I just buy one of your books and
fantastic text. Simply the best computer book learn the topic in no time. Wo nder s! I have even
series I have ever seen. I will look for , re commend, trained my friends to give me Visual books as
and purchase more of the same.” gif ts.”
—David E. Prince (NeoNome.com) —Illona Bergstrom (Aventura, FL)
“I have several of your Visual books and they ar e “I just had to let you and your company know how
the best I have ever used.” great I think your books are. I just purchased my
—Stanley Clark (Crawfordville, FL) third Visual book (my fir st two are dog-eared
now!) and, once again, your product has surpassed
“I just want to let you know that I really enjoy all my expectations. The exper tise, thought, and ef for t
your books. I’m a strong visual learner. Yo u really that go into each book are obvious, and I sincerel y
know how to get people addicted to learning! I’m appreciate your ef for ts.”
a ver y satisf ied Visual customer. Keep up the —Tr acey Moore (Memphis, TN)
excellent work!”
—Helen Lee (Calgar y, Alber ta, Canada) “Compliments to the chef!! Yo ur books ar e
extraordinar y! Or, simply put, extra-ordinar y,
“I have several books from the Visual series and meaning way above the rest! THANK YO U THANK
have always found them to be valuable resources.” YO U THANK YO U! I buy them for friends, family,
—Stephen P. Miller (Ballston Spa, NY) and colleagues.”
—Christine J. Manfrin (Castle Rock, CO)
“ This book is PERFECT for me — it’s highly visual
and gets right to the point. Wh at I like most about “I write to extend my thanks and appreciation for
it is that each page presents a new task that you your books. They are clear, easy to follow, and
can tr y verbatim or, alternatively, take the ideas straight to the point. Keep up the good work! I
and build your own examples. Also, this book isn’t bought several of your books and they are just
bogged down with tr ying to ‘tell all’ – it gets right right! No regrets! I will always buy your books
to the point. This is an EXCELLENT , EXCELLENT , because they are the best.”
EXCELLENT book and I look for ward to purchasing —Seward Kollie (Dakar, Senegal)
other books in the series.”
—To m Dierickx (Malta, IL) “I am an avid purchaser and reader of the Visual
series, and they are the greatest computer books
“I have quite a few of your Visual books and have I've seen. Thank you ver y much for the hard work,
been ver y pleased with all of them. I love the way ef for t, and dedication that you put into this
the lessons are presented!” series.”
—Ma ry Jane Newman (Yo rba Linda, CA) —Alex Diaz (Las Ve gas, NV)
Ju ly 05
01_132296 ffirs.qxp 5/24/07 12:39 PM Page iv
Credits
Project Editor Layout
Sarah Hellert Carrie A. Foster
Jennifer Mayberry
Melanee Prendergast
Acquisitions Editor
Heather Ryan
Jody Lefevere
Amanda Spagnuolo
Copy Editor
Screen Artist
Lauren Kennedy
Jill A. Proll
Technical Editor
Cover Illustration
Namir Shammas
Elizabeth Cardenas-Nelson
Editorial Manager
Proofreader
Robyn Siesky
Laura L. Bowman
Business Manager
Quality Control
Amy Knies
Cynthia Fields
Media Projects Supervisor
Indexer
Laura Moss-Hollister
Broccoli Information
Media Development Specialist
Wiley Bicentennial Logo
Josh Frank
Richard J. Pacifico
Manufacturing
Special Help
Allan Conley
Barbara Moore
Linda Cook
Paul Gilchrist
Jennifer Guynn Vice President and Executive
Group Publisher
Book Design Richard Swadley
Kathryn Rickard
Vice President Publisher
Production Coordinator Barry Pruett
Adrienne Martinez
Composition Director
Debbie Stailey
01_132296 ffirs.qxp 5/24/07 12:39 PM Page v
About the Author
Denise Etheridgeis a certified public accountant as well as the president and founder of Baycon Group,
Inc. She publishes Web sites, provides consulting services on accounting-related software, and authors
computer-related books. You can visit www.baycongroup.com to view her online tutorials.
Author’s Acknowledgments
I would like to thank all of the people at Wiley who assisted me in writing this book, with particular
thanks to Jody Lefevere, Sarah Hellert, Namir Shammas, and Lauren Kennedy.
I would also like to thank Malinda McCain for her assistance on this and many other projects.
This book is dedicated to my parents, Frederick and Catherine Etheridge.
02_132296 ftoc.qxp 5/24/07 12:41 PM Page vi
TABLE OF CONTENTS
CHAPTER 1: GETTING STARTED . . . . . . . . . . . . . . . . . . . . .2
Introduction to Data Analysis with Excel ..............................................................2
Understanding the Excel Window..........................................................................3
Enter Data ............................................................................................................4
Format Numbers....................................................................................................6
Format Cells ........................................................................................................10
Select Data ..........................................................................................................14
Copy, Cut, and Paste Cells....................................................................................16
Copy with the Office Clipboard ............................................................................18
Insert and Delete Cells ........................................................................................20
Find and Replace ................................................................................................22
Find and Replace Formats ..................................................................................24
CHAPTER 2: CREATING FORMULAS . . . . . . . . . . . . . . . . .26
Understanding Formulas ....................................................................................26
Create Formulas ..................................................................................................28
Edit Formulas......................................................................................................30
Name Cells and Ranges ......................................................................................32
Define and Display Constants..............................................................................34
Create Formulas that Include Names....................................................................36
Check Formulas for Errors ..................................................................................38
Trace Precedents and Dependents........................................................................40
CHAPTER 3: CREATING AND USING FUNCTIONS . . . . . .42
Understanding the Function Wizard....................................................................42
Round a Number ................................................................................................44
Determine the Nth Largest Value ........................................................................46
Create a Conditional Formula ..............................................................................48
Calculate a Conditional Sum................................................................................50
Calculate Products and Square Roots ..................................................................52
Look Up Information ..........................................................................................54
Determine the Location of a Value ......................................................................56
Perform Time Calculations ..................................................................................58
Perform Date Calculations....................................................................................60
vi
02_132296 ftoc.qxp 5/24/07 12:41 PM Page vii
CHAPTER 4: USING FINANCIAL FUNCTIONS . . . . . . . . . .62
Calculate Future Value ........................................................................................62
Calculate Present Value........................................................................................64
Calculate Loan Payments ....................................................................................66
Calculate Principal or Interest..............................................................................68
Calculate the Interest Rate ..................................................................................70
Calculate the Internal Rate of Return ..................................................................72
Calculate Straight-Line Depreciation....................................................................74
Calculate Declining Balance Depreciation ............................................................76
Calculate Double-Declining Balance Depreciation................................................78
Calculate Sum-of-the-Years-Digits Depreciation ..................................................80
CHAPTER 5: USING STATISTICAL FUNCTIONS
AND TOOLS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .82
Calculate an Average ..........................................................................................82
Calculate the Median or the Mode........................................................................84
Calculate Rank ....................................................................................................86
Calculate Frequency ............................................................................................88
Calculate Variance and Standard Deviation..........................................................90
Find the Correlation ............................................................................................92
Install Excel Add-Ins ..........................................................................................94
Calculate a Moving Average ................................................................................96
Compare Variances..............................................................................................98
Using the Data Analysis Toolpak to Determine Rank and Percentile ................100
Calculate Descriptive Statistics ..........................................................................102
CHAPTER 6: ORGANIZING WORKSHEET DATA . . . . . . .104
Enter Data with a Form ....................................................................................104
Filter Duplicate Records ....................................................................................106
Perform Simple Sorts and Filters ......................................................................108
Perform Complex Sorts......................................................................................110
Sort by Cell Color, Font Color, or Icon................................................................112
Perform Complex Filters....................................................................................114
Enter Criteria to Find Records............................................................................116
vii