Data Table in Excel - What-if-Analysis - Powerful Analysis feature

Data Tables are a powerful but somewhat unknown Excel feature. Data Tables, not to be confused with Tables in Excel is part of What-if-Analysis. In Excel, we frequently have complex formulas using multiple input cells. A Data Table offers one-input or two-input tables. Data Table is one of three items under What-if-Analysis and is located on the Data tab. The PMT (payment) function is an excellent example of when to use a data table. There are at least three input cells in the PMT function. The interest rate, loan amount, and years (the term). A data table allows you to change an input cell and get multiple results. For example, What if you want to see the PMT outcome at different interest rates? Using a data table, you can quickly see all the results at once without having to keep changing the interest rate. Data tables can be one-input tables or two-input tables.

Definitions:
* Input cells - cells you can change that are constants.
* Dependent cells - cells that are formulas.
For example, if A1 to A3 contains the numbers 9, 4, and 7, respectively, and A4 is =SUM(A1:A3), the answer is 20. Cells A1 to A3 are input cells. Cell A4 is a dependent cell.

Keyboard trick to hide a cell. Go to Format cell (CTRL + 1) and under Custom use three semi-colons (;;;). Click OK.

Chapters:
00:00 Introduction
01:09 The input cells
01:26 The dependent cells
02:06 TIP Find dependent cells
02:35 TIP Find the input cells
03:01 Setting up a data table
03:55 One-input data table
06:03 Change the input in data table
06:31 Two-input data table
08:51 House Note Example
12:46 Closing remarks

#datatable #excel #msexcel #chrismenard #chrismenardtraining #chrismenardexcel

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[robinherrick]

The chapter headed two column data table should be 2D data table. A two column data table is a 1D data table with two columns - a useful feature that would save you swapping between 1D outputs. In fact you have can have any number of rows or columns (within reason) in a 1D data table. Cheers!