Excel 2007 Data Analysis For Dummies

by Nelson, Stephen L.

• ISBN13: 9780470045992
• ISBN10: 047004599X
• Format: Paperback
• Copyright: 2007-02-20
• Publisher: For Dummies

Summary

Shows ordinary users how to tap the rich data analysis functionality of Excel, make sense of their organization's critical financial and statistical information, and put together compelling data presentations Now revised with over 30 percent new content to cover the enhancements in Excel 2007, including the completely redesigned user interface, augmented charting and PivotTable capabilities, improved security, and better data exchange through XML Provides thorough coverage of Excel features that are critical to data analysis-working with external databases, creating PivotTables and PivotCharts, using Excel statistical and financial functions, sharing data, harnessing the Solver, taking advantage of the Small Business Finance Manager, and more

Author Biography

Stephen L. Nelson, MBA, CPA, provides accounting, business advisory, tax planning, and tax preparation services to small businesses. He belongs to the American Institute of CPAs and is a prolific author.

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. 3
How This Book Is Organized	p. 3
Where's the Beef?	p. 3
PivotTables and PivotCharts	p. 3
Advanced Tools	p. 4
The Part of Tens	p. 4
Appendix	p. 4
Special Icons	p. 5
Where to Next?	p. 5
Where's the Beef?	p. 7
Introducing Excel Tables	p. 9
What Is a Table and Why Do I Care?	p. 9
Building Tables	p. 12
Exporting from a database	p. 12
Building a table the hard way	p. 12
Building a table the semi-hard way	p. 13
Analyzing Table Information	p. 15
Simple statistics	p. 17
Sorting table records	p. 19
Using AutoFilter on a table	p. 21
Undoing a filter	p. 23
Turning off filter	p. 23
Using the custom AutoFilter	p. 23
Filtering a filtered table	p. 25
Using advanced filtering	p. 26
Grabbing Data from External Sources	p. 31
Getting Data the Export-Import Way	p. 31
Exporting: The first step	p. 32
Importing: The second step (if necessary)	p. 36
Querying External Databases and Web Page Tables	p. 45
Running a Web query	p. 45
Importing a database table	p. 47
Querying an external database	p. 50
It's Sometimes a Raw Deal	p. 56
Scrub-a-Dub-Dub: Cleaning Data	p. 57
Editing Your Imported Workbook	p. 57
Delete unnecessary columns	p. 58
Delete unnecessary rows	p. 58
Resize columns	p. 59
Resize rows	p. 60
Erase unneeded cell contents	p. 61
Format numeric values	p. 61
Copying worksheet data	p. 61
Moving worksheet data	p. 62
Replacing data in fields	p. 62
Cleaning Data with Text Functions	p. 63
What's the big deal, Steve?	p. 63
The answer to some of your problems	p. 65
The Clean function	p. 65
The Concatenate function	p. 66
The Exact function	p. 66
The Find function	p. 67
The Fixed function	p. 68
The Left function	p. 68
The Len function	p. 68
The Lower function	p. 69
The Mid function	p. 69
The Proper function	p. 70
The Replace function	p. 70
The Rept function	p. 70
The Right function	p. 71
The Search function	p. 71
The Substitute function	p. 72
The T function	p. 72
The Text function	p. 73
The Trim function	p. 73
The Upper function	p. 73
The Value function	p. 74
Converting text function formulas to text	p. 74
Using Validation to Keep Data Clean	p. 75
PivotTables and PivotCharts	p. 79
Working with PivotTables	p. 81
Looking at Data from Many Angles	p. 81
Getting Ready to Pivot	p. 82
Running the PivotTable Wizard	p. 83
Fooling Around with Your Pivot Table	p. 88
Pivoting and re-pivoting	p. 88
Filtering pivot table data	p. 88
Refreshing pivot table data	p. 92
Sorting pivot table data	p. 92
Pseudo-sorting	p. 94
Grouping and ungrouping data items	p. 95
Selecting this, selecting that	p. 97
Where did that cell's number come from?	p. 97
Setting value field settings	p. 97
Customizing How Pivot Tables Work and Look	p. 100
Setting pivot table options	p. 100
Formatting pivot table information	p. 103
Building PivotTable Formulas	p. 107
Adding Another Standard Calculation	p. 107
Creating Custom Calculations	p. 111
Using Calculated Fields and Items	p. 115
Adding a calculated field	p. 115
Adding a calculated item	p. 117
Removing calculated fields and items	p. 119
Reviewing calculated field and calculated item formulas	p. 121
Reviewing and changing solve order	p. 122
Retrieving Data from a Pivot Table	p. 123
Getting all the values in a pivot table	p. 123
Getting a value from a pivot table	p. 124
Arguments of the GETPIVOTDATA function	p. 126
Working with PivotCharts	p. 127
Why Use a PivotChart?	p. 127
Getting Ready to Pivot	p. 128
Running the PivotTable Wizard	p. 129
Fooling Around with Your Pivot Chart	p. 134
Pivoting and re-pivoting	p. 134
Filtering pivot chart data	p. 135
Refreshing pivot chart data	p. 137
Grouping and ungrouping data items	p. 138
Using Chart Commands to Create Pivot Charts	p. 139
Customizing PivotCharts	p. 143
Selecting a Chart Type	p. 143
Working with Chart Layouts	p. 144
Working with Chart Styles	p. 144
Setting Chart Options	p. 144
Chart titles	p. 145
Chart legend	p. 146
Chart data labels	p. 147
Chart data tables	p. 149
Chart axes	p. 150
Chart gridlines	p. 152
Changing a Chart's Location	p. 152
Formatting the Plot Area	p. 154
Formatting the Chart Area	p. 155
Chart fill patterns	p. 155
Chart area fonts	p. 155
Formatting 3-D Charts	p. 156
Formatting the walls of a 3-D chart	p. 156
Using the 3-D View command	p. 156
Advanced Tools	p. 157
Using the Database Functions	p. 159
Quickly Reviewing Functions	p. 159
Understanding function syntax rules	p. 160
Entering a function manually	p. 160
Entering a function with the Function command	p. 161
Using the DAVERAGE Function	p. 165
Using the DCOUNT and DCOUNTA Functions	p. 168
Using the DGET Function	p. 170
Using the DMAX and DMAX Functions	p. 172
Using the DPRODUCT Function	p. 174
Using the DSTDEV and DSTDEVP Functions	p. 174
Using the DSUM Function	p. 176
Using the DVAR and DVARP Functions	p. 178
Using the Statistics Functions	p. 181
Counting Items in a Data Set	p. 181
COUNT: Counting cells with values	p. 181
COUNTA: Alternative counting cells with values	p. 182
COUNTBLANK: Counting empty cells	p. 183
COUNTIF: Counting cells that match criteria	p. 183
PERMUT: Counting permutations	p. 184
COMBIN: Counting combinations	p. 184
Means, Modes, and Medians	p. 184
AVEDEV: An average absolute deviation	p. 185
AVERAGE: Average	p. 185
AVERAGEA: An alternate average	p. 186
TRIMMEAN: Trimming to a mean	p. 186
MEDIAN: Median value	p. 187
MODE: Mode value	p. 187
GEOMEAN: Geometric mean	p. 188
HARMEAN: Harmonic mean	p. 188
Finding Values, Ranks, and Percentiles	p. 188
MAX: Maximum value	p. 188
MAXA: Alternate maximum value	p. 189
MIN: Minimum value	p. 189
MINA: Alternate minimum value	p. 189
LARGE: Finding the kth largest value	p. 189
SMALL: Finding the kth smallest value	p. 190
RANK: Ranking an array value	p. 190
PERCENTRANK: Finding a percentile ranking	p. 191
PERCENTILE: Finding a percentile ranking	p. 192
FREQUENCY: Frequency of values in a range	p. 193
PROB: Probability of values	p. 194
Standard Deviations and Variances	p. 195
STDEV: Standard deviation of a sample	p. 195
STDEVA: Alternate standard deviation of a sample	p. 196
STDEVP: Standard deviation of a population	p. 196
STDEVPA: Alternate standard deviation of a population	p. 197
VAR: Variance of a sample	p. 197
VARA: Alternate variance of a sample	p. 198
VARP: Variance of a population	p. 198
VARPA: Alternate variance of a population	p. 198
COVAR: Covariance	p. 199
DEVSQ: Sum of the squared deviations	p. 199
Normal Distributions	p. 199
NORMDIST: Probability X falls at or below a given value	p. 199
NORMINV: X that gives specified probability	p. 200
NORMSDIST: Probability variable within z-standard deviations	p. 201
NORMSINV: z-value equivalent to a probability	p. 201
STANDARDIZE: z-value for a specified value	p. 202
CONFIDENCE: Confidence interval for a population mean	p. 202
KURT: Kurtosis	p. 203
SKEW: Skewness of a distribution	p. 204
t-distributions	p. 204
TDIST: Probability of given t-value	p. 204
TINV: t-value of a given probability	p. 205
TTEST: Probability two samples from same population	p. 205
f-distributions	p. 206
FDIST: f-distribution probability	p. 206
FINV: f-value given f-distribution probability	p. 206
FTEST: Probability data set variances not different	p. 207
Binomial Distributions	p. 207
BINOMDIST: Binomial probability distribution	p. 207
NEGBINOMDIST: Negative binominal distribution	p. 208
CRITBINOM: Cumulative binomial distribution	p. 209
HYFGEOMDIST: Hypergeometric distribution	p. 209
Chi-Square Distributions	p. 210
CHIDIST: Chi-square distribution	p. 210
CHIINV: Chi-square value for a given level of significance	p. 211
CHITEST: Chi-square test	p. 212
Regression Analysis	p. 212
FORECAST: Forecast dependent variables using a best-fit line	p. 213
INTERCEPT: y-axis intercept of a line	p. 213
Linest	p. 213
SLOPE: Slope of a regression line	p. 214
STEYX: Standard error	p. 214
Trend	p. 214
LOGEST: Exponential regression	p. 214
GROWTH: Exponential growth	p. 215
Correlation	p. 215
CORREL: Correlation coefficient	p. 215
PEARSON: Pearson correlation coefficient	p. 216
RSQ: r-squared value for a Pearson correlation coefficient	p. 216
Fisher	p. 216
Fisherinv	p. 216
Some Really Esoteric Probability Distributions	p. 217
BETADIST: Cumulative beta probability density	p. 217
BETAINV: Inverse cumulative beta probability density	p. 217
EXPONDIST: Exponential probability distribution	p. 218
GAMMADIST: Gamma distribution probability	p. 218
GAMMAINV: X for a given gamma distribution probability	p. 219
GAMMALN: Natural logarithm of a gamma distribution	p. 219
LOGNORMDIST: Probability of lognormal distribution	p. 220
LOGINV: Value associated with lognormal distribution probability	p. 220
POISSON: Poisson distribution probabilities	p. 220
WEIBULL: Weibull distribution	p. 221
ZTEST: Probability of a z-test	p. 221
Descriptive Statistics	p. 223
Using the Descriptive Statistics Tool	p. 224
Creating a Histogram	p. 228
Ranking by Percentile	p. 231
Calculating Moving Averages	p. 233
Exponential Smoothing	p. 235
Generating Random Numbers	p. 238
Sampling Data	p. 240
Inferential Statistics	p. 245
Using the t-test Data Analysis Tool	p. 246
Performing z-test Calculations	p. 248
Creating a Scatter Plot	p. 250
Using the Regression Data Analysis Tool	p. 255
Using the Correlation Analysis Tool	p. 256
Using the Covariance Analysis Tool	p. 259
Using the Anova Data Analysis Tools	p. 260
Creating an f-test Analysis	p. 261
Using Fourier Analysis	p. 262
Optimization Modeling with Solver	p. 263
Understanding Optimization Modeling	p. 264
Optimizing your imaginary profits	p. 264
Recognizing constraints	p. 264
Setting Up a Solver Worksheet	p. 265
Solving an Optimization Modeling Problem	p. 268
Reviewing the Solver Reports	p. 273
The Answer Report	p. 273
The Sensitivity Report	p. 274
The Limits Report	p. 276
Some other notes about Solver reports	p. 277
Working with the Solver Options	p. 277
Setting a limit on Solver	p. 278
Deciding how nit-picky to be	p. 278
Saying when	p. 279
When you assume...	p. 279
Using automatic scaling	p. 280
Showing iteration results	p. 280
Tangent versus quadratic estimates	p. 280
Forward versus central derivatives	p. 280
Newton versus conjugate algorithms	p. 281
Saving and reusing model information	p. 281
Understanding the Solver Error Messages	p. 282
Solver has converged to the current solution	p. 282
Solver cannot improve the current solution	p. 282
Stop chosen when maximum time limit was reached	p. 283
Stop chosen when maximum iteration limit was reached	p. 283
Set target cell values do not converge	p. 283
Solver could not find a feasible solution	p. 284
Conditions for assume linear model are not satisfied	p. 284
Solver encountered an error value in a target or constraint cell	p. 284
There is not enough memory available to solve the problem	p. 285
The Part of Tens	p. 287
Almost Ten Things You Ought to Know about Statistics	p. 289
Descriptive Statistics Are Straightforward	p. 290
Averages Aren't So Simple Sometimes	p. 290
Standard Deviations Describe Dispersion	p. 291
An Observation Is an Observation	p. 292
A Sample Is a Subset of Values	p. 293
Inferential Statistics Are Cool but Complicated	p. 293
Probability Distribution Functions Aren't Always Confusing	p. 294
Uniform distribution	p. 295
Normal distribution	p. 295
Parameters Aren't So Complicated	p. 297
Skewness and Kurtosis Describe a Probability Distribution's Shape	p. 297
Almost Ten Tips for Presenting Table Results and Analyzing Data	p. 299
Work Hard to Import Data	p. 299
Design Information Systems to Produce Rich Data	p. 300
Don't Forget about Third-Party Sources	p. 301
Just Add It	p. 301
Always Explore Descriptive Statistics	p. 302
Watch for Trends	p. 302
Slicing and Dicing: Cross-Tabulation	p. 303
Chart It, Baby	p. 303
Be Aware of Inferential Statistics	p. 303
Ten Tips for Visually Analyzing and Presenting Data	p. 305
Using the Right Chart Type	p. 305
Using Your Chart Message as the Chart Title	p. 307
Beware of Pie Charts	p. 307
Consider Using Pivot Charts for Small Data Sets	p. 309
Avoiding 3-D Charts	p. 310
Never Use 3-D Pie Charts	p. 312
Be Aware of the Phantom Data Markers	p. 313
Use Logarithmic Scaling	p. 313
Don't Forget to Experiment	p. 316
Get Tufte	p. 316
Appendix	p. 319
Glossary of Data Analysis and Excel Terms	p. 321
Index	p. 331
Table of Contents provided by Ingram. All Rights Reserved.