Excel data analysis : your visual blueprint for analyzing data, charts, and PivotTables

338 Pages·2013·57.48 MB·English

by  McFedries

Excel® Data Analysis
Your visual blueprint™ for analyzing data,
charts, and PivotTables, 4th Edition
Paul McFedries
Excel® Data Analysis: Your visual blueprint™ for LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND
analyzing data, charts, and PivotTables, 4th Edition THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT
TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK
Published by
AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT
John Wiley & Sons, Inc.
LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO
10475 Crosspoint Boulevard
WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL
Indianapolis, IN 46256
MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY
www.wiley.com NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH
Published simultaneously in Canada THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN
RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF
Copyright © 2013 by John Wiley & Sons, Inc., Indianapolis, Indiana
PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT
No part of this publication may be reproduced, stored in a retrieval PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER
system or transmitted in any form or by any means, electronic, mechani- NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM.
cal, photocopying, recording, scanning or otherwise, except as permit- THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN
ted under Sections 107 or 108 of the 1976 United States Copyright THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER
Act, without either the prior written permission of the Publisher, or INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER
authorization through payment of the appropriate per-copy fee to the ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY
Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS
[phone], fax [phone]. Requests to the Publisher for per- SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK
mission should be addressed to the Permissions Department, John Wiley MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS
& Sons, Inc., 111 River Street, Hoboken, NJ 07030, 201-748-6011, fax WRITTEN AND WHEN IT IS READ.
201-748-6008, or online at www.wiley.com/go/permissions.
FOR PURPOSES OF ILLUSTRATING THE CONCEPTS AND TECHNIQUES
Wiley publishes in a variety of print and electronic formats and by print- DESCRIBED IN THIS BOOK, THE AUTHOR HAS CREATED VARIOUS NAMES,
on-demand. Some material included with standard print versions of this COMPANY NAMES, MAILING, E-MAIL AND INTERNET ADDRESSES, PHONE
book may not be included in e-books or in print-on-demand. If this book AND FAX NUMBERS AND SIMILAR INFORMATION, ALL OF WHICH ARE
refers to media such as a CD or DVD that is not included in the version FICTITIOUS. ANY RESEMBLANCE OF THESE FICTITIOUS NAMES, ADDRESSES,
you purchased, you may download this material at http://booksupport. PHONE AND FAX NUMBERS AND SIMILAR INFORMATION TO ANY ACTUAL
wiley.com. For more information about Wiley products, visit www.wiley. PERSON, COMPANY AND/OR ORGANIZATION IS UNINTENTIONAL AND
com. PURELY COINCIDENTAL.
Library of Congress Control Number: 2013938097
Contact Us
ISBN: 978-1-118-51714-7
For general information on our other products and services please con-
Manufactured in the United States of America
tact our Customer Care Department within the U.S. at 877-762-2974,
10 9 8 7 6 5 4 3 2 1 outside the U.S. at 317-572-3993 or fax [phone].
For technical support please visit www.wiley.com/techsupport.
Trademark Acknowledgments
Wiley, the Wiley 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. Excel is a
registered trademark of Microsoft Corporation in the United States and/
or other countries. All other trademarks are the property of their respec-
tive owners. John Wiley & Sons, Inc. is not associated with any product
or vendor mentioned in this book.
Sales | Contact Wiley at [phone] or fax [phone].
Credits
Executive Editor Project Coordinator
Jody Lefevere Katherine Crocker
Project Editor Graphics and Production Specialists
Jade L. Williams Ronda David-Burroughs
Andrea Hornberger
Technical Editor
Jennifer Mayberry
Namir Shammas
Quality Control Technician
Copy Editor
Jessica Kramer
Marylouise Wiack
Proofreading
Editorial Director
Joni Heredia Language Services
Robyn Siesky
Indexing
Business Manager
Potomac Indexing, LLC
Amy Knies
Senior Marketing Manager
Sandy Smith
Vice President and Executive
Group Publisher
Richard Swadley
Vice President and Executive
Publisher
Barry Pruett
About the Author
Paul McFedries is a full-time technical writer. He has been authoring computer
books since 1991 and has more than 80 books to his credit, including Teach
Yourself VISUALLY Excel 2013, Teach Yourself VISUALLY Windows 8, Windows 8 Visual
Quick Tips, The Facebook Guide for People Over 50, iPhone 5 Portable Genius, and
iPad 4th Generation and iPad mini Portable Genius, all available from Wiley. Paul’s
books have sold more than 4 million copies worldwide. Paul is also the proprietor
of Word Spy (http://www.wordspy.com), a website that tracks new words
and phrases as they enter the English language. Paul invites you to drop by his
personal website at www.mcfedries.com, or you can follow him on Twitter
@paulmcf and @wordspy.
Author’s Acknowledgments
It goes without saying that writers focus on text and I certainly enjoyed focusing
on the text that you will read in this book. However, this book is more than just
the usual collection of words and phrases designed to educate and stimulate the
mind. A quick thumb through the pages will show you that this book is also chock
full of treats for the eye, including copious screen shots, meticulous layouts, and
sharp fonts. Those sure make for a beautiful book and that beauty comes from a lot
of hard work by Wiley’s immensely talented group of designers and layout artists.
They are all listed in the Credits section on the previous page, and I thank them for
creating another gem. Of course, what you read in this book must also be accurate,
logically presented, and free of errors. Ensuring all this was an excellent group of
editors that I got to work with directly, including project editor Jade Williams,
copy editor Marylouise Wiack, and technical editor Namir Shammas. Thanks to all
of you for your exceptional competence and hard work. Thanks, as well, to Wiley
Executive Editor Jody Lefevere for asking me to write this book.
How to Use This Visual Blueprint Book
Who This Book Is For 3 Icons and Buttons
This book is for advanced computer users who want to Icons and buttons show you exactly what you need to
take their knowledge of this particular technology or click to perform a step.
software application to the next level.
4 Extra or Apply It
The Conventions in This Book An Extra section provides additional information about
the preceding task — insider information and tips for
1 Steps ease and efficiency. An Apply It section takes the code
This book uses a step-by-step format to guide you easily from the preceding task one step further and allows you
through each task. Numbered steps are actions you must to take full advantage of it.
do; bulleted steps clarify a point, step, or optional
5 Bold
feature; and indented steps give you the result.
Bold type shows text or numbers you must type.
2 Notes
6 Italics
Notes give additional information — special conditions
Italic type introduces and defines a new term.
that may occur during an operation, a situation that you
want to avoid, or a cross reference to a related area of 7 Courier Font
the book.
Courier font indicates the use of scripting language
code such as statements, operators, or functions, and
code such as objects, methods, or properties.
CHAPTER
3
LOOK UP A VALUE Enhancing Formulas with Functions
Usae r aEnxgceel ’asn ldo orkeutupr nfu an cctoiornress tpoo nlodoinkg u6 ipt eam v afrloume wthitahti n ayonud wcoanlt_ iton dloeoxk_ nupu;m t (aobr lreo_wa_rirnadye xis_ nthuem rfaonrg He LoOf OvKaUluPe)s ; 7 4 cTyopluem an c (oimf ymoau (a,)re f oulsloinwge Vd LbOyO tKhUeP )
range. For example, you can look up an income value in a is the column (or row) number within table_array that or row (if you are using HLOOKUP)
tax table and return the corresponding tax rate. To look up contains the value to retrieve. An optional fourth argument number that contains the value you
a value within a column, use VLOOKUP; to look up a value is range_lookup: if you omit it, the function looks for the want to retrieve.
within a row, use HLOOKUP. These functions have three closest match; if you set it to FALSE, the function looks for 5 Type a closing parenthesis, ).
required arguments: lookup_value specifies the value an exact match. 6 Click the Enter button or press
Enter.
Look Up a Value
1 1 In the cell where you want the
r=evtlroieovkeudp v(a olur e= thol oaopkpuepar(,. type 5
2 Type or select the value you
want to look up.
A Excel evaluates the logical
expression and then displays the
result in the cell.
3 Type a comma (,) followed by
the address of the lookup range.
2
Note: Make sure the first column of
the selected range is the column you
want to use for the lookup.
4
EXTRA
If you are searching text data, make sure the column you are searching does not contain any nonprinting
characters, or leading or trailing spaces, and that quotation marks are used consistently. If you are searching for
numbers or dates, make sure they are not formatted as text. If the first column of the table is text, you can use the
standard wildcard characters in the lookup_value argument (use ? to substitute for individual characters; use *
to substitute for multiple characters). For the col_index_num (or row_index_num for HLOOKUP) argument, the
first column is 1, the second column is 2, and so on. Finally, if you omit range_lookup, you must sort the values in
the first column in ascending order.
T C
able of onTenTs
Chapter 1 Building Formulas for Data Analysis
Introducing Data Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Introducing Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Understanding Formula Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Build a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Add a Range Name to a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Reference Another Worksheet Range in a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Move or Copy a Formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Switch to Absolute Cell References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Create an Array Formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Turn On Iterative Calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Chapter 2 Troubleshooting Formulas
Understanding Error Values in Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Show Formulas Instead of Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Use a Watch Window to Monitor a Cell Value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Step Through a Formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Display Text Instead of Error Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Check for Formula Errors in a Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Audit a Formula to Locate Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Chapter 3 Enhancing Formulas with Functions
Understanding Excel Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Understanding Function Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Add a Function to a Formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Add a Row or Column of Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Build an AutoSum Formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Round a Number. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Create a Conditional Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Calculate a Conditional Sum. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Calculate a Conditional Count. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Find the Square Root. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Retrieve a Column or Row Number. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Look Up a Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Determine the Location of a Value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Return a Cell Value with INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Perform Date and Time Calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Chapter 4 Analyzing Financial Data
Calculate Future Value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Calculate Present Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Determine the Loan Payments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Calculate the Principal or Interest. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Find the Required Interest Rate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Determine the Internal Rate of Return. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Calculate Straight-Line Depreciation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Return the Fixed-Declining Balance Depreciation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Determine the Double-Declining Balance Depreciation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Figure the Sum-of-the-Years-Digits Depreciation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
T C
able of onTenTs
Chapter 5 Analyzing Statistical Data
Calculate an Average. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Calculate a Conditional Average . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Determine the Median or the Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Find the Rank. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Determine the Nth Largest or Smallest Value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Create a Grouped Frequency Distribution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Calculate the Variance and Standard Deviation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Find the Correlation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Chapter 6 Building Tables for Data Analysis
Understanding Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Introducing Table Features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Convert a Range to a Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Select Table Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Insert a Table Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Insert a Table Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Delete a Table Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Delete a Table Column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Add a Column Subtotal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104