John Walkenbach's Favorite Excel Tips & Tricks

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780764598166 | 0764598163
Cover: Paperback
Copyright: 7/8/2005

Buy Used

Usually Ships in 2-4 Business Days

$33.07
Buy New

Print on Demand: 2-4 Weeks. This item cannot be cancelled or returned.

$43.20

This unique book offers valuable tips and tricks to maximize the capabilities of Excel, the indispensable and immensely popular spreadsheet application An ideal resource for advancing beginners and intermediate-level users, this book offers shortcuts that are sure to speed up application development with Excel and take readers' skills to the next level Some of the author's favorite tips and tricks include dealing with function arguments, creating "impossible" charts, tweaking Pivot Tables, copying print settings across sheets, taming "automatic" toolbars, and using a UserForm Additional tips and tricks show how to create dynamic chart data, use Analysis Toolpak, sort on more than three columns, attach toolbars to worksheets, enter fake data for testing purposes, apply custom functions, and much more

Introduction

(1)

What You Should Know

(1)

What You Should Have

(1)

Conventions in This Book

(2)

Entering VBA Code

(1)

How This Book Is Organized

(1)

How to Use This Book

(1)

About the Power Utility Pak Offer

(1)

Reach Out

(1)

Part I: Basic Excel Usage

(60)

Understanding Excel Versions

(2)

Maximizing Menu Efficiency

(2)

Selecting Cells Efficiently

(4)

Making ``Special'' Range Selections

(4)

Undoing, Redoing, and Repeating

(2)

Changing the Number of Undo Levels

(2)

Discovering Some Useful Shortcut Keys

(2)

Navigating Sheets in a Workbook

(2)

Resetting the Used Area of a Worksheet

(2)

Understanding Workbooks versus Windows

(2)

Avoiding the Task Pane When Using Excel 2003 Help

(2)

Customizing the Default Workbook

(2)

Changing the Sheet Tab Appearance

(2)

Hiding User Interface Elements

(2)

Hiding Columns or Rows

(2)

Hiding Cell Contents

(2)

Performing Inexact Searches

(2)

Replacing Formatting

(2)

Increasing the Number of Rows and Columns

(2)

Limiting the Usable Area in a Worksheet

(4)

Using an Alternative to Cell Comments

(2)

Changing the Text Size in Excel's Help Window

(2)

Making a Worksheet ``Very Hidden''

(2)

Troubleshooting Common Setup Problems

(6)

Part II: Data Entry

(36)

Understanding the Types of Data

(4)

Moving the Cell Pointer after Entering Data

(2)

Selecting a Range of Input Cells before Entering Data

(2)

Using AutoComplete to Automate Data Entry

(2)

Keeping Titles in View by Freezing Panes

(2)

Automatically Filling a Range with a Series

(4)

Working with Fractions

(2)

Proofing Your Data with Audio

(2)

Controlling Automatic Hyperlinks

(2)

Entering Credit Card Numbers

(2)

Using Excel's Built-In Data Entry Form

(2)

Customizing and Sharing AutoCorrect Entries

(2)

Restricting Cursor Movement to Input Cells

(2)

Controlling the Office Clipboard

(2)

Creating a Drop-Down List in a Cell

101

(2)

Part III: Formatting

103

(54)

Quick Number Formatting

105

(2)

Using ``Tear Off'' Toolbars

107

(2)

Creating Custom Number Formats

109

(4)

Using Custom Number Formats to Scale Values

113

(4)

Using Custom Date Formatting

117

(2)

Some Useful Custom Number Formats

119

(4)

Showing Text and a Value in a Cell

123

(2)

Merging Cells

125

(2)

Formatting Individual Characters in a Cell

127

(2)

Displaying Times That Exceed 24 Hours

129

(2)

Fixing Non-Numeric Numbers

131

(2)

Using AutoFormats

133

(2)

Dealing with Gridlines, Borders, and Underlines

135

(2)

Creating 3D Formatting Effects

137

(2)

Wrapping Text in a Cell

139

(2)

Seeing All Characters in a Font

141

(2)

Entering Special Characters

143

(2)

Using Named Styles

145

(2)

Understanding How Excel Handles Color

