Excel Data Tables

Download Data Table example workbook

Data Tables are a range of cells that are used for testing and analyzing outcomes on a large scale.

A Data Table will show you how by changing certain values in your formulas you can affect the result of your formula. Data Tables can store the results of many different scenarios for you in one table, so that you can analyze them to select which scenario is your best option. The results are then written into a table form in your Workbook in a location specified by you. Data Tables are written as array formulas , which therefore allows them to perform multiple calculations in a single location.

There are two types of Data Tables, One-variable data tables and two-variable data tables.

One-variable data tables

The first thing you must do is to create a base or test model and tell your Data Table which formulas from your base model you want to test. This is easily done from inside the Data Table by placing a formula to reference the formula in the base model.

Here is an example:

Lets say that we wish to purchase a new tractor for work on our family farm. We need to know that if interest rates fluctuate we can still afford to pay for the tractor. So we need to know what our loan repayments will be, what our total repayments will be and how much interest we are paying.

1. Open The Attached Workbook On The Base Model Worksheet. The Highlighted Cells Contain Formulas.
2. Now Click On The Worksheet Tab Named OneVariable Table And Notice That This Has Exactly The Same Data As The Previous Table, Plus An Additional Area Already Set Up For The Data Table.
3. Click In Cell E3 And Type In =B11, Which Is The Monthly Loan Repayment
4. Click In F3 And Type In =B13 Which Is The Total Repayment.
5. Click In G3 And Type In =B14 Which Is The Total Amount Of Interest Paid.
6. Highlight The Range Of The Table D3:G9, Click Data>Table
7. Leave The First Box, Row Input Cell, Blank. Nothing Is Required For A One-Variable Table
8. Click In Column Input Cell, Click On The Collapse Dialog Button And Select Cell B5 Which Contains The Original Interest Rate Of 8.97%
9. Collapse Back Through To Your Table Dialog Box And Select OK.

You should now see the results of the calculations given the values entered in D4:D9 that would appear in cells B11, B13, and B14 of your base model pasted into the table.

Two-variable Data Tables

You can use a two-variable Data Table to gauge the effect on one formula by changing the value of two input cells within the one table. With this type of table, you can nominate two series of data that can be placed back into the original model into two different input cells. Using the example above, this means that you could nominate a series of interest rates to place in the original interest rate cell (B5) and a series of loan terms to place in the Term of Loan cell (B7). When creating a two-variable table, one series is entered into the first column of the table and the other into the first row of the table. As when creating a one-variable table, the formula reference that we want to test needs to be placed into the blank cell at the top of the first column of the table. The attached workbook shows an example of a Two-variable Data Table.

Rules

A couple of rules for Data Tables:

1. Set Up A "Base Model"
2. Do Not Change The Values In The "Base Model"
3. It Is A Good Idea To Document The Area Around Your Data Table, So You And Other Users Can Tell What It Is You Are Analysing.
4. You Can Use Data Tables To Change Up To Two Variables Only
5. You Can Create As Many One-Variable Or Two-Variable Data Tables As You Like In A Workbook.

source: http://www.ozgrid.com/Excel/data-tables.htm

More...

How to securely password protect an Excel file

Password protecting your Excel worksheet can prevent others from tampering with your data, but it can also be a great way to protect your other passwords as well. When using Microsoft Excel, there are many ways that you can password protect your spreadsheet.

One of the first and easiest methods is to password protect the entire sheet or workbook. To do this in Microsoft Excel 2007, first go to the Review tab and then click on Protect Sheet or Protect Workbook.

When a pop-up window appears, select your options and type in the desired password. Your password can be something crazy, that no one would ever suspect (like 45pQ93S21!). In order to remember your password, copy it into a different workbook for safe keeping.

Create a new file, just to store your passwords. You can name this file anything you want, but it would probably be safer to name it something mundane – like HistoricalStockPrices.xls – instead of something that screams “open me” like ListOfMyPasswords.xls. In this new file, create your password list that you can then refer to whenever you want to open a document.

Next, password protect the entire workbook. To do this, click on File, then Save As. When the file-save window appears, type in your file name, then select the Toolsdrop down menu. From that menu, select General Options.

This will enable you to password protect the entire workbook and prevent anyone else from opening it and viewing its contents. For this password – remember to use one that you will remember. There is no easy way to retrieve a lost Excel password, so make this one something unique that you won’t forget.

Now, whenever you want to open that file, you will first see a screen like the one below asking for your password. This will enable you to protect all of your passwords and never forget them.

Useful Tip: Creating a password file can be especially useful if you are required to frequently change your password at work and always have trouble remembering the new password.

source: http://www.online-tech-tips.com/ms-office-tips/the-password-you-can-always-remember/

More...