Jan 07, 2014

Excel is the perfect application for storing lists. Lists of parts, lists of products, lists of people… the list, as it were, is endless as to what you can store. And with that list comes a large number of Excel features that enable you to work with that data. This post isn’t so much about what you can do with lists in Excel than how they should (and shouldn’t) be designed to be able to do things with them. There are certain basic rules to laying out a list that need to be followed in order for things like Sorting, Filtering, Subtotals and PivotTables to work properly. These include: Rule 1: The top row (and only one row) contains headers. OK, a list doesn’t have to have headers, but if it doesn’t, how is anyone going to know which is the latitude column and which is the longitude column? (What do you mean you don’t store GPS coordinates in your lists?!) Rule 2: Each of the following rows is an item (part, product, person, etc.) of the list. Only one row per item. Rule 3: Each column should be a distinct category of data (first name, last name, ID number, unit price, etc.) Rule 4: No other data should be immediately adjacent to the list (the list should be an ‘island’ of data). So, no merged cells or blank rows or blank columns should be present in the list. Another major consideration is validation. When data is entered, is it:

1. Valid – should the data meet certain criteria?
2. Consistent – is the data entered the same as existing data?

Data Validation (covered in the New Horizons Excel 2010/2013 Level 3 course) can help to ensure that the data entered meets the required criteria. Now, so far I have only mentioned lists, but the title of this post refers to tables. So what’s a table? In a nutshell, a table is a list, on steroids. Once you convert a list into a table, extra functionality is added that make a table better than a mere list. To convert a list into a table:

1. Click inside the list (anywhere)
2. Choose Insert tab , Table OR Home tab, Format as table (or press Ctrl +T on the keyboard)
3. A dialog box appears confirming the range and whether you have headers
4. Click OK

You now have a table. You also have a new tab on the ribbon under the heading Table Tools called Design. On this tab are basic commands for changing the appearance of the table as well as more advanced options for exporting the table’s data to SharePoint lists and Visio PivotDiagrams. One of the best features of tables, however, is the fact that as you add more data to the table, the table automatically grows. One benefit of this is with PivotTables. If a PivotTable is based on a table, it will automatically include new data upon being refreshed! Another good feature is the ability to create calculated columns that automatically copy down the entire length of the table with the same formula thus saving time and making the file size less. The New Horizons Excel 2010 and 2013 Level 2 courses cover lists, tables and PivotTables in depth to enable you to take full advantage of using tables in Excel.