Getting old Excel Lists functionality in “Excel Tables”

I used to use Excel Lists all the time for documents that needed we wanted users to do data entry in a structured way. Excel Lists were great because you could quickly and easily edit columns and push the validation schemes, etc. down the rows. That was then. Now, Excel has removed any mentions of “Lists” and instead are referring the the similar functionality as “Excel Tables” and, with a little work, you can get what you need. The other major concern is the ability to render properly on Mac or Windows. Thankfully, Tables do in my tests. Here is what I do now.

(Screenshots are Excel 2007 under WINE)

Get the worksheet ready and highlight the rows and columns you want to put into an “Excel Table” then from the 2007 Insert ribbon use “Table”

etable1

You will get a prompt to confirm the region and after you “OK” it will render what used to look like an Excel List.

etable2

From there on out, the cells in the Table work in a group, but you will have to use Fill Down and other regular functions that you used to just program in at the list column settings. For example, I do validation for entry referencing an external list column of a few values and want the user to click into a cell, get a pop-up list and have it validate. Here is how I do it. Remember, rather than being able to set these at the header of a column, you have to do it at the cell and fill down or up.

Select the cell in the column you want to make a validated pop-up list for and then go to the “Data” ribbon in Excel to “Data Validation”

etable3

In Data Validation, you can point it at your list of values and enable the in-cell dropdown.

etable4

Confirm the cell is working for you properly, then you have to “fill down” the rest of the cells in the column.

etable5

Fill down is now in the Home ribbon

etable6

I also do conditional formatting on a column for this worksheet that is just an =Exact(J2,L2) but I want it to generate red or green if the J column and L column have different values.

Conditional formatting setups work the same way in that you have to highlight the region of cells and then add the conditional formatting rules. Conditional formatting is on the Home ribbon.

etable7

and I want some shading for the value “FALSE”

etable8

and I want shading for the value of “TRUE”

etable9

Anyway, this gets you pretty close to what I used to love about Excel Lists. You don’t have the nice functionality of dealing with validation and formatting at the column headers, but you can get there through the Tables options.

Leave a comment or reply