Cover image for Excel data analysis for dummies
Excel data analysis for dummies
Nelson, Stephen L., 1959-
Personal Author:
Publication Information:
New York : Wiley, [2002]

Physical Description:
xxii, 360 pages : illustrations ; 24 cm.
General Note:
Includes index.
Electronic Access:
Table of contents
Format :


Call Number
Material Type
Home Location
Item Holds
HF5548.4.M523 N4515 2002 Adult Non-Fiction Central Closed Stacks

On Order



So here's a funny deal: You know how to use Excel. You know how to create simple workbooks. And how to print stuff. And you can even, with just a little bit of fiddling, create cool-looking charts. But sometimes you wish that you could do more with Excel, such as how to use Excel to really gain insights into information, the data, that you work with in your job.

Using Excel for this kind of stuff is what Excel Data Analysis For Dummies is all about. This is a book that assumes that you want to use Excel to learn new stuff, discover new secrets, and gain new insights into the information you're already working with in Excel.

Ready to take Excel to the next level? This plain-English guide covers all of these concepts, and more, to ensure that you're using Excel to its fullest capacity:

Harnessing information in lists Querying external databases and Web pages tables Cleaning data with text functions Building and working with PivotTables Customizing PivotCharts Using the statistics functions Understanding Optimization Modeling

If all this talk of statistics and advanced tools makes you light-headed, don't worry. Excel Data Analysis For Dummies distills the most important fundamentals into everyday language. You'll find just enough information to help you get your work done ? without leaving you gasping for air in a sea of technobabble. It'd be a real shame if you didn't at least know what bells and whistles Excel has to offer and the basic steps that you need to use them.

Author Notes

Stephen L. Nelson, MBA, CPA, is the bestselling author of more than 100 books on computer and business topics

Table of Contents

