Excel Data Analysis For Dummies

Book description

Want to take the guesswork out of analyzing data? Let Excel do all the work for you!

Data collection, management and analysis is the key to making effective business decisions, and if you are like most people, you probably don't take full advantage of Excel's data analysis tools. With Excel Data Analysis For Dummies, 3rd Edition, you'll learn how to leverage Microsoft Excel to take your data analysis to new heights by uncovering what is behind all of those mind-numbing numbers. The beauty of Excel lies in its functionality as a powerful data analysis tool.  

This easy-to-read guide will show you how to use Excel in conjunction with external databases, how to fully leverage PivotTables and PivotCharts, tips and tricks for using Excel's statistical and financial functions, how to visually present your data so it makes sense, and information about the fancier, more advanced tools for those who have mastered the basics! Once you're up to speed, you can stop worrying about how to make use of all that data you have on your hands and get down to the business of discovering meaningful, actionable insights for your business or organization.

Excel is the most popular business intelligence tool in the world, and the newest update – Microsoft Excel 2016 – features even more powerful features for data analysis and visualization. Users can slice and dice their data and create visual presentations that turn otherwise indecipherable reports into easy-to-digest presentations that can quickly and effectively illustrate the key insights you are seeking.

  • Fully updated to cover the latest updates and features of Excel 2016  
  • Learn useful details about statistics, analysis, and visual presentations for your data
  • Features coverage of database and statistics functions, descriptive statistics, inferential statistics, and optimization modeling with Solver
  • Helps anyone who needs insight into how to get things done with data that is unwieldy and difficult to understand

With Excel Data Analysis For Dummies, 3rd Edition, you'll soon be quickly and easily performing key analyses that can drive organizational decisions and create competitive advantages.

Table of contents

    1. Cover
    2. 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
      6. Beyond the Book
      7. Where to Go from Here
    3. Part I: Getting Started with Data Analysis
      1. Chapter 1: Introducing Excel Tables
        1. What Is a Table and Why Do I Care?
        2. Building Tables
        3. Analyzing Table Information
      2. Chapter 2: Grabbing Data from External Sources
        1. Getting Data the Export-Import Way
        2. Querying External Databases and Web Page Tables
        3. It’s Sometimes a Raw Deal
      3. Chapter 3: Scrub-a-Dub-Dub: Cleaning Data
        1. Editing Your Imported Workbook
        2. Cleaning Data with Text Functions
        3. Using Validation to Keep Data Clean
    4. 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
        5. Customizing How Pivot Tables Work and Look
      2. Chapter 5: Building PivotTable Formulas
        1. Adding Another Standard Calculation
        2. Creating Custom Calculations
        3. Using Calculated Fields and Items
        4. Retrieving Data from a Pivot Table
      3. Chapter 6: Working with PivotCharts
        1. Why Use a Pivot Chart?
        2. Getting Ready to Pivot
        3. Running the PivotChart Wizard
        4. Fooling Around with Your Pivot Chart
        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
        4. Changing a Chart’s Location
        5. Formatting the Plot Area
        6. Formatting the Chart Area
        7. Formatting 3-D Charts
    5. Part III: Advanced Tools
      1. Chapter 8: Using the Database Functions
        1. Quickly Reviewing Functions
        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
        2. Means, Modes, and Medians
        3. Finding Values, Ranks, and Percentiles
        4. Standard Deviations and Variances
        5. Normal Distributions
        6. t-distributions
        7. f-distributions
        8. Binomial Distributions
        9. Chi-Square Distributions
        10. Regression Analysis
        11. Correlation
        12. Some Really Esoteric Probability Distributions
      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
        2. Setting Up a Solver Worksheet
        3. Solving an Optimization Modeling Problem
        4. Reviewing the Solver Reports
        5. Working with the Solver Options
        6. Understanding the Solver Error Messages
    6. 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
        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
      4. Appendix: Glossary of Data Analysis and Excel Terms
    7. About the Author
    8. Cheat Sheet
    9. Advertisement Page
    10. Connect with Dummies
    11. End User License Agreement

Product information

  • Title: Excel Data Analysis For Dummies
  • Author(s): Stephen L. Nelson, E. C. Nelson
  • Release date: December 2015
  • Publisher(s): For Dummies
  • ISBN: 9781119077206