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

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