Excel v. Custom Lists

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.

Permissions – What if you want some people to add to your list and others to simply be able to read the list. What if some people reading the list want to be able to synchronize a local copy? What if you want a select few people to be able to add values to the named range you’re using to drive your choices? You’re not going to get this to work in Excel. You can do all that in SharePoint, quick and easy like, and, if you absolutely positively have to have it in a spreadsheet, you can export the list to Excel. How cool is that?

3 thoughts on “Excel v. Custom Lists

  1. I think you may have missed one of the biggest advantages of lists over spreadsheets – many people can add/edit list items simultaneously. No worrying about someone having the excel file open for editing.

  2. I do end-user SharePoint training and I couldn't agree more – I "preach" the same argument in my classes. I, like you, love both tools – but stick by the axiom: use the right tool for the job! Thanks for the info.

Comments are closed.