147

(4)

Setting Up Alternate Row Shading

151

(4)

Adding a Background Image to a Worksheet

155

(2)

Part IV: Basic Formulas and Functions

157

(50)

When to Use Absolute References

159

(2)

When to Use Mixed References

161

(2)

Changing the Type of a Cell Reference

163

(2)

AutoSum Tricks

165

(2)

Using the Status Bar Selection Statistics Feature

167

(2)

Converting Formulas to Values

169

(2)

Transforming Data without Using Formulas

171

(2)

Transforming Data by Using Formulas

173

(2)

Deleting Values While Keeping Formulas

175

(2)

Dealing with Function Arguments

177

(2)

Annotating a Formula without Using a Comment

179

(2)

Making an Exact Copy of a Range of Formulas

181

(2)

Monitoring Formula Cells from Any Location

183

(2)

Displaying and Printing Formulas

185

(2)

Avoiding Error Displays in Formulas

187

(2)

Using Goal Seeking

189

(2)

Understanding the Secret about Names

191

(2)

Using Named Constants

193

(2)

Using Functions in Names

195

(2)

Editing Name References

197

(2)

Using Dynamic Names

199

(2)

Creating Worksheet-Level Names

201

(2)

Working with Pre-1900 Dates

203

(2)

Working with Negative Time Values

205

(2)

Part V: Useful Formula Examples

207

(64)

Calculating Holidays

209

(4)

Calculating a Weighted Average

213

(2)

Calculating a Person's Age

215

(2)

Ranking Values with an Array Formula

217

(2)

Counting Characters in a Cell

219

(2)

Expressing a Number as an Ordinal

221

(2)

Extracting Words from a String

223

(2)

Parsing Names

225

(2)

Removing Titles from Names

227

(2)

Generating a Series of Dates

229

(2)

Determining Specific Dates

231

(4)

Displaying a Calendar in a Range

235

(2)

Various Methods of Rounding Numbers

237

(4)

Rounding Time Values

241

(2)

Returning the Last Nonblank Cell in a Column or Row

243

(2)

Using the COUNTIF Function

245

(2)

Counting Cells That Meet Multiple Criteria

247

(4)

Counting Distinct Entries in a Range

251

(2)

Calculating Single-Criterion Conditional Sums

253

(2)

Calculating Multiple-Criterion Conditional Sums

255

(2)

Looking Up an Exact Value

257

(2)

Performing a Two-Way Lookup

259

(2)

Performing a Two-Column Lookup

261

(2)

Performing a Lookup Using an Array

263

(2)

Using the INDIRECT Function

265

(2)

Creating Megaformulas

267

(4)

Part VI: Charts and Graphics

271

(42)

Creating a Text Chart Directly in a Range

273

(2)

Annotating a Chart

275

(2)

Creating a Self-Expanding Chart

277

(4)

Creating Combination Charts

281

(2)

Dealing with Missing Data in a Line Chart

283

(2)

Creating a Gantt Chart

285

(2)

Creating a Thermometer-Style Chart

287

(4)

Creating a Picture Chart

291

(2)

Plotting Single-Variable Mathematical Functions

293

(2)

Plotting Two-Variable Mathematical Functions

295

(2)

Creating a Semi-Transparent Chart Series

297

(2)

Saving a Chart as a Graphics File

299

(2)

Making Charts the Same Size

301

(2)

Displaying Multiple Charts on a Chart Sheet

303

(2)

Freezing a Chart

305

(2)

Adding a Watermark to a Worksheet

307

(2)

Changing the Shape of a Cell Comment

309

(2)

Inserting a Graphic into a Cell Comment

311

(2)

Part VII: Data Analysis and Lists

313

(38)

Using the List Feature in Excel 2003

315

(4)

Sorting on More Than Three Columns

319

(2)

Using Custom Views with AutoFiltering

321

(2)

Putting Advanced Filter Results on a Different Sheet

323

(2)

Comparing Two Ranges with Conditional Formatting

325

(4)

Randomizing a List

329

(2)

Filling the Gaps in a Report

331

(2)

Creating a List from a Summary Table

333

(4)

Finding Duplicates by Using Conditional Formatting

