John Walkenbach's Favorite Excel Tips & Tricks

by Walkenbach, John

• ISBN13: 9780764598166
• ISBN10: 0764598163
• Format: Paperback
• Copyright: 2005-07-08
• Publisher: Wiley

Summary

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

Author Biography

John Walkenbach is a leading authority on spreadsheet software, and principal of J-Walk and Associates Inc., an Arizona–based consulting firm that specializes in spreadsheet application development. John is the author of about 40 spreadsheet books, and has written more than 300 articles and reviews for a variety of publications, including PC World, InfoWorld, PC Magazine, Windows, and PC/Computing. He also maintains a popular Internet Web site (The Spreadsheet Page, www.j-walk.com/ss), and is the developer of the Power Utility Pak, an award-winning add-in for Microsoft Excel. John graduated from the University of Missouri, and earned a Masters and PhD from the University of Montana.

Table of Contents

Introduction	1	(1)
What You Should Know	1	(1)
What You Should Have	2	(1)
Conventions in This Book	2	(2)
Entering VBA Code	4	(1)
How This Book Is Organized	5	(1)
How to Use This Book	5	(1)
About the Power Utility Pak Offer	6	(1)
Reach Out	6	(1)
Part I: Basic Excel Usage	7	(60)
Understanding Excel Versions	9	(2)
Maximizing Menu Efficiency	11	(2)
Selecting Cells Efficiently	13	(4)
Making ``Special'' Range Selections	17	(4)
Undoing, Redoing, and Repeating	21	(2)
Changing the Number of Undo Levels	23	(2)
Discovering Some Useful Shortcut Keys	25	(2)
Navigating Sheets in a Workbook	27	(2)
Resetting the Used Area of a Worksheet	29	(2)
Understanding Workbooks versus Windows	31	(2)
Avoiding the Task Pane When Using Excel 2003 Help	33	(2)
Customizing the Default Workbook	35	(2)
Changing the Sheet Tab Appearance	37	(2)
Hiding User Interface Elements	39	(2)
Hiding Columns or Rows	41	(2)
Hiding Cell Contents	43	(2)
Performing Inexact Searches	45	(2)
Replacing Formatting	47	(2)
Increasing the Number of Rows and Columns	49	(2)
Limiting the Usable Area in a Worksheet	51	(4)
Using an Alternative to Cell Comments	55	(2)
Changing the Text Size in Excel's Help Window	57	(2)
Making a Worksheet ``Very Hidden''	59	(2)
Troubleshooting Common Setup Problems	61	(6)
Part II: Data Entry	67	(36)
Understanding the Types of Data	69	(4)
Moving the Cell Pointer after Entering Data	73	(2)
Selecting a Range of Input Cells before Entering Data	75	(2)
Using AutoComplete to Automate Data Entry	77	(2)
Keeping Titles in View by Freezing Panes	79	(2)
Automatically Filling a Range with a Series	81	(4)
Working with Fractions	85	(2)
Proofing Your Data with Audio	87	(2)
Controlling Automatic Hyperlinks	89	(2)
Entering Credit Card Numbers	91	(2)
Using Excel's Built-In Data Entry Form	93	(2)
Customizing and Sharing AutoCorrect Entries	95	(2)
Restricting Cursor Movement to Input Cells	97	(2)
Controlling the Office Clipboard	99	(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