I was reading Contextures post about duplicate entries. In it, she creates a named range that refers to a Table column. That named range will expand and contract as the Table does.
Tables have their own built-in names and they are so handy that I find myself using Tables whenever possible. Here’s me using the table name and an open bracket to get a list of columns while entering a formula.
You may have guessed that I prefix all of my Tables with “tbl”. I also use the singular noun, so it’s tblCustomer and not tblCustomers. One of my favorite uses of Table references is using them in VLOOKUPS. The third argument to VLOOKUP is the column from which to pull. I’ve never been a fan of making that dynamic. Instead, I would hard code the number and change it if the columns change. But with Table references, I find myself making that argument dynamic. It’s a little less error prone, a lot more self-documenting, and while it’s more typing, I don’t have to count columns. An old VLOOKUP of mine might look like
And now it looks like this
Instead of hard coding “2” for the column, I’m hard coding “Carrier”, which is the column name. Obviously that could change, but it will give me an #N/A! rather than pulling wrong information.
The autocomplete feature of the table references makes this easy to enter. I can type “tbl” and get a list of all the tables in my workbook.
Then I can hit the tab key to finish the job. Next, the open bracket gives me all the elements in the table, including #Headers.
But this was supposed to be about naming columns and I got off on this VLOOKUP thing. One of the limitations of Table references is that you can’t use them directly in data validation and conditional formatting. The way around that, as Contextures employs, is to create a named range that refers to the column. I use a convention when naming Table columns.
The name is prefixed with “dv” (for data validation). Then the table name without the “tbl” prefix. Then the column name. The big advantage to me to using this convention is that it’s easier to type the named range in the Data Validation box. There’s no intellisense in the Data Validation box, so you have to remember the named range exactly. Also, when you’re entering a formula in the formula bar, it’s nice to type “dv” and see all of the named ranges that start with that. Or to type “dvCustomer” to see all of the columns in that table that have a named range.
One of the downsides is that the named range can get long. If you have tblCustomer that has a CustomerName column, the named range becomes dvCustomerCustomerName. I think the benefits outweigh the costs.
Leave a comment with your conventions for naming ranges.