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
Mr Excel
ON EXCEL
Copyright© 2003 by Bill Jelen and Joseph Rubin
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system without written permission from the publisher.
Written by:
Bill Jelen and Joseph Rubin
Edited by:
Marie Erb
Cover Design:
Design by Irubin Consulting (http://www.irubin.com)
Published by:
Holy Macro! Books
13386 Judy Avenue Northwest
Uniontown, Ohio, USA 44685
Distributed by:
Holy Macro! Books
First printing:
April 2003
Printed in Hong Kong
Library of Congress Data
Jelen, Bill and Joseph Rubin
Mr Excel ON EXCEL / Bill Jelen and Joseph Rubin
Library of Congress Control Number: 2002117108
ISBN: 0-9724258-3-7
Trademarks:
All brand names and product names used in this book are trade names, service marks, trademarks, or registered trade marks of their respective owners. Holy Macro! Books is not associated with any product or vendor mentioned in this book.
ABOUT THE AUTHORS
Bill Jelen is the principal behind the popular MrExcel.com website. With over 10 million annual pageviews, MrExcel.com is a leading Excel website. Along with a dedicated team of Excel gurus, Bill Jelen provides custom Excel and Access applications for hundreds of clients around the world. He is the author of “Guerilla Data Analysis Using Microsoft Excel” and offers an entertaining seminar on the power tips and tricks for Microsoft Excel.
Contact the Author - consult@MrExcel.com, http://www.MrExcel.com
Joseph Rubin, CPA has over 25 years of financial experience in the accounting industry. He has served as CFO, Controller and run his CPA practice for years. Joseph Rubin, CPA is an independent consultant specializing in the development of applications using Microsoft Excel for the financial industry and has instructed thousands of professionals on Microsoft Excel.
Contact the Author - jrubin@exceltip.com, http://www.exceltip.com
ACKNOWLEDGEMENTS
The authors would like to thank Marie Erb for excellent editing of the book. Jacquie Skrzypiec and Anne Troy for technical expertise with Microsoft Word. Sara Amihud for final layout. Roy Rubin of Irubin Consulting for cover design, Chank Diesel for font design, Cecilia Sveda for logo design.
The original galley proofs of this book were reviewed by several of the MVP’s from the MrExcel.com message board. Thanks to Yogi Anand, Chris Davison, Paddy Davies, Juan Pablo Gonzalez, G. Russell Hauf, Ian MacConnell, Ivan Moala, Mark O’Brien, Nate Oliver, William Roe, Richie Sills, Tom Urtis and Joe Was.
Thanks to our families for patience and understanding.
Thanks to the Bill Jelen family, Mary Ellen Jelen, Josh & Zeke Jelen.
Thanks to Joseph Rubin family, Mati Rubin, Roy, Liron & Gil Rubin.
Table of Contents at a Glance
Chapter 1: Time is Money, Give Your Mouse a Break. 1
Chapter 2: Text19
Chapter 3: Formatting Numbers. 43
Chapter 4: Date and Time. 55
Chapter 5: Styles. 69
Chapter 6: Name. 77
Chapter 7: Formulas. 91
Chapter 8: Summing and Counting. 125
Chapter 9: Security and Protection. 139
Chapter 10: Information. 149
Chapter 11: Printing. 165
Chapter 12: Charts. 193
Chapter 13: Customizing Excel213
Chapter 14: Opening, Closing and Saving Workbooks. 231
Chapter 15: Data. 239
Chapter 16: Importing Text Files. 247
Chapter 17: Sorting. 255
Chapter 18: Filtering. 263
Chapter 19: Subtotals. 279
Chapter 20: Grouping and Outlining. 289
Chapter 21: Consolidating Data. 295
Chapter 22: PivotTable. 315
Chapter 23: Using Functions and Objects to Extract Data. 355
Chapter 24: Loans. 385
Chapter 25: What-if393
Chapter 26: Running a Macro. 409
Chapter 27: Write Your First Program.. 421
Chapter 28: Other VBA Techniques. 437
Index. 453
Function Reference. 459
Table of Contents
Chapter 1:
Time is Money, Give Your Mouse a Break. 1
Cell, Cells, Move and Select2
Moving to the last cell in a range. 2
Selecting a horizontal or vertical range of adjacent cells. 3
Selecting a range of non-adjacent cells. 4
Selecting the current region. 5
Moving the Cellpointer around a selected range. 6
Selecting the first cell in a worksheet7
Selecting the last cell in the used range. 7
Reducing the used area in a worksheet8
Rows and Columns. 9
Selecting a column or columns. 9
Selecting a row or rows. 9
Adding a cell, a row, a column. 9
Deleting a cell, a row, a column. 9
Hiding or unhiding a column or columns. 9
Hiding or unhiding a row or rows. 9
View All Data in Worksheet10
Moving between Sheets in a Workbook. 12
Using keyboard shortcuts to move between sheets. 12
Selecting a sheet from the shortcut menu. 12
Jumping Quickly between Cells in a Workbook. 12
Using the Name box. 13
Copying, Cutting and Pasting. 13
Keyboard shortcuts. 14
Copying and pasting, using the mouse and keyboard. 14
Cutting and pasting, using the mouse and keyboard. 14
Copying a cell with text or a formula to thousands of cells easily. 15
Copying a cell by double-clicking. 15
Moving between Open Workbooks. 16
Copying or Moving a Sheet16
Chapter 2: Text19
Text and Toolbars. 19
Entering Text in Cells. 20
Using AutoComplete. 20
Selecting from list of Previous Entries. 21
Validating data. 21
Moving selection after Enter21
Selecting a range. 22
Using AutoCorrect22
Validating Data. 23
List25
Preventing duplicate data entry. 26
Validating text entries. 27
Copying validation. 27
Deleting validation criteria. 27
Wrapping Text28
Text wrapping techniques. 28
Wrapping text automatically. 28
Canceling automatic text wrap. 29
Wrapping text manually. 29
Canceling manual text wrap. 29
Wrapping text beyond the data range. 30
Adding a wrap text shortcut31
Changing the Indentation in a Cell32
Dividing a Title in a Cell33
Transposing Data. 33
Changing a Numeric Heading to Text34
Selecting Cells That Only Contain Text34
Searching and Replacing. 35
Searching All the Sheets in the Workbook. 36
Using Text Formulas. 37
Joining Text37
Joining text using a keyboard shortcut37
Joining text using a manual formula. 38
Joining text using the concatenate function. 38
Joining text with a linked number39
Extracting Characters from Text40
Using formulas to extract characters from text40
Parsing characters from text without formulas. 41
Chapter 3: Formatting Numbers. 43
Where Custom Formats Are Saved. 44
Symbols Used in Excel to Format Numbers. 44
Special formats, rounding a number to the thousands,
displaying in the thousands, displaying text and a number46
The Four Sections of the Format46
Formatting a negative number with parentheses,
replacing 0 with a dash. 47
Special Formats – Examples. 48
Adding special symbols to the number format49
Formatting Numbers According to Criteria. 50
Using custom formats for numbers with criteria. 50
Conditional Formatting. 51
Totaling Rounded Numbers. 51
How Excel performs calculations. 51
Chapter 4: Date and Time. 55
How Excel Calculates Date and Time. 55
Entering the Date in a Cell55
Easily entering the date in a cell56
Shortcuts for entering current date and time. 57
Typing dates in cells quickly. 57
Date and time – displaying the number behind the format57
Entering dates Automatically............................................................... 58
Using the series dialog box to enter a series of dates. 58
Using the shortcut menu to enter a series of dates. 59
Automatically Entering Time Data. 59
Entering a time series in intervals of one minute. 59
Entering a time series in intervals of one hour60
Custom Dates. 60
Customizing the date. 60
Date format table. 60
Date – Formulas and Calculations. 61
Calculating the difference between dates. 62
Calculating the number of the week. 63
Calculating a quarter number63
Inserting a function for calculating the number of a
quarter in VBA. 64
Custom Formatting for Time. 64
Time values exceeding 24 hours. 64
Time format table. 65
Time – Formulas and Calculations. 65
Converting hours to decimals. 66
Calculating the difference between hours. 66
Rounding hours up. 67
Converting a number to a time value. 67
Calculating time differences between regions of the world. 68
Chapter 5: Styles. 69
Copying Formatting. 70
Copying formatting with the Format Painter70
Painting a format repeatedly. 70
Using F4 to copy formatting to adjacent and non-adjacent ranges. 70
Using Paste Special to copy formatting. 70
Changing the Default Style in a Workbook. 71
Preventing the display of 0. 72
Creating and Saving Custom Styles. 72
Copying (merging) styles from one workbook to another73
Applying a style to a cell or cells in a worksheet74
Displaying statements rounded to thousands. 76
Chapter 6: Name. 77
What is a Name?. 77
Why Define Names?. 77
Name syntax. 78
Defining a Name. 78
Saving names. 79
Deleting names. 79
Changing a reference. 79
Reviewing names. 79
Automatically defining Names according to the text in
the top row and the left column. 80
Entering and Pasting a Name in a Formula. 80
Automatically entering a name in a formula. 81
Pasting a name in a formula. 81
Pasting Names in a formula’s argument boxes. 82
Replacing a reference in a formula with a newly
defined name, after entering the formula into the cell84
Saving a Formula or Numeric Value for Reuse in the Name Box. 84
Saving values in the Define Name dialog box. 85
Automatically Updating a Name’s Reference. 86
Formula for updating an automatic reference,
with a vertical range of cells. 87
Formula for updating an automatic reference,
for the name of the current region (data table)88
Defining a Name and Updating a Range Reference with a Macro. 89
Chapter 7: Formulas. 91
Entering a Formula into a Cell91
Copying a Formula, Relative and Absolute Reference. 92
Relative reference. 92
Absolute reference. 92
The F4 key. 93
Maintaining a relative reference when pasting a formula. 93
Nesting Formulas. 94
Copying and pasting a formula within a formula. 96
Adding Statistical Formulas. 97
Analysis ToolPak, Analysis ToolPak-VBA. 97
Array Formula. 98
The technical side of array formulas. 99
Using array formulas to create links with a change of direction. 99
Creating a link between cells in a workbook. 100
Auditing. 101
Auditing formulas toolbar101
Moving to a precedent cell / moving to a dependent cell101
Moving to a precedent cell101
Moving to a dependent cell102
Moving between linked cells in a sheet102
Keyboard shortcuts to precedent and dependent cells. 102
Stepping into the formula....................................................................... 103
Handling Errors. 104
Marking Cells Containing Errors. 105
Tracing errors. 106
Smart tag. 106
Circular References. 107
Iteration. 108
Links Between Workbooks. 109
Creating links between workbook sheets. 109
Canceling the message about creating links
when opening a workbook. 110
The Edit Links Dialog box. 110
Startup prompt111
Break Links. 111
Changing a source. 111
Changing links and deleting unwanted links. 112
Deleting Formulas, Saving Calculation Results. 113
Deleting formulas, maintaining calculated values. 113
Deleting formulas and pasting values using a single
line of code and a keyboard shortcut114
Selecting Cells with Formulas. 114
Identifying and Formatting Cells with Formulas. 114
Combining the Get.Cell formula and conditional
formatting to format a cell with a formula. 115
Protecting Formulas in Cells. 117
Locking, the first condition. 117
Protecting a sheet, the second condition. 117
Protecting formulas. 117
Displaying a Formula in a Cell and Printing Formulas. 119
Displaying formula syntax. 119
Printing the formula syntax. 119
Using VBA to Create and Add Functions to the Function Wizard. 120
Custom functions, example and explanation. 120
Using a function to calculate accrued income tax
from gross salary. 122
Chapter 8: Summing and Counting. 125
Summing Data Easily. 125
Summing values in a vertical range. 126
Summing values in vertical and horizontal ranges. 126
Summing data by selecting cells. 127
Sum subtotals. 127
Extending the range of sums for the SUM formula. 128
AutoSum, additional functions. 128
Adding, subtracting, multiplying and dividing
without using formulas. 129
Summing data in the intersection of two ranges. 130
Summing data from acell in a number of sheets. 130
Summing According to Criteria, the SUMIF Formula. 131
The SUMIF formula, arguments. 131
Using two arguments of the SumIf formula. 132
Using comparison operators (< ) as criteria. 132
Using SUMIF to sum two ranges according to criteria. 132
Summing totals with text characters as criteria. 133
The COUNT Formula. 134
Counting the number of cells in a range that includes numbers. 134
Counting the number of cells in a range that includes text135
Counting the cells with data according to criteria. 135
Comparing and Merging Lists, the COUNTIF Formula. 136
Chapter 9: Security and Protection. 139
Security Tab Options. 140
Password to open. 140
Advanced. 140
Digital signature. 140
Macro security. 141
Using a password to prevent opening a workbook. 141
Protecting Workbooks. 141
Unprotecting a workbook. 141
Hiding sheets. 142
Unhiding sheets. 142
Protecting Sheets/Cells. 142
Conditions for protecting cells. 142
Conditions for hiding text/formula in the Formula bar142
Locking/hiding a cell143
Protecting a sheet143
Moving between unprotected cells in a protected sheet144
Protecting Data by Hiding Rows and Columns. 144
Unhiding rows and columns. 145
Preventing Movement in Protected Areas. 145
Changing properties in the Properties dialog box. 145
Using the VBA macro statements. 146
Protecting Cells That Contain Formulas or Text146
Allowing Multiple Users to Edit ranges. 147
Forgotten the Password? There’s a Solution. 147
Chapter 10: Information. 149
Viewing Worksheet Name, Workbook Name and Path. 149
Using the CELL worksheet function. 149
Adding the path to the title bar or status bar150
Viewing data and information in cells. 151
Watch Window. 151
Picture. 151
Creating links for viewing through a picture. 152
Adding the camera icon to the toolbar152
Linking Cells to a Textbox or Object153
Using a MsgBox to Display Information from Cells. 153
Adding Comments to a Formula. 155
Saving Information in Comments. 155
Adding Comments. 155
Changing the Name of the Comment Author155
Changing the Default Comment Format156
Viewing Comments. 157
Displaying a single comment157
Copying Comments to Different Cells. 157
Deleting Comments. 157
Deleting all comments in a sheet158
Printing Comments. 158
Adding Pictures to Comments. 159
Send Information to Comments. 160
Importing and Refreshing Information from the Internet160
Excel 97. 160
Excel 2000. 161
Excel 2002. 162
Refreshing the Internet Data (Excel versions 97, 2000, 2002)163
Refreshing the Internet Data Automatically. 163
Running a Saved Query. 164
Chapter 11: Printing. 165
Adding Icons to Toolbars. 165
Changing and Customizing the Default Settings in a Workbook. 166
Changing the default settings for all sheets. 167
Adding information to headers and footers on all printed
pages. 167
Adding the full path of the saved file. 168
Using a macro to add data to each sheet
printed from the workbook. 168
Black and white printing. 170
First page number170
Scaling. 170
Important Printing Techniques. 170
Printing the page number and the running page number170
Selecting the print area. 170
Defining a contiguous print area. 171
Defining a non-contiguous print area. 171
Repeating rows and columns at the top of each page (Sheet tab)172
Row and column headings (Sheet tab)172
Printing comments (Sheet tab)172
Hiding data before printing. 173
Hiding errors in formulas before printing. 173
Hiding cell errors while printing. 174
Scaling (Page tab)174
Continuous numbering of pages, first page number175
Copying page settings to other sheets. 175
Printing page numbers in a report containing both
portrait and landscape layouts. 176
Page break. 179
Removing or changing a page break. 180
Removing page breaks from the sheet180
Inserting a watermark behind the text180
Inserting a watermark. 181
Printing objects. 182
Selecting print options. 182
Custom Views. 183
Inserting a custom view. 184
Printing a custom view. 184
Adding a custom views icon. 185
Adding a custom view quickly. 185
Deleting a custom view. 185
Custom views are saved at the workbook level185
Report Manager186
Installing the Report Manager Add-in. 186
Adding a report and saving in Report Manager187
Using custom views to add pages to reports. 188
Printing, editing or deleting a report188
Creating a custom Report Manager by writing
a small program in VBA. 189
Chapter 12: Charts. 193
Creating Charts with F11. 193
Creating Charts Quickly Using the Chart Toolbar194
Sizing Charts with the Window. 195
Setting the Default Chart Type. 195
Printing a Chart196
Saving chart printing definitions separately from data. 196
Preventing charts from being printed. 196
Using the Keyboard to Select Chart Items. 197
Changing the Layout of the Chart from Rows to
Columns and Vice Versa. 197
Using the Formatting Tools. 198
Nudge the Chart to a Different Place in the Sheet198
Adding a Linked Picture to a Chart198
Creating a picture of cells that is linked to the source data. 199
Adding the camera icon to the toolbar199
Guidelines for working with linked pictures. 199
Updating Charts by Dragging and Dropping. 200
Automatically Updating Charts with New Data. 201
Displaying Numbers in Thousands in a Chart204
Displaying Different Data Using a ComboBox. 204
Adding a data table to which a chart will be linked. 205
Adding Option Buttons to Charts. 206
Data sheet207
Adding option buttons to worksheets. 207
Adding a Pie of Pie Chart208
Replacing Data Markers with Pictures. 210
Chapter 13: Customizing Excel213
Changing the Window View. 213
Options Dialog box. 214
Displaying zero values (View tab)215
Displaying gridlines (View tab)215
Recently used files list (General tab)215
Set the number of sheets in a new workbook (General tab)215
Standard font (General tab)215
Default file location (General tab)215
User name (General tab)216
Custom lists, in the Custom Lists tab. 216
Customizing Toolbars. 218
Adding icons to the toolbars. 219
Removing icons from toolbars. 219
Adding icons to the menu bar220
Preventing icons from disappearing. 220
Adding a new customized toolbar221
Saving your toolbar in the workbook. 221
Removing customizations from toolbars. 222
Displaying a menu. 222
Customizing an icon. 223
Adding icons to an Excel menu. 224
Saving changes to toolbars and menu. 224
Template. 225
Saving a customized workbook as a template. 225
Opening a template. 226
Opening a template file automatically upon starting Excel227
Inserting a sheet into the workbook from the template. 228
Updating a workbook template. 229
Chapter 14:
Opening, Closing and Saving Workbooks. 231
Opening a New Workbook. 231
Opening a Previously Saved Workbook. 232
Opening a workbook from the recently used file list232
Saving a Workbook. 233
Saving a workbook using Save As. 233
Inserting the path where the workbook is saved to
the title bar or the status bar234
Saving Workbooks in a Customized Workspace. 235
Opening workbooks saved in a workspace. 236
Saving shortcuts to files/folders in Favorites. 236
Opening workbooks from a list of hyperlinks. 237
Closing a workbook / workbooks and Excel238
Chapter 15: Data. 239
Guidelines for Organizing Data in Excel239
Preparing the List to Sort, Filter and Insert Subtotals. 241
Using formatting to set apart the column labels
row (field names)241
Freezing column labels. 242
Entering Data into the List242
Using forms to enter data. 242
Applying Color to Data Based on Criteria. 243
Using conditional formatting to color data. 243
Applying color to maximum or minimum values. 245
Chapter 16: Importing Text Files. 247
Creating a Text File. 247
Importing ASCII Text Files. 248
Troubleshooting problems while importing text250
A minus appears to the right of the number250
Data that is not formatted as a number or date. 251
Erase Unnecessary Characters. 252
Create and Add a Function for Reversed Text Characters. 252
Chapter 17: Sorting. 255
Performing a Simple Sort256
Guidelines for Sorting Data. 256
Sorting a Large Number of Fields. 257
Sorting by Column. 258
Sorting by Custom Lists. 259
Inserting a Line between Groups of Data after Sorting. 261
Chapter 18: Filtering. 263
Excel offers two filtering options. 263
Adding Icons to the Toolbar263
AutoFilter264
Printing data after AutoFilter265
Saving AutoFilter criteria by using Custom Views. 265
Custom AutoFilter266
Filtering by wildcard text characters. 267
Filtering by the date field. 267
Filtering according to date by changing the format267
Color rows according to criteria. 269
Summing filtered data. 270
Advanced Filter271
Using advanced filter271
Canceling advanced filter272
Copying the advanced filter results to another location. 273
Using the Database Functions to Sum Data According to Criteria. 274
Disadvantage of using the Database functions. 274
Unique Records. 275
Filtering a record into one unique record. 276
Using the COUNTIF function to filter a record into a unique record. 277
Coloring a unique record. 278
Chapter 19: Subtotals. 279
Adding the Subtotal Icon to a Toolbar279
Adding Subtotals. 279
Removing subtotals from a list282
Adding subtotals according to two fields. 282
Adding additional subtotals and using additional functions. 283
Adding subtotals to a date field. 283
Printing. 284
Copying consolidation of subtotals. 284
Applying colors to subtotal rows. 285
Conditional Formatting. 286
Replacing/deleting the word Total286
Chapter 20: Grouping and Outlining. 289
Grouping and Outlining Guidelines. 290
Adding Icons to Toolbars. 290
Adding Manual Outlining. 291
Clearing the outline. 292
Combining Automatic and Manual Outlining. 292
Copying or applying color to reports created by
grouping and outlining. 293
Chapter 21: Consolidating Data. 295
Consolidating Data Tables. 295
Consolidate with formulas if the row & column
headers of all worksheets are identical296
Consolidate with Copy & Paste if the columns
of all worksheets are identical but the rows
contain different records. 297
Adding a department name column to each table. 297
Consolidation. 299
Rules for consolidating data with the Consolidate technique. 299
Consolidation options. 300
Consolidating without links. 300
Consolidating with links. 305
Comparing Lists. 308
Comparing Three or More Lists. 310
Consolidating Data Tables by Using a PivotTable,
Multiple Consolidation Ranges. 310
Chapter 22: PivotTable. 315
Basic Concepts: Terminology Used in PivotTables. 315
Creating a PivotTable. 316
Rules for organizing data to create a PivotTable. 316
Defining a name for the data table. 317
Steps 1 and 2. 317
Data table in another workbook, open or closed. 318
Data table in an open workbook. 318
Data table in a closed workbook. 319
Step 3. 319
Formatting data fields in a PivotTable. 320
The PivotTable toolbar is not displayed. 320
Regular Work with the PivotTable. 321
Filtering and inserting a query into the PivotTable. 321
Inserting subtotals. 322
Hiding items. 324
Canceling "hide items"325
Sorting items. 325
Displaying Top 10 Records with AutoShow. 325
Refreshing data. 328
Inserting a sub-detail as an item.. 328
Sending drill-down detail to a new sheet329
PivotTable Fields. 329
Inserting a field. 330
Deleting a field. 330
Grouping items and inserting a new field. 330
Grouping a field with items that are numbers or dates. 331
Grouping a date field. 332
Inserting a calculated field. 336
Formatting a calculated field. 337
Updating a calculated field / deleting a formula. 338
Adding a data field and changing the calculation method. 338
Changing the calculation function and formatting the field. 339
Inserting fields to calculate % and more. 340
Adding a running balance column. 342
Changing the presentation of multiple data fields in a PivotTable. 343
Formatting a PivotTable. 344
Printing a PivotTable. 345
The options dialog box. 345
Inserting Additional PivotTables from a Single Data Table. 346
Quickly creating several PivotTables from a single data table. 347
Retrieving Data from a PivotTable. 348
The INDEX and MATCH formulas. 350
The SUMIF and OFFSET formulas. 350
The GETPIVOTDATA formula. 351
Inserting a Chart from PivotTable Data. 352
Creating a PivotTable by Consolidating Several Data Ranges. 353
Chapter 23: Using Functions and Objects to Extract Data 355
Assign names to the data table and add a column
with consecutive numbers. 356
Assign names. 356
Add a first field with ascending consecutive order357
The Lookup formula group. 358
Lookup formula. 358
Returning a value from one data range. 359
The Lookup formula in the Function Arguments. 360
The Lookup formula: returning a value from a parallel range. 361
The VLookup formula. 363
Enter the VLookup formula. 364
The Match formula. 366
Enter the Match formula. 366
The Index formula. 367
Boost the clout of your calculations: combine formulas. 368
Combine the VLookup and Match formulas. 368
Combine the Index and Match formulas. 371
Combo Box. 372
Where do I find the Combo Box?. 372
Working with the combo box. 372
Add a combo box. 373
Advantages of working with names in a combo box. 374
Deleting a combo box. 375
Deleting all the objects in a worksheet375
Adjusting the size of the combo box to the cells. 375
Automatic adjustment of the combo box. 375
Combine the VLookup formula and the Combo Box. 376
Adjust the number of the column in the data table
to the number that is linked to the combo box. 377
Combine the Index formula, Validation and Combo Box. 378
Add a validation list379
Summarize data according to criteria from the data table. 380
Chapter 24: Loans. 385
Syntax for loan calculation functions. 386
PMT (Rate, Nper, -Loan Amount)386
PPMT (Rate, Which Period, Nper, -Loan Amount)386
IPMT (Rate, Which Period, Nper, -Loan Amount)387
NPER (Rate, Pmt, -Loan Amount)387
RATE (Nper, Pmt, -Loan Amount)387
PV (Rate, Nper, Pmt)387
Create an Amortization Schedule. 388
Simple amortization schedule. 388
Amortization schedule with a grace period. 388
Amortization schedule for random payment389
Table for Calculating Payments for a Loan with Two Variables. 390
Chapter 25: What-if393
Sensitivity Analysis (Data Table)393
Creating a Data Table. 394
Adding a Break-Even Point395
Finding a Solution by Goal Seeking. 396
Calculating the sales required to achieve desired profits. 396
Installing the Solver Add-in in Excel Versions 97 and 2000. 397
In Excel Version 2002. 397
Work Techniques With the Solver397
Creating reports with Solver400
Solver Options. 400
Solver on the Internet401
Scenarios. 401
Adding Different Scenarios. 402
Step 1: Define Names. 402
Step 2: Create Scenarios. 403
Enter values using a temporary formula. 405
Updating data in a scenario. 405
Show the scenario in the worksheet405
Adding a Scenario icon to your toolbar405
Reports. 406
Printing Scenarios. 407
Chapter 26: Running a Macro. 409
You don’t see a dialog box that resembles the
dialog box in the picture?. 411
Adding a macro to a module. 411
Running a macro from the VBE dialog box. 412
Running a macro up to a stopping point413
Stopping a macro. 414
Using an icon to run a macro. 414
Attaching a macro to an icon on a toolbar415
Recording a macro. 416
Shortcut keys. 417
Storing a macro. 417
Storing a macro in a personal workbook. 417
Stop recording. 417
Checking the lines of code you recorded. 418
Visual Basic Editor – VBE. 419
Colors in lines of code. 419
Changing the module name. 420
Deleting a module. 420
Easy Help on any command. 420
Using the recording as help. 420
Chapter 27: Write Your First Program.. 421
Writing a Program to Consolidate Data Tables. 421
Reducing the lines of code. 427
Exchanging repeated actions with loops. 427
Loop no. 1. 427
Loop no. 2. 428
Making code more efficient428
Do not select objects. 428
Do not select sheets. 429
Combine copy and paste into a single statement429
Eliminate redundant loops. 430
Find the last used row using End ()430
Copying data from another workbook (opened or closed)431
Switching between open workbooks. 431
Switching to a closed workbook. 431
How can I tell if the workbook is open or closed?. 431
I copied the data and I want to close the workbook I opened. 432
I want to save the data in a new sheet in the workbook. 433
I want to save the data in a new workbook, change its name
and save it in a folder433
The data in the Consolidate Tables sheet is not formatted. 434
I would like to define a name for a table to consolidate data. 434
Now that you have finished, you will certainly want to print435
Chapter 28: Other VBA Techniques. 437
Variables. 437
Variable Declaration. 438
Display a message to the user438
Displaying the results of calculations on variables in MsgBox. 439
Events. 440
Conditional Statements – IF. 441
Syntax of IF statements. 441
Using IF to prevent a macro from running. 442
Loops. 442
Do While loop. 443
For loop. 443
For Each loop. 444
Planning Your Work Efficiently with Macros and Modules. 444
Saving macros by subject in modules. 444
Your work in Excel includes a number of related workbooks. 445
Using xla Add-in files to store macros and functions. 445
Why create Add-in files?. 446
How do I create an Add-in file?. 446
I created an Add-in file, and I want to convert it to a
regular xls file. 447
How do I hide the modules of an Add-in file and protect them?. 447
How do I install an Add-in file in the Add-in list?. 448
Tips, Useful Commands and Answers to Commonly
Asked Questions. 448
How do I insert the SUM formula?. 448
How do I prevent the Workbook_Open macro from running?. 449
How do I freeze the window and sheets and keep
them from jumping all over the place when the macro
is running?. 450
How do I prevent Excel alerts from being displayed?. 450
How do I run a macro on a protected sheet?. 450
When inserting the macro into the module, I have a
problem with lines of code being split up, so the macro
does not work. 451
How can I run a macro on a specific date or time?. 451
How do I prevent the Update Links message from
being displayed?. 451
How do I paste a number format from an Excel sheet
directly into the lines of code?. 452
Index. 453
Function Reference. 459
Foreword
If you are like me, you consider yourself a power user of Microsoft Excel. While we consider ourselves far more proficient than those who are more comfortable in Word than in a spreadsheet, almost everyone that I’ve met does not understand the full depth of features available in Excel.
In this book, Jelen and Rubin provide us with a wealth of rarely used tips and techniques for unleashing the power of Microsoft Excel. Because Excel was developed to accommodate former users of Microsoft Multiplan and Lotus 1-2-3, we all find that there are many ways to accomplish the same task in Excel. From the first chapter with simple but brilliant methods for saving time navigating the spreadsheet to the powerful & dynamic charts, you will find many new techniques that will take your usage of Excel to the next level.
You will find that the book is dense with tips. There are not long-winded explanations here – you need to stay alert and pay attention because you will learn something new in every paragraph. If you study and learn these tips, you will find that you are far more efficient with Excel, while at the same time, your range of techniques available in Excel will be greatly enhanced.
Best of all, the authors are living, breathing 24/7 resources for your Excel needs. Through their websites at www.MrExcel.com and http://www.exceltip.com, you can find an excellent community of Excel gurus who are ready to share their knowledge and expertise in order to help your Excel skills grow.
Ivana Taylor, Third Force Marketing
Introduction
Microsoft Excel is an incredibly feature-rich product. Far too many people are introduced to Excel through a trial-by-fire basis – they discover they need Excel for their job, learn the basics and then continue to do things the hard way without learning the tips which can cut hours and hours from their routine tasks.
It is very possible to use Excel for 40 hours a week and not be aware of many of the incredibly powerful techniques available to them in Excel. Both of the authors teach others these power tricks in Excel. My favorite experience is to be in front of a room full of accountants who believe they must know everything about Excel because it is their main tool at work. When I hear the first collective gasp from the room within the first two minutes of the seminar, I know that I have hit my stride and will provide an enriching class to these experienced Excel users.
This book is designed for the intermediate to advanced Excel user. It is for the person who can perform the basics with their eyes closed all day but has been too busy to really delve into the menus and learn all of the “new” techniques that were introduced since we all switched over from Lotus to Excel in the 1990’s. You should be able to turn to any chapter and find that it is dripping with tips designed to make your use of Excel far more efficient. Dig in & enjoy!
Bill Jelen & Joseph Rubin
2003