Top
Note: Supplemental materials are not guaranteed with Rental or Used book purchases.
Purchase Benefits
Free Shipping On Orders Over $35!
Get Rewarded for Ordering Your Textbooks! Enroll Now
Customer Reviews Read Reviews
Write a Review
What is included with this book?
Paul Mcfedries (Toronto, Ontario) runs Logophilia Limited, a technical writing company, and has been writing computer books for more than 17 years. He is the author or coauthor of more than 60 books that have sold more than 3 million copies worldwide. Paul is also the proprietor of Wordspy.com, a website that tracks new words and phrases as they enter the language.
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
The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.
Need Help? Copyright © 1999-2024