ebook img

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

338 Pages·2013·57.48 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

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

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 (978) 750-8400, fax (978) 646-8600. 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 317-572-4002. 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 (877) 762-2974 or fax (317) 572-4002. 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

Description:
Aimed at Excel power users who appreciate logical, clean explanations of techniques, this visual guide features numerous screenshots and easy-to-follow numbered steps in order to show you how to perform professional-level modeling, charting, data sharing, data access, data slicing, and other functio
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.