Excel Data Analysis For Dummies, 5th Edition

Excel Data Analysis For Dummies, 5th Edition

by Paul McFedries
Released February 2022
Publisher(s): For Dummies
ISBN: 9781119844426

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

Turn jumbles of numbers into graphics, insights, and answers with Excel

With Microsoft Excel, you can, well, excel at data analysis. And Excel Data Analysis For Dummies can help, with clear and easy explanations of the mountain of features for creating, visualizing, and analyzing data. PivotTables, charts, what-if analysis, statistical functions—it's all in here, with examples and ideas for Excel users of all skill levels.

This latest edition covers the most recent updates to Excel and Microsoft 365. You'll beef up your data skills and learn powerful techniques for turning numbers into knowledge. For students, researchers, and business professionals, Excel is the spreadsheet and data application of choice—and Dummies is the best choice for learning how to make those numbers sing.

  • Learn how to use Excel's built-in data analysis features and write your own functions to explore patterns in your data
  • Create striking charts and visualizations, and discover multiple ways to tell the stories hidden in the numbers
  • Clean up large datasets and identify statistical operations that will answer your questions
  • Perform financial calculations, database operations, and more—without leaving Excel

Excel Data Analysis For Dummies is the go-to resource for Excel users who are looking for better ways to crunch the numbers.

Table of contents

  1. Cover
  2. Title Page
  3. Copyright
  4. 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
  5. 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. Chapter 5: 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
  6. Part 2: Analyzing Data Using PivotTables and PivotCharts
    1. Chapter 6: Creating and Using PivotTables
      1. Understanding PivotTables
      2. Exploring PivotTable Features
      3. Building a PivotTable from an Excel Range or 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 7: 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 8: Building PivotCharts
      1. Introducing the PivotChart
      2. Creating a PivotChart
      3. Working with PivotCharts
  7. Part 3: Discovering Advanced Data-Analysis Tools
    1. Chapter 9: Dealing with Data Models
      1. Understanding Excel Data Models
      2. Managing a Data Model with Power Pivot
      3. Transforming Data
      4. Creating a PivotTable or PivotChart from Your Data Model
    2. 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
      11. Creating a Forecast Sheet
    3. Chapter 11: Analyzing Data Using 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
    4. Chapter 12: Analyzing Data Using 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
    5. Chapter 13: Analyzing Data Using 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
  8. 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
  9. Appendix: Glossary of Data Analysis and Excel Terms
  10. Index
  11. Author Bio
  12. Advertisement Page
  13. Connect with Dummies
  14. End User License Agreement

Product information

  • Title: Excel Data Analysis For Dummies, 5th Edition
  • Author(s): Paul McFedries
  • Release date: February 2022
  • Publisher(s): For Dummies
  • ISBN: 9781119844426