Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Excel data table recipe
Deze pagina in het Nederlands

Batchprocessing: how to calculate your model with many input values

An article by Niek Otten, Excel MVP.

Introduction

Sometimes you want to apply an existing worksheet calculation to many sets of data rather than just the one case for which it was designed. For example, you developed a worksheet which calculates your pension. It accepts some twenty input variables and generates five output variables.

Then your boss demands that you apply that one-time worksheet to 500 employees.

Of course you could write some VBA code to do this. But many people hesitate to use VBA and often they are not even allowed to, on their work PC. Fortunately, there is a solution that does not require VBA. Excel has a feature for doing this, the Data, Table command. But it's not well documented.

Each time I was asked to do something complex using a Data Table, it took me a while to remember how to do it. So I developed this recipe. It may seem like a lot of steps, but it's actually very straightforward and can be done in minutes.  

Excel 2007 and 2010 UI

In Excel 2007 and 2010 the Data, Table feature is no longer available in the user interface. You can still use the feature however; add the needed button by right-clicking your Quick Access Toolbar and selecting Modify Quick Access Toolbar.

Even though the Data table command isn't on the ribbon, you will not find it under the "Commands not in the ribbon" category. Set it to "All Commands" and then find the "Data Table" entry.

Step 1.

Make a copy of your original workbook, and use that to work with in the following steps. (Always make a backup before attempting anything big or new.)

Step 2.

Just an informational step. So that you can keep things clear, we will be dealing with three pages, called:

Source WS (worksheet):

The big table of input data you want to work with, such as the 500 employees' data. Aside from making it row oriented in the next step, this will not be edited/affected in any way.

Model WS:

Your existing worksheet which calculates one set of inputs at a time. This will only have its input cells changed, in Step 6.

Data Table WS:

A new worksheet with the Data Table, including its results. This is where almost all your editing will be done.

Step 3.

Import your source data into an Excel worksheet (of course). This is the "Source WS". All the data (input variables) for each Source WS record must be on one row (line). So work your data into a one-row format, if it covers multiple rows per input situation. If you have extra columns of data, they don't matter; just leave them. All that's needed is that each row of input data has all its data on one row. Input columns do not have to be next to each other.

Step 4.

Now on to the good stuff.   In your Source WS, define Names (Insert /Name /Define) for the columns you need as input. If the columns have a header, do not include the header in the name. So the first line of the named area should be the first line of your variable data.

You can save yourself some time by having Excel define the names for you by using the button "Create From Selection" in the Defined Names group on the Formulas tab of the ribbon. In Excel 2003, find this option in the menu through: Insert, Name Create. Make sure you only select the "Top row" check box.

Step 5.

Insert a new sheet into your workbook. This is your "Data Table WS". Across row 1, starting in column B, fill the column headers (text) with the input column headers as defined in step 4. In cell A2, enter the number 1. In cell B2, enter the formula =INDEX(SourceColumn,A2), where SourceColumn is just an example of a Named range you created in step 4. Each input variable should get a similar formula in its own column.

Step 6.

On your Model WS, make all your Model's input cells refer to the input columns of row 2 of the Data Table WS. So the first Model input field now has "=DataTable!B2" as a formula, instead of an input value, the second one "=DataTable!C2", etc. Do this for all the input fields in the Model.

Step 7.

Extend your Data Table WS to receive output by making new column headers in Row 1, to the right of the input columns. (Actually, they don't have to be to the right, as long as you keep things straight.).

Now make the output fields in row 2 of the Data Table WS refer to the output fields of the Model WS. Just simple references like "=Model!C37" or "=Sheet17!C37".

Step 8.

Test it. Enter an index number in A2 and check that it retrieves the correct input from the Source WS and generates the correct output, all on this one line.

Step 9.

Starting in cell A2, fill down a series of consecutive numbers (1,2,3...), for however many Source records you have. Don’t use a formula for this; use the fill handle, the Edit>Fill>Series command, fill it in by hand, use a +1 formula and convert using Copy/Paste Special/Values - anything that will make it a "hard" number, NOT a formula.

Step 10.

On the Data Table WS, select cells A2 to the last line and the last column of the table and then choose Data, Table from the menu or click the Data Table button on your Quick Access Toolbar. In the dialog box, leave "Row input cell" blank. In the "Column input cell" box, enter A2.   Click OK, and see your table filled with input data and computed results. Voila!

How long did it all take... 7 minutes?


Comments

All comments about this page:


Comment by: Simon Hurst (1/19/2012 7:49:34 AM)

I think it is still in the interface. Data ribbon, Data Tools group, What-If Analysis drop down?

 


Comment by: Jan Karel Pieterse (1/19/2012 10:04:18 AM)

Hi Simon,

Well spotted, I couldn't find the thing!

 


Comment by: Pete (5/21/2012 3:59:50 PM)

This article was a lifesaver. Thank you!

 


Comment by: Colin Delane (12/18/2013 2:31:02 AM)

A very useful tip for any multiple scenario model, but especially good for a repetitive task such as 500 employees.

Some pictures showing model layout (especially of the Data Table WS) would be a useful addition to this article.

 


Comment by: C. Weaver (4/1/2015 4:40:21 PM)

I created a data table, but included an extra column that I did not want (say A1:D20 and I should not have included column D). How do I undo/reset column D so that is no longer part of the data table. I've tried overwriting, paste special, and deleting cell contents and cannot perform any of these function because of the data table.

 


Comment by: Jan Karel Pieterse (4/2/2015 7:27:00 AM)

Hi Carol,

I'm afraid you will have to get rid of the table formulas and redo creating the table.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].