About the Author -- Table of Contents -- Chapter 1: Building Formulas for Data Analysis -- Introducing Data Analysis -- Introducing Formulas -- Understanding Formula Types -- Build a Formula -- Add a Range Name to a Formula -- Reference Another Worksheet Range in a Formula -- Move or Copy a Formula -- Switc h to Absolute Cell References -- Create an Array Formula -- Turn On Iterative Calculations -- Chapter 2: Troubleshooting Formulas -- Understanding Error Values in Excel -- Show Formulas Instead of Results -- Use a Watch Window to Monitor a Cell Value -- Step Through a Formula -- Display Text Instead of Error Values -- Check for Formula Errors in a Worksheet -- Audit a Formula to Locate Errors -- Chapter 3: Enhancing Formulas with Functions -- Understanding Excel Functions -- Understanding Function Types -- Add a Function to a Formula -- Add a Row or Column of Numbers -- Build an AutoSum Formula -- Round a Number -- Create a Conditional Formula -- Calculate a Conditional Sum -- Calculate a Conditional Count -- Find the Square Root -- Retrieve a Column or Row Number -- Look Up a Value -- Determine the Location of a Value -- Return a Cell Value with INDEX -- Perform Date and Time Calculations -- Chapter 4: Analyzing Financial Data -- Calculate Future Value -- Calculate Present Value -- Determine the Loan Payments -- Calculate the Principal or Interest -- Find the Required Interest Rate -- Determine the Internal Rate of Return -- Calculate Straight-Line Depreciation -- Return the Fixed-Declining Balance Depreciation -- Determine the Double-Declining Balance Depreciation -- Figure the Sum-of-the-Years-Digits Depreciation -- Chapter 5: Analyzing Statistical Data -- Calculate an Average -- Calculate a Conditional Average -- Determine the Median or the Mode -- Find the Rank -- Determine the Nth Largest or Smallest Value.

Create a Grouped Frequency Distribution -- Calculate the Variance and Standard Deviation -- Find the Correlation -- Chapter 6: Building Tables for Data Analysis -- Understanding Tables -- Introducing Table Features -- Convert a Range to a Table -- Select Table Data -- Insert a Table Row -- Insert a Table Column -- Delete a Table Row -- Delete a Table Column -- Add a Column Subtotal -- Chapter 7: Sorting and Filtering Data -- Perform a Simple Sort or Filter -- Perform a Multilevel Sort -- Create a Custom Sort -- Sort by Cell Color, Font Color, or Cell Icon -- Using Quick Filters for Complex Sorting -- Enter Criteria to Find Records -- Create an Advanced Filter -- Display Unique Records in the Filter Results -- Count Filtered Records -- Chapter 8: Learning Data Analysis Techniques -- Highlight Cells That Meet Some Criteria -- Highlight the Top or Bottom Values in a Range -- Show Duplicate Values -- Show Cells That Are Above or Below Average -- Analyze Cell Values with Data Bars -- Analyze Cell Values with Color Scales -- Analyze Cell Values with Icon Sets -- Create a Custom Conditional Formatting Rule -- Highlight Cells Based On a Formula -- Modify a Conditional Formatting Rule -- Remove Conditional Formatting from a Range -- Remove Conditional Formatting from a Worksheet -- Set Data Validation Rules -- Summarize Data with Subtotals -- Group Related Data -- Remove Duplicate Values from a Range or Table -- Consolidate Data from Multiple Worksheets -- Chapter 9: Working with Data Analysis Tools -- Create a Data Table -- Create a Two-Input Data Table -- Skip Data Tables When Calculating Workbooks -- Analyze Data with Goal Seek -- Analyze Data with Scenarios -- Understanding Solver -- Load the Solver Add-In -- Optimize a Result with Solver -- Add Constraints to Solver -- Save a Solver Solution as a Scenario.

Chapter 10: Tracking Trends and Making Forecasts -- Plotting a Best-Fit Trendline -- Calculating Best-Fit Values -- Plotting Forecasted Values -- Extending a Linear Trend -- Calculating Forecasted Linear Values -- Plotting an Exponential Trendline -- Calculating Exponential Trend Values -- Plotting a Logarithmic Trendline -- Plotting a Power Trendline -- Plotting a Polynomial Trendline -- Chapter 11: Working with the Analysis ToolPak -- Load the Analysis ToolPak -- Calculate a Moving Average -- Compare Variances -- Calculate Correlation -- Run a Regression Analysis -- Determine Rank and Percentile -- Calculate Descriptive Statistics -- Generate Random Numbers -- Create a Frequency Distribution -- Chapter 12: Analyzing Data with PivotTables -- Understanding PivotTables -- Explore PivotTable Features -- Build a PivotTable from an Excel Range or Table -- Create a PivotTable from External Data -- Refresh PivotTable Data -- Add Multiple Fields to the Row or Column Area -- Add Multiple Fields to the Data Area -- Move a Field to a Different Area -- Group PivotTable Values -- Change the PivotTable Summary Calculation -- Introducing Custom Calculations -- Insert a Custom Calculated Field -- Insert a Custom Calculated Item -- Chapter 13: Visualizing Data with Charts -- Examine Chart Elements -- Understanding Chart Types -- Create a Chart -- Display a Data Table -- Change the Chart Layout and Style -- Select a Different Chart Type -- Add a Sparkline to a Cell -- Chapter 14: Importing Data into Excel -- Understanding External Data -- Import Data from a Data Source -- Import Data from an Access Table -- Import Data from a Word Table -- Import Data from a Text File -- Import Data from a Web Page -- Import Data from an XML File -- Refresh Imported Data -- Separate Cell Text into Columns -- Chapter 15: Querying Data Sources -- Understanding Microsoft Query.

Define a Data Source -- Start Microsoft Query -- Tour the Microsoft Query Window -- Add a Table to a Query -- Add Fields to a Query -- Filter the Records with Query Criteria -- Sort Query Records -- Return the Query Results -- Chapter 16: Learning VBA for Data Analysis -- Record a Macro -- Open the VBA Editor -- Explore the Excel Object Model -- Add a Macro to a Module -- Run a Macro -- Assign a Shortcut Key to a Macro -- Assign a Macro to the Quick Access Toolbar -- Assign a Macro to the Ribbon -- Set the Macro Security Level -- Digitally Sign Your Excel Macros -- Appendix A: Using Excel Keyboard Shortcuts -- Index.