Excel Timesaving Techniques For Dummies

410 Pages·2005·15.3 MB·English

by  Greg Harvey

00a_574272_ffirs.qxd 10/1/04 10:21 PM Page i
E xce l
T i m e s a v i n g
Te c h n i q u e s
™
FOR
DUMmIES
‰
00a_574272_ffirs.qxd 10/1/04 10:21 PM Page ii
00a_574272_ffirs.qxd 10/1/04 10:21 PM Page iii
E xce l
T i m e s a v i n g
Te c h n i q u e s
™
FOR
DUMmIES
‰
by Greg Harvey
00a_574272_ffirs.qxd 10/1/04 10:21 PM Page iv
Excel Timesaving Techniques™For Dummies®
Published by
Wiley Publishing, Inc.
909 Third Avenue
New York, NY 10022
www.wiley.com
Copyright © 2005 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any
means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under
Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the
Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center,
222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for per-
mission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd.,
Indianapolis, IN 46256, [phone], fax [phone], e-mail: [email protected].
Trademarks:Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of
Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Timesaving Techniques and
related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in
the United States and other countries, and may not be used without written permission. All other trademarks
are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor
mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTA-
TIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS
WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FIT-
NESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMO-
TIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY
SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN REN-
DERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS
REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUB-
LISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANI-
ZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF
FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMA-
TION THE ORGANIZATION OF WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READ-
ERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR
DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services or to obtain technical support, please contact our
Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax [phone].
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be
available in electronic books.
Library of Congress Control Number: 2004112340
ISBN: 0-7645-7427-2
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
1V/SQ/RQ/QU/IN
00a_574272_ffirs.qxd 10/1/04 10:21 PM Page v
About the Author
Greg Harveyhas authored tons of computer books, the most recent being Adobe
Acrobat6 PDF For Dummiesand Roxio Easy Media Creator For Dummies.He started
outtraining business users on how to use IBM personal computers and their attendant
computer software in the rough and tumble days of DOS, WordStar, and Lotus 1-2-3 in
the mid-80s of the last century. After working for a number of independent training firms,
he went on to teach semester-long courses in spreadsheet and database management
software at Golden Gate University in San Francisco.
His love of teaching has translated into an equal love of writing. For the last ten years or
more, For Dummiesbooks have been his favorites to write because they enable him to
address his favorite audience, the beginner. They also enable him to use humor (a key
element to success in the training room) and, most delightful of all, to express an opin-
ion or two about the subject matter at hand.
Dedication
To all the people connected with the For Dummies phenomenon with whom I’ve had to the
pleasure to work over the last decade, be they in the editing, management, production,
or marketing end of the business, starting with IDG Books and extending to Hungry Minds
and all the way up to Wiley Publishing: Thanks for the great memories and many good
times!
Author’s Acknowledgments
I am always so grateful to the many people who work so hard to bring my book projects
into being, and this one is no exception. If anything, I am even more thankful for the talents
given the special task-oriented nature of the Timesaving Techniques series.
This time, special thanks are in order to Andy Cummings and Tiffany Franklin for giving me
this opportunity to write and write and write about Excel in this new Timesaving Techniques
format. Next, I want to express great thanks to my project editor, Paul Levesque, for all
his ready help and seasoned answers to my questions on the nature of this series and,
tomy partner in crime, Christopher Aiken (I really appreciate all your encouragement
onthis one). Thanks also go to Kim Darosett for the great copy edit, Doug Sahlin for the
great technical edit, Courtney MacIntyre and Erin Smith for coordinating its production,
and everybody at Wiley Publishing and TECHBOOKS Production Services for proofread-
ing and indexing.
00a_574272_ffirs.qxd 10/1/04 10:21 PM Page vi
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments through our online
registration form located at www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development Production
Project Editor: Paul Levesque Project Coordinators:Courtney MacIntyre, Erin Smith
Acquisitions Editor: Tiffany Franklin Layout and Graphics:LaurenGoddard,
StephanieD.Jumper, MichaelKruzil, JacqueRoth
Senior Copy Editor: Kim Darosett
Proofreaders:Jennifer Connolly, Carl Pierce,
Technical Editor: Doug Sahlin
DwightRamsey, Brian H. Walls
Editorial Manager: Kevin Kirschner
Indexer:Joan Griffitts
Media Development Manager:Laura VanWinkle
Media Development Supervisor:Richard Graves
Editorial Assistant: Amanda Foxworth
Cartoons: Rich Tennant(www.the5thwave.com)
Publishing and Editorial for Technology Dummies
Richard Swadley,Vice President and Executive Group Publisher
Andy Cummings,Vice President and Publisher
Mary Bednarek,Executive Acquisitions Director
Mary C. Corder,Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele,Vice President and Publisher
Joyce Pepple,Acquisitions Director
Composition Services
Gerry Fahey,Vice President of Production Services
Debbie Stailey,Director of Composition Services
00b_574272_ftoc.qxd 10/1/04 10:24 PM Page vii
Contents at a Glance
Introduction 1 Part III: Handy Ways to Format and
Present Worksheet Data 85
Part I: Making Excel Work Your Way 5
Technique17: Instant Range Formatting 87
Technique1: Customizing the Excel Screen
Display 7 Technique18: Style Formatting Magic 92
Technique2: Customizing the Excel Menus Technique19: Controlling When Certain
andToolbars 13 Formats Are Used 97
Technique3: Perfecting Your Spreadsheet Technique20: Customizing Number Formats 102
Workspace 19
Technique21: Dazzling Alignments for Data
Technique4: Saving Your Worksheets So Entries 111
YouCanFind Them 22
Technique22: Charting Data in a Snap 117
Technique5: Tailoring Excel’s Error
Technique23: Chart Customization Tricks 123
CheckingtoYour Needs 27
Technique6: Utilizing Excel’s Editing Settings 31 Part IV: Worksheet Formula
Timesavers 129
Technique7: Streamlining Excel Program
Startup 36 Technique24: Efficient Formula Copying 131
Technique8: Saving Time with Excel Add-ins 40 Technique25: Speeding Up Table Creation
withArray Formulas 137
Part II: Quick Worksheet Creation Tricks 45
Technique26: Using Range Names 141
Technique9: Navigating the Worksheet
Technique27: Smarter Formula Construction 148
in a Snap 47
Technique28: Trapping Those Terrible
Technique10: Making the Most Efficient
Errors 154
CellSelections 53
Technique29: Eliminating Errors with Error
Technique11: Speeding Through Long Data
Tracing 158
Entries with AutoCorrect 58
Technique30: Creating Efficient Date and
Technique12: Data Entry Tricks 62
TimeFormulas 164
Technique13: Speeding Up Data Entry
withAutoFill 67 Part V: Worksheet Editing Timesavers 169
Technique14: Ensuring Accurate Data Technique31: Quickly Finding the Workbook
Entrieswith Data Validation 72 YouWant to Edit 171
Technique15: All Aboard the Numerical Technique32: Controlling the Worksheet
EntryExpress! 78 WindowDisplay 177
Technique16: Verifying Entries with Text Technique33: Managing Worksheet Windows 182
toSpeech 82
Technique34: Quick and Easy Insertion and
Deletion 187
00b_574272_ftoc.qxd 10/1/04 10:24 PM Page viii
Technique35: Outline and Subtotal Magic 191 Part VIII: Internet-Related Timesavers 301
Technique36: Consolidating Data from Technique50: Saving Worksheets
DifferentWorksheets 195 as Web Pages 303
Technique37: Editing with Search & Replace Technique51: Importing Web Data
andSpell Check 199 into the Worksheet 310
Technique52: Using Hyperlinks to
Part VI: Tips for Printing, Sharing,
Make Jumps in Workbooks 317
andReviewing Workbooks 205
Part IX: The Scary (Or Fun) Stuff 323
Technique38: Spreadsheet Security 207
Technique53: Instant Lists in Excel 2003 325
Technique39: Printing Tricks for
Flawless Reports 219 Technique54: Sharing Excel Workbooks
and Lists with a SharePoint Web Site 330
Technique40: Sharing Data with Other
OfficePrograms 230 Technique55: Entering Data and
IssuingCommands by Voice 337
Technique41: Sharing Workbooks
on a Network 238 Technique56: Sprucing Up Your
Spreadsheetswith Graphics 342
Technique42: Sending Workbooks Out
for Review 247 Technique57: Doing Automated
Table Lookups 349
Part VII: Streamlining Data Listing
Technique58: Using Text Formulas
and Data Analysis 253
for Fun and Profit 355
Technique43: Adding and Editing Data Lists
Technique59: Creating Queries to Import
withthe Data Form 255
Datafrom an External Database 358
Technique44: Sorting Worksheet Data 261
Technique60: Automating Repetitive Tasks
Technique45: Quick and Easy Basic Data withMacros 365
ListFiltering 267
Technique61: Creating Custom Functions
Technique46: More Data List Filtering to Use in Your Worksheets 374
plusStatistical Analysis 272
Index 379
Technique47: Doing What-if Analysis
inaSnapwith Data Tables 280
Technique48: Easy What-if Analysis through
Scenarios and Goal Seeking 284
Technique49: Summarizing Data with
Pivot Tables and Pivot Charts 291
00b_574272_ftoc.qxd 10/1/04 10:24 PM Page ix
Table of Contents A Toolbar and Menu of Your Own 16
Adding Macros and Links to Toolbars and Menus 17
Technique3: Perfecting Your Spreadsheet
Introduction 1
Workspace 19
Saving Time with This Book 1
Saving Your Workspace 19
Foolish Assumptions 2
Opening a Workspace Whenever Excel Launches 20
What’s In This Book 2
Part I: Making Excel Work Your Way 2 Technique4: Saving Your Worksheets So
Part II: Quick Worksheet Creation Tricks 2 YouCan Find Them 22
Part III: Handy Ways to Format and Present
Modifying the Default File Location 23
Worksheet Data 2
Saving New Files with Summary Information 24
Part IV: Worksheet Formula Timesavers 3
Part V: Worksheet Editing Timesavers 3 Changing the AutoRecover Settings 25
Part VI: Tips for Printing, Sharing, and Technique5: Tailoring Excel’s Error
ReviewingWorkbooks 3
Checking to Your Needs 27
Part VII: Streamlining Data Listing and
DataAnalysis 3 Modifying the Error Checking Settings 28
Part VIII: Internet-Related Timesavers 3 Suppressing All Error Indicators 29
Part IX: The Scary (Or Fun) Stuff 3 Hiding Error Values On-Screen and in Print 29
Icons Used in This Book 4
Technique6: Utilizing Excel’s Editing
Where to Go from Here 4
Settings 31
Part I: Making Excel Work Your Way 5 Putting the Cell Pointer in the Right Direction 32
Completely Turned Off to AutoComplete 32
Technique1: Customizing the
Doing the Drag-and-Drop Thing 33
ExcelScreenDisplay 7
Please make room for me 33
Standard Display Settings 8
When drag-and-drop flops 33
Switching to Full Screen 8 Doing Direct Cell Editing 34
Customizing the Worksheet Display 9
Technique7: Streamlining Excel Program
Setting a new standard column width 9
Startup 36
Setting a new standard row height 10
Modifying the number of sheets in a workbook 10 Excel Desktop Shortcut 36
Customizing the worksheet gridlines 11 Adding Excel to the Quick Launch Toolbar 37
Saving Custom Display Settings 11 Pinning Excel to the Start Menu 38
Creating a template 11 Launching Excel on Windows Startup 39
Using a template 12
Technique8: Saving Time with
Technique2: Customizing the Excel Menus
Excel Add-ins 40
andToolbars 13
Installing Add-ins 40
Showing the Toolbars and Menus
Using the Built-in Add-ins 41
in All Their Glory 13
Getting Online Add-ins 42
Toolbars and Menus Made to Order 14