Introductionp. 1
About This Bookp. 1
What You Can Safely Ignorep. 1
What You Shouldn't Ignore (Unless You're a Masochist)p. 2
Three Foolish Assumptionsp. 2
How This Book is Organizedp. 3
Special Iconsp. 4
Where to Next?p. 5
Part I Where's the Beef?p. 7
Chapter 1 Introducing Excel Listsp. 9
What Is a List and Why Do I Care?p. 9
Building Listsp. 12
Analyzing List Informationp. 18
Chapter 2 Grabbing Data from External Sourcesp. 31
Getting Data the Export-Import Wayp. 31
Querying External Databases and Web Pages Tablesp. 43
It's Sometimes a Raw Dealp. 54
Chapter 3 Scrub-a-Dub-Dub: Cleaning Datap. 55
Editing Your Imported Workbookp. 55
Cleaning Data with Text Functionsp. 60
Using Validation to Keep Data Cleanp. 72
Part II PivotTables and PivotChartsp. 77
Chapter 4 Working with PivotTablesp. 79
Looking at Data from Many Anglesp. 79
Getting Ready to Pivotp. 80
Running the PivotTable Wizardp. 81
Fooling Around with Your Pivot Tablep. 87
Customizing How Pivot Tables Work and Lookp. 100
Chapter 5 Building PivotTable Formulasp. 105
Adding Another Standard Calculationp. 105
Creating Custom Calculationsp. 108
Using Calculated Fields and Itemsp. 113
Retrieving Data from a Pivot Tablep. 121
Chapter 6 Working with PivotChartsp. 125
Why Use a PivotChart?p. 125
Getting Ready to Pivotp. 126
Running the PivotTable Wizardp. 127
Fooling Around with Your Pivot Chartp. 133
Using Chart Wizard to Create PivotChartsp. 142
Chapter 7 Customizing PivotChartsp. 145
Selecting a Chart Typep. 145
Setting Chart Optionsp. 147
Changing a Chart's Locationp. 152
Formatting the Plot Areap. 153
Formatting the Chart Areap. 154
Formatting 3-D Chartsp. 156
Formatting Gridlinesp. 157
Formatting Legendsp. 159
Formatting Axesp. 160
Part III Advanced Toolsp. 166
Chapter 8 Using the Database Functionsp. 167
Quickly Reviewing Functionsp. 167
Using the DAVERAGE Functionp. 172
Using the DCOUNT and DCOUNTA Functionsp. 176
Using the DGET Functionp. 178
Using the DMAX and DMAX Functionsp. 179
Using the DPRODUCT Functionp. 181
Using the DSTDEV and DSTDEVP Functionsp. 181
Using the DSUM Functionp. 183
Using the DVAR and DVARP Functionsp. 184
Chapter 9 Using the Statistics Functionsp. 187
Counting Items in a Data Setp. 187
Means, Modes, and Mediansp. 190
Finding Values, Ranks, and Percentilesp. 194
Standard Deviations and Variancesp. 201
Normal Distributionsp. 205
T-distributionsp. 210
F-distributionsp. 211
Binomial Distributionsp. 212
Chi-Square Distributionsp. 215
Regression Analysisp. 217
Correlationp. 220
Some Really Esoteric Probability Distributionsp. 222
Chapter 10 Descriptive Statisticsp. 229
Using the Descriptive Statistics Toolp. 230
Creating a Histogramp. 233
Ranking by Percentilep. 237
Calculating Moving Averagesp. 239
Exponential Smoothingp. 241
Generating Random Numbersp. 244
Sampling Datap. 245
Chapter 11 Inferential Statisticsp. 249
Using the t-test Data Analysis Toolp. 250
Performing z-test Calculationsp. 253
Creating a Scatter Plotp. 255
Using the Regression Data Analysis Toolp. 261
Using the Correlation Analysis Toolp. 264
Using the Covariance Analysis Toolp. 265
Using the ANOVA Data Analysis Toolsp. 267
Creating an f-test Analysisp. 268
Using Fourier Analysisp. 268
Chapter 12 Optimization Modeling with Solverp. 271
Understanding Optimization Modelingp. 272
Setting Up a Solver Worksheetp. 273
Solving an Optimization Modeling Problemp. 276
Reviewing the Solver Reportsp. 280
Working with the Solver Optionsp. 284
Setting a Limit on Solverp. 285
Understanding the Solver Error Messagesp. 288
Part IV The Part of Tensp. 293
Chapter 13 Almost Ten Things You Ought to Know about Statisticsp. 295
Descriptive Statistics Are Straightforwardp. 296
Averages Aren't So Simple Sometimesp. 296
Standard Deviations Describe Dispersionp. 297
An Observation Is an Observationp. 298
A Sample Is a Subset of Valuesp. 299
Inferential Statistics Are Cool but Complicatedp. 299
Probability Distribution Functions Aren't Always Confusingp. 300
Parameters Aren't So Complicatedp. 303
Skewness and Kurtosis Describe a Probability Distribution's Shapep. 303
Chapter 14 Almost Ten Tips for Presenting List Results and Analyzing Datap. 305
Work Hard to Import Datap. 305
Design Information Systems to Produce Rich Datap. 306
Don't Forget about Third-Party Sourcesp. 307
Just Add Itp. 307
Always Explore Descriptive Statisticsp. 308
Watch for Trendsp. 308
Slicing and Dicing: Cross-Tabulationp. 309
Chart It, Babyp. 309
Be Aware of Inferential Statisticsp. 309
Chapter 15 Ten Tips for Visually Analyzing and Presenting Datap. 311
Using the Right Chart Typep. 311
Using Your Chart Message as the Chart Titlep. 313
Beware of Pie Chartsp. 314
Consider Using Pivot Charts for Small Data Setsp. 314
Avoiding 3-D Chartsp. 316
Never Use 3-D Pie Chartsp. 318
Be Aware of the Phantom Data Markersp. 318
Use Logarithmic Scalingp. 319
Don't Forget to Experimentp. 321
Get Tuftep. 321
Part V Appendixp. 323
Appendix Glossary of Data Analysis and Excel Termsp. 325
Indexp. 335