I was surprised to find a List Wizard in Excel 2008 (Mac) because Excel 2007 (Windows) has Tables, and had already been out for nearly a year when Excel 2008 was released.
Here I’ll walk you through the process of setting up a List in Excel 2008 but won’t go into every detail because, as you’ll soon see, there are lots of features in the List Wizard. I’ll start with the same data table from my last post.
To create a List in Excel 2008, select your existing data table and choose Insert > List…, and the List Wizard will appear.
Step 1 of 3 wants to know Where is the data is for your list? and has correctly selected the range and guessed that My list has headers. It also wants to know Where do you want to put the list?
I leave all of these selections unchanged and select Next > to move along to Step 2 of 3.
Here you can select each column in the List and change the Column name and Data Type.
The Settings button takes you down-the-rabbit-hole, so to speak, and brings up a Column Settings dialog box.
Here you can also change the Column Name and set the Data Type but you can also choose the cell Formatting, set up Conditional Formatting, and Data Validation.
Click Formatting… to bring up the Format Cells dialog box. Select the Condition Formatting… button to bring up the Conditional Formatting dialog box. Click the Validation… button to bring up the Data Validation dialog box. Having these three features available is rather amazing. (I’ll forgo the screen shots of each dialog box.)
There are three more options on the Column Settings dialog box. Formula, Default value and Unique values only. If you select Calculated Column for the Data Type, then the Formula box becomes active and you can enter a formula for that column. If you select the check box for Default Value that box becomes active and you can enter a value that will appear each time a new row is generated. Selecting the check box for Unique Values will then only allow unique values to be entered into that particular column. You get a warning when trying to enter a duplicate value.
Remember, all of these features can be performed on every column of data in the List.
Clicking Next > brings you to Step 3 of 3 in the List Wizard.
Here you can change the List name. If you select the box for Autoformat list after editing the AutoFormat… button becomes active and will bring up a dialog box if selected.
Finally, you can also click the box to Show totals row, and Show List visuals to On or Off or leave the default setting of Auto.
Click Finish to create the List.
Notice the floating toolbar with List controls. The bottom three on the right toggle AutoFilters, Total Row, and Visuals. The latter being the exaggerated border around the List that shows when the list is active.
Along the top row of this toolbar you can access the List Wizard, Column Settings, insert a column, or row, and access AutoFormat.
Then there’s the List drop-down menu (second row, first item) that gives you lots of options.
The menu item Remove List Manager is used to convert the List to a range.
Finally, there is an arrow head on the right side, in the middle of the toolbar that you can click with your mouse to bring up what looks to be a navigation menu.
When adding a List to Excel 2008 you can utilize numerous features that seem to be quite extensive.
Give Your List a Defined Name – Oh Wait, Don’t Bother
For all the List features you get in Excel 2008, the most disappointing thing is that when you give the List a Defined Name, Excel will not automatically expand the associated range when you add data to the List.
I selected the List range, chose Insert > Name > Define… and gave my List the name TableOne, then clicked OK.
I entered another row of data and the Defined Name TableOne still refers to the same range, cells A4:C7.
This is surprising to me because in Excel 2003 (Windows) you can give your List a Defined Name and Excel will automatically expand the range as you add more data.
Gregory, did you try defining TableOne as A4:C8? Perhaps once it’s converted to a list it includes the row below the last record as well.
That’s what I did the first time and it didn’t work. I just tried including the entry row again, but no luck.
Gregory, why not just use the List name instead of defining a new name? Is there any functionality in a regular defined named range that is not available in a List name?
The List name does not show up in Defined Names and if you try and use it in a formula like =ROWS(List1) you get a #NAME? error, so for all intents and purposes, the List name is worthless as far as I can tell.
This is consistent in Excel 2008 (Windows) and Excel 2008 (Mac).
The new Tables have Defined Table names in Excel 2007, 2010 (Windows) and 2011 (Mac) where they fixed this problem.