
Back to Excel Formulas , Tips, Tricks & General Use.
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, Onevariable data tables and twovariable data tables.
Onevariable 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.
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.
Twovariable Data Tables
You can use a twovariable 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 twovariable 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 onevariable 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 Twovariable Data Table.
Rules
A couple of rules for Data Tables:
Download Data Table example workbook
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates