101 Excel 2013 Tips, Tricks and Timesavers

John Walkenbach, arguably the foremost authority on Excel, has written 50+ books, including the bestselling Excel Bible, Excel Formulas, and Excel Power Programming with VBA, as well as more than 300 articles for publications such as PC World, InfoWorld, and Windows. He created the award-winning Power Utility Pak, and provides Excel information and insight at www.spreadsheetpage.com.

• 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 7Cosmetic changes 7Hiding the Ribbon 8Using options on the View tab 8Hiding other elements 9Hiding the status bar 9Tip 2: Customizing the Quick Access Toolbar 10About the Quick Access toolbar 10Adding new commands to the Quick Access toolbar 10Performing other Quick Access toolbar actions 13Tip 3: Customizing the Ribbon 14How to customize the Ribbon 14Tip 4: Understanding Protected View 17What causes Protected View? 17Printing and copying 18Forcing a file to open in Normal view 18Tip 5: Understanding AutoRecover 20Recovering versions of the current workbook 20Recovering unsaved work 20Tip 6: Using a Workbook in a Browser 22Tip 7: Saving to a Read-Only Format 24Send a printed copy 24Send an electronic copy in the form of a PDF file. 24Send an MHTML file 25Tip 8: Generating a List of Filenames 27Tip 9: Generating a List of Sheet Names 29Tip 10: Using Document Themes 32Applying a theme 34Customizing a theme 35Tip 11: Understanding Excel Compatibility Issues 37The Excel 2013 file formats 37The Office Compatibility Pack 37Checking compatibility 38Tip 12: Where to Change Printer Settings 39Part II: FormattingTip 13: Working with Merged Cells 43Other merge actions 44Potential problems with merged cells 44Locating all merged cells 45Unmerging all merged cells 46Alternatives to merged cells 47Tip 14: Indenting Cell Contents 48Tip 15: Using Named Styles 50Using the Style gallery 50Modifying an existing style 51Creating new styles 52Merging styles from other workbooks 53Tip 16: Creating Custom Number Formats 54Parts of a number format string 55Custom number format codes 55Tip 17: Using Custom Number Formats to Scale Values 58Tip 18: Creating a Bulleted List 60Using a bullet character. 60Using SmartArt 61Tip 19: Shading Alternate Rows Using Conditional Formatting 62Displaying alternate row shading 62Creating checkerboard shading 63Shading groups of rows 64Tip 20: Formatting Individual Characters in a Cell 65Tip 21: Using the Format Painter 66Painting basics 66Format Painter variations 67Tip 22: Inserting a Watermark 68Tip 23: Showing Text and a Value in a Cell 70Using concatenation 70Using the TEXT function 71Using a custom number format 71Tip 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 83About the Watch Window 83Customizing the Watch Window 84Navigating with the Watch Window 84Tip 28: Learning Some AutoSum Tricks 85Tip 29: Knowing When to Use Absolute and Mixed References 87Using absolute references 87Using mixed references 88Tip 30: Avoiding Error Displays in Formulas 90Using the IFERROR function 90Using the ISERROR function 91Tip 31: Creating Worksheet-Level Names 92Tip 32: Using Named Constants 94Tip 33: Sending Personalized E-Mail from Excel 96About the HYPERLINK function 96A practical example using HYPERLINK 97Tip 34: Looking Up an Exact Value 99Tip 35: Performing a Two-Way Lookup 101Using a formula 101Using implicit intersection 102Tip 36: Performing a Two-Column Lookup 103Tip 37: Calculating Holidays 105New Year’s Day 105Martin Luther King Jr. Day 105Presidents’ Day 106Easter 106Memorial Day 106Independence Day 106Labor Day 107Columbus Day 107Veterans Day 107Thanksgiving Day 107Christmas Day 107Tip 38: Calculating a Person’s Age 108Method 1 108Method 2 108Method 3 108Tip 39: Working with Pre-1900 Dates 110Use three columns 110Use custom functions 111Use a different product 113Tip 40: Displaying a Live Calendar in a Range 114Tip 41: Returning the Last Nonblank Cell in a Column or Row 116Cell counting method 116Array formula method 117Standard formula method 117Tip 42: Various Methods of Rounding Numbers 118Rounding to the nearest multiple 118Rounding currency values 119Using the INT and TRUNC functions 119Rounding to n significant digits 120Tip 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 132Workbook analysis 132Diagram tools 133Compare files 133Other options 134Tip 49: Hiding and Locking Your Formulas 135Hiding and locking formula cells 135Unlocking nonformula cells 136Protecting the worksheet 136Tip 50: Using the INDIRECT Function 138Specifying rows indirectly 138Specifying worksheet names indirectly 139Making a cell reference unchangeable 140Tip 51: Formula Editing in Dialog Boxes 141Tip 52: Converting a Vertical Range to a Table 142Part IV: Working with DataTip 53: Selecting Cells Efficiently 147Selecting a range by using the Shift and arrow keys 147Selecting the current region 148Selecting a range by Shift+clicking 148Selecting noncontiguous ranges 148Selecting entire rows 149Selecting entire columns 149Selecting multisheet ranges 149Tip 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 157Transforming data without formulas 157Transforming data by using temporary formulas 158Tip 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 168Entering credit card numbers manually 168Importing credit card numbers 169Tip 62: Identifying Excess Spaces 170Tip 63: Transposing a Range 173Using Paste Special 173Using the TRANSPOSE function 174Tip 64: Using Flash Fill to Extract Data 176Changing the case of text 176Extracting last names 177Extracting first names. 177Extracting middle names 178Extracting domain names from URLs 178Potential problems 178Tip 65: Using Flash Fill to Combine Data 179Tip 66: Inserting Stock Information 181Hiding irrelevant rows and columns 182Behind the scenes 182Tip 67: Getting Data from a Web Page 184Pasting static information 184Pasting refreshable information 185Opening the web page directly 187Tip 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 196Adding speech commands to the Ribbon 196Using the speech commands 196Tip 73: Getting Data from a PDF File 198Using copy and paste 198Using Word 2013 as an intermediary 200Part V: Tables and Pivot TablesTip 74: Understanding Tables 205Understanding what a table is 205Range versus table 206Limitations of using a table 207Tip 75: Using Formulas with a Table 208Working with the Total row 208Using formulas within a table 209Referencing data in a table 211Tip 76: Numbering Table Rows Automatically 212Tip 77: Identifying Data Appropriate for a Pivot Table 214Tip 78: Using a Pivot Table Instead of Formulas 218Inserting subtotals 218Using formulas 220Using Excel’s PivotTable feature 220Tip 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 232Using slicers 232Using a timeline 234Part VI: Charts and GraphicsTip 84: Understanding Recommended Charts 239Tip 85: Customizing Charts 241Adding or removing chart elements 241Modifying a chart style or colors 241Filtering chart data 242Tip 86: Making Charts the Same Size 243Tip 87: Creating a Chart Template 245Creating a template 245Using a template 246Tip 88: Creating a Combination Chart 247Inserting a preconfigured combination chart 247Customizing a combination chart 248Tip 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 257Converting a chart into a picture 257Converting range references into arrays 258Tip 94: Creating a Chart Directly in a Range 260Using conditional formatting data bars 260Using formulas to display repeating characters 261Tip 95: Creating Minimalistic Charts 264Simple column charts 264Simple pie charts 264Simple line charts 265A gauge chart 266Tip 96: Applying Chart Data Labels from a Range 268Tip 97: Grouping Charts and Other Objects 270Grouping charts 270Grouping other objects 271Tip 98: Taking Pictures of Ranges 273Creating a static image of a range 273Creating a live image of a range 274Saving a range as a graphic image 275Tip 99: Changing the Look of Cell Comments 276Setting up your Quick Access toolbar 276Formatting a comment 276Changing the shape of a comment 277Adding an image to a cell comment 278Tip 100: Enhancing Images 279Tip 101: Saving Shapes, Charts, and Ranges as Images 281Index 283