
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:- Valid – should the data meet certain criteria?
- Consistent – is the data entered the same as existing data?
- Click inside the list (anywhere)
- Choose Insert tab , Table OR Home tab, Format as table (or press Ctrl +T on the keyboard)
- A dialog box appears confirming the range and whether you have headers
- Click OK
How do your Excel skills stack up?
Test NowNext up:
- Cast out that colourcast in Photoshop
- Power View just got better!
- How to do an IF statement in Excel
- PowerShell’s Remote Control in a Nutshell
- Master the tricks behind numbered lists in Microsoft Word
- The challenges with selling human services
(Part 1) - Flatten Me! I want to be printed!
- Continuous Integration with Visual Studio
- Earned Value Analysis with Microsoft Project
- Owning the runway with BI Semantic Model
Previously
- Work offline with SharePoint Workspace 2010
- Happy New Year!
- The truth about Santa Claus
- High Availability in Lync Server 2013
- The impact of discounting and why you should not be offering it
- PowerPivot’s CALCULATE function to the rescue
- Manipulate dates with custom number formats in Excel 2010
- The Pros and Cons of SSAS Tabular Models in SQL Server 2012
- Customising Layouts in SharePoint Designer 2010
- Deploying Web and Service Applications to Windows Azure