Skip to main content

Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables, 4th Edition

Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables, 4th Edition

Paul McFedries

ISBN: 978-1-118-78032-9

Jul 2013

336 pages

Select type: E-Book

Product not available for purchase


Professional-level coverage and techniques for Excel power users

Aimed at Excel power users who appreciate logical, clean explanations of techniques, this visual guide features numerous screenshots and easy-to-follow numbered steps in order to show you how to perform professional-level modeling, charting, data sharing, data access, data slicing, and other functions. You'll find super techniques for getting the most out of Excel's statistical and financial functions, Excel PivotTables and PivotCharts, Excel Solver, and more.

  • Demonstrates how to crunch and analyze Excel data the way the professionals do in an uncluttered, visual style
  • Offers a clear look at power-using the new Excel 2013, the latest version of the world's leading spreadsheet application from Microsoft
  • Expands your Excel knowledge and helps you use Excel data more efficiently
  • Explains how to retrieve data from databases; cut, slice, and pivot data using PivotTables; model data and chart data; and use advanced formulas
  • Explores all features and functions in two-color pages packed with screenshots, numbered steps, and other visual graphics that clearly show you how to accomplish tasks
  • Includes practical examples, tips, and advice to help you get the most out of Excel's features and functions

Learn the full power of Excel 2013 with this helpful guide!

Chapter 1 Building Formulas for Data Analysis

Introducing Data Analysis 2

Introducing Formulas 4

Understanding Formula Types 6

Build a Formula 8

Add a Range Name to a Formula 10

Reference Another Worksheet Range in a Formula 12

Move or Copy a Formula 14

Switch to Absolute Cell References 16

Create an Array Formula 18

Turn On Iterative Calculations 20

Chapter 2 Troubleshooting Formulas

Understanding Error Values in Excel 22

Show Formulas Instead of Results 24

Use a Watch Window to Monitor a Cell Value 26

Step Through a Formula 28

Display Text Instead of Error Values 30

Check for Formula Errors in a Worksheet 32

Audit a Formula to Locate Errors 34

Chapter 3 Enhancing Formulas with Functions

Understanding Excel Functions 36

Understanding Function Types 38

Add a Function to a Formula 40

Add a Row or Column of Numbers 42

Build an AutoSum Formula 44

Round a Number 46

Create a Conditional Formula 48

Calculate a Conditional Sum 50

Calculate a Conditional Count 51

Find the Square Root 52

Retrieve a Column or Row Number 53

Look Up a Value 54

Determine the Location of a Value 56

Return a Cell Value with INDEX 57

Perform Date and Time Calculations 58

Chapter 4 Analyzing Financial Data

Calculate Future Value 60

Calculate Present Value 62

Determine the Loan Payments 64

Calculate the Principal or Interest 66

Find the Required Interest Rate 68

Determine the Internal Rate of Return 70

Calculate Straight-Line Depreciation 72

Return the Fixed-Declining Balance Depreciation 74

Determine the Double-Declining Balance Depreciation 76

Figure the Sum-of-the-Years-Digits Depreciation 78

Chapter 5 Analyzing Statistical Data

Calculate an Average 80

Calculate a Conditional Average 81

Determine the Median or the Mode 82

Find the Rank 84

Determine the Nth Largest or Smallest Value 86

Create a Grouped Frequency Distribution 88

Calculate the Variance and Standard Deviation 90

Find the Correlation 92

Chapter 6 Building Tables for Data Analysis

Understanding Tables 94

Introducing Table Features 95

Convert a Range to a Table 96

Select Table Data 98

Insert a Table Row 100

Insert a Table Column 101

Delete a Table Row 102

Delete a Table Column 103

Add a Column Subtotal 104

Chapter 7 Sorting and Filtering Data

Perform a Simple Sort or Filter 106

Perform a Multilevel Sort 108

Create a Custom Sort 110

Sort by Cell Color, Font Color, or Cell Icon 111

Using Quick Filters for Complex Sorting 112

Enter Criteria to Find Records 114

Create an Advanced Filter 116

Display Unique Records in the Filter Results 118

