Excel Data Analysis: Your visual blueprint for creating and analyzing data, charts and PivotTables

371 Pages·2010·33.01 MB·English

by  Denise Etheridge

spine=.8638"
Denise Etheridge
Excel® Data Analysis 3rd Edition
Welcome to the only guidebook series that takes a visual approach to professional-level computer
topics. Open the book and you’ll discover step-by-step screen shots that demonstrate over 190
Microsoft®
key Excel data analysis techniques, including:
EM Excel® Data Analysis
(cid:129) Using Web-based applications in Excel (cid:129) Creating PivotTables and using Slicer
i
c
(cid:129) Creating formulas and using functions (cid:129) Performing “what-if” analysis xro
s
(cid:129) Calculating interest rates and depreciation (cid:129) Automating Excel with macros o
f
(cid:129) Working with statistical functions (cid:129) Adding illustrations to worksheets ct® 3rd Edition
(cid:129) Using database functions (cid:129) Customizing the Ribbon e
l
®

D
a
t
a

A
n
a
(cid:129) High-resolution screen shots
l
demonstrate each task
y
(cid:129) Succinct explanations walk you
through step by step s
i
(cid:129) T wo-page lessons break big topics
s
into bite-sized modules Companion Web site includes
example files to illustrate
(cid:129) “ Apply It” and “Extra” sidebars
highlight useful tips the instructions
3rd Edition
Your visual blueprint for creating and
™
Desktop Applications/Spreadsheets Visual
Blueprint
$29.99 US analyzing data, charts, and PivotTables
www.wiley.com/go/visual
$35.99 CAN
www.wiley.com/go/exceldataanalysis
£21.99 UK Etheridge
0011__558844776677--ffffiirrss..iinndddd iiii 55//2200//1100 99::5533 PPMM
Excel® Data Analysis
Your visual blueprint™ for creating and
analyzing data, charts, and
PivotTables, 3rd Edition
by Denise Etheridge
0011__559911660044--ffffiirrss..iinndddd ii 55//2255//1100 1122::3344 PPMM
Excel® Data Analysis: Your visual blueprint™ for FOR PURPOSES OF ILLUSTRATING THE CONCEPTS AND TECHNIQUES
creating and analyzing data, charts, and PivotTables, DESCRIBED IN THIS BOOK, THE AUTHOR HAS CREATED VARIOUS
NAMES, COMPANY NAMES, MAILING, E-MAIL AND INTERNET
3rd Edition
ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR
Published by INFORMATION, ALL OF WHICH ARE FICTITIOUS. ANY RESEMBLANCE
Wiley Publishing, Inc. OF THESE FICTITIOUS NAMES, ADDRESSES, PHONE AND FAX
10475 Crosspoint Boulevard NUMBERS AND SIMILAR INFORMATION TO ANY ACTUAL PERSON,
Indianapolis, IN 46256 COMPANY AND/OR ORGANIZATION IS UNINTENTIONAL AND
PURELY COINCIDENTAL.
www.wiley.com
Published simultaneously in Canada Contact Us
Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana For general information on our other products and services please contact
our Customer Care Department within the U.S. at 877-762-2974, outside
No part of this publication may be reproduced, stored in a retrieval system
the U.S. at 317-572-3993 or fax [phone].
or transmitted in any form or by any means, electronic, mechanical,
photocopying, recording, scanning or otherwise, except as permitted For technical support please visit www.wiley.com/techsupport.
under Sections 107 or 108 of 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 to the
Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923,
[phone], fax [phone]. Requests to the Publisher for
permission should be addressed to the Permissions Department, John
The Metropolitan Cathedral of Brasilia
Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, 201-748-6011,
fax [phone], or online at www.wiley.com/go/permissions. A vision of architect Oscar Niemeyer, the breathtaking
Metropolitan Cathedral of Brasilia reflects the innovative
Library of Congress Control Number: 2010928469
architecture and design that characterize this young and
ISBN: 978-0-470-59160-4 original city. Sweeping skyward, the Cathedral’s sixteen
gracefully curved columns symbolize hands lifted in prayer,
Manufactured in the United States of America while the unique bell tower replicates a candelabra. Begun in
1959, the Cathedral was designated a Historic Monument in
10 9 8 7 6 5 4 3 2 1 1967, thereby accessing public funds for its completion. It was
dedicated in 1970.
Learn more about
Trademark Acknowledgments
Brasilia’s unusual
Wiley, the Wiley Publishing logo, Visual, the Visual logo, Visual Blueprint, history and
Read Less - Learn More and related trade dress are trademarks or architecture in
registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. Excel Frommer’s Brazil, 5th
is a registered trademark of Microsoft Corporation in the United States Edition (ISBN 978-0-
and/or other countries. All other trademarks are the property of their 470-59151-2),
respective owners. Wiley Publishing, Inc. is not associated with any available wherever
product or vendor mentioned in this book. books are sold or at
www.Frommers.com.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER
AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES
WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE
Disclaimer
CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL
WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF
In order to get this information to you in a timely manner, this book was
FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE
CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. based on a pre-release version of Microsoft Office 2010. There may be
THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE some minor changes between the screenshots in this book and what you
SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE see on your desktop. As always, Microsoft has the final word on how
UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN programs look and function; if you have any questions or see any
RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL discrepancies, consult the online help for further information about the
SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE software.
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 Sales
INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE
Contact Wiley
PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR
WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. at [phone]
FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES or [phone].
LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED
BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
0011__559911660044--ffffiirrss..iinndddd iiii 55//2255//1100 1122::3344 PPMM
Credits
Executive Editor Project Coordinator
Jody Lefevere Lynsey Stanford
Project Editor Graphics and Production Specialists
Jade L. Williams Carrie Cesavice
Jennifer Mayberry
Technical Editor
Namir Shammas Quality Control Technician
Lauren Mandelbaum
Copy Editor
Lauren Kennedy Proofreading
Sossity R. Smith
Editorial Director
Robyn Siesky Indexing
Johnna VanHoose Dinse
Business Manager
Amy Knies Media Development Project Manager
Laura Moss
Senior Marketing Manager
Sandy Smith Media Development Assistant Project
Manager
Vice President and Executive Group Jenny Swisher
Publisher
Richard Swadley Media Development Associate
Producer
Vice President and Executive Publisher Shawn Patrick
Barry Pruett
Screen Artist
Ana Carrillo
Jill A. Proll
Illustrator
Cheryl Grubbs
Special Help
Microsoft Corporation, Inc.
0011__559911660044--ffffiirrss..iinndddd iiiiii 55//2255//1100 1122::3344 PPMM
About the Author
Denise Etheridge is a certified public accountant as well as the president and founder of Baycon Group,
Inc. She publishes Web sites and authors’ computer related books. You can visit www.baycongroup.com to
view her online tutorials.
Author’s Acknowledgments
Writing this book was an absolute privilege. I would like to thank all of the people who assisted me. I give
special thanks to Jody Lefevere, for allowing me this privilege; Jade Williams, for keeping things on track;
Namir Shammas, for his technical review; and Lauren Kennedy, for her copy review.
Dedication
This book is dedicated to my brother, Erskine Etheridge.
0011__559911660044--ffffiirrss..iinndddd iivv 55//2255//1100 1122::3344 PPMM
How to Use This Visual Blueprint Book
Who This Book Is For 4 Extra or Apply It
This book is for advanced computer users who want An Extra section provides additional information
to take their knowledge of this particular technology about the preceding task — insider information and
or software application to the next level. tips for ease and efficiency. An Apply It section takes
the code from the preceding task one step further
The Conventions in This Book and allows you to take full advantage of it.
1 Steps 5 Bold
This book uses a step-by-step format to guide you Bold type shows text or numbers you must type.
easily through each task. Numbered steps are
6 Italics
actions you must do; bulleted steps clarify a point,
step, or optional feature; and indented steps give Italic type introduces and defines a new term.
you the result.
7 Courier Font
2 Notes Courier font indicates the use of scripting
Notes give additional information — special language code such as statements, operators, or
conditions that may occur during an operation, a functions, and code such as objects, methods,
situation that you want to avoid, or a cross reference or properties.
to a related area of the book.
3 Icons and Buttons
Icons and buttons show you exactly what you need
to click to perform a step.
67 W=YsyBUciefcnoaouxo ouscrCCluicu‘uecsmlshlu u Bh tdtcu uidluhe anaarasleelsesnagtsun e h f eIiatctdafiulenneolad enllcscr finyald ocecsdte nr trmhuico uslaiettodaotne,sS ha et Cl vn e o itilsbftenaunele aso,lhycdl cl;s alcadpau e alle o ucuol ’r ce crcvpiPlawcd tauecaaateeilie+aotlilvcnrllacneesutl uaog u idte,ge2rntlt e ala oemT sPoda0dgFfcftr aott0 ieehiishefiaeblnrdr0 e dl me eseliv dsfe erulnfioav,fid uF, x c deccrlesalyuihmelaias ldnoaoesdl w:ticusadlu. i ou nie,iscl Fn tagsatlc gwhao +.a al a lbltrhfil,TnYcee e odoiefie–d odscxclnx,e u. hd r P fialat*etrF sdir,oemy an. oo fl aotcoudrbYnjpenaue rsoloa elcmde n;lumuxct e, leibas ua/ doyewc;e unml n d roaat y e.au bpfino acytla eadauteoslen ,udre asr , cudyLclabIPClaifaaroeoasiis bsvellytleluxccet ecaoo u u.arutlt,tfu sehl llToclltaa. a iahr afiwtttstY leehbeie ucaoodddrlelusendenu i ela,fififfstas oo lc eec;titoyrr anehlloFmgeddond o i y ssu epoubt fioeoh llisoldnenaenuaeex ,lcols r ad y ref t It ePsohil t olonpey pirie asvnmlm oom aytroLgur ecsuoototiere , Tsuld f arfi atca a rciv al waf nbaFtyawylelldlloaoci c erlou nretuusa.Shmrx lt, blYake ewaicultsttso etoosefatlehu a oi dlnidunuoe nsr t censgmmfi fi aotett e ehnoucntp.alh lad enl tdOnualile oasocdPinss un tbOn e ici utvl enp .epaht uosll hYtsteatcentiee,hooiTrcd ddr eeoeuna Σ ea bn rfi sbrd tcya V lereaate moldaanty dwabF,loe s u a i su eteol.orlssrd o 6789•• TaocYTCdTnDeiTaaCnpaxnshfhiyeyoaloa ili ppwpddeelctucsu+lcho e ee keut b2uItcecifiag finhlnlOaaa0l aareea nseFgnl b0sntK-cltnei ied ecno0ve ufi.oaardp.dlalgx.tlimtedp la c l afiluFt aettkdeLhcheeiepr e eiealade,tfpsdl oo tn d stteeo fifi .hr i uanunre eetr cdsl lh sddhe e.. 76 89 Chapter 9: Working with PivotTables 5
the Σ Values box.
1 Create a PivotTable Calculated Field • Values for the
1 Click any field in your 2 cthaelc dualatate adr efiae.ld fill
PivotTable.
The PivotTable tools
appear.
2• CIfl itchke tPhiev oOtpTtaiobnles F tiaebld. List 1
does not open, click Field
List.
4
34 CCSAlle iimcctskke. nCFuiae llacdpuspl,a eItatioermsn.ss., & 3 45 IyciCnfoh a yauyloconruu ugP lerani v toPtoiho itvleonTo nsfato.Tgb rCealmerbl irucwleekla.ap Fn,Co ityrel iottlc,douk ys ioc,tn hauIcten lec u Ommadnpseo , tdd ai&oei fc lnySea steli ecttt.u sa i Ctlbaa.l .ntiI cefCd kdy l ti oachfiuknee ynlwdw c ahilnniec trk et o YfiIwcteoeehllumldi c i.cn h SaI fyoyn ooyl vbouueaur w Os Pdeair ovndoo,ten tyrieTot d acutiboa aml llcepoua.eg ylTr a fbhwtooeeradx mnP tfiti ov yetoo oltdtue Tu lrola s cnEeba x latlecchn euteolol attCohhtalieesol r cnoa ucpsrald.ap leCcteerual diilcrna .k t ead 3
5 Click Calculated Field. Calculated Field. The Insert Calculated Field dialog box Click the Options tab. Click Calculations. Click Fields,
appears. Click the next to the Name field. Click the Items, & Sets and then click Solve Order. The Calculated
field you want to modify or delete. Click Modify if you Item Solve Order dialog box appears. Use the Move Up
want to modify the field. Excel makes the formula button to move a calculation up in the solve order. Use
available for modification. Click Delete if you want to the Move Down button to move a calculation down in
delete the field. Excel deletes the field. the solve order.
0011__559911660044--ffffiirrss..iinndddd vv 55//2255//1100 1122::3344 PPMM
TABLE OF CONTENTS
HOW TO USE THIS BOOK . . . . . . . . . . . . . . . . . . . . . . V
1 GETTING STARTED . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Introducing Data Analysis with Excel ....................................................................2
Tour the Excel Window ..........................................................................................3
Explore the Ribbon ................................................................................................4
Using the Mini Toolbar and Context Menu ............................................................5
Select Options on the Status Bar ............................................................................6
Take a Look at Backstage View .............................................................................7
Upload a File to Excel’s Web-based Application ....................................................8
Create a File Using Excel’s Web-based Application ..............................................10
Enter Data ............................................................................................................12
Select Cells ...........................................................................................................14
2 FORMATTING A WORKSHEET . . . . . . . . . . . . . . . . 16
Using the Ribbon to Format Numbers ..................................................................16
Using the Format Cells Dialog Box .......................................................................18
Understanding Dates and Times ..........................................................................20
Format Percentages ..............................................................................................22
Format Fractions ..................................................................................................23
Format in Scientific Notation ...............................................................................24
Format as Text .....................................................................................................25
Add a Border .......................................................................................................26
Change the Font or Font Size ...............................................................................28
Add a Background Color ......................................................................................30
Change the Font Color..........................................................................................31
Bold, Underline, or Italicize ..................................................................................32
Align Data ...........................................................................................................33
Rotate Data ..........................................................................................................34
Wrap Text ............................................................................................................35
Merge and Center .................................................................................................36
Apply a Style .......................................................................................................37
Using Format Painter ...........................................................................................38
Clear Formats .......................................................................................................39
vi
0022__559911660044--ffttoocc..iinndddd vvii 55//2255//1100 1122::3322 PPMM
3 STRUCTURING YOUR WORKBOOK . . . . . . . . . . . . 40
Cut, Copy, and Paste Cells ....................................................................................40
Using Live Preview with Paste .............................................................................42
Paste from the Office Clipboard ............................................................................44
Insert or Delete ....................................................................................................46
Find and Replace Information ..............................................................................48
Change the Name of a Worksheet ........................................................................50
Change Column Widths or Row Heights ..............................................................51
Hide Columns or Rows ........................................................................................52
Hide a Worksheet ................................................................................................53
Move or Copy a Worksheet ..................................................................................54
Freeze Worksheet Titles .......................................................................................56
Hide Gridlines, Headings, or the Formula Bar......................................................57
4 CREATING FORMULAS . . . . . . . . . . . . . . . . . . . . . . 58
Understanding Formulas ......................................................................................58
Calculate with an Operator ...................................................................................60
Calculate Using a Function and Cell Addresses ....................................................61
Create an Array Formula ......................................................................................62
Using the Sum, Average, Count, Min, and Max Functions ...................................64
Create a Formula that Refers to Another Worksheet ............................................65
Understanding Relative and Absolute Cell Addresses ..........................................66
Edit Formulas ......................................................................................................68
Name Cells and Ranges ........................................................................................70
Define and Display Constants ..............................................................................72
Create Formulas That Include Names ...................................................................74
Check Formulas for Errors ...................................................................................76
Trace Precedents and Dependents ........................................................................78
5 USING FUNCTIONS . . . . . . . . . . . . . . . . . . . . . . . . . 80
Understanding the Function Wizard ....................................................................80
Round a Number .................................................................................................82
Create a Conditional Formula ...............................................................................84
Calculate a Conditional Sum .................................................................................86
Calculate a Conditional Count ..............................................................................87
Find the Square Root ...........................................................................................88
vii
0022__559911660044--ffttoocc..iinndddd vviiii 55//2255//1100 1122::3322 PPMM
TABLE OF CONTENTS
Retrieve Column or Row Numbers .......................................................................89
Using VLOOKUP ..................................................................................................90
Determine the Location of a Value ......................................................................92
Using INDEX .......................................................................................................93
Perform Date and Time Calculations ....................................................................94
6 USING FINANCIAL FUNCTIONS . . . . . . . . . . . . . . . 96
Calculate Future Value .........................................................................................96
Calculate Present Value ........................................................................................98
Calculate Loan Payments ...................................................................................100
Calculate Principal or Interest .............................................................................102
Calculate the Interest Rate .................................................................................104
Calculate the Internal Rate of Return .................................................................106
Calculate Straight-Line Depreciation ..................................................................108
Calculate Declining Balance Depreciation ...........................................................110
Calculate Double-Declining Balance Depreciation ...............................................112
Calculate Sum-of-the-Years-Digits Depreciation.................................................114
7 USING STATISTICAL FUNCTIONS AND TOOLS. . . 116
Calculate an Average .........................................................................................116
Calculate a Conditional Average .........................................................................117
Calculate the Median or the Mode ......................................................................118
Calculate Rank ...................................................................................................120
Determine the Nth Largest Value .......................................................................122
Calculate Frequency ...........................................................................................124
Calculate Variance and Standard Deviation ........................................................126
Find the Correlation ...........................................................................................128
Install Excel Add-Ins..........................................................................................130
Calculate a Moving Average ...............................................................................132
Compare Variances ............................................................................................134
Using the Data Analysis Toolpak to Determine Rank and Percentile .................136
Calculate Descriptive Statistics ...........................................................................138
8 ORGANIZING WORKSHEET DATA . . . . . . . . . . . . 140
Enter Data with a Form .....................................................................................140
Perform Simple Sorts and Filters ........................................................................142
Perform Multilevel Sorts ....................................................................................144
Perform a Custom Sort .......................................................................................146
viii
0022__559911660044--ffttoocc..iinndddd vviiiiii 55//2255//1100 1122::3322 PPMM