337

(2)

Preventing Row or Column Insertions within a Range

339

(2)

Creating a Quick Frequency Tabulation

341

(2)

Controlling References to Cells within a PivotTable

343

(2)

Grouping Items by Date in a PivotTable

345

(4)

Hiding the Field Buttons in a PivotChart

349

(2)

Part VIII: Working with Files

351

(26)

Importing a Text File into a Worksheet Range

353

(2)

Getting Data from a Web Page

355

(4)

Displaying a Workbook's Full Path

359

(2)

Saving a Preview of Your Workbook

361

(2)

Using Document Properties

363

(2)

Learning Who Opened a File Last

365

(2)

Finding the Missing No To All Button When Closing Files

367

(2)

Getting a List of Filenames

369

(2)

Understanding Excel's Passwords

371

(2)

Using Workspace Files

373

(2)

Reducing the Size of a Workbook

375

(2)

Part IX: Printing

377

(28)

Controlling What Gets Printed

379

(2)

Displaying Repeating Rows or Columns on a Printout

381

(2)

Printing Noncontiguous Ranges on a Single Page

383

(2)

Preventing Objects from Printing

385

(2)

Page Numbering Tips

387

(2)

Previewing Page Breaks

389

(2)

Adding and Removing Page Breaks

391

(2)

Printing to a PDF File

393

(2)

Avoiding Printing Specific Rows

395

(2)

Making Your Printout Fit on One Page

397

(2)

Printing Formulas

399

(2)

Copying Page Setup Settings Across Sheets

401

(2)

Using Custom Views for Printing

403

(2)

Part X: Customizing Toolbars and Menus

405

(18)

Finding the Multifunctional Toolbar Buttons

407

(2)

Finding the Hidden Menu Commands

409

(2)

Customizing Menus and Toolbars

411

(2)

Creating a Custom Toolbar

413

(4)

Taming Pop-Up Toolbars

417

(2)

Attaching Toolbars to Worksheets

419

(2)

Backing Up Your Customized Toolbars and Menus

421

(2)

Part XI: Spotting, Fixing, and Preventing Errors

423

(20)

Using Excel's Error-Checking Features

425

(2)

Identifying Formula Cells

427

(2)

Dealing with Floating-Point Number Problems

429

(2)

Creating a Table of Cell and Range Names

431

(2)

Viewing Names Graphically

433

(2)

Locating Phantom Links

435

(2)

Understanding Displayed versus Actual Values

437

(2)

Tracing Cell Relationships

439

(4)

Part XII: Basic VBA and Macros

443

(40)

Learning about Macros and VBA

445

(2)

Recording a Macro

447

(4)

Understanding Security Issues Related to Macros

451

(2)

Using a Personal Macro Workbook

453

(2)

Understanding Functions versus Subs

455

(2)

Displaying Pop-Up Messages

457

(4)

Getting Information from the User

461

(2)

Running a Macro When a Workbook Is Opened

463

(4)

Creating Simple Worksheet Functions

467

(4)

Making Excel Talk

471

(2)

Understanding Custom Function Limitations

473

(2)

Executing a Menu Item with a Macro

475

(2)

Storing Custom Functions in an Add-In

477

(2)

Displaying a Pop-Up Linked Calendar

479

(2)

Using Add-Ins

481

(2)

Part XIII: Conversions and Mathematical Calculations

483

(28)

Converting Between Measurement Systems

485

(8)

Converting Temperatures

493

(2)

Solving Right Triangles

495

(2)

Calculating Area, Surface, Circumference, and Volume

497

(4)

Solving Simultaneous Equations

501

(2)

Generating Unique Random Integers

503

(2)

Generating Random Numbers

505

(2)

Calculating Roots and a Remainder

507

(2)

Calculating a Conditional Average

509

(2)

Part XIV: Sources for Excel Information

511

(10)

Using Excel's Help System

513

(2)

Searching the Internet for Help

515

(2)

Using Excel Newsgroups

517

(2)

Browsing Excel-Related Web Sites

519

(2)

Index

521

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

John Walkenbach's Favorite Excel Tips & Tricks

Summary

Author Biography

Table of Contents

Supplemental Materials