Data Tables – How to Set Up and Troubleshoot One of Excel’s Most Powerful Tools

The Data Table is one of the most powerful tools in Excel because it allows users to sensitize their data. However, Data Tables are also a frequent source of headaches for Excel users. This article describes the steps required to create a Data Table, and more importantly, how to troubleshoot the most common errors that users experience with Data Tables.

The example below uses Excel’s Data Table to sensitize 2019 EBITDA based on different assumptions for 2019 revenue growth and EBITDA margins.

There are four steps to create a Data Table:

1. Create a Blank Table: Create a table with various assumptions for 2019 revenue growth across the top of the table (cells H4:L4) and various assumptions for 2019 EBITDA margins along the left of the table (cells G5:G9). Inside the table, we will sensitize the 2019 EBITDA to these inputs.

2. Link the Top Left Cell of the Table to the Output Being Sensitized: Link the top left cell of the table (cell G4, highlighted in yellow below) to the value being recalculated inside the table, which in this case is 2019 EBITDA (cell D6).

3. Run the Data Table Tool: Highlight the entire table including the row headings and column headings above and to the left of the table (i.e. the range G4:L9) and select the Data Table from the ribbon (Data > What-If Analysis > Data Table). In the box that appears, select: (i) 2019 revenue growth rate as the “Row input cell:” (cell D4); and (ii) 2019 EBITDA margin as the “Column input cell:” (cell D7). Click OK.

4. Format the Table: Finally, format or conditionally format the values in the table. Set the font colour of the cell in the top left corner to white so that it is not visible.

Troubleshooting Data Tables

Many users find that they need to troubleshoot their Data Tables. The following is a list of the six most common Data Table errors users encounter:

1. Workbook Calculation Settings: Multiple Data Tables in an Excel file can slow down the speed and performance of the file. As a result, there is a setting in Excel to perform automatic workbook calculations except for Data Tables. Go to File > Options > Formulas. Under Calculation options, select “Automatic except for data tables”.

2. Data Table Input Cells Are Reversed (“Row Input Cell” and “Column Input Cell” are Switched): If the Data Table is calculating but the values are incorrect, you may have mis-linked your Data Table in step 3 above. Try reversing the cells in the Row Input Cell and Column Input Cell fields to see if this fixes the problem.

3. The Row Headings and Column Headings are Linked to the Original Inputs: If the Data Table is calculating with incorrect values and you have confirmed the Data Table is properly linked (i.e. the issue is not #2 above), the problem may be due to the row headings or column headings in the table. When using a Data Table, the row headings and column headings must be independent of the original inputs. You cannot link any of the row headings or column headings to the actual data inputs that drive the original calculations (cells D4 or D7 in the example above).

4. Data Table is on a Different Worksheet than the Original Inputs: Excel’s Data Table must be on the same worksheet as the original inputs being sensitized. In our example, the Data Table must be located on the same worksheet as the original revenue growth and EBITDA margin assumptions. If you attempt to link the Data Table to cells on a different worksheet, you will get an error message.

5. Cells in the Table Are Not Consistently Locked/Unlocked: Another issue that leads to error messages occurs when cells are inconsistently locked. All of the cells in a Data Table must have the same “locked” attribute. The cells must all either be “locked” or “unlocked”. Attempting to run the Data Table tool when all the cells in the table are not consistent will result in an error. To check or change the “locked” settings of a cell, select the cell, go to the Format Cells menu (CTRL + 1), and choose the Protection tab.

6. Other Data Table Issues: If you attempt to delete a single cell in a Data Table or if you try to insert a column or row into your Data Table, you will get an error message. These errors occur because the Data Table creates an array and a defining characteristic of arrays is that you cannot change only a section of an array (you must change all of it or none of it).