John Walkenbach's Favorite Excel 2010 Tips and Tricks

Chapter 4. Basic Formulas and Functions

The ability to create formulas is what makes a spreadsheet a spreadsheet. In this part, you'll find formula-related tips that can make your workbooks more powerful than ever.

Tips and Where to Find Them

• 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

Using Formula AutoComplete

Excel 2007 introduced a useful feature known as Formula AutoComplete. ...