Intro -- 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.

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!.

Description based on publisher supplied metadata and other sources.

Electronic reproduction. Ann Arbor, Michigan : ProQuest Ebook Central, 2018. Available via World Wide Web. Access may be limited to ProQuest Ebook Central affiliated libraries.