Microsoft Excel Data Analysis For Dummies (3rd Ed.) - Wiley

387 Pages·2016·27.97 MB·English

Microsoft®
Excel ® Data
Analysis
3rd Edition
by Stephen L. Nelson and
Elizabeth C. Nelson
Microsoft® Excel® Data Analysis For Dummies®, 3rd Edition
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030‐5774, www.wiley.com
Copyright © 2016 by John Wiley & Sons, Inc., Hoboken, New Jersey
Media and software compilation copyright © 2016 by John Wiley & Sons, Inc. All rights reserved.
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or
by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-
ted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission
of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions
Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748‐6011, fax (201)
748‐6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and
related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be
used without written permission. All trademarks are the property of their respective owners. John Wiley &
Sons, Inc. is not associated with any product or vendor mentioned in this book.
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 CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING
WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY
BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES
CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE
UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR
OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE 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 DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE
INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY
MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK
MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT
IS READ.
For general information on our other products and services, please contact our Customer Care Department
within the U.S. at 877‐762‐2974, outside the U.S. at 317‐572‐3993, or fax 317‐572‐4002. For technical support,
please visit www.wiley.com/techsupport.
Wiley publishes in a variety of print and electronic formats and by print‐on‐demand. Some material
included with standard print versions of this book may not be included in e‐books or in print‐on‐demand.
If this book refers to media such as a CD or DVD that is not included in the version you purchased, you
may download this material at http://booksupport.wiley.com. For more information about Wiley
products, visit www.wiley.com.
Library of Congress Control Number: 2015955631
ISBN 978‐1‐119‐07720‐6 (pbk); ISBN 978‐1‐119‐07716‐9 (epub); 978‐1‐119‐07740‐4 (epdf)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Contents at a Glance
Introduction ................................................................ 1
Part I: Getting Started with Data Analysis..................... 7
Chapter 1: Introducing Excel Tables ...............................................................................9
Chapter 2: Grabbing Data from External Sources ........................................................31
Chapter 3: Scrub-a-Dub-Dub: Cleaning Data .................................................................59
Part II: PivotTables and PivotCharts ............................ 81
Chapter 4: Working with PivotTables ...........................................................................83
Chapter 5: Building PivotTable Formulas ...................................................................111
Chapter 6: Working with PivotCharts .........................................................................133
Chapter 7: Customizing PivotCharts ...........................................................................147
Part III: Advanced Tools ........................................... 161
Chapter 8: Using the Database Functions ...................................................................163
Chapter 9: Using the Statistics Functions ...................................................................183
Chapter 10: Descriptive Statistics................................................................................237
Chapter 11: Inferential Statistics ..................................................................................257
Chapter 12: Optimization Modeling with Solver ........................................................277
Part IV: The Part of Tens .......................................... 303
Chapter 13: Ten Things You Ought to Know about Statistics .................................305
Chapter 14: Almost Ten Tips for Presenting Table Results
and Analyzing Data ......................................................................................................317
Chapter 15: Ten Tips for Visually Analyzing and Presenting Data ..........................323
Appendix: Glossary of Data Analysis and Excel Terms ............................................335
Index ...................................................................... 345

