Cover -- Title Page -- Copyright -- About the Author -- Contents -- Introduction -- What You Should Know -- What You Should Have -- Conventions in This Book -- Formula listings -- Key names -- The Ribbon -- Functions, procedures, and named ranges -- Mouse conventions -- What the icons mean -- How This Book Is Organized -- How to Use This Book -- About the Power Utility Pak Offer -- Part I: Workbooks and Files -- Tips and Where to Find Them -- Tip 1: Changing the Look of Excel -- Cosmetic changes -- Hiding the Ribbon -- Using options on the View tab -- Hiding other elements -- Hiding the status bar -- Tip 2: Customizing the Quick Access Toolbar -- About the Quick Access toolbar -- Adding new commands to the Quick Access toolbar -- Performing other Quick Access toolbar actions -- Tip 3: Customizing the Ribbon -- How to customize the Ribbon -- Tip 4: Understanding Protected View -- What causes Protected View? -- Printing and copying -- Forcing a file to open in Normal view -- Tip 5: Understanding AutoRecover -- Recovering versions of the current workbook -- Recovering unsaved work -- Tip 6: Using a Workbook in a Browser -- Tip 7: Saving to a Read-Only Format -- Send a printed copy -- Send an electronic copy in the form of a PDF file -- Send an MHTML file -- Tip 8: Generating a List of Filenames -- Tip 9: Generating a List of Sheet Names -- Tip 10: Using Document Themes -- Applying a theme -- Customizing a theme -- Tip 11: Understanding Excel Compatibility Issues -- The Excel 2013 file formats -- The Office Compatibility Pack -- Checking compatibility -- Tip 12: Where to Change Printer Settings -- Part II: Formatting -- Tips and Where to Find Them -- Tip 13: Working with Merged Cells -- Other merge actions -- Potential problems with merged cells -- Locating all merged cells -- Unmerging all merged cells -- Alternatives to merged cells.

Tip 14: Indenting Cell Contents -- Tip 15: Using Named Styles -- Using the Style gallery -- Modifying an existing style -- Creating new styles -- Merging styles from other workbooks -- Tip 16: Creating Custom Number Formats -- Parts of a number format string -- Custom number format codes -- Tip 17: Using Custom Number Formats to Scale Values -- Tip 18: Creating a Bulleted List -- Using a bullet character -- Using SmartArt -- Tip 19: Shading Alternate Rows Using Conditional Formatting -- Displaying alternate row shading -- Creating checkerboard shading -- Shading groups of rows -- Tip 20: Formatting Individual Characters in a Cell -- Tip 21: Using the Format Painter -- Painting basics -- Format Painter variations -- Tip 22: Inserting a Watermark -- Tip 23: Showing Text and a Value in a Cell -- Using concatenation -- Using the TEXT function -- Using a custom number format -- Tip 24: Avoiding Font Substitution for Small Point Sizes -- Tip 25: Updating Old Fonts -- Part III: Formulas -- Tips and Where to Find Them -- Tip 26: Resizing the Formula Bar -- Tip 27: Monitoring Formula Cells from Any Location -- About the Watch Window -- Customizing the Watch Window -- Navigating with the Watch Window -- Tip 28: Learning Some AutoSum Tricks -- Tip 29: Knowing When to Use Absolute and Mixed References -- Using absolute references -- Using mixed references -- Tip 30: Avoiding Error Displays in Formulas -- Using the IFERROR function -- Using the ISERROR function -- Tip 31: Creating Worksheet-Level Names -- Tip 32: Using Named Constants -- Tip 33: Sending Personalized E-Mail from Excel -- About the HYPERLINK function -- A practical example using HYPERLINK -- Tip 34: Looking Up an Exact Value -- Tip 35: Performing a Two-Way Lookup -- Using a formula -- Using implicit intersection -- Tip 36: Performing a Two-Column Lookup -- Tip 37: Calculating Holidays.

New Year's Day -- Martin Luther King Jr. Day -- Presidents' Day -- Easter -- Memorial Day -- Independence Day -- Labor Day -- Columbus Day -- Veterans Day -- Thanksgiving Day -- Christmas Day -- Tip 38: Calculating a Person's Age -- Method 1 -- Method 2 -- Method 3 -- Tip 39: Working with Pre-1900 Dates -- Use three columns -- Use custom functions -- Use a different product -- Tip 40: Displaying a Live Calendar in a Range -- Tip 41: Returning the Last Nonblank Cell in a Column or Row -- Cell counting method -- Array formula method -- Standard formula method -- Tip 42: Various Methods of Rounding Numbers -- Rounding to the nearest multiple -- Rounding currency values -- Using the INT and TRUNC functions -- Rounding to n significant digits -- Tip 43: Converting Between Measurement Systems -- Tip 44: Counting Nonduplicated Entries in a Range -- Tip 45: Using the AGGREGATE Function -- Tip 46: Making an Exact Copy of a Range of Formulas -- Tip 47: Using the Background Error-Checking Features -- Tip 48: Using the Inquire Add-In -- Workbook analysis -- Diagram tools -- Compare files -- Other options -- Tip 49: Hiding and Locking Your Formulas -- Hiding and locking formula cells -- Unlocking nonformula cells -- Protecting the worksheet -- Tip 50: Using the INDIRECT Function -- Specifying rows indirectly -- Specifying worksheet names indirectly -- Making a cell reference unchangeable -- Tip 51: Formula Editing in Dialog Boxes -- Tip 52: Converting a Vertical Range to a Table -- Part IV: Working with Data -- Tips and Where to Find Them -- Tip 53: Selecting Cells Efficiently -- Selecting a range by using the Shift and arrow keys -- Selecting the current region -- Selecting a range by Shift+clicking -- Selecting noncontiguous ranges -- Selecting entire rows -- Selecting entire columns -- Selecting multisheet ranges.

