Title Details

Introduction 1

Part I: Basic Excel Usage

Tip 1: Understanding Excel Versions 9

Tip 2: Maximizing Ribbon Efficiency 12

Tip 3: Understanding Protected View 15

Tip 4: Selecting Cells Efficiently 17

Tip 5: Making “Special” Range Selections 20

Tip 6: Undoing, Redoing, and Repeating 22

Tip 7: Discovering Some Useful Shortcut Keys 25

Tip 8: Navigating Sheets in a Workbook 26

Tip 9: Resetting the Used Area of a Worksheet 27

Tip 10: Understanding Workbooks versus Windows 28

Tip 11: Customizing the Quick Access Toolbar 30

Tip 12: Customizing the Ribbon 34

Tip 13: Accessing the Ribbon with Your Keyboard 37

Tip 14: Recovering Your Work 38

Tip 15: Customizing the Default Workbook 40

Tip 16: Using Document Themes 42

Tip 17: Hiding User Interface Elements 47

Tip 18: Hiding Columns or Rows 50

Tip 19: Hiding Cell Contents 52

Tip 20: Taking Pictures of Ranges 53

Tip 21: Performing Inexact Searches 55

Tip 22: Replacing Formatting 57

Tip 23: Changing the Excel Color Scheme 60

Tip 24: Limiting the Usable Area in a Worksheet 61

Tip 25: Using an Alternative to Cell Comments 64

Tip 26: Understanding the Excel Help System 65

Tip 27: Making a Worksheet “Very Hidden” 68

Tip 28: Working with the Backstage View 70

Part II: Data Entry

Tip 29: Understanding the Types of Data 73

Tip 30: Moving the Cell Pointer after Entering Data 77

Tip 31: Selecting a Range of Input Cells before Entering Data 78

Tip 32: Using AutoComplete to Automate Data Entry 79

Tip 33: Removing Duplicate Rows 81

Tip 34: Keeping Titles in View 83

Tip 35: Automatically Filling a Range with a Series 84

Tip 36: Working with Fractions 87

Tip 37: Resizing the Formula Bar 89

Tip 38: Proofing Your Data with Audio 91

Tip 39: Controlling Automatic Hyperlinks 93

Tip 40: Entering Credit Card Numbers 95

Tip 41: Using the Excel Built-In Data Entry Form 96

Tip 42: Customizing and Sharing AutoCorrect Entries 98

Tip 43: Restricting Cursor Movement to Input Cells 100

Tip 44: Controlling the Office Clipboard 102

Tip 45: Creating a Drop-Down List in a Cell 104

Part III: Formatting

Tip 46: Using the Mini Toolbar 109

Tip 47: Indenting Cell Contents 110

Tip 48: Quick Number Formatting 112

Tip 49: Creating Custom Number Formats 113

Tip 50: Using Custom Number Formats to Scale Values 117

Tip 51: Using Custom Date and Time Formatting 119

Tip 52: Examining Some Useful Custom Number Formats 120

Tip 53: Updating Old Fonts 123

Tip 54: Understanding Conditional Formatting Visualization 125

Tip 55: Showing Text and a Value in a Cell 128

Tip 56: Merging Cells 130

Tip 57: Formatting Individual Characters in a Cell 131

Tip 58: Displaying Times That Exceed 24 Hours 132

Tip 59: Fixing Non-Numeric Numbers 134

Tip 60: Adding a Frame to a Range 135

Tip 61: Dealing with Gridlines, Borders, and Underlines 136

Tip 62: Inserting a Watermark 138

Tip 63: Adding a Background Image to a Worksheet 140

Tip 64: Wrapping Text in a Cell 141

Tip 65: Seeing All Characters in a Font 143

Tip 66: Entering Special Characters 145

Tip 67: Using Named Styles 147

Part IV: Basic Formulas and Functions

Tip 68: Using Formula AutoComplete 153

Tip 69: Knowing When to Use Absolute References 155

Tip 70: Knowing When to Use Mixed References 157

