Excel Data Analysis For Dummies (4^th Ed.)

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.

Introduction

About This Book

What You Can Safely Ignore

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Part 1: Getting Started with Data Analysis

Chapter 1: Learning Basic Data-Analysis Techniques

What Is Data Analysis, Anyway?

Cooking raw data

Dealing with data

Building data models

Performing what-if analysis

Analyzing Data with Conditional Formatting

Highlighting cells that meet some criteria

Showing pesky duplicate values

Highlighting the top or bottom values in a range

Analyzing cell values with data bars

Analyzing cell values with color scales

Analyzing cell values with icon sets

Creating a custom conditional formatting rule

Editing a conditional formatting rule

Removing conditional formatting rules

Summarizing Data with Subtotals

Grouping Related Data

Consolidating Data from Multiple Worksheets

Consolidating by position

Consolidating by category

Chapter 2: Working with Data-Analysis Tools

Working with Data Tables

Creating a basic data table

Creating a two-input data table

Skipping data tables when calculating workbooks

Analyzing Data with Goal Seek

Analyzing Data with Scenarios

Create a scenario

Apply a scenario

Edit a scenario

Delete a scenario

Optimizing Data with Solver

Understanding Solver

The advantages of Solver

When should you use Solver?

Loading the Solver add-in

Optimizing a result with Solver

Adding constraints to Solver

Save a Solver solution as a scenario

Chapter 3: Introducing Excel Tables 53

What Is a Table and Why Should I Care?

Building a Table

Getting the data from an external source

Converting a range to a table

Basic table maintenance

Analyzing Table Information

Displaying simple statistics

Adding a column subtotal

Sorting table records

Filtering table records

Clearing a filter

Turning off AutoFilter

Applying a predefined AutoFilter

Applying multiple filters

Applying advanced filters

Chapter 4: Grabbing Data from External Sources

What’s All This About External Data?

Exporting Data from Other Programs

Importing External Data into Excel

Importing data from an Access table

Importing data from a Word table

Introducing text file importing

Importing a delimited text file

Importing a fixed-width text file

Importing data from a web page

Importing an XML file

Querying External Databases

Defining a data source

Querying a data source

It’s Sometimes a Raw Deal

Chapter 5: Scrub-a-Dub-Dub: Cleaning Data

Editing Your Imported Workbook

Deleting unnecessary columns

Deleting unnecessary rows

Resizing columns

Resizing rows

Erasing unneeded contents in a cell or range

Formatting numeric values

Copying worksheet data

Moving worksheet data

Replacing data in fields

Cleaning Data with Text Functions

The CLEAN function

The CONCAT function

The EXACT function

The FIND function

The LEFT function

The LEN function

The LOWER function

The MID function

The NUMBERVALUE function

The PROPER function

The REPLACE function

The RIGHT function

The SEARCH function

The SUBSTITUTE function

The TEXT function

The TEXTJOIN function

The TRIM function

The UPPER function

The VALUE function

Converting text function formulas to text

Using Validation to Keep Data Clean

Chapter 6: Analyzing Table Data with Functions

The Database Functions: Some General Remarks

Retrieving a Value from a Table

Summing a Column’s Values

Counting a Column’s Values

Averaging a Column’s Values

Determining a Column’s Maximum and Minimum Values

Multiplying a Column’s Values

Deriving a Column’s Standard Deviation

Calculating a Column’s Variance

Part 2: Analyzing Data with PivotTables and PivotCharts

Chapter 7: Creating and Using PivotTables

Understanding PivotTables

Exploring PivotTable Features

Building a PivotTable from an Excel Table

Creating a PivotTable from External Data

Refreshing PivotTable Data

Refreshing PivotTable data manually

Refreshing PivotTable data automatically

Adding Multiple Fields to a PivotTable Area

Pivoting a Field to a Different Area

Grouping PivotTable Values

Grouping numeric values

Grouping date and time values

Grouping text values

Filtering PivotTable Values

Applying a report filter

Filtering row or column items

Filtering PivotTable values

Filtering a PivotTable with a slicer

Chapter 8: Performing PivotTable Calculations

