Excel Data Analysis For Dummies, 2nd Edition

Excel Data Analysis For Dummies, 2nd Edition

by E. C. Nelson, Stephen L. Nelson
Released May 2014
Publisher(s): For Dummies
ISBN: 9781118898093

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Harness the power of Excel to discover what your numbers are hiding

Excel Data Analysis For Dummies, 2nd Edition is the ultimate guide to getting the most out of your data. Veteran Dummies author Stephen L. Nelson guides you through the basic and not-so-basic features of Excel to help you discover the gems hidden in your rough data. From input, to analysis, to visualization, the book walks you through the steps that lead to superior data analysis.

Excel is the number-one spreadsheet application, with ever-expanding capabilities. If you're only using it to balance the books, you're missing out on a host of functions that can benefit your business or personal finances by uncovering trends and other important information hidden within the numbers. Excel Data Analysis For Dummies, 2nd Edition eliminates the need for advanced statistics or analysis courses by allowing you to harness the full power of Excel to do the heavy lifting for you. This 2nd Edition is fully updated to include information about Excel's latest features, making it a your go-to Excel guide for data analysis. Topics include:

  • Working with external databases

  • PivotTables and PivotCharts

    • Using

  • Excel for statistical and financial functions

  • Solver, Small Business Finance Manager, and more

    • The book also includes a guide to chart types and formatting, and advice on effective visual data presentation. You already have the data, so you might as well get something great out of it. Excel Data Analysis For Dummies, 2nd Edition is the key to discovering what your numbers are hiding.

    Table of contents

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

    Product information

    • Title: Excel Data Analysis For Dummies, 2nd Edition
    • Author(s): E. C. Nelson, Stephen L. Nelson
    • Release date: May 2014
    • Publisher(s): For Dummies
    • ISBN: 9781118898093