In an earlier post I looked at why we might want to use names in Excel, especially with Tables to ease readability and remove sources of error.
In this post I will look at another use for tables, creating dynamic data validation lists and also look at some other Table features not yet covered.
Data Validation
When creating spreadsheets that will be used by others, or indeed if used by you over a period of time, one way to limit the options for data entry errors is to create data validation controlled cells rather than manual typing.
To begin with I create a new sheet called Lists which I will use to host any lists I need throughout the workbook and for this example I have created an new table called tblProgress which has a single column for status. This example is based on a simple task tracking concept.
In another sheet I put a title and then in the cell next to it I select and use the Data Validation function (from the Data ribbon) 
.
This gives a number of options for different types of validation but for this example I will choose List as I want to have a set number of status updates available.
Now, the source of the list would traditionally be the cell references for the list we created earlier but as we are concentrating on names we will instead look to use the tblProgress[Status] list. However, if you simply type =tblProgress[Status] Excel will give you an error.
Instead, to use table names within Lists you need instead to use the INDIRECT function as follows:
=INDIRECT("tblProgress[Status]")
[INSERT DV BOX] [INSERT DV BOX DROPDOWN]
Of course, if instead of a single line we create a one row table then when we now add rows the data validation will automatically flow through.
At this point we have a marginal improvement in terms of readability but the real power of using a table for the list comes as we develop the spreadsheet further.
After we have shown the output to some people we get feedback that actually they would like to add some more status options. If we add these to the bottom of a standard list we will need to go back and manually alter the data validation list which if we have used it in one location may be fine but we may of course have used it in multiple sheets within the workbook which increases the risk of missing an example.
Using a table however means that if we add to the end it will automatically be added to the validation list.
This ability to add or remove items from the list without having to touch any of the cells using the data validation makes life so much easier.
And we retain the benefits in terms of readability if we want to add additional features to the table. As a quick example we have added a narrative description to our statuses so that people viewing know what we are saying.
We can then add another column to our task tracker called Description and enter the following into the first row:
=VLOOKUP(B4,tblProgress[#All],2,FALSE)
This will select the appropriate description from the table.
Although this is a fairly simple example, imagine if you had a tracker covering a number of work streams each with a list of tasks that needed a status. We can now use the exact same formulas in each as we are using the workbook wide Table names rather than cell references or even more traditional named ranges.
Table Special Specifiers
In the example above I slipped in the use of #All so before moving on I wanted to quickly touch on this and some other examples of what are called specifiers.
Previously we have addressed specific columns and used the @ to specify a single row. But often we want to consider more of the table and that is where the # specifiers come in.
- #All – the entire table including column headers, data and totals (if used)
- #Data – just the data rows (which could also have been used in the formula above to the same ends)
- #Headers – just the header row
- #Totals – just the total row (if it exists – none of my tables so far have used this feature)
There is also #This Row which is more or less the same as @ but does have some quirks so @ is probably safer (and less to type!).
Where these are typically used are in cases where functions use arrays (such as the VLOOKUP above) and from a readability point of view continue to improve things – the non-table form would have been Lists!$a$4:$b$10 which would also have needed manually changing as I added or removed status update items.
Total Row
One of the items above refers to a Table Total row and to date we haven’t looked at this automated feature. The context for the examples here would be a budget for an IT refresh across a number of rooms.
To help with the process we have created a Sheet with the unit prices (tblUnitPrice) for the various elements that are found within each room and we can then use this build up the budget using Tables and simple lookups.
We then create a Sheet for the Room template with a table for listing all of the elements within that room that we want to cost. When creating we selected Total Row from the Table Ribbon.
To complete the template we can just use a VLOOKUP like we did earlier to match the Item to the relevant cost.
=VLOOKUP(A4,tblUnitCost[#Data],2,FALSE)
As you can see, the Total row defaults to the SUM of the final column which in this case is the correct result.
However, the row is able to support multiple calculations so if for example it would be useful to know how many items in total we can also add a total to the Number column.
As you can see, there are a number of standard functions that can be used in the Total row and you can swap between them at any time should you wish.
In order to use the Totals row data we use a familiar formula albeit with a twist where we only want to surface the Total for one column (which is often the case).
In our example we have created three rooms with various requirements and are now preparing a summary sheet. To bring through the totals we need to use the following:
=tblRoom1[[#Totals],[Total]]
In this example we are accessing just the #Totals row and are looking for just the column Total.
The obvious thought might be to create the summary page with a table and total row to automate the process but there is a gotcha. As the table will autofill the rows of a column as soon as you enter the above in the first row (for Room 1 for example) it will use the same formula all the way down whereas you want Room 2 in row 2 etc.
To overcome this, type the formula into the first row and let it autocomplete, then use the undo to go back, then if you add again you should see a small icon pop up next to the cell which if clicked gives you the option to stop automatically calculating the cells in that column.
You can now enter the formula into each cell or copy and paste and change the table name to the match the appropriate entry. The table Total row will automatically update as you add more rows.
By building our sheet up using these basic blocks we are now in a great position to use this as a tool throughout the project. In our example we have an extendable model for defining what each room will include and standardised unit costs. At any time we can add items to our unit costs, so if for example one room needs higher specification laptops we simply add another entry to that table and add it into the specific room.
As the project moves on we may get revised pricing estimates so creating a v2 copy and altering a single line in the unit cost table will automatically recalculate the entire budget without any further manual intervention meaning that we have less opportunities for missed updates or typos.
What’s more, by using meaningful names, if others need to use the spreadsheet later it should be easier for them to see what the intent was behind the calculations and indeed, it may help us if we don’t touch it for a few months and then have to come back and use it.