Messing around with PivotTable Summary Calculations

Changing the PivotTable summary calculation

Trying out the difference summary calculation

Applying a percentage summary calculation

Adding a running total summary calculation

Creating an index summary calculation

Working with PivotTable Subtotals

Turning off subtotals for a field

Displaying multiple subtotals for a field

Introducing Custom Calculations

Formulas for custom calculations

Checking out the custom calculation types

Understanding custom calculation limitations

Inserting a Custom Calculated Field

Inserting a Custom Calculated Item

Editing a Custom Calculation

Deleting a Custom Calculation

Chapter 9: Building PivotCharts

Introducing the PivotChart

Understanding PivotChart pros and cons

Taking a PivotChart tour

Understanding PivotChart limitations

Creating a PivotChart

Creating a PivotChart from a PivotTable

Embedding a PivotChart on a PivotTable’s worksheet

Creating a PivotChart from an Excel table

Working with PivotCharts

Moving a PivotChart to another sheet

Filtering a PivotChart

Changing the PivotChart type

Adding data labels to your PivotChart

Sorting the PivotChart

Adding PivotChart titles

Moving the PivotChart legend

Displaying a data table with the PivotChart

Part 3: Discovering Advanced Data Analysis Tools

Chapter 10: Tracking Trends and Making Forecasts

Plotting a Best-Fit Trend line

Calculating Best-Fit Values

Plotting Forecasted Values

Extending a Linear Trend

Extending a linear trend using the fill handle

Extending a linear trend using the Series command

Calculating Forecasted Linear Values

Plotting an Exponential Trend Line

Calculating Exponential Trend Values

Plotting a Logarithmic Trend Line

Plotting a Power Trend Line

Plotting a Polynomial Trend Line

Chapter 11: Analyzing Data with Statistics

Counting Things

Counting numbers

Counting nonempty cells

Counting empty cells

Counting cells that match criteria

Counting cells that match multiple criteria

Counting permutations

Counting combinations

Averaging Things

Calculating an average

Calculating a conditional average

Calculating an average based on multiple conditions

Calculating the median

Calculating the mode

Finding the Rank

Determining the Nth Largest or Smallest Value

Calculating the nth highest value

Calculating the nth smallest value

Creating a Grouped Frequency Distribution

Calculating the Variance

Calculating the Standard Deviation

Finding the Correlation

Chapter 12: Analyzing Data with Descriptive Statistics

Loading the Analysis ToolPak

Generating Descriptive Statistics

Calculating a Moving Average

Determining Rank and Percentile

Generating Random Numbers

Creating a Frequency Distribution

Chapter 13: Analyzing Data with Inferential Statistics 253

Sampling Data

Using the t-Test Tools

Performing a z-Test

Determining the Regression

Calculating the Correlation

Calculating the Covariance

Using the Anova Tools

Performing an f-test

Part 4: The Part of Tens

Chapter 14: 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 Distributions 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 15: Ten Ways to Analyze Financial Data 285

Calculating Future Value

Calculating Present Value

Determining Loan Payments

Calculating a Loan Payment’s Principal and Interest

Calculating Cumulative Loan Principal and Interest

Finding the Required Interest Rate

Determining the Internal Rate of Return

Calculating Straight-Line Depreciation

Returning the Fixed-Declining Balance Depreciation

Determining the Double-Declining Balance Depreciation

Chapter 16: Ten Ways to Raise Your PivotTable Game

Turn the PivotTable Fields Task Pane On and Off

Change the PivotTable Fields Task Pane Layout

Display the Details Behind PivotTable Data

Apply a PivotTable Style

Create a Custom PivotTable Style

Preserve PivotTable Formatting

Rename the PivotTable

Turn Off Grand Totals

Reduce the Size of PivotTable Workbooks

Use a PivotTable Value in a Formula

Appendix: Glossary of Data Analysis and Excel Terms

Index

Paul McFedries is the president of Logophilia Limited, a technical writing company, and has worked with computers large and small since 1975. Although now primarily a writer, Paul has worked as a programmer, consultant, database developer, and website developer. Paul has written more than 90 books that have sold over four million copies worldwide.