Tip 71: Changing the Type of a Cell Reference 159

Tip 72: Converting a Vertical Range to a Table 160

Tip 73: AutoSum Tricks 162

Tip 74: Using the Status Bar Selection Statistics Feature 164

Tip 75: Converting Formulas to Values 166

Tip 76: Transforming Data without Using Formulas 167

Tip 77: Transforming Data by Using Temporary Formulas 168

Tip 78: Deleting Values While Keeping Formulas 170

Tip 79: Summing Across Sheets 171

Tip 80: Dealing with Function Arguments 173

Tip 81: Annotating a Formula without Using a Comment 175

Tip 82: Making an Exact Copy of a Range of Formulas 176

Tip 83: Monitoring Formula Cells from Any Location 178

Tip 84: Displaying and Printing Formulas 179

Tip 85: Avoiding Error Displays in Formulas 181

Tip 86: Using Goal Seeking 183

Tip 87: Understanding the Secret about Names 185

Tip 88: Using Named Constants 187

Tip 89: Using Functions in Names 189

Tip 90: Creating a List of Names 191

Tip 91: Using Dynamic Names 193

Tip 92: Creating Worksheet-Level Names 196

Tip 93: Working with Pre-1900 Dates 198

Tip 94: Working with Negative Time Values 200

Part V: Useful Formula Examples

Tip 95: Calculating Holidays 205

Tip 96: Calculating a Weighted Average 208

Tip 97: Calculating a Person’s Age 209

Tip 98: Ranking Values 211

Tip 99: Converting Inches to Feet and Inches 213

Tip 100: Using the DATEDIF Function 214

Tip 101: Counting Characters in a Cell 216

Tip 102: Numbering Weeks 218

Tip 103: Using a Pivot Table Instead of Formulas 220

Tip 104: Expressing a Number as an Ordinal 224

Tip 105: Extracting Words from a String 226

Tip 106: Parsing Names 228

Tip 107: Removing Titles from Names 230

Tip 108: Generating a Series of Dates 231

Tip 109: Determining Specific Dates 233

Tip 110: Displaying a Calendar in a Range 236

Tip 111: Various Methods of Rounding Numbers 237

Tip 112: Rounding Time Values 240

Tip 113: Using the New AGGREGATE Function 241

Tip 114: Returning the Last Nonblank Cell in a Column or Row 244

Tip 115: Using the COUNTIF Function 246

Tip 116: Counting Cells That Meet Multiple Criteria 247

Tip 117: Counting Nonduplicated Entries in a Range 251

Tip 118: Calculating Single-Criterion Conditional Sums 252

Tip 119: Calculating Multiple-Criterion Conditional Sums 254

Tip 120: Looking Up an Exact Value 256

Tip 121: Performing a Two-Way Lookup 258

Tip 122: Performing a Two-Column Lookup 260

Tip 123: Performing a Lookup by Using an Array 262

Tip 124: Using the INDIRECT Function 264

Tip 125: Creating Megaformulas 267

Part VI: Conversions and Mathematical Calculations

Tip 126: Converting Between Measurement Systems 273

Tip 127: Converting Temperatures 275

Tip 128: Solving Simultaneous Equations 276

Tip 129: Solving Recursive Equations 278

Tip 130: Generating Random Numbers 280

Tip 131: Calculating Roots 282

Tip 132: Calculating a Remainder 283

Part VII: Charts and Graphics

Tip 133: Creating a Text Chart Directly in a Range 287

Tip 134: Selecting Elements in a Chart 290

Tip 135: Creating a Self-Expanding Chart 293

Tip 136: Creating Combination Charts 294

Tip 137: Creating a Gantt Chart 297

Tip 138: Creating a Gauge Chart 299

Tip 139: Using Pictures in Charts 301

Tip 140: Plotting Mathematical Functions 303

Tip 141: Using High-Low Lines in a Chart 306

Tip 142: Linking Chart Text to Cells 307

Tip 143: Creating a Chart Template 308

