Get the most out of Excel 2013 with this exceptional advice from Mr. Spreadsheet himself!Excel 2013 is excellent, but there's lots to learn to truly excel at Excel! In this latest addition to his popular Mr. Spreadsheet's Bookshelf series, John Walkenbach, aka "Mr. Spreadsheet," shares new and exciting ways to accomplish and master all of your spreadsheet tasks. From taming the Ribbon bar to testing and tables, creating custom functions, and overcoming "impossible" charts, mixing nesting limits, and more, 101 Excel 2013 Tips, Tricks, & Timesavers will save you time and help you avoid common spreadsheet stumbling blocks.* Reveals ways to maximize the power of Excel to create robust applications* Draws on John Walkenbach's years of experience using Excel and writing more than 50 books* Shares tips and tricks for dealing with function arguments, creating add-ins, using UserForms, working with dynamic chart data, and changing data entry orientation* Provides shortcuts and helpful techniques for sorting more than three columns, entering fake data for testing purposes, and setting up powerful pivot tables101 Excel 2013 Tips, Tricks, & Timesavers is packed with information that you need to know in order to confidently and seamlessly master the challenges that come with using Excel!

Introduction 1What You Should Know 1What You Should Have 1Conventions in This Book 2Formula listings 2Key names 2The Ribbon 2Functions, procedures, and named ranges 3Mouse conventions 3What the icons mean 3How This Book Is Organized 4How to Use This Book 4About the Power Utility Pak Offer 4Part I: Workbooks and FilesTip 1: Changing the Look of Excel 7Tip 2: Customizing the Quick Access Toolbar 10Tip 3: Customizing the Ribbon 14Tip 4: Understanding Protected View 17Tip 5: Understanding AutoRecover 20Tip 6: Using a Workbook in a Browser 22Tip 7: Saving to a Read-Only Format 24Tip 8: Generating a List of Filenames 27Tip 9: Generating a List of Sheet Names 29Tip 10: Using Document Themes 32Tip 11: Understanding Excel Compatibility Issues 37Tip 12: Where to Change Printer Settings 39Part II: FormattingTip 13: Working with Merged Cells 43Tip 14: Indenting Cell Contents 48Tip 15: Using Named Styles 50Tip 16: Creating Custom Number Formats 54Tip 17: Using Custom Number Formats to Scale Values 58Tip 18: Creating a Bulleted List 60Tip 19: Shading Alternate Rows Using Conditional Formatting 62Tip 20: Formatting Individual Characters in a Cell 65Tip 21: Using the Format Painter 66Tip 22: Inserting a Watermark 68Tip 23: Showing Text and a Value in a Cell 70Tip 24: Avoiding Font Substitution for Small Point Sizes 72Tip 25: Updating Old Fonts 75Part III: FormulasTip 26: Resizing the Formula Bar 81Tip 27: Monitoring Formula Cells from Any Location 83Tip 28: Learning Some AutoSum Tricks 85Tip 29: Knowing When to Use Absolute and Mixed References 87Tip 30: Avoiding Error Displays in Formulas 90Tip 31: Creating Worksheet-Level Names 92Tip 32: Using Named Constants 94Tip 33: Sending Personalized E-Mail from Excel 96Tip 34: Looking Up an Exact Value 99Tip 35: Performing a Two-Way Lookup 101Tip 36: Performing a Two-Column Lookup 103Tip 37: Calculating Holidays 105Tip 38: Calculating a Person's Age 108Tip 39: Working with Pre-1900 Dates 110Tip 40: Displaying a Live Calendar in a Range 114Tip 41: Returning the Last Nonblank Cell in a Column or Row 116Tip 42: Various Methods of Rounding Numbers 118Tip 43: Converting Between Measurement Systems 121Tip 44: Counting Nonduplicated Entries in a Range 123Tip 45: Using the AGGREGATE Function 125Tip 46: Making an Exact Copy of a Range of Formulas 128Tip 47: Using the Background Error-Checking Features 130Tip 48: Using the Inquire Add-In 132Tip 49: Hiding and Locking Your Formulas 135Tip 50: Using the INDIRECT Function 138Tip 51: Formula Editing in Dialog Boxes 141Tip 52: Converting a Vertical Range to a Table 142Part IV: Working with DataTip 53: Selecting Cells Efficiently 147Tip 54: Automatically Filling a Range with a Series 151Tip 55: Fixing Trailing Minus Signs 154Tip 56: Restricting Cursor Movement to Input Cells 155Tip 57: Transforming Data with and Without Using Formulas 157Tip 58: Creating a Drop-Down List in a Cell 160Tip 59: Comparing Two Ranges by Using Conditional Formatting 162Tip 60: Finding Duplicates by Using Conditional Formatting 165Tip 61: Working with Credit Card Numbers 168Tip 62: Identifying Excess Spaces 170Tip 63: Transposing a Range 173Tip 64: Using Flash Fill to Extract Data 176Tip 65: Using Flash Fill to Combine Data 179Tip 66: Inserting Stock Information 181Tip 67: Getting Data from a Web Page 184Tip 68: Importing a Text File into a Worksheet Range 188Tip 69: Using the Quick Analysis Feature 190Tip 70: Filling the Gaps in a Report 192Tip 71: Performing Inexact Searches 194Tip 72: Proofing Your Data with Audio 196Tip 73: Getting Data from a PDF File 198Part V: Tables and Pivot TablesTip 74: Understanding Tables 205Tip 75: Using Formulas with a Table 208Tip 76: Numbering Table Rows Automatically 212Tip 77: Identifying Data Appropriate for a Pivot Table 214Tip 78: Using a Pivot Table Instead of Formulas 218Tip 79: Controlling References to Cells Within a Pivot Table 222Tip 80: Creating a Quick Frequency Tabulation 224Tip 81: Grouping Items by Date in a Pivot Table 227Tip 82: Creating Pivot Tables with Multiple Groupings 230Tip 83: Using Pivot Table Slicers and Timelines 232Part VI: Charts and GraphicsTip 84: Understanding Recommended Charts 239Tip 85: Customizing Charts 241Tip 86: Making Charts the Same Size 243Tip 87: Creating a Chart Template 245Tip 88: Creating a Combination Chart 247Tip 89: Handling Missing Data in a Chart 250Tip 90: Using High-Low Lines in a Chart 252Tip 91: Using Multi-Level Category Labels 253Tip 92: Linking Chart Text to Cells 255Tip 93: Freezing a Chart 257Tip 94: Creating a Chart Directly in a Range 260Tip 95: Creating Minimalistic Charts 264Tip 96: Applying Chart Data Labels from a Range 268Tip 97: Grouping Charts and Other Objects 270Tip 98: Taking Pictures of Ranges 273Tip 99: Changing the Look of Cell Comments 276Tip 100: Enhancing Images 279Tip 101: Saving Shapes, Charts, and Ranges as Images 281Index 283