Count Filtered Records 120

Chapter 8 Learning Data Analysis Techniques

Highlight Cells That Meet Some Criteria 122

Highlight the Top or Bottom Values in a Range 124

Show Duplicate Values 126

Show Cells That Are Above or Below Average 127

Analyze Cell Values with Data Bars 128

Analyze Cell Values with Color Scales 130

Analyze Cell Values with Icon Sets 132

Create a Custom Conditional Formatting Rule 134

Highlight Cells Based On a Formula 136

Modify a Conditional Formatting Rule 138

Remove Conditional Formatting from a Range 140

Remove Conditional Formatting from a Worksheet 141

Set Data Validation Rules 142

Summarize Data with Subtotals 144

Group Related Data 146

Remove Duplicate Values from a Range or Table 148

Consolidate Data from Multiple Worksheets 150

Chapter 9 Working with Data Analysis Tools

Create a Data Table 154

Create a Two-Input Data Table 156

Skip Data Tables When Calculating Workbooks 158

Analyze Data with Goal Seek 160

Analyze Data with Scenarios 162

Understanding Solver 166

Load the Solver Add-In 168

Optimize a Result with Solver 170

Add Constraints to Solver 172

Save a Solver Solution as a Scenario 174

Chapter 10 Tracking Trends and Making Forecasts

Plotting a Best-Fit Trendline 176

Calculating Best-Fit Values 178

Plotting Forecasted Values 180

Extending a Linear Trend 182

Calculating Forecasted Linear Values 184

Plotting an Exponential Trendline 186

Calculating Exponential Trend Values 188

Plotting a Logarithmic Trendline 190

Plotting a Power Trendline 192

Plotting a Polynomial Trendline 194

Chapter 11 Working with the Analysis ToolPak

Load the Analysis ToolPak 196

Calculate a Moving Average 198

Compare Variances 200

Calculate Correlation 202

Run a Regression Analysis 204

Determine Rank and Percentile 206

Calculate Descriptive Statistics 208

Generate Random Numbers 210

Create a Frequency Distribution 212

Chapter 12 Analyzing Data with PivotTables

Understanding PivotTables 214

Explore PivotTable Features 215

Build a PivotTable from an Excel Range or Table 216

Create a PivotTable from External Data 218

Refresh PivotTable Data 222

Add Multiple Fields to the Row or Column Area 224

Add Multiple Fields to the Data Area 226

Move a Field to a Different Area 228

Group PivotTable Values 230

Change the PivotTable Summary Calculation 232

Introducing Custom Calculations 234

Insert a Custom Calculated Field 236

Insert a Custom Calculated Item 238

Chapter 13 Visualizing Data with Charts

Examine Chart Elements 240

Understanding Chart Types 241

Create a Chart 242

Display a Data Table 244

Change the Chart Layout and Style 245

Select a Different Chart Type 246

Add a Sparkline to a Cell 248

Chapter 14 Importing Data into Excel

Understanding External Data 250

Import Data from a Data Source 252

Import Data from an Access Table 254

Import Data from a Word Table 256

Import Data from a Text File 258

Import Data from a Web Page 262

Import Data from an XML File 264

Refresh Imported Data 266

Separate Cell Text into Columns 268

Chapter 15 Querying Data Sources

Understanding Microsoft Query 270

Define a Data Source 272

Start Microsoft Query 276

Tour the Microsoft Query Window 277

Add a Table to a Query 278

Add Fields to a Query 280

Filter the Records with Query Criteria 282

Sort Query Records 284

Return the Query Results 286

Chapter 16 Learning VBA for Data Analysis

Record a Macro 288

Open the VBA Editor 290

Explore the Excel Object Model 292

Add a Macro to a Module 294

Run a Macro 296

Assign a Shortcut Key to a Macro 298

Assign a Macro to the Quick Access Toolbar 300

Assign a Macro to the Ribbon 302

Set the Macro Security Level 304

Digitally Sign Your Excel Macros 306

Appendix A Using Excel Keyboard Shortcuts

Using Excel Keyboard Shortcuts 308

Index 314

Sample Files