Table of Contents
Introduction ................................................................. 1
About This Book ..............................................................................................1
What You Can Safely Ignore ...........................................................................1
What You Shouldn’t Ignore (Unless You’re a Masochist) ..........................2
Foolish Assumptions .......................................................................................3
How This Book Is Organized ..........................................................................3
Beyond the Book .............................................................................................5
Where to Go from Here ...................................................................................5
Part I: Getting Started with Data Analysis ..................... 7
Chapter 1: Introducing Excel Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
What Is a Table and Why Do I Care? .............................................................9
Building Tables ..............................................................................................12
Exporting from a database .................................................................12
Building a table the hard way.............................................................12
Building a table the semi-hard way ...................................................12
Analyzing Table Information ........................................................................16
Simple statistics ...................................................................................16
Sorting table records ...........................................................................18
Using AutoFilter on a table .................................................................21
Undoing a filter .....................................................................................23
Turning off filter ...................................................................................23
Using the custom AutoFilter ...............................................................23
Filtering a filtered table .......................................................................26
Using advanced filtering .....................................................................26
Chapter 2: Grabbing Data from External Sources . . . . . . . . . . . . . . . . . 31
Getting Data the Export-Import Way ...........................................................31
Exporting: The first step .....................................................................32
Importing: The second step (if necessary) .......................................37
Querying External Databases and Web Page Tables ................................45
Running a web query ...........................................................................45
Importing a database table .................................................................48
Querying an external database ..........................................................50
It’s Sometimes a Raw Deal ............................................................................56
vi
Excel Data Analysis For Dummies
Chapter 3: Scrub-a-Dub-Dub: Cleaning Data . . . . . . . . . . . . . . . . . . . . . 59
Editing Your Imported Workbook ...............................................................59
Delete unnecessary columns ..............................................................60
Delete unnecessary rows ....................................................................60
Resize columns.....................................................................................60
Resize rows ...........................................................................................62
Erase unneeded cell contents ............................................................62
Format numeric values .......................................................................63
Copying worksheet data .....................................................................63
Moving worksheet data .......................................................................64
Replacing data in fields .......................................................................64
Cleaning Data with Text Functions ..............................................................65
What’s the big deal, Steve? .................................................................65
The answer to some of your problems .............................................67
The CLEAN function ............................................................................67
The CONCATENATE function .............................................................68
The EXACT function ............................................................................68
The FIND function ................................................................................69
The FIXED function ..............................................................................70
The LEFT function................................................................................70
The LEN function .................................................................................70
The LOWER function ...........................................................................71
The MID function .................................................................................71
The PROPER function ..........................................................................72
The REPLACE function ........................................................................72
The REPT function ...............................................................................72
The RIGHT function .............................................................................73
The SEARCH function ..........................................................................73
The SUBSTITUTE function ..................................................................74
The T function ......................................................................................74
The TEXT function ...............................................................................75
The TRIM function ...............................................................................75
The UPPER function ............................................................................75
The VALUE function ............................................................................76
Converting text function formulas to text ........................................76
Using Validation to Keep Data Clean ..........................................................76
Part II: PivotTables and PivotCharts ............................ 81
Chapter 4: Working with PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Looking at Data from Many Angles .............................................................83
Getting Ready to Pivot ..................................................................................84
Running the PivotTable Wizard ...................................................................85
vii
Table of Contents
Fooling Around with Your Pivot Table .......................................................90
Pivoting and re-pivoting ......................................................................90
Filtering pivot table data .....................................................................91
Using a slicer or timeline ....................................................................92
Refreshing pivot table data ................................................................94
Sorting pivot table data .......................................................................95
Pseudo-sorting .....................................................................................96
Grouping and ungrouping data items ...............................................97
Selecting this, selecting that ...............................................................99
Where did that cell’s number come from? .......................................99
Setting value field settings ................................................................100
Customizing How Pivot Tables Work and Look .......................................102
Setting pivot table options ...............................................................102
Formatting pivot table information .................................................107
Chapter 5: Building PivotTable Formulas . . . . . . . . . . . . . . . . . . . . . . . 111
Adding Another Standard Calculation ......................................................111
Creating Custom Calculations ....................................................................115
Using Calculated Fields and Items .............................................................119
Adding a calculated field ...................................................................120
Adding a calculated item ..................................................................122
Removing calculated fields and items .............................................125
Reviewing calculated field and calculated item formulas ............126
Reviewing and changing solve order ..............................................127
Retrieving Data from a Pivot Table ...........................................................128
Getting all the values in a pivot table ..............................................128
Getting a value from a pivot table ...................................................130
Arguments of the GETPIVOTDATA function ..................................131
Chapter 6: Working with PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Why Use a Pivot Chart? ..............................................................................133
Getting Ready to Pivot ................................................................................134
Running the PivotChart Wizard .................................................................135
Fooling Around with Your Pivot Chart .....................................................140
Pivoting and re-pivoting ....................................................................140
Filtering pivot chart data ..................................................................141
Refreshing pivot chart data ..............................................................143
Grouping and ungrouping data items .............................................144
Using Chart Commands to Create Pivot Charts ......................................145
Chapter 7: Customizing PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Selecting a Chart Type ................................................................................147
Working with Chart Styles ..........................................................................148
Changing Chart Layout ...............................................................................149
Chart and axis titles ...........................................................................149
Chart legend .......................................................................................151
viii
Excel Data Analysis For Dummies
Chart data labels ................................................................................152
Chart data tables ................................................................................153
Chart axes ...........................................................................................155
Chart gridlines ....................................................................................156
Changing a Chart’s Location ......................................................................156
Formatting the Plot Area ............................................................................158
Formatting the Chart Area ..........................................................................158
Chart fill patterns ...............................................................................159
Chart area fonts .................................................................................159
Formatting 3-D Charts .................................................................................160
Formatting the walls of a 3-D chart..................................................160
Using the 3-D View command ...........................................................160
Part III: Advanced Tools ............................................ 161
Chapter 8: Using the Database Functions . . . . . . . . . . . . . . . . . . . . . . . 163
Quickly Reviewing Functions .....................................................................163
Understanding function syntax rules ..............................................164
Entering a function manually ...........................................................164
Entering a function with the Function command ..........................165
Using the DAVERAGE Function ..................................................................169
Using the DCOUNT and DCOUNTA Functions .........................................172
Using the DGET Function ............................................................................174
Using the DMAX and DMAX Functions .....................................................175
Using the DPRODUCT Function .................................................................177
Using the DSTDEV and DSTDEVP Functions ............................................178
Using the DSUM Function ...........................................................................180
Using the DVAR and DVARP Functions .....................................................181
Chapter 9: Using the Statistics Functions . . . . . . . . . . . . . . . . . . . . . . . 183
Counting Items in a Data Set ......................................................................183
COUNT: Counting cells with values .................................................184
COUNTA: Alternative counting cells with values ..........................184
COUNTBLANK: Counting empty cells .............................................185
COUNTIF: Counting cells that match criteria .................................185
COUNTIFS: Counting cells that match criteria ...............................186
PERMUT and PERMUTATIONA: Counting permutations ..............186
COMBIN: Counting combinations ....................................................187
Means, Modes, and Medians ......................................................................187
AVEDEV: An average absolute deviation ........................................188
AVERAGE: Average ............................................................................188
AVERAGEA: An alternate average ....................................................189
AVERAGEIF and AVERAGEIFS: Selective averages.........................189
TRIMMEAN: Trimming to a mean ....................................................190