Tip 144: Saving a Chart as a Graphics File 309

Tip 145: Saving a Range as a Graphic Image 311

Tip 146: Making Charts the Same Size 312

Tip 147: Resetting All Chart Formatting 314

Tip 148: Freezing a Chart 316

Tip 149: Creating Picture Effects with a Chart 318

Tip 150: Creating Sparkline Graphics 319

Tip 151: Selecting Objects on a Worksheet 321

Tip 152: Making a Greeting Card 323

Tip 153: Enhancing Text Formatting in Shapes 325

Tip 154: Using Images as Line Chart Markers 327

Tip 155: Changing the Shape of a Cell Comment 329

Tip 156: Adding an Image to a Cell Comment 330

Tip 157: Enhancing Images 331

Part VIII: Data Analysis and Lists

Tip 158: Using the Table Feature 335

Tip 159: Working with Tables 338

Tip 160: Using Formulas with a Table 341

Tip 161: Numbering Rows in a Table 345

Tip 162: Using Custom Views with Filtering 347

Tip 163: Putting Advanced Filter Results on a Different Sheet 349

Tip 164: Comparing Two Ranges by Using Conditional Formatting 350

Tip 165: Randomizing a List 353

Tip 166: Filling the Gaps in a Report 355

Tip 167: Creating a List from a Summary Table 357

Tip 168: Finding Duplicates by Using Conditional Formatting 360

Tip 169: Creating a Quick Frequency Tabulation 362

Tip 170: Controlling References to Cells within a Pivot Table 365

Tip 171: Grouping Items by Date in a Pivot Table 366

Tip 172: Unlinking a Pivot Table from Its Source 369

Tip 173: Using Pivot Table Slicers 371

Part IX: Working with Files

Tip 174: Understanding the New Excel File Formats 377

Tip 175: Importing a Text File into a Worksheet Range 379

Tip 176: Getting Data from a Web Page 381

Tip 177: Displaying a Workbook’s Full Path 385

Tip 178: Using Document Properties 387

Tip 179: Inspecting a Workbook 389

Tip 180: Finding the Missing No to All Button When Closing Files 391

Tip 181: Getting a List of Filenames 392

Tip 182: Using Workspace Files 394

Part X: Printing

Tip 183: Controlling What Gets Printed 397

Tip 184: Displaying Repeated Rows or Columns on a Printout 399

Tip 185: Printing Noncontiguous Ranges on a Single Page 400

Tip 186: Preventing Objects from Printing 403

Tip 187: Page-Numbering Tips 404

Tip 188: Adding and Removing Page Breaks 406

Tip 189: Saving to a PDF File 407

Tip 190: Making Your Printout Fit on One Page 408

Tip 191: Printing the Contents of a Cell in a Header or Footer 410

Tip 192: Copying Page Setup Settings Across Sheets 412

Tip 193: Printing Cell Comments 413

Tip 194: Printing a Giant Banner 414

Part XI: Spotting, Fixing, and Preventing Errors

Tip 195: Using the Excel Error-Checking Features 419

Tip 196: Identifying Formula Cells 421

Tip 197: Dealing with Floating-Point Number Problems 424

Tip 198: Removing Excess Spaces 426

Tip 199: Viewing Names Graphically 428

Tip 200: Locating Phantom Links 429

Tip 201: Understanding Displayed versus Actual Values 430

Tip 202: Tracing Cell Relationships 431

Part XII: Basic VBA and Macros

Tip 203: Learning about Macros and VBA 435

Tip 204: Recording a Macro 437

Tip 205: Executing Macros 439

Tip 206: Understanding Functions Versus Subs 442

Tip 207: Creating Simple Worksheet Functions 444

Tip 208: Describing Function Arguments 447

Tip 209: Making Excel Talk 449

Tip 210: Understanding Custom Function Limitations 450

Tip 211: Executing a Ribbon Command with a Macro 451

Tip 212: Understanding Security Issues Related to Macros 453

Tip 213: Using a Personal Macro Workbook 455

Index 457