Tip 54: Automatically Filling a Range with a Series -- Tip 55: Fixing Trailing Minus Signs -- Tip 56: Restricting Cursor Movement to Input Cells -- Tip 57: Transforming Data with and Without Using Formulas -- Transforming data without formulas -- Transforming data by using temporary formulas -- Tip 58: Creating a Drop-Down List in a Cell -- Tip 59: Comparing Two Ranges by Using Conditional Formatting -- Tip 60: Finding Duplicates by Using Conditional Formatting -- Tip 61: Working with Credit Card Numbers -- Entering credit card numbers manually -- Importing credit card numbers -- Tip 62: Identifying Excess Spaces -- Tip 63: Transposing a Range -- Using Paste Special -- Using the TRANSPOSE function -- Tip 64: Using Flash Fill to Extract Data -- Changing the case of text -- Extracting last names -- Extracting first names -- Extracting middle names -- Extracting domain names from URLs -- Potential problems -- Tip 65: Using Flash Fill to Combine Data -- Tip 66: Inserting Stock Information -- Hiding irrelevant rows and columns -- Behind the scenes -- Tip 67: Getting Data from a Web Page -- Pasting static information -- Pasting refreshable information -- Opening the web page directly -- Tip 68: Importing a Text File into a Worksheet Range -- Tip 69: Using the Quick Analysis Feature -- Tip 70: Filling the Gaps in a Report -- Tip 71: Performing Inexact Searches -- Tip 72: Proofing Your Data with Audio -- Adding speech commands to the Ribbon -- Using the speech commands -- Tip 73: Getting Data from a PDF File -- Using copy and paste -- Using Word 2013 as an intermediary -- Part V: Tables and Pivot Tables -- Tips and Where to Find Them -- Tip 74: Understanding Tables -- Understanding what a table is -- Range versus table -- Limitations of using a table -- Tip 75: Using Formulas with a Table -- Working with the Total row -- Using formulas within a table.

Referencing data in a table -- Tip 76: Numbering Table Rows Automatically -- Tip 77: Identifying Data Appropriate for a Pivot Table -- Tip 78: Using a Pivot Table Instead of Formulas -- Inserting subtotals -- Using formulas -- Using Excel's PivotTable feature -- Tip 79: Controlling References to Cells Within a Pivot Table -- Tip 80: Creating a Quick Frequency Tabulation -- Tip 81: Grouping Items by Date in a Pivot Table -- Tip 82: Creating Pivot Tables with Multiple Groupings -- Tip 83: Using Pivot Table Slicers and Timelines -- Using slicers -- Using a timeline -- Part VI: Charts and Graphics -- Tips and Where to Find Them -- Tip 84: Understanding Recommended Charts -- Tip 85: Customizing Charts -- Adding or removing chart elements -- Modifying a chart style or colors -- Filtering chart data -- Tip 86: Making Charts the Same Size -- Tip 87: Creating a Chart Template -- Creating a template -- Using a template -- Tip 88: Creating a Combination Chart -- Inserting a preconfigured combination chart -- Customizing a combination chart -- Tip 89: Handling Missing Data in a Chart -- Tip 90: Using High-Low Lines in a Chart -- Tip 91: Using Multi-Level Category Labels -- Tip 92: Linking Chart Text to Cells -- Tip 93: Freezing a Chart -- Converting a chart into a picture -- Converting range references into arrays -- Tip 94: Creating a Chart Directly in a Range -- Using conditional formatting data bars -- Using formulas to display repeating characters -- Tip 95: Creating Minimalistic Charts -- Simple column charts -- Simple pie charts -- Simple line charts -- A gauge chart -- Tip 96: Applying Chart Data Labels from a Range -- Tip 97: Grouping Charts and Other Objects -- Grouping charts -- Grouping other objects -- Tip 98: Taking Pictures of Ranges -- Creating a static image of a range -- Creating a live image of a range -- Saving a range as a graphic image.

Tip 99: Changing the Look of Cell Comments.