101 Excel 2013 Tips, Tricks and Timesavers

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