Contents at a Glance -- Table of Contents -- Introduction -- What You Should Have -- Conventions Used in This Book -- Entering VBA Code -- How This Book Is Organized -- How to Use This Book -- About the Power Utility Pak Offer -- Reach Out -- Part I: Basic Excel Usage -- Tips and Where to Find Them -- Understanding Excel Versions -- Maximizing Ribbon Efficiency -- Understanding Protected View -- Selecting Cells Efficiently -- Making " Special" Range Selections -- Undoing, Redoing, and Repeating -- Discovering Some Useful Shortcut Keys -- Navigating Sheets in a Workbook -- Resetting the Used Area of a Worksheet -- Understanding Workbooks versus Windows -- Customizing the Quick Access Toolbar -- Customizing the Ribbon -- Accessing the Ribbon with Your Keyboard -- Recovering Your Work -- Customizing the Default Workbook -- Using Document Themes -- Hiding User Interface Elements -- Hiding Columns or Rows -- Hiding Cell Contents -- Taking Pictures of Ranges -- Performing Inexact Searches -- Replacing Formatting -- Changing the Excel Color Scheme -- Limiting the Usable Area in a Worksheet -- Using an Alternative to Cell Comments -- Understanding the Excel Help System -- Making a Worksheet " Very Hidden" -- Working with the Backstage View -- Part II: Data Entry -- Tips and Where to Find Them -- Understanding the Types of Data -- Moving the Cell Pointer after Entering Data -- Selecting a Range of Input Cells before Entering Data -- Using AutoComplete to Automate Data Entry -- Removing Duplicate Rows -- Keeping Titles in View -- Automatically Filling a Range with a Series -- Working with Fractions -- Resizing the Formula Bar -- Proofing Your Data with Audio -- Controlling Automatic Hyperlinks -- Entering Credit Card Numbers -- Using the Excel Built-In Data Entry Form -- Customizing and Sharing AutoCorrect Entries.

Restricting Cursor Movement to Input Cells -- Controlling the Office Clipboard -- Creating a Drop-Down List in a Cell -- Part III: Formatting -- Tips and Where to Find Them -- Using the Mini Toolbar -- Indenting Cell Contents -- Quick Number Formatting -- Creating Custom Number Formats -- Using Custom Number Formats to Scale Values -- Using Custom Date and Time Formatting -- Examining Some Useful Custom Number Formats -- Updating Old Fonts -- Understanding Conditional Formatting Visualization -- Showing Text and a Value in a Cell -- Merging Cells -- Formatting Individual Characters in a Cell -- Displaying Times That Exceed 24 Hours -- Fixing Non-Numeric Numbers -- Adding a Frame to a Range -- Dealing with Gridlines, Borders, and Underlines -- Inserting a Watermark -- Adding a Background Image to a Worksheet -- Wrapping Text in a Cell -- Seeing All Characters in a Font -- Entering Special Characters -- Using Named Styles -- Part IV: Basic Formulas and Functions -- Tips and Where to Find Them -- Using Formula AutoComplete -- Knowing When to Use Absolute References -- Knowing When to Use Mixed References -- Changing the Type of a Cell Reference -- Converting a Vertical Range to a Table -- AutoSum Tricks -- Using the Status Bar Selection Statistics Feature -- Converting Formulas to Values -- Transforming Data without Using Formulas -- Transforming Data by Using Temporary Formulas -- Deleting Values While Keeping Formulas -- Summing Across Sheets -- Dealing with Function Arguments -- Annotating a Formula without Using a Comment -- Making an Exact Copy of a Range of Formulas -- Monitoring Formula Cells from Any Location -- Displaying and Printing Formulas -- Avoiding Error Displays in Formulas -- Using Goal Seeking -- Understanding the Secret about Names -- Using Named Constants -- Using Functions in Names -- Creating a List of Names -- Using Dynamic Names.

Creating Worksheet-Level Names -- Working with Pre-1900 Dates -- Working with Negative Time Values -- Part V: Useful Formula Examples -- Tips and Where to Find Them -- Calculating Holidays -- Calculating a Weighted Average -- Calculating a Person's Age -- Ranking Values -- Converting Inches to Feet and Inches -- Using the DATEDIF Function -- Counting Characters in a Cell -- Numbering Weeks -- Using a Pivot Table Instead of Formulas -- Expressing a Number as an Ordinal -- Extracting Words from a String -- Parsing Names -- Removing Titles from Names -- Generating a Series of Dates -- Determining Specific Dates -- Displaying a Calendar in a Range -- Various Methods of Rounding Numbers -- Rounding Time Values -- Using the New AGGREGATE Function -- Returning the Last Nonblank Cell in a Column or Row -- Using the COUNTIF Function -- Counting Cells That Meet Multiple Criteria -- Counting Nonduplicated Entries in a Range -- Calculating Single-Criterion Conditional Sums -- Calculating Multiple-Criterion Conditional Sums -- Looking Up an Exact Value -- Performing a Two-Way Lookup -- Performing a Two-Column Lookup -- Performing a Lookup by Using an Array -- Using the INDIRECT Function -- Creating Megaformulas -- Part VI: Conversions and Mathematical Calculations -- Tips and Where to Find Them -- Converting Between Measurement Systems -- Converting Temperatures -- Solving Simultaneous Equations -- Solving Recursive Equations -- Generating Random Numbers -- Calculating Roots -- Calculating a Remainder -- Part VII: Charts and Graphics -- Tips and Where to Find Them -- Creating a Text Chart Directly in a Range -- Selecting Elements in a Chart -- Creating a Self-Expanding Chart -- Creating Combination Charts -- Creating a Gantt Chart -- Creating a Gauge Chart -- Using Pictures in Charts -- Plotting Mathematical Functions -- Using High-Low Lines in a Chart.

Linking Chart Text to Cells -- Creating a Chart Template -- Saving a Chart as a Graphics File -- Saving a Range as a Graphic Image -- Making Charts the Same Size -- Resetting All Chart Formatting -- Freezing a Chart -- Creating Picture Effects with a Chart -- Creating Sparkline Graphics -- Selecting Objects on a Worksheet -- Making a Greeting Card -- Enhancing Text Formatting in Shapes -- Using Images as Line Chart Markers -- Changing the Shape of a Cell Comment -- Adding an Image to a Cell Comment -- Enhancing Images -- Part VIII: Data Analysis and Lists -- Tips and Where to Find Them -- Using the Table Feature -- Working with Tables -- Using Formulas with a Table -- Numbering Rows in a Table -- Using Custom Views with Filtering -- Putting Advanced Filter Results on a Different Sheet -- Comparing Two Ranges by Using Conditional Formatting -- Randomizing a List -- Filling the Gaps in a Report -- Creating a List from a Summary Table -- Finding Duplicates by Using Conditional Formatting -- Creating a Quick Frequency Tabulation -- Controlling References to Cells within a Pivot Table -- Grouping Items by Date in a Pivot Table -- Unlinking a Pivot Table from Its Source -- Using Pivot Table Slicers -- Part IX: Working with Files -- Tips and Where to Find Them -- Understanding the New Excel File Formats -- Importing a Text File into a Worksheet Range -- Getting Data from a Web Page -- Displaying a Workbook's Full Path -- Using Document Properties -- Inspecting a Workbook -- Finding the Missing No to All Button When Closing Files -- Getting a List of Filenames -- Using Workspace Files -- Part X: Printing -- Tips and Where to Find Them -- Controlling What Gets Printed -- Displaying Repeated Rows or Columns on a Printout -- Printing Noncontiguous Ranges on a Single Page -- Preventing Objects from Printing -- Page-Numbering Tips.

Adding and Removing Page Breaks -- Saving to a PDF File -- Making Your Printout Fit on One Page -- Printing the Contents of a Cell in a Header or Footer -- Copying Page Setup Settings Across Sheets -- Printing Cell Comments -- Printing a Giant Banner -- Part XI: Spotting, Fixing, and Preventing Errors -- Tips and Where to Find Them -- Using the Excel Error-Checking Features -- Identifying Formula Cells -- Dealing with Floating-Point Number Problems -- Removing Excess Spaces -- Viewing Names Graphically -- Locating Phantom Links -- Understanding Displayed versus Actual Values -- Tracing Cell Relationships -- Part XII: Basic VBA and Macros -- Tips and Where to Find Them -- Learning about Macros and VBA -- Recording a Macro -- Executing Macros -- Understanding Functions Versus Subs -- Creating Simple Worksheet Functions -- Describing Function Arguments -- Making Excel Talk -- Understanding Custom Function Limitations -- Executing a Ribbon Command with a Macro -- Understanding Security Issues Related to Macros -- Using a Personal Macro Workbook -- Index.