Excel Timesaving Techniques For Dummies (For Dummies (Computer Tech))

Covering formulas, charts, data lists, macros, and versions 2000, 2002, and 2003, this book offers more than seventy easy-to-follow techniques that show both new and experienced Excel users how to save time and avoid tedious or redundant tasks. Bestselling author Greg Harvey includes information on customizing Excel's menus and toolbars; modifying Excel's editing settings; using add-ins, AutoCorrect, AutoFill, AutoFormat, and data validation; copying and moving data between worksheets; encrypting workbook files; creating external database queries; converting worksheet data and charts into Web pages; and much more. * Written by Greg Harvey, one of the bestselling technology authors of all time, whose books have combined sales of more than four million copies * A unique, two-column format makes it easy for readers to grasp and apply each technique-and begin working more efficiently Contents at a Glance......Page 8 Table of Contents......Page 10 Saving Time with This Book......Page 16 What’s In This Book......Page 17 Part VI: Tips for Printing, Sharing, and Reviewing Workbooks......Page 18 Where to Go from Here......Page 19 Part I Making Excel Work Your Way......Page 20 Customizing the Excel Screen Display......Page 22 Switching to Full Screen......Page 23 Setting a new standard column width......Page 24 Setting a new standard row height......Page 25 Creating a template......Page 26 Using a template......Page 27 Showing the Toolbars and Menus in All Their Glory......Page 28 A Toolbar and Menu of Your Own......Page 31 Adding Macros and Links to Toolbars and Menus......Page 32 Saving Your Workspace......Page 34 Saving Your Worksheets So You Can Find Them......Page 37 Modifying the Default File Location......Page 38 Saving New Files with Summary Information......Page 39 Changing the AutoRecover Settings......Page 40 Tailoring Excel’s Error Checking to Your Needs......Page 42 Modifying the Error Checking Settings......Page 43 Suppressing All Error Indicators......Page 44 Utilizing Excel’s Editing Settings......Page 46 Completely Turned Off to AutoComplete......Page 47 Please make room for me......Page 48 Doing Direct Cell Editing......Page 49 Excel Desktop Shortcut......Page 51 Pinning Excel to the Start Menu......Page 53 Launching Excel on Windows Startup......Page 54 Installing Add-ins......Page 55 Using the Built-in Add-ins......Page 56 Getting Online Add-ins......Page 57 Part II Quick Worksheet Creation Tricks......Page 60 Saving the Cell Pointer’s Position......Page 62 Leaping through data ranges and hopping over blanks......Page 63 Going right to the last cell in a sheet......Page 64 Zipping through the sheets......Page 65 A Little Go To Magic......Page 66 Zooming Out to Get the Big Picture......Page 67 Making the Most Efficient Cell Selections......Page 68 AutoSelect at Your Service......Page 69 Go To It and Select It......Page 70 Going for the Big Selections......Page 71 Setting the Correct AutoCorrect Settings......Page 73 Losing the Links......Page 74 Taming the Smart Tags......Page 75 Making the Same Entry in Many Places......Page 77 Putting the Wraps on the Data Entry......Page 78 Let’s Do It as a Group!......Page 80 Getting Your Fill of AutoFill......Page 82 Copying an entry instead of filling in a series......Page 84 Incrementally Speaking......Page 85 Only the Valid Need Apply......Page 87 Data entries from a list......Page 88 Copying data validation settings......Page 89 Information Please!......Page 90 Warnings to Make Them Wary......Page 91 Taking Advantage of the Numeric Keypad......Page 93 Putting the Decimal Places at Your Service......Page 94 Number Please!......Page 95 Verifying Entries with Text to Speech......Page 97 Unknown......Page 0 Reading by columns and rows......Page 98 Modifying the Text to Speech Settings......Page 99 Part III Handy Ways to Format and Present Worksheet Data......Page 100 Head-to-Toe Table Formatting with AutoFormat......Page 102 Getting Artistic with the Format Painter......Page 104 Cutting and Pasting Formats Only......Page 105 Style Formatting Magic......Page 107 Styling......Page 108 Customizing predefined styles......Page 109 Merging Styles from One Book into Another......Page 110 Formats to Suit Every Condition......Page 112 When two conditions are better than one......Page 114 Finding cells with conditional formatting......Page 115 Creating Custom Number Formats......Page 117 Custom formats that conditionally format entries......Page 120 Custom formats that hide certain entries......Page 121 Applying Euro Currency Formats......Page 124 Line Me Up......Page 126 Indenting data entries......Page 127 Rotating text entries......Page 128 Making text wrap within a cell......Page 129 Shrinking the text to fit within a cell......Page 130 Centering a heading across columns......Page 131 Instant Charts......Page 132 Chart Wizard Magic......Page 134 Getting the Chart Titles and Headings in Balance......Page 138 Scaling and Formatting the Chart Axes......Page 140 Tricks for Making the Plotted Data Easier to Decipher......Page 142 Part IV Worksheet Formula Timesavers......Page 144 Going from Relative to Absolute......Page 146 When It’s Copy Time......Page 148 Making one-dimensional copies down or across......Page 149 Making two-dimensional copies both down and across......Page 150 A Quick Look at Array Ranges......Page 152 Editing Array Formulas......Page 155 Name That Range!......Page 156 Creating names from row and column headings......Page 157 Assigning range names that span different sheets......Page 158 Assigning Range Names to Constants......Page 159 Assigning range names to existing formulas......Page 160 Pointing Out Cell References in Formulas......Page 163 Putting the Insert Function Feature at Your Service......Page 165 Using Labels Instead of Cell References in Formulas......Page 167 If I Were a Logical Function......Page 169 Trapping Division by Zero Errors......Page 170 Trapping All Types of Error Values......Page 171 Formula Auditing 101......Page 173 Tracing Formula Precedents......Page 175 Tracing Formula Dependents......Page 176 Finding the Original Error and Fixing Its Formula......Page 177 The Deal with Dates and Times......Page 179 You Do the Date Math!......Page 180 When Your Time Is Up......Page 181 Part V Worksheet Editing Timesavers......Page 184 Opening Recently Used Files......Page 186 Doing a basic file search......Page 187 Conducting the file search and using the results......Page 188 Doing an advanced file search......Page 189 Searching for Files in the Open Dialog Box......Page 190 Zooming In for the Edits......Page 192 Frozen Panes in My Windows......Page 194 A Worksheet with a Custom View......Page 195 Opening Panes in the Worksheet Window......Page 197 Comparing Sheets in the Same Workbook......Page 199 Comparing Sheets in Separate Workbooks......Page 200 Inserting and Deleting Cells in an Existing Range......Page 202 Inserting and Deleting Rows and Columns......Page 203 Adding Outline Levels to a Table or List......Page 206 Subtotaling a Table or List......Page 208 Consolidating Data by Position......Page 210 Linking Consolidated Data......Page 212 Using Find and Replace......Page 214 Eliminating Typing Errors with Spell Check......Page 216 Customizing the Spell Check settings......Page 217 Adding words to a custom dictionary......Page 218 Part VI Tips for Printing, Sharing, and Reviewing Workbooks......Page 220 Assigning a Password for Opening a Workbook......Page 222 Opening a password-protected workbook......Page 224 Changing or deleting passwords......Page 225 Unlocking cells for data entry......Page 226 Turning on worksheet protection......Page 227 Removing protection from a worksheet......Page 228 Enabling cell range editing by certain users......Page 229 Protecting the structure of the workbook file......Page 231 Hiding Sensitive Worksheet Data......Page 232 Making Last-Minute Adjustments with Print Preview......Page 234 Manipulating the page settings......Page 236 Controlling Bad Page Breaks......Page 237 Using the Page Break Preview feature......Page 238 Changing the scale of the printing......Page 239 Headers and footers made to order......Page 240 Print titles on every page......Page 241 Printing the Formulas in the Report......Page 242 Printing the Charts in the Report......Page 243 Swapping Data via the Clipboard......Page 245 Importing Text Files into Excel......Page 247 Embedding Excel Data in Other Office Documents......Page 250 Linking worksheet data......Page 252 Let’s All Learn to Share......Page 253 Sharing a workbook......Page 254 Setting your sharing options......Page 255 Turning on change tracking......Page 256 Conflict resolution worksheet style......Page 258 Accepting or rejecting highlighted changes......Page 259 Distributing the copies......Page 260 Merging the changes......Page 261 Getting a Workbook Ready for Review......Page 262 Getting your two cents in......Page 263 Deleting comments in a worksheet......Page 264 Replying with changes......Page 265 Part VII Streamlining Data Listing and Data Analysis......Page 268 Creating a New Data List and Data Form......Page 270 Adding new records with the data form......Page 272 Finding records with the data form......Page 273 Editing records in the data form......Page 274 Removing records from the data list with the data form......Page 275 Don’t Be Out of Sorts......Page 276 Sorting records in a data list......Page 277 Sorting on a record number field to restore a list to its original order......Page 279 Sorting the Field Names in a Data List......Page 280 AutoFilter Basics......Page 282 Making it into the top-ten list......Page 283 Saving subsets of a data list as custom views......Page 284 Customizing the AutoFilter Settings......Page 285 Putting the Advanced Filter in Service......Page 287 Setting up AND and OR filtering criteria......Page 289 Setting up calculated filtering criteria......Page 291 Getting Data List Statistics......Page 292 Creating a One-Variable Data Table......Page 295 Exploring Different Scenarios......Page 299 Performing goal seeking......Page 303 Goal seeking graph style......Page 304 Creating Pivot Tables......Page 306 Pivoting the fields in the table......Page 309 Formatting the values in the pivot table......Page 310 Selecting new summary functions......Page 311 Creating a calculated field for the pivot table......Page 312 Creating a Pivot Chart......Page 313 Part VIII Internet-Related Timesavers......Page 316 Saving Worksheets as Web Pages......Page 318 Saving all the worksheets in a workbook......Page 320 Adding data to an existing Web page......Page 321 Creating Interactive Web Pages......Page 322 Capturing Information for the Spreadsheet with Web Queries......Page 325 Importing XML Data into a Worksheet......Page 328 Adding Hyperlinks to a Worksheet......Page 332 Following Links in a Worksheet......Page 335 Creating Hyperlinks for Custom Menus and Toolbars......Page 336 Part IX The Scary (Or Fun) Stuff......Page 338 Creating an Excel 2003 List......Page 340 Converting an existing list into an Excel list......Page 341 Sorting and Filtering the List......Page 342 Toggling the List’s Total Row On and Off......Page 343 Deleting a list......Page 344 Adding Excel Spreadsheets to the SharePoint Site......Page 345 Opening the spreadsheets on the SharePoint site......Page 347 Adding a list of your spreadsheets to the SharePoint site home page......Page 348 Publishing an Excel list on a SharePoint site......Page 349 Synchronizing list data......Page 351 Hands-Free Data Entry......Page 352 Just Tell Me What to Do......Page 354 Choosing menu items, dialog box options, and toolbar buttons......Page 355 Jazz It Up with Clip Art......Page 357 Adding Images from Graphics Files......Page 358 Drawing Objects for the Spreadsheet......Page 359 Drawing various shapes......Page 360 Using text boxes as callouts......Page 361 Drawing diagrams and organization charts......Page 362 Looking Up a Single Table Value......Page 364 Performing a vertical table lookup......Page 365 Performing a horizontal lookup......Page 366 Doing a Two-Way Lookup in a Data Table......Page 367 Getting Right on the Case......Page 370 Joining Separate Text Entries Together......Page 371 Replacing Text Formulas with Their Results......Page 372 Setting Up the Data Source Definition......Page 373 Creating the Database Query......Page 375 Recording and Playing Back Macros......Page 380 Recording the macro......Page 381 Playing back the macro......Page 383 Editing Macros in the Visual Basic Editor......Page 384 Modifying the settings for VBA properties......Page 385 Getting user input by adding a custom dialog box......Page 386 Creating Custom Functions......Page 389 Saving Custom Functions in an Excel Add-in......Page 391 A......Page 394 C......Page 395 D......Page 397 E......Page 399 F......Page 400 H......Page 401 L......Page 402 N......Page 403 P......Page 404 R......Page 405 S......Page 406 T......Page 408 W......Page 409 Z......Page 410 Covering formulas, charts, data lists, macros, and versions 2000, 2002, and 2003, this book offers more than seventy easy-to-follow techniques that show both new and experienced Excel users how to save time and avoid tedious or redundant tasks. Bestselling author Greg Harvey includes information on customizing Excel#x92; s menus and toolbars; modifying Excel#x92; s editing settings; using add-ins, AutoCorrect, AutoFill, AutoFormat, and data validation; copying and moving data between worksheets; encryptin workbook files; creating external database queries; converting worksheet data and charts into Web pages; and much more. Written by Greg Harvey, one of the bestselling technology authors of all time, whose books have combined sales of more than four million copies A unique, two-column format makes it easy for readers to grasp and apply each technique#x96; and begin working more efficiently