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, (978) 750-8400, fax (978) 750-4744. Requests to the Publisher for per- mission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, 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 317-572-4002. 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