I thought I had a great way to distinguish SharePoint Custom Lists from Excel in our upcoming training session on Custom Lists. My tag line was going to be “Using Excel to track a list of items instead of SharePoint is like using a garbage bag instead of luggage”. I tried that on my coworker John, and he said: “You know; using garbage bags might not be a bad idea.” Thanks to today’s airlines, he might be right, sigh…
Why am I attacking Excel? I’m not, spreadsheets are the definitive killer app for personal computers and Excel is a great spreadsheet. It’s just that using Excel to keep a list is a bad idea. I’m not just saying Excel is bad for lists because I’m a fan of SharePoint; it’s always been a bad idea. As long as Excel has been around, Access has been on the same menu (oh, right, unless you had Office Standard), and lists belong in a database not a spreadsheet. Why? I’m glad you asked:
Row-Column Muscle – Spreadsheets shine in their ability to manipulate data in row-column relationships. From the simplest sum() to several layers of nested if()’s, it’s amazing what you can actually pull off in a Spreadsheet. But, if you’re not using that strength, you’re subjecting yourself to a series of weaknesses just to have the convenience of typing in a flexible grid. What might these weaknesses be? Let’s look at a few:
Views – Well, in a spreadsheet, there aren’t any. Oh sure, you can hide columns and you can hide rows and you can sort, filter and create Pivot Tables based on “list” content. But seriously, if you’re not also using that spreadsheet to calculate some stuff, it isn’t worth it, even if you’re among the few who know how to create a Pivot Table. SharePoint views, on the other hand are easy to establish, easy to manipulate and easy for the list user to select.
Validation – Let’s assume you do know how to create a Pivot Table and you want to restrict your view based on date. Of course you can enter and format dates in Excel, but I’ve also seen people enter things like “11/28”, “Yesterday”, “Thanksgiving” and “November” into cells that are supposed to hold dates. You can setup validation, but just like Pivot Tables, most people don’t understand validation. The myriad column types in a SharePoint List let you get the right type of data into each column and they’re intuitive.
Options – What if you want to let people choose among several options for a text field? If you want to sort or filter, you need those choices to be identical – you can’t have “Car”, “Auto” and “Automobile” as answers to “What is your preferred mode of transportation”. You can put “Car”, “Train”, “Plane” into a named range, reference that range in a validation drop-down in Excel (if you understand validation) but there are still limitations. For example: you can’t default one choice over another and you can’t allow for multiple choices unless you enter all the permutations into your named range. SharePoint choice columns solve all these problems and add the ability to reference other lists as input.
Workflow – SharePoint workflows add a measure of utility to a Custom List, the value of which is hard to over-estimate. We are actually in the process of replacing small application systems with the combination of Custom Lists and Workflows. I suppose that if you had a series of related spreadsheets, you could mimic some of the functions of workflows but given the other limitations we talked about here, I wouldn’t trust the results.