Excel Data Analysis for Dummies

✍ Scribed by Nelson, Stephen L

• Publisher: For Dummies
• Year: 2014
• Tongue: English
• Leaves: 363
• Series: For Dummies
• Category: Library

✦ Synopsis

What the book covers: Focuses on the features of Excel that are important to businesses and researchers including working with external databases, PivotTables and Pivot Charts, using Excel for statistical and financial functions, data sharing, working with Solver, using the Small Business Finance Manager, and more.Updated coverage of the new version: A technology update to the existing edition for Excel 2013. This new edition will have approximately 30% new content covering the latest updates and enhancements made to Microsoft Excel.Series features: Information presented in the straightforward but fun language that has defined the "Dummies" series.

✦ Table of Contents

Title Page......Page 3
Copyright Page......Page 4
Contents at a Glance......Page 5
Table of Contents......Page 7
What You Can Safely Ignore......Page 15
What You Shouldn’t Ignore (Unless You’re a Masochist)......Page 16
How This Book Is Organized......Page 17
Icons Used in This Book......Page 18
Where to Go from Here......Page 19
Part I: Where’s the Beef?......Page 21
What Is a Table and Why Do I Care?......Page 23
Building Tables......Page 26
Analyzing Table Information......Page 30
Getting Data the Export-Import Way......Page 45
Querying External Databases and Web Page Tables......Page 58
It’s Sometimes a Raw Deal......Page 69
Editing Your Imported Workbook......Page 71
Cleaning Data with Text Functions......Page 77
Using Validation to Keep Data Clean......Page 88
Part II: PivotTables and PivotCharts......Page 93
Looking at Data from Many Angles......Page 95
Getting Ready to Pivot......Page 96
Running the PivotTable Wizard......Page 97
Fooling Around with Your Pivot Table......Page 101
Customizing How Pivot Tables Work and Look......Page 113
Adding Another Standard Calculation......Page 121
Creating Custom Calculations......Page 125
Using Calculated Fields and Items......Page 129
Retrieving Data from a Pivot Table......Page 137
Why Use a Pivot Chart?......Page 141
Getting Ready to Pivot......Page 142
Running the PivotTable Wizard......Page 143
Fooling Around with Your Pivot Chart......Page 147
Using Chart Commands to Create Pivot Charts......Page 153
Selecting a Chart Type......Page 155
Working with Chart Styles......Page 156
Changing Chart Layout......Page 157
Changing a Chart’s Location......Page 164
Formatting the Chart Area......Page 166
Formatting 3-D Charts......Page 168
Part III: Advanced Tools......Page 169
Quickly Reviewing Functions......Page 171
Using the DAVERAGE Function......Page 177
Using the DCOUNT and DCOUNTA Functions......Page 180
Using the DGET Function......Page 182
Using the DMAX and DMAX Functions......Page 183
Using the DPRODUCT Function......Page 184
Using the DSTDEV and DSTDEVP Functions......Page 185
Using the DSUM Function......Page 187
Using the DVAR and DVARP Functions......Page 188
Counting Items in a Data Set......Page 191
Means, Modes, and Medians......Page 195
Finding Values, Ranks, and Percentiles......Page 199
Standard Deviations and Variances......Page 206
Normal Distributions......Page 211
t-distributions......Page 216
f-distributions......Page 219
Binomial Distributions......Page 221
Chi-Square Distributions......Page 225
Regression Analysis......Page 229
Correlation......Page 232
Some Really Esoteric Probability Distributions......Page 233
Chapter 10: Descriptive Statistics......Page 239
Using the Descriptive Statistics Tool......Page 240
Creating a Histogram......Page 244
Ranking by Percentile......Page 247
Calculating Moving Averages......Page 249
Exponential Smoothing......Page 251
Generating Random Numbers......Page 253
Sampling Data......Page 255
Chapter 11: Inferential Statistics......Page 259
Using the t-test Data Analysis Tool......Page 260
Performing z-test Calculations......Page 263
Creating a Scatter Plot......Page 265
Using the Regression Data Analysis Tool......Page 268
Using the Correlation Analysis Tool......Page 271
Using the Covariance Analysis Tool......Page 272
Using the ANOVA Data Analysis Tools......Page 274
Creating an f-test Analysis......Page 275
Using Fourier Analysis......Page 276
Understanding Optimization Modeling......Page 277
Setting Up a Solver Worksheet......Page 279
Solving an Optimization Modeling Problem......Page 282
Reviewing the Solver Reports......Page 287
Working with the Solver Options......Page 291
Understanding the Solver Error Messages......Page 296
Part IV: The Part of Tens......Page 301
Chapter 13: Ten Things You Ought to Know about Statistics......Page 303
Averages Aren’t So Simple Sometimes......Page 304
Standard Deviations Describe Dispersion......Page 305
An Observation Is an Observation......Page 306
Inferential Statistics Are Cool but Complicated......Page 307
Probability Distribution Functions Aren’t Always Confusing......Page 308
Parameters Aren’t So Complicated......Page 310
Confidence Intervals Seem Complicated at First, but Are Useful......Page 311
Work Hard to Import Data......Page 315
Design Information Systems to Produce Rich Data......Page 316
Just Add It......Page 317
Watch for Trends......Page 318
Be Aware of Inferential Statistics......Page 319
Using the Right Chart Type......Page 321
Using Your Chart Message as the Chart Title......Page 323
Consider Using Pivot Charts for Small Data Sets......Page 324
Avoiding 3-D Charts......Page 326
Never Use 3-D Pie Charts......Page 327
Be Aware of the Phantom Data Markers......Page 328
Use Logarithmic Scaling......Page 329
Get Tufte......Page 331
Appendix: Glossary of Data Analysis and Excel Terms......Page 333
Index......Page 343
About the Authors......Page 361

✦ Subjects

Reference;Nonfiction