add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
A
adding
Without using formulas, 129
Advanced Filter
Copying, 273
Show all, 272
Using, 271
Amortization, 388
Array Formula
Complex calculations, 98
Key combination, 98
Rounding, 53
Transposing, 99
AutoComplete, 20
AutoCorrect, 22
AutoFilter, 264
Custom criteria, 266
Data fields, 267
Saving by using custom views, 265
Summing filtered data, 270
Wildcards, 267
AutoSum, 125
Additional functions, 128
Controlling by selecting cells, 127
Extending, 128
Horizontal vs. Vertical, 126
C
Calculations
Without using formulas, 130
Camera tool, 152, 199
Cell Function, 149
Cellpointer
Direction after enter, 20
Charts
Adding linked picture, 198
ComboBox, 204
Creating with F11, 193
Creating with toolbar, 194
Default type, 195
Nudge location, 198
Numeric format, 204
Option buttons, 206
Pie of pie, 208
Preventing from printing, 196
Printing, 196
Rows vs. columns, 197
Selecting items, 197
Sizing to window, 195
Updating automatically, 201
Updating by dragging and dropping new data, 200
Circular Reference, 107
ComboBox, 372
Comments
Adding to cell, 155
Adding to formula, 155
Changing the author, 155
Copying, 157
Default format, 156
Deleting, 157
Displaying picture, 159
Displaying single, 157
Printing, 158
Viewing, 157
Consolidation
Across worksheets, 296
Using data consolidate, 299
Using PivotTable, 310
Count, 134
CountIf
Unique Records, 277
Custom Lists, 216
Custom Views
For saving AutoFilter, 265
D
Data
List structure, 239
Data Table
With two variables, 391, 393
Database Functions, 274
Dates
Calculating difference, 62
Enter series automatically, 58
Formulas, 57, 62
How Excel calculates, 55
Quarter number, 63
Shortcut for entering, 57
Week number, 63
Digital Signature, 140
Dividing
Without using formulas, 129
Duplicate Data
Preventing duplicates, 26
E
Errors
Handling, 104
Marking cells, 105
Tracing, 106
Excel 2002
New toolbars, 20
F
Format Painter, 70
Formatting
Adding lines between groups, 261
Conditional formatting, 51, 243
Criteria, 50
Dates, 60
Dividing title in cell, 33
Numbers, 44
Special symbols, 49
Time, 65
Forms
Data entry, 242
Formulas
Auditing, 101
Deleting, 113
Displaying syntax, 119
Formatting with Get.Cell, 115
Nesting, 94
Printing syntax, 119
Protecting, 117
Relative vs. absolute, 92
Selecting cells with, 114
Statistical, 97
Stepping into, 103
Toggle formulas and values, 57
Freeze Panes, 242
Function Wizard
Add functions using VBA, 120
Functions
Custom, 120
G
Get.Cell, 115
GetPivotData, 351
Goal Seek, 396
Gridlines
Displaying, 215
Group and Outline
Automatic, 289
Combining, 292
Manual, 291
H
Hyperlinks, 238
I
Indentation, 32
Index, 367
With match, 350, 371
Internet
Web queries, 160
Iteration, 108
L
Links
Between workbooks, 109
Breaking, 111
Changing source, 111
Lists
Comparing, 136, 308
Merging, 137
Loans, 385
Lookup, 358
M
Macros
Absolute and relative reference, 449
Adding to a module, 411
Break point, 413
Copying data, 424
Defining range name, 434
Efficient code, 428
Entering text in cell, 421
Events, 440
Finding last row, 430
Formatting cells, 434
Get sheet name, 422
Getting help, 420
If statements, 441
Loops, 427, 443
Module name, 420
Moving cellpointer, 422
Moving to end of range, 422
MsgBox, 438
Opening and closing workbooks,
431
Personal workbook, 417
Recording, 416
Running from an icon, 414
Running from VBE, 412
Running in step mode, 412
Running on workbook open, 440
Scheduling, 451
Screen updating, 450
Selecting cells, 409
Selecting current region, 424
Selecting entire column, 425
Selecting sheets, 421
Shortcut key, 417
Stopping a runaway, 414
Storing as add-ins, 445
To consolidate data tables, 421
Variables, 437
Match, 366
With index, 350
MsgBox, 153
Multiplying
Without using formulas, 129
N
Names
Advantages, 77
Automatically defining, 80
Changing reference, 79
Named formulas, 84
Pasting in formula, 80
Storing value as name, 85
Updating reference automatically, 86
Navigation
First Cell in Worksheet, 7
Jumping quickly to a cell, 12
Moving around a range, 6
Moving between sheets, 12
Moving between workbooks, 16
Moving to the last cell in a range, 2
Selecting the last cell in the worksheet , 7
Worksheet shortcut menu, 12
Numbers as Text, 34
O
Offset
With SumIf, 350
Option buttons, 206
Options, 214
P
Page Breaks, 179
Paste Special, 129
Path
Adding to title bar, 150
Displaying in cell, 150
Picture
Adding to comment, 159
Camera tool, 152, 199
PivotTable
Calculating percent, 340
Charts, 352
Drill down report, 329
Formatting data fields, 320
Grouping by date, 332
Grouping by week, 335
Grouping items, 330
Hiding items, 324
Inserting calculated field, 336
Multiple data fields, 343
Printing, 345
Refreshing data, 328
Retrieving data from, 348
Running balance, 342
Sorting items, 325
Subtotals, 322
Terminology, 315
Toolbar, 320
Top 10 AutoShow, 325
Total rows, 346
Printing
Headers and footers, 167
Increasing speed, 182
Non-contiguous print area, 171
Page numbers, 170, 176
Repeating headings on each page, 172
Set print area, 165
Protecting
Cells, 142
Formulas, 117
Lost password, 147
Workbooks, 141
R
Recently Used File List, 232
Replace
Search all sheets, 36
Wildcards, 35
Report Manager, 186
Rounding, 51
S
Scenarios, 401
Scroll Area, 145
Scrollbars
Locking the scrollbars, 2
Security, 139
Macro security, 141
Selecting
Adjacent Cells, 3
Columns, 9
Current Region, 4
Non-Adjacent Cells, 4
Rows, 9
Using F8 to Extend Region, 4
Selecting
Only text cells, 34
Sensitivity Analysis, 393
Shortcuts, 13
Solver, 396
Sorting
Adding lines between groups, 261
By column, 258
Custom lists, 259
More than three fields, 257
Sequence column to undo, 255
Sort order, 256
With one click, 256
Styles, 31
Applying, 74
Custom styles, 72
Default style, 71
Merging, 73
Shortcut for creating, 75
Subtotals
Applying color, 285
Copying, 284
Date fields, 283
Deleting the word total, 286
Multiple fields, 282
Removing, 282
Removing group buttons, 281
Using, 279
Subtracting
Without using formulas, 129
SumIf
Arguments, 131
Comparison operators, 132
Multiple criteria, 136
Text as criteria, 133
Two ranges, 132
With offset, 350
Summing
According to criteria, 131
Across sheets, 130
Conditionally, 131
Intersection of two ranges, 130
T
Templates, 225
Text
Concatenation, 37
Extracting characters, 40
Formulas, 37
Parsing, 41
Reversing text, 252
Wrapping, 28
Wrapping manually, 29
Text Files
Importing, 247
Textbox
Linking cells, 153
Thousands, 39, 76
Time
Calculating difference, 65
Enter series automatically, 59
Formulas, 65
Values exceeding 24 hours, 64
Toolbars
Customizing, 218
Removing customizing, 222
Saving, 221
Trailing Minus, 250
Transposing, 33
U
Unique Records
Advanced Filter, 275
Coloring, 278
CountIf, 277
Used Area
Reducing, 8
Username, 216
V
Validation, 23
Copying, 27
Deleting, 28
VBE, 410
Views
Custom views, 183
Visual Basic Editor, 410
Vlookup, 363
W
Watermark, 179
Wheel Mouse, 11
Worksheet
Copying, 16
Workspace, 235
Z
Zero
Displaying, 215
Preventing display of, 72
Zoom, 10