Excel Data Table Tutorial

Welcome to our Excel Data Table Tutorial!

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.

Click here to download the start file for Video 1.
Click here to download the completed file for Video 1.
Click here to download the completed file for Video 2.

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.

Excel VBA Move Data Thumb

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. The 1st 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).

Simply put, a data table automates the process of finding the best output from lots of inputs. It’s 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.

EXCEL DATA TABLE TUTORIAL VIDEO 1 – CONCEPT AND SIMPLE EXAMPLE

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!)

Further, data tables are difficult to tweak / delete, you might be familiar with the ‘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 two where we explore a powerful real-life application of an Excel data table.

EXCEL DATA TABLE TUTORIAL VIDEO 2 – REAL-LIFE APPLICATION

First, thank you to channel viewer Milan for sending this one in. Milan is working in the garage door industry and wishes to find the best combination of two panels (of different lengths) for a specified door height. Without data tables, this kind of problem could take hours to solve!

In the video, Chris takes you through the model he built to give Milan an instant optimal answer for any garage door height. The secret: the power of data tables.

We know from part 1 that we cannot use data tables without a ‘model’. A model consists of input, process and output. In this case, inputs are a combination of the two panels, and the output is the garage door height that this combination creates. Simple arithmetic formulae comprise the ‘process’ because they do the working out. It’s a simple Excel model – they do not have to be complex!

Then, Chris goes through the setting up of the data table, emphasising the need to be steady and systematic in your approach. We also deal with the tricky issue of editing and deleting data tables; once implemented, they never seem to go away! Chris demonstrates how to do it.

With the two variable data table in place, it is possible to ‘optimise’ by simply looking at the table and doing some simple workings manually. But, we want more than that: we want a fully automated solution that makes the best combination absolutely clear.

To do so, we implement more formulae (=abs(), =min() and =match()) to identify the specific combination of panels that provide the best door height. Moreover, we display the combination at the top of the spreadsheet (everybody hates scrolling down to find important information), and even include useful information like excess height.

It’s an illustrative example of a simple and powerful Excel model, with data tables deployed to powerful effect.

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 on the YouTube video, Chris will get back to you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

This entry was posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.