Deborah’s Excel Page

Copy Columns

This is not an ordinary copy example. Using the Worksheet_Change function, you can automatically copy user-specified columns to another sheet by entering a date. Thank you to Kathy, the MGMT 393 student who posed the question on a newsgroup and asked me for help implementing the solution that contributor Nigel gave her.

The Retirement Planner!

Here is The Retirement Planner! This is designed to be used as one of the many tools you should use when planning your retirement. A user’s manual is included with the spreadsheet and all the calculations have been explained. Though designed to work with Excel 97, you should have no problems if you are using Excel 2000. If you enjoy using The Retirement Planner!, please consider making a donation to the Susan G. Komen Foundation.

Auto Loan Calculator

This easy-to-use spreadsheet will calculator your payments over 36, 48, 60, 66 or 72 months. Simply enter the required data such as principal, interest and other fees. Graphs are used liberally to give you a visual representation of the loan comparisons. Download this self-extracting file now.

This application was developed by George Frenya as an assignment for CISS393 Management Information Systems at Columbia College during the January 2000 semester and is used with his permission.

Examples of Formulas

This spreadsheet has examples that will show you how to use
  • Parsing Dates (added November 16, 2002)
  • Data Validation
  • the SUMIF function
  • Nested IFs
  • the ISERROR function
  • the Subtotal function
  • the Count function
  • the VLookUp function
  • Nested VLookUps
  • VLookUp with Nested IF
  • Visual Basic to open a workbook with the same sheet always active (added May 7, 2007)
  • Command Buttons to avoid a circular reference (added May 7, 2007)

Mortgage CalculatorUpdated April 11, 2007

Here is a mortgage calculator. You input the principal, interest, length of mortgage and starting date, as well as an anticipated inflation rate over the life of the mortgage. This will calculate the total principal to be paid (which will be the same as the principal you inputted and the total interest you will pay if the payments are made monthly or bi-monthly). Paying bi-monthly saves a lot of money.
The Mortgage Calculator also calculates the net present value of the interest. That means that it calculates how much those future interest payments are worth in today’s dollars based on the inflation rate you anticipate.
Here is the story that led to me putting the Mortgage Calculator on this page.

Conditional Formatting

added on March 4, 2002
Conditional formatting is a nice feature in Excel. Unfortunately, it is limited. The user cannot format one cell based on the contents of another. Let me restate that: I cannot find a way to use conditional formatting to format one cell based on the contents of another.
I worked around this by writing a macro. Download the Conditional Formatting spreadsheet. It includes data validation, a VLOOKUP to calculate a result the formatting is based on and a macro to apply the formatting.