Cover -- Copyright -- Table of Contents -- Dedication -- About the Author -- Acknowledgments -- Foreword -- Introduction -- How to Use This Book -- A Note from the Author About Stepped Procedures -- A Note from the Publisher About Figures in the Print Edition -- Working Along With This Book -- 1: How Excel Is Set Up -- 2: Keyboard Shortcuts -- Ctrl Key Shortcuts (and a few others) -- Moving Data -- Highlighting the Current Region -- Copying Data -- Amazing Excel 2010 Ctrl Key Smart Tags -- Transposing a Range of Cells -- Highlighting a Column of Data -- Ctrl + Enter Keyboard Magic -- Alt Shortcuts -- 3: Data in Excel -- Raw Data Versus Information -- SUMIFS Function -- Verifying Formula Results (and tracking down bad data) -- PivotTables -- Table Format Structure -- Sorting -- Excel Table Feature (Excel as a database) -- Convert the table format structure to an Excel Table -- Dynamic ranges -- Adding a New Record in a Table -- AutoComplete -- Trouble with AutoComplete -- Table Names and Table Formula Nomenclature -- Number Formatting as Façade -- Decimal Number Formatting -- Date Number Formatting -- Time Number Formatting -- Percentage Number Formatting -- Questions -- Answers -- Data Alignment (types of data Excel recognizes) -- Numbers Stored as Text -- 4: Style Formatting and Page Setup -- Applying Style Formatting -- Understanding Page Setup -- Printing and Print Preview -- Printing an Entire Workbook -- Page Setup for More Than One Page -- Set Print Area -- Page Break View -- Page Setup for Large Spreadsheets -- Built-in Styles -- Table Styles -- Cell Styles -- Formulas and Functions -- Formulas Defined -- Five Types of Formulas -- Creating Formulas -- Formula Elements (10 examples) -- Order of Operations -- Cost of Goods Sold Formula -- TRUE/FALSE Formula -- IF Function -- Putting Cell References in a Formula -- Cell References.

Relative Cell References -- Absolute Cell References -- Mixed with Row Locked Cell References -- Mixed with Column Locked Cell References -- Three Types of Cell References in One Formula -- Assumption Tables / Formula Input Area -- Efficient Formula Creation -- What-If Analysis -- Worksheet Cell References (sheet references) -- Workbook Cell References -- 3D Cell References -- Entering Formulas into Cells -- Efficient Formulas That Use Ranges -- Built-in Functions -- Finding Functions with the Insert Function Dialog Box -- FV Function for Investment Value -- Figuring Out How a Function Works -- Average Without Zeros Using Find -- Average Without Zeros Using the AVERAGEIF Function -- PMT Function for Loan Payment Amount -- Defined Names Feature -- MAX for Finding the Largest Value -- Defined Name Manager -- Create Names from Selection -- MIN for Finding the Smallest Value -- COUNTA for Counting the Total Number of Words -- SUMIFS for Adding with Two Criteria -- COUNTIFS for Counting with Two Criteria -- COUNTIF Function to Create a Small Report -- AVERAGEIFS for Averaging with Two Criteria -- Logical Tests (and/or) -- SUMPRODUCT (to multiply and then add in succession) -- Multiple Criteria Counting and Summing for Excel Versions Before 2007 -- IF Function (for putting one of two items in a cell or formula) -- IF Function (to check whether two columns are in balance) -- OR Function -- Nesting Functions -- AND Function -- IF Function (to analyze customer creditworthiness) -- IF Function (for commission calculation) -- Nesting IF Functions -- Lookup Functions -- VLOOKUP (for looking up values) -- VLOOKUP Function (to assign a category to a sales number) -- VLOOKUP Function (to help calculate commissions earned) -- VLOOKUP Function (to assign a grade) -- Data Validation and VLOOKUP (to complete an invoice).

MATCH Function with VLOOKUP (to do a two-way lookup) -- VLOOKUP (to retrieve customer information from a different sheet) -- Defined Name for Data Validation List and VLOOKUP and MATCH Functions -- MATCH Function (to check whether item is in master list) -- INDEX and MATCH Functions (for two-way lookup) -- INDEX and MATCH (for one-way lookup to the left) -- INDEX and MATCH (for one-way lookup above) -- ROUND Function -- Averaging with AVERAGE, MEDIAN, and MODE Functions -- AVERAGE Function -- MEDIAN Function -- MODE Function -- New Excel 2010 Functions (RANK.EQ and RANK.AVE) and Earlier Version Compatibility Function (RANK) -- RANK.EQ Function -- RANK.AVE Function -- Goal Seek -- Array Formulas -- Array Formulas Use Less Spreadsheet Real Estate -- SUMPRODUCT (to avoid Ctrl + Shift + Enter) -- Array Formulas as the Only Efficient Option -- Array Functions -- Transposing a Range of Cells with the TRANSPOSE Array Function -- Errors -- Data Analysis Features -- Sort Feature -- Sorting Numbers in a Single Column A to Z (smallest to biggest) -- Sorting Numbers in a Data Set with More Than One Column A to Z (smallest to biggest) -- Sorting Z to A by Using Right-Click -- Sorting Words A to Z -- Hierarchy for Sort When the Column Has Mixed Data -- Sorting Blanks to Bottom to Remove Unwanted Records (A to Z) -- Sorting #N/A Errors to the Top to Remove Prospective Customers Who Are Already in the Master List -- Sorting with Keyboard Shortcuts -- Using the Sort Dialog Box When Field Names Are Accidentally Sorted with Raw Data -- Randomly Sort a Data Set -- Sorting Left to Right -- Sorting by Color -- Sorting on More Than One Field -- Using Icon Sort Buttons, Sort the Major Sort Field Last -- Using the Sort Dialog Box, Select the Major Sort Field as the First Level -- Rules for Sorting More Than One Field -- Subtotal Feature.

