### Available:*

Library | Call Number | Material Type | Home Location | Status | Item Holds |
---|---|---|---|---|---|

Searching... | HF5548.4.M523 N4515 2002 | Adult Non-Fiction | Central Closed Stacks | Searching... | Searching... |

### On Order

### Summary

### Summary

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 ModelingIf 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

Introduction | p. 1 |

About This Book | p. 1 |

What You Can Safely Ignore | p. 1 |

What You Shouldn't Ignore (Unless You're a Masochist) | p. 2 |

Three Foolish Assumptions | p. 2 |

How This Book is Organized | p. 3 |

Special Icons | p. 4 |

Where to Next? | p. 5 |

Part I Where's the Beef? | p. 7 |

Chapter 1 Introducing Excel Lists | p. 9 |

What Is a List and Why Do I Care? | p. 9 |

Building Lists | p. 12 |

Analyzing List Information | p. 18 |

Chapter 2 Grabbing Data from External Sources | p. 31 |

Getting Data the Export-Import Way | p. 31 |

Querying External Databases and Web Pages Tables | p. 43 |

It's Sometimes a Raw Deal | p. 54 |

Chapter 3 Scrub-a-Dub-Dub: Cleaning Data | p. 55 |

Editing Your Imported Workbook | p. 55 |

Cleaning Data with Text Functions | p. 60 |

Using Validation to Keep Data Clean | p. 72 |

Part II PivotTables and PivotCharts | p. 77 |

Chapter 4 Working with PivotTables | p. 79 |

Looking at Data from Many Angles | p. 79 |

Getting Ready to Pivot | p. 80 |

Running the PivotTable Wizard | p. 81 |

Fooling Around with Your Pivot Table | p. 87 |

Customizing How Pivot Tables Work and Look | p. 100 |

Chapter 5 Building PivotTable Formulas | p. 105 |

Adding Another Standard Calculation | p. 105 |

Creating Custom Calculations | p. 108 |

Using Calculated Fields and Items | p. 113 |

Retrieving Data from a Pivot Table | p. 121 |

Chapter 6 Working with PivotCharts | p. 125 |

Why Use a PivotChart? | p. 125 |

Getting Ready to Pivot | p. 126 |

Running the PivotTable Wizard | p. 127 |

Fooling Around with Your Pivot Chart | p. 133 |

Using Chart Wizard to Create PivotCharts | p. 142 |

Chapter 7 Customizing PivotCharts | p. 145 |

Selecting a Chart Type | p. 145 |

Setting Chart Options | p. 147 |

Changing a Chart's Location | p. 152 |

Formatting the Plot Area | p. 153 |

Formatting the Chart Area | p. 154 |

Formatting 3-D Charts | p. 156 |

Formatting Gridlines | p. 157 |

Formatting Legends | p. 159 |

Formatting Axes | p. 160 |

Part III Advanced Tools | p. 166 |

Chapter 8 Using the Database Functions | p. 167 |

Quickly Reviewing Functions | p. 167 |

Using the DAVERAGE Function | p. 172 |

Using the DCOUNT and DCOUNTA Functions | p. 176 |

Using the DGET Function | p. 178 |

Using the DMAX and DMAX Functions | p. 179 |

Using the DPRODUCT Function | p. 181 |

Using the DSTDEV and DSTDEVP Functions | p. 181 |

Using the DSUM Function | p. 183 |

Using the DVAR and DVARP Functions | p. 184 |

Chapter 9 Using the Statistics Functions | p. 187 |

Counting Items in a Data Set | p. 187 |

Means, Modes, and Medians | p. 190 |

Finding Values, Ranks, and Percentiles | p. 194 |

Standard Deviations and Variances | p. 201 |

Normal Distributions | p. 205 |

T-distributions | p. 210 |

F-distributions | p. 211 |

Binomial Distributions | p. 212 |

Chi-Square Distributions | p. 215 |

Regression Analysis | p. 217 |

Correlation | p. 220 |

Some Really Esoteric Probability Distributions | p. 222 |

Chapter 10 Descriptive Statistics | p. 229 |

Using the Descriptive Statistics Tool | p. 230 |

Creating a Histogram | p. 233 |

Ranking by Percentile | p. 237 |

Calculating Moving Averages | p. 239 |

Exponential Smoothing | p. 241 |

Generating Random Numbers | p. 244 |

Sampling Data | p. 245 |

Chapter 11 Inferential Statistics | p. 249 |

Using the t-test Data Analysis Tool | p. 250 |

Performing z-test Calculations | p. 253 |

Creating a Scatter Plot | p. 255 |

Using the Regression Data Analysis Tool | p. 261 |

Using the Correlation Analysis Tool | p. 264 |

Using the Covariance Analysis Tool | p. 265 |

Using the ANOVA Data Analysis Tools | p. 267 |

Creating an f-test Analysis | p. 268 |

Using Fourier Analysis | p. 268 |

Chapter 12 Optimization Modeling with Solver | p. 271 |

Understanding Optimization Modeling | p. 272 |

Setting Up a Solver Worksheet | p. 273 |

Solving an Optimization Modeling Problem | p. 276 |

Reviewing the Solver Reports | p. 280 |

Working with the Solver Options | p. 284 |

Setting a Limit on Solver | p. 285 |

Understanding the Solver Error Messages | p. 288 |

Part IV The Part of Tens | p. 293 |

Chapter 13 Almost Ten Things You Ought to Know about Statistics | p. 295 |

Descriptive Statistics Are Straightforward | p. 296 |

Averages Aren't So Simple Sometimes | p. 296 |

Standard Deviations Describe Dispersion | p. 297 |

An Observation Is an Observation | p. 298 |

A Sample Is a Subset of Values | p. 299 |

Inferential Statistics Are Cool but Complicated | p. 299 |

Probability Distribution Functions Aren't Always Confusing | p. 300 |

Parameters Aren't So Complicated | p. 303 |

Skewness and Kurtosis Describe a Probability Distribution's Shape | p. 303 |

Chapter 14 Almost Ten Tips for Presenting List Results and Analyzing Data | p. 305 |

Work Hard to Import Data | p. 305 |

Design Information Systems to Produce Rich Data | p. 306 |

Don't Forget about Third-Party Sources | p. 307 |

Just Add It | p. 307 |

Always Explore Descriptive Statistics | p. 308 |

Watch for Trends | p. 308 |

Slicing and Dicing: Cross-Tabulation | p. 309 |

Chart It, Baby | p. 309 |

Be Aware of Inferential Statistics | p. 309 |

Chapter 15 Ten Tips for Visually Analyzing and Presenting Data | p. 311 |

Using the Right Chart Type | p. 311 |

Using Your Chart Message as the Chart Title | p. 313 |

Beware of Pie Charts | p. 314 |

Consider Using Pivot Charts for Small Data Sets | p. 314 |

Avoiding 3-D Charts | p. 316 |

Never Use 3-D Pie Charts | p. 318 |

Be Aware of the Phantom Data Markers | p. 318 |

Use Logarithmic Scaling | p. 319 |

Don't Forget to Experiment | p. 321 |

Get Tufte | p. 321 |

Part V Appendix | p. 323 |

Appendix Glossary of Data Analysis and Excel Terms | p. 325 |

Index | p. 335 |