Home Page Icon
Home Page
Table of Contents for
Cheat Sheet
Close
Cheat Sheet
by Stephen L. Nelson, E. C. Nelson
Excel Data Analysis For Dummies, 2nd Edition
Introduction
Introduction
About This Book
What You Can Safely Ignore
What You Shouldn’t Ignore (Unless You’re a Masochist)
Foolish Assumptions
How This Book Is Organized
Part I: Where’s the Beef?
Part II: PivotTables and PivotCharts
Part III: Advanced Tools
Part IV: The Part of Tens
Icons Used in This Book
Beyond the Book
Where to Go from Here
Part I: Where's the Beef?
Chapter 1: Introducing Excel Tables
What Is a Table and Why Do I Care?
Building Tables
Exporting from a database
Building a table the hard way
Building a table the semi-hard way
Analyzing Table Information
Simple statistics
Sorting table records
Using AutoFilter on a table
Undoing a filter
Turning off filter
Using the custom AutoFilter
Filtering a filtered table
Using advanced filtering
Chapter 2: Grabbing Data from External Sources
Getting Data the Export-Import Way
Exporting: The first step
Importing: The second step (if necessary)
Querying External Databases and Web Page Tables
Running a web query
Importing a database table
Querying an external database
It's Sometimes a Raw Deal
Chapter 3: Scrub-a-Dub-Dub: Cleaning Data
Editing Your Imported Workbook
Delete unnecessary columns
Delete unnecessary rows
Resize columns
Resize rows
Erase unneeded cell contents
Format numeric values
Copying worksheet data
Moving worksheet data
Replacing data in fields
Cleaning Data with Text Functions
What’s the big deal, Steve?
The answer to some of your problems
The CLEAN function
The CONCATENATE function
The EXACT function
The FIND function
The FIXED function
The LEFT function
The LEN function
The LOWER function
The MID function
The PROPER function
The REPLACE function
The REPT function
The RIGHT function
The SEARCH function
The SUBSTITUTE function
The T function
The TEXT function
The TRIM function
The UPPER function
The VALUE function
Converting text function formulas to text
Using Validation to Keep Data Clean
Part II: PivotTables and PivotCharts
Chapter 4: Working with PivotTables
Looking at Data from Many Angles
Getting Ready to Pivot
Running the PivotTable Wizard
Fooling Around with Your Pivot Table
Pivoting and re-pivoting
Filtering pivot table data
Refreshing pivot table data
Sorting pivot table data
Pseudo-sorting
Grouping and ungrouping data items
Selecting this, selecting that
Where did that cell’s number come from?
Setting value field settings
Customizing How Pivot Tables Work and Look
Setting pivot table options
Formatting pivot table information
Chapter 5: Building PivotTable Formulas
Adding Another Standard Calculation
Creating Custom Calculations
Using Calculated Fields and Items
Adding a calculated field
Adding a calculated item
Removing calculated fields and items
Reviewing calculated field and calculated item formulas
Reviewing and changing solve order
Retrieving Data from a Pivot Table
Getting all the values in a pivot table
Getting a value from a pivot table
Arguments of the GETPIVOTDATA function
Chapter 6: Working with PivotCharts
Why Use a Pivot Chart?
Getting Ready to Pivot
Running the PivotTable Wizard
Fooling Around with Your Pivot Chart
Pivoting and re-pivoting
Filtering pivot chart data
Refreshing pivot chart data
Grouping and ungrouping data items
Using Chart Commands to Create Pivot Charts
Chapter 7: Customizing PivotCharts
Selecting a Chart Type
Working with Chart Styles
Changing Chart Layout
Chart and axis titles
Chart legend
Chart data labels
Chart data tables
Chart axes
Chart gridlines
Changing a Chart’s Location
Formatting the Plot Area
Formatting the Chart Area
Chart fill patterns
Chart area fonts
Formatting 3-D Charts
Formatting the walls of a 3-D chart
Using the 3-D View command
Part III: Advanced Tools
Chapter 8: Using the Database Functions
Quickly Reviewing Functions
Understanding function syntax rules
Entering a function manually
Entering a function with the Function command
Using the DAVERAGE Function
Using the DCOUNT and DCOUNTA Functions
Using the DGET Function
Using the DMAX and DMAX Functions
Using the DPRODUCT Function
Using the DSTDEV and DSTDEVP Functions
Using the DSUM Function
Using the DVAR and DVARP Functions
Chapter 9: Using the Statistics Functions
Counting Items in a Data Set
COUNT: Counting cells with values
COUNTA: Alternative counting cells with values
COUNTBLANK: Counting empty cells
COUNTIF: Counting cells that match criteria
PERMUT: Counting permutations
COMBIN: Counting combinations
Means, Modes, and Medians
AVEDEV: An average absolute deviation
AVERAGE: Average
AVERAGEA: An alternate average
TRIMMEAN: Trimming to a mean
MEDIAN: Median value
MODE: Mode value
GEOMEAN: Geometric mean
HARMEAN: Harmonic mean
Finding Values, Ranks, and Percentiles
MAX: Maximum value
MAXA: Alternate maximum value
MIN: Minimum value
MINA: Alternate minimum value
LARGE: Finding the kth largest value
SMALL: Finding the kth smallest value
RANK: Ranking an array value
PERCENTRANK: Finding a percentile ranking
PERCENTILE: Finding a percentile ranking
FREQUENCY: Frequency of values in a range
PROB: Probability of values
Standard Deviations and Variances
STDEV: Standard deviation of a sample
STDEVA: Alternate standard deviation of a sample
STDEVP: Standard deviation of a population
STDEVPA: Alternate standard deviation of a population
VAR: Variance of a sample
VARA: Alternate variance of a sample
VARP: Variance of a population
VARPA: Alternate variance of a population
COVARIANCE.P and COVARIANCE.S: Covariances
DEVSQ: Sum of the squared deviations
Normal Distributions
NORM.DIST: Probability X falls at or below a given value
NORM.INV: X that gives specified probability
NORM.S.DIST: Probability variable within z-standard deviations
NORM.S.INV: z-value equivalent to a probability
STANDARDIZE: z-value for a specified value
CONFIDENCE: Confidence interval for a population mean
KURT: Kurtosis
SKEW and SKEW.P: Skewness of a distribution
t-distributions
T.DIST: Left-tail Student t-distribution
T.DIST.RT: Right-tail Student t-distribution
T.DIST.2T: Two-tail Student t-distribution
T.INV: Left-tailed Inverse of Student t-distribution
T.INV.2T: Two-tailed Inverse of Student t-distribution
T.TEST: Probability two samples from same population
f-distributions
F.DIST: Left-tailed f-distribution probability
F.DIST.RT: Right-tailed f-distribution probability
F.INV:Left-tailed f-value given f-distribution probability
F.INV.RT:Right-tailed f-value given f-distribution probability
F.TEST: Probability data set variances not different
Binomial Distributions
BINOM.DIST: Binomial probability distribution
BINOM.INV: Binomial probability distribution
BINOM.DIST.RANGE: Binomial probability of Trial Result
NEGBINOM.DIST: Negative binominal distribution
CRITBINOM: Cumulative binomial distribution
HYPGEOM.DIST: Hypergeometric distribution
Chi-Square Distributions
CHISQ.DIST.RT: Chi-square distribution
CHISQ.DIST: Chi-square distribution
CHISQ.INV.RT: Right-tailed chi-square distribution probability
CHISQ.INV: Left-tailed chi-square distribution probability
CHISQ.TEST: Chi-square test
Regression Analysis
FORECAST: Forecast dependent variables using a best-fit line
INTERCEPT: y-axis intercept of a line
LINEST
SLOPE: Slope of a regression line
STEYX: Standard error
TREND
LOGEST: Exponential regression
GROWTH: Exponential growth
Correlation
CORREL: Correlation coefficient
PEARSON: Pearson correlation coefficient
RSQ: r-squared value for a Pearson correlation coefficient
FISHER
FISHERINV
Some Really Esoteric Probability Distributions
BETA.DIST: Cumulative beta probability density
BETA.INV: Inverse cumulative beta probability density
EXPON.DIST: Exponential probability distribution
GAMMA.DIST: Gamma distribution probability
GAMMAINV: X for a given gamma distribution probability
GAMMALN: Natural logarithm of a gamma distribution
LOGNORMDIST: Probability of lognormal distribution
LOGINV: Value associated with lognormal distribution probability
POISSON: Poisson distribution probabilities
WEIBULL: Weibull distribution
ZTEST: Probability of a z-test
Chapter 10: Descriptive Statistics
Using the Descriptive Statistics Tool
Creating a Histogram
Ranking by Percentile
Calculating Moving Averages
Exponential Smoothing
Generating Random Numbers
Sampling Data
Chapter 11: Inferential Statistics
Using the t-test Data Analysis Tool
Performing z-test Calculations
Creating a Scatter Plot
Using the Regression Data Analysis Tool
Using the Correlation Analysis Tool
Using the Covariance Analysis Tool
Using the ANOVA Data Analysis Tools
Creating an f-test Analysis
Using Fourier Analysis
Chapter 12: Optimization Modeling with Solver
Understanding Optimization Modeling
Optimizing your imaginary profits
Recognizing constraints
Setting Up a Solver Worksheet
Solving an Optimization Modeling Problem
Reviewing the Solver Reports
The Answer Report
The Sensitivity Report
The Limits Report
Some other notes about Solver reports
Working with the Solver Options
Using the All Methods options
Using the GRG Nonlinear tab
Using the Evolutionary tab
Saving and reusing model information
Understanding the Solver Error Messages
Solver has found a solution
Solver has converged to the current solution
Solver cannot improve the current solution
Stop chosen when maximum time limit was reached
Solver stopped at user’s request
Stop chosen when maximum iteration limit was reached
Objective Cell values do not converge
Solver could not find a feasible solution
Linearity conditions required by this LP Solver are not satisfied
The problem is too large for Solver to handle
Solver encountered an error value in a target or constraint cell
There is not enough memory available to solve the problem
Error in model. Please verify that all cells and constraints are valid
Part IV: The Part of Tens
Chapter 13: Ten Things You Ought to Know about Statistics
Descriptive Statistics Are Straightforward
Averages Aren’t So Simple Sometimes
Standard Deviations Describe Dispersion
An Observation Is an Observation
A Sample Is a Subset of Values
Inferential Statistics Are Cool but Complicated
Probability Distribution Functions Aren't Always Confusing
Uniform distribution
Normal distribution
Parameters Aren't So Complicated
Skewness and Kurtosis Describe a Probability Distribution’s Shape
Confidence Intervals Seem Complicated at First, but Are Useful
Chapter 14: Almost Ten Tips for Presenting Table Results and Analyzing Data
Work Hard to Import Data
Design Information Systems to Produce Rich Data
Don’t Forget about Third-Party Sources
Just Add It
Always Explore Descriptive Statistics
Watch for Trends
Slicing and Dicing: Cross-Tabulation
Chart It, Baby
Be Aware of Inferential Statistics
Chapter 15: Ten Tips for Visually Analyzing and Presenting Data
Using the Right Chart Type
Using Your Chart Message as the Chart Title
Beware of Pie Charts
Consider Using Pivot Charts for Small Data Sets
Avoiding 3-D Charts
Never Use 3-D Pie Charts
Be Aware of the Phantom Data Markers
Use Logarithmic Scaling
Don’t Forget to Experiment
Get Tufte
Appendix: Glossary of Data Analysis and Excel Terms
About the Authors
Cheat Sheet
More Dummies Products
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
About the Authors
Next
Next Chapter
More Dummies Products
To access the cheat sheet specifically for this book, go to
www.dummies.com/cheatsheet/exceldataanalysis
.
Find out ”HOW” at
Dummies.com
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset