Excel Data Table Tutorial Part 1/2

Welcome to Tiger's Excel Data Table Tutorial Series - the only Excel videos you will need to get you up to speed with this powerful - and often misunderstood - technique.

Download File Link:

What is a data table? Like so many things in Excel, its name does not help us understand its role. An Excel data table does much more than store 'data'; it actually presents outputs from an Excel model. Applied correctly, it allows us to instantly test any number of model inputs, and identify the input(s) that create(s) the best, or 'optimal' output. It's quick and powerful 'what-if' - analysis: what would the output be if this were the input value? So, I suggest it would be more accurately called an 'Outputs' table, or even an 'Optimisation' table.

That's exactly what an Excel data table allows us to do - quickly optimise a model. There is an important point to note here, we certainly need a 'model' to use a data table! A model consists of an input cell (or cells) linked to some calculations and an output. This video features what Chris calls 'the simplest possible model'; but, it is still a model because it consists of input, process and output (eg. 4 x 3 = 12).

This process of optimisation is something we could do manually - inputting values into the model and noting down the outputs in Excel or somewhere else as we move towards the optimal solution. A data table does this for us at the click of a button, instantly tabulating outputs for any number of 'candidate values' that we choose ourselves. How much time could that save us? Cool!

As we see in the video, we can use a one-way data table to test a single input, or a two-way data table to test two inputs. You may also hear the terms 'one-variable data table' or 'two-variable data table' used. Both can be tricky to set up: a particular layout is required, the table must be properly linked to the model's output cell, and we must understand the meaning of 'row' and 'column input' in the data table dialogue box (it's confusing!) We go through this step-by-step in the video.

Further, data tables are difficult to tweak / delete - you might be familiar with the annoying 'you cannot change part of a data table' error message, for example. We deal with how to quickly delete a data table in the second video in the series.

Armed with this simple foundational example, you are ready to move onto video 2 where we explore a powerful real-life application of an Excel data table, sent in by a viewer of the channel.

Video 1 link (released 10/05/19)
Video 2 link (released 24/05/19)

Thanks for reading! So, did you manage to get the data table working? Can you feel the power of this awesome optimisation technique? Have you found an application in your work? Leave a comment below, Chris will get back to you.
---------------------------------------------------------------
🎁YOUR EXCEL CHEATSHEET
🎁1 Page Summary Of What You Need To Know

Also on Insta :-)

[TigerSpreadsheetSolutions]

Thanks for watching! So, did you manage to get the data table working? Can you feel the power of this awesome optimisation technique? Have you found an application in your work? Leave a comment below, Chris will get back to you.

[anthonyls]

Thank you for getting straight to the point. The last time I did this was over 20 years ago. I needed a quick refresher.

[nitinyss]

Thank you so much, for explaining it so well it helped me understand how to finish my assignment.💯😃

[alfredbinimoy]

You're really a great teacher....your teaching method is for helping us ....Go ahead wishes

[DailyDoseOfDisciplin]

You explain beautifully. request if the video stops showing the dog as it irritates

[CarlosSanchez-xzxv]

Great technique ! Thanks for sharing Chris.

[ca.lakshminarayanreddyjamb]

Really it's a great feature and thanks for explaining in detail.

[thegrubzz]

this was great thanks! Helped me for my excel interview

[thecliniconlinetv]

Well simplified. It's been so helpful

[cruisysoozi]

Why does the dog image appear from time to time??

[kylepender]

Does the table have to be located in the same sheet as the input row/column cells? My table works fine if i have it in the same sheet as the input cells but when i try to make one on a different sheet its telling me the input cell reference is not valid?!

[Piccolo_Re]

The data table row input and column input dialog box is super confusing when working with multiple products or items.

[alaajaber]

how can I make a drop down list ( with check boxes ) that when I choose an entry from the list it gives me all its related data with it ( in the same column) as a table format