Subtotals to Summarize Data by Region (sum calculation) -- Selecting Visible Cells Only (copy, paste) -- Subtotals to Summarize Data by Region (average calculation) -- Removing Subtotals -- Subtotal to Count Words -- Subtotals Within Subtotals: Summarize Sales Representative's Sales Within Each Region -- PivotTable Feature -- PivotTables Are Easy to Make! -- Creating a PivotTable in Four Clicks. -- Changing the Report Layout to Show Field Names Rather Than Generic Labels -- Style Formatting -- Number Formatting -- Changing the Calculation from Adding to Averaging -- Pivoting the PivotTable -- Listing Two Fields in Row Labels Area (similar to the Subtotal feature) -- Collapsing PivotTable Row Labels -- Filtering a Field in a PivotTable -- Listing Three or More Fields in the PivotTable -- Removing Fields from PivotTables -- PivotChart Feature -- Pivoting PivotTables and PivotCharts -- Grouping Dates -- Blanks in Data Sets -- Words in Date Field -- Amazing Report Filter Area -- Creating Five Reports with One Click Using the Show Report Filter Pages Feature -- Slicer Feature in Excel 2010 -- Frequency Distribution Reports -- Creating Product Frequency Tables -- Creating Sales Frequency Tables -- Percentage of Total to Show Employee Performance -- Creating Percentage of Total Reports -- Creating Running Total Reports -- Showing Three Calculations for the Same Field: AVERAGE, MAX, MIN -- Creating AVERAGE, MAX, MIN Reports -- When Source Data Changes, PivotTables Update Only After You Refresh -- Filter Feature -- Turning on the Filter Feature -- Filtering with One Criterion -- Filtering with "And" Criteria -- Filtering with "Or" Criteria -- Filtering to Extract Records from Data Sets -- Removing Applied Filters -- Right-Click Filtering -- Filtering on Cell Fill Color -- Filtering Below Average Values -- Filtering Top Five Sales -- Filtering Words/Text.

Filtering Dates -- Filtering Records to Add or Average with Multiple Criteria -- Advanced Filter Feature -- Extracting Records with One Criterion -- Extracting Records with Two Criteria (and criteria is on one line) -- Extracting Records with Two Criteria (or criteria is on two lines) -- Extracting Records with "And" and "Or" Criteria -- Extracting Records with "Between" Criteria -- Extracting Records with Date Criteria -- Extracting Records to a New Sheet -- Extracting Unique Records/Items to a New Location (safe method for removing duplicates) -- Text to Columns Feature -- Separating First and Last Name from One Cell into Two Cells -- Separating First and Last Name from 1 Cell into 2 Cells (formula) -- Separating Full Address from One Cell into Four Cells -- Importing Data -- Importing Data from a Different Excel Workbook -- Copy and Paste -- Using the Get External Data Feature -- Importing Comma-Separated or Tab-Separated Data -- Importing Data from Access -- Using the Stock Quote Web Query -- 7: Charts -- Categories and Series -- Chart Types -- Pie Charts -- Histograms -- Bar Charts -- Stacked Bar or Column Charts -- Line Charts -- X-Y Scatter Charts -- Creating and Formatting Charts -- Pie Charts -- Linking Chart Labels to Cells -- Adding Chart Data Labels -- Moving Charts -- Printing Charts -- Changing Chart Styles -- Saving Chart Templates -- Keyboard Shortcuts to Create Complete Charts -- Column Charts -- Histograms -- Bar Charts -- Stacked Bar or Column Charts -- Line Charts -- X-Y Scatter Charts -- Trendlines -- Break-Even Analysis -- Two Chart Types in One Chart -- Selecting/Editing Source Data for a Chart -- Sparklines -- 8: Conditional Formatting -- Built-in Conditional Formatting -- Highlighting Duplicates -- Highlighting Values That Are Greater Than the Average -- Highlighting Values That Are in the Top 10% -- Icons -- Data Bars.

Logical Formulas.