Excel data analysis your visual blueprint for analyzing data, charts, and PivotTables

Content: 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