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

Tip 2: Customizing the Quick Access Toolbar 10

Tip 3: Customizing the Ribbon 14

Tip 4: Understanding Protected View 17

Tip 5: Understanding AutoRecover 20

Tip 6: Using a Workbook in a Browser 22

Tip 7: Saving to a Read-Only Format 24

Tip 8: Generating a List of Filenames 27

Tip 9: Generating a List of Sheet Names 29

Tip 10: Using Document Themes 32

Tip 11: Understanding Excel Compatibility Issues 37

Tip 12: Where to Change Printer Settings 39

Part II: Formatting

Tip 13: Working with Merged Cells 43

Tip 14: Indenting Cell Contents 48

Tip 15: Using Named Styles 50

Tip 16: Creating Custom Number Formats 54

Tip 17: Using Custom Number Formats to Scale Values 58

Tip 18: Creating a Bulleted List 60

Tip 19: Shading Alternate Rows Using Conditional Formatting 62

Tip 20: Formatting Individual Characters in a Cell 65

Tip 21: Using the Format Painter 66

Tip 22: Inserting a Watermark 68

Tip 23: Showing Text and a Value in a Cell 70

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

Tip 28: Learning Some AutoSum Tricks 85

Tip 29: Knowing When to Use Absolute and Mixed References 87

Tip 30: Avoiding Error Displays in Formulas 90

Tip 31: Creating Worksheet-Level Names 92

Tip 32: Using Named Constants 94

Tip 33: Sending Personalized E-Mail from Excel 96

Tip 34: Looking Up an Exact Value 99

Tip 35: Performing a Two-Way Lookup 101

Tip 36: Performing a Two-Column Lookup 103

Tip 37: Calculating Holidays 105

Tip 38: Calculating a Person's Age 108

Tip 39: Working with Pre-1900 Dates 110

Tip 40: Displaying a Live Calendar in a Range 114

Tip 41: Returning the Last Nonblank Cell in a Column or Row 116

Tip 42: Various Methods of Rounding Numbers 118

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

Tip 49: Hiding and Locking Your Formulas 135

Tip 50: Using the INDIRECT Function 138

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

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

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

Tip 62: Identifying Excess Spaces 170

Tip 63: Transposing a Range 173

Tip 64: Using Flash Fill to Extract Data 176

Tip 65: Using Flash Fill to Combine Data 179

Tip 66: Inserting Stock Information 181

Tip 67: Getting Data from a Web Page 184

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

Tip 73: Getting Data from a PDF File 198

Part V: Tables and Pivot Tables

Tip 74: Understanding Tables 205

Tip 75: Using Formulas with a Table 208

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

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

Part VI: Charts and Graphics

Tip 84: Understanding Recommended Charts 239

Tip 85: Customizing Charts 241

Tip 86: Making Charts the Same Size 243

Tip 87: Creating a Chart Template 245

Tip 88: Creating a Combination Chart 247

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

Tip 94: Creating a Chart Directly in a Range 260

Tip 95: Creating Minimalistic Charts 264

Tip 96: Applying Chart Data Labels from a Range 268

Tip 97: Grouping Charts and Other Objects 270

Tip 98: Taking Pictures of Ranges 273

Tip 99: Changing the Look of Cell Comments 276

Tip 100: Enhancing Images 279

Tip 101: Saving Shapes, Charts, and Ranges as Images 281

Index 283