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.
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. 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
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 optimization technique? Have you found an application in your work? Leave a comment on the YouTube video, Chris will get back to you.