Excel Data Analysis For Dummies, 4th Edition

Book description

Take Excel to the next level

Excel is the world’s leading spreadsheet application. It’s a key module in Microsoft Office—the number-one productivity suite—and it is the number-one business intelligence tool. An Excel dashboard report is a visual presentation of critical data and uses gauges, maps, charts, sliders, and other graphical elements to present complex data in an easy-to-understand format. 

Excel Data Analysis For Dummies explains in depth how to use Excel as a tool for analyzing big data sets. In no time, you’ll discover how to mine and analyze critical data in order to make more informed business decisions.

  • Work with external databases, PivotTables, and Pivot Charts
  • Use Excel for statistical and financial functions and data sharing
  • Get familiar with Solver
  • Use the Small Business Finance Manager

If you’re familiar with Excel but lack a background in the technical aspects of data analysis, this user-friendly book makes it easy to start putting it to use for you.

Table of contents

  1. Cover
  2. Introduction
    1. About This Book
    2. What You Can Safely Ignore
    3. Foolish Assumptions
    4. Icons Used in This Book
    5. Beyond the Book
    6. Where to Go from Here
  3. Part 1: Getting Started with Data Analysis
    1. Chapter 1: Learning Basic Data-Analysis Techniques
      1. What Is Data Analysis, Anyway?
      2. Analyzing Data with Conditional Formatting
      3. Summarizing Data with Subtotals
      4. Grouping Related Data
      5. Consolidating Data from Multiple Worksheets
    2. Chapter 2: Working with Data-Analysis Tools
      1. Working with Data Tables
      2. Analyzing Data with Goal Seek
      3. Analyzing Data with Scenarios
      4. Optimizing Data with Solver
    3. Chapter 3: Introducing Excel Tables
      1. What Is a Table and Why Should I Care?
      2. Building a Table
      3. Analyzing Table Information
    4. Chapter 4: Grabbing Data from External Sources
      1. What’s All This About External Data?
      2. Exporting Data from Other Programs
      3. Importing External Data into Excel
      4. Querying External Databases
      5. It's Sometimes a Raw Deal
    5. Chapter 5: Scrub-a-Dub-Dub: Cleaning Data
      1. Editing Your Imported Workbook
      2. Cleaning Data with Text Functions
      3. Using Validation to Keep Data Clean
    6. Chapter 6: Analyzing Table Data with Functions
      1. The Database Functions: Some General Remarks
      2. Retrieving a Value from a Table
      3. Summing a Column's Values
      4. Counting a Column’s Values
      5. Averaging a Column's Values
      6. Determining a Column’s Maximum and Minimum Values
      7. Multiplying a Column’s Values
      8. Deriving a Column’s Standard Deviation
      9. Calculating a Column’s Variance
  4. Part 2: Analyzing Data with PivotTables and PivotCharts
    1. Chapter 7: Creating and Using PivotTables
      1. Understanding PivotTables
      2. Exploring PivotTable Features
      3. Building a PivotTable from an Excel Table
      4. Creating a PivotTable from External Data
      5. Refreshing PivotTable Data
      6. Adding Multiple Fields to a PivotTable Area
      7. Pivoting a Field to a Different Area
      8. Grouping PivotTable Values
      9. Filtering PivotTable Values
    2. Chapter 8: Performing PivotTable Calculations
      1. Messing around with PivotTable Summary Calculations
      2. Working with PivotTable Subtotals
      3. Introducing Custom Calculations
      4. Inserting a Custom Calculated Field
      5. Inserting a Custom Calculated Item
      6. Editing a Custom Calculation
      7. Deleting a Custom Calculation
    3. Chapter 9: Building PivotCharts
      1. Introducing the PivotChart
      2. Creating a PivotChart
      3. Working with PivotCharts
  5. Part 3: Discovering Advanced Data Analysis Tools
    1. Chapter 10: Tracking Trends and Making Forecasts
      1. Plotting a Best-Fit Trend line
      2. Calculating Best-Fit Values
      3. Plotting Forecasted Values
      4. Extending a Linear Trend
      5. Calculating Forecasted Linear Values
      6. Plotting an Exponential Trend Line
      7. Calculating Exponential Trend Values
      8. Plotting a Logarithmic Trend Line
      9. Plotting a Power Trend Line
      10. Plotting a Polynomial Trend Line
    2. Chapter 11: Analyzing Data with Statistics
      1. Counting Things
      2. Averaging Things
      3. Finding the Rank
      4. Determining the Nth Largest or Smallest Value
      5. Creating a Grouped Frequency Distribution
      6. Calculating the Variance
      7. Calculating the Standard Deviation
      8. Finding the Correlation
    3. Chapter 12: Analyzing Data with Descriptive Statistics
      1. Loading the Analysis ToolPak
      2. Generating Descriptive Statistics
      3. Calculating a Moving Average
      4. Determining Rank and Percentile
      5. Generating Random Numbers
      6. Creating a Frequency Distribution
    4. Chapter 13: Analyzing Data with Inferential Statistics
      1. Sampling Data
      2. Using the t-Test Tools
      3. Performing a z-Test
      4. Determining the Regression
      5. Calculating the Correlation
      6. Calculating the Covariance
      7. Using the Anova Tools
      8. Performing an f-test
  6. Part 4: The Part of Tens
    1. Chapter 14: 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 Distributions 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 15: Ten Ways to Analyze Financial Data
      1. Calculating Future Value
      2. Calculating Present Value
      3. Determining Loan Payments
      4. Calculating a Loan Payment's Principal and Interest
      5. Calculating Cumulative Loan Principal and Interest
      6. Finding the Required Interest Rate
      7. Determining the Internal Rate of Return
      8. Calculating Straight-Line Depreciation
      9. Returning the Fixed-Declining Balance Depreciation
      10. Determining the Double-Declining Balance Depreciation
    3. Chapter 16: Ten Ways to Raise Your PivotTable Game
      1. Turn the PivotTable Fields Task Pane On and Off
      2. Change the PivotTable Fields Task Pane Layout
      3. Display the Details Behind PivotTable Data
      4. Apply a PivotTable Style
      5. Create a Custom PivotTable Style
      6. Preserve PivotTable Formatting
      7. Rename the PivotTable
      8. Turn Off Grand Totals
      9. Reduce the Size of PivotTable Workbooks
      10. Use a PivotTable Value in a Formula
  7. Appendix: Glossary of Data Analysis and Excel Terms
  8. Index
  9. Author Bio
  10. Connect with Dummies
  11. End User License Agreement

Product information

  • Title: Excel Data Analysis For Dummies, 4th Edition
  • Author(s): Paul McFedries
  • Release date: November 2018
  • Publisher(s): For Dummies
  • ISBN: 9781119518167