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 tables 101 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!
Table of contents
Part I: Workbooks and Files Tip 1: Changing the Look of Excel Tip 2: Customizing the Quick Access Toolbar Tip 3: Customizing the Ribbon Tip 4: Understanding Protected View Tip 5: Understanding AutoRecover Tip 6: Using a Workbook in a Browser Tip 7: Saving to a Read-Only Format Tip 8: Generating a List of Filenames Tip 9: Generating a List of Sheet Names Tip 10: Using Document Themes Tip 11: Understanding Excel Compatibility Issues Tip 12: Where to Change Printer Settings Part II: Formatting Tip 13: Working with Merged Cells Tip 14: Indenting Cell Contents Tip 15: Using Named Styles Tip 16: Creating Custom Number Formats Tip 17: Using Custom Number Formats to Scale Values Tip 18: Creating a Bulleted List Tip 19: Shading Alternate Rows Using Conditional Formatting Tip 20: Formatting Individual Characters in a Cell Tip 21: Using the Format Painter Tip 22: Inserting a Watermark Tip 23: Showing Text and a Value in a Cell Tip 24: Avoiding Font Substitution for Small Point Sizes Tip 25: Updating Old Fonts Part III: Formulas Tip 26: Resizing the Formula Bar Tip 27: Monitoring Formula Cells from Any Location Tip 28: Learning Some AutoSum Tricks Tip 29: Knowing When to Use Absolute and Mixed References Tip 30: Avoiding Error Displays in Formulas Tip 31: Creating Worksheet-Level Names Tip 32: Using Named Constants Tip 33: Sending Personalized E-Mail from Excel Tip 34: Looking Up an Exact Value Tip 35: Performing a Two-Way Lookup Tip 36: Performing a Two-Column Lookup Tip 37: Calculating Holidays Tip 38: Calculating a Person’s Age Tip 39: Working with Pre-1900 Dates Tip 40: Displaying a Live Calendar in a Range Tip 41: Returning the Last Nonblank Cell in a Column or Row Tip 42: Various Methods of Rounding Numbers 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 Tip 49: Hiding and Locking Your Formulas Tip 50: Using the INDIRECT Function Tip 51: Formula Editing in Dialog Boxes Tip 52: Converting a Vertical Range to a Table Part IV: Working with Data Tip 53: Selecting Cells Efficiently 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 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 Tip 62: Identifying Excess Spaces Tip 63: Transposing a Range Tip 64: Using Flash Fill to Extract Data Tip 65: Using Flash Fill to Combine Data Tip 66: Inserting Stock Information Tip 67: Getting Data from a Web Page 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 Tip 73: Getting Data from a PDF File Part V: Tables and Pivot Tables Tip 74: Understanding Tables Tip 75: Using Formulas with 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 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 Part VI: Charts and Graphics Tip 84: Understanding Recommended Charts Tip 85: Customizing Charts Tip 86: Making Charts the Same Size Tip 87: Creating a Chart Template Tip 88: Creating 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 Tip 94: Creating a Chart Directly in a Range Tip 95: Creating Minimalistic Charts Tip 96: Applying Chart Data Labels from a Range Tip 97: Grouping Charts and Other Objects Tip 98: Taking Pictures of Ranges Tip 99: Changing the Look of Cell Comments Tip 100: Enhancing Images Tip 101: Saving Shapes, Charts, and Ranges as Images
About The Author
John Walkenbach, a.k.a. Mr. Spreadsheet, is arguably the world's foremost authority on Microsoft Excel. His fifty-plus Excel books include Excel 2013 Power Programming with VBA, Excel 2013 Formulas, and the bestselling Excel 2013 Bible, all published by Wiley. He has also written hundreds of articles and software reviews, and created the award-winning Power Utility Pak add-in for Excel.
John lives in Tucson, Arizona. He also plays the banjo - but don't let that prevent you from buying his books.