Excel Data Analysis

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