Every time someone comes to me with a system that they have built in Excel and asks me for help making it better, I feel like Steve McGarrett in his perennial battle with Wo Fat. Inevitably, that “system” is a list of stuff loosely arranged in row-column order. Also inevitably, when I suggest moving the system into SharePoint, I get major push-back. I have written about this before, in fact, Excel vs. Custom Lists is one of the top-5 most popular SharePoint Stories of all time. This time, I thought that I would share some of the questions and comments I normally receive, along with my answers:
“So, you really push SharePoint don’t you?” – No, I don’t “push” SharePoint, I recommend the best technology we have available to meet the business requirements. When those requirements include tracking a list of items that are characterized by custom but common attributes, I am prone to recommend building a Custom List – I’m just funny that way.
“I think these things should be stored in a database.” – I usually avoid saying “then why did you put them in Excel?” I also try to avoid going into the weeds to explain SharePoint from the ground up, and how it really is a fancy way of sticking stuff in SQL Server. I’ve learned that the way to win this battle is to ask “why?” Sometimes, people don’t know why they think stuff should be in a database. These are the same people who buy all sorts of storage systems for their garage because the garage in the picture looks so good. The people who do have a reason for wanting their list of items to be in a database usually want one of the following three things:
- Relationships – whether it’s one to many, or many to one, they want to relate a few things to one or more other things. This is a great reason to put stuff in a database. It’s also a great reason to put stuff in a series of connected SharePoint Custom Lists. Why do I often choose SharePoint instead of SQL Server?…I mean we have both. Well, SharePoint offers all the features we normally need to meet these requirements, programmers are usually not required and it’s easy to share the lists on a web page and out to a mobile device.
- Ease of use – When people say that they want their storage mechanism to be easy to use, they are almost always still talking about relationships. They want people to be able to pick from a list of standard terms, or they want to select a facility name and automatically find the policy in force and the underwriter for that policy. Perhaps you can appreciate how hard it is for me to not say “seriously, why did you put this in Excel?” I am a huge fan of vLookup(), but I’ll buy a beer for every Excel user at the bar who can tell me how it works. SharePoint, by contrast gives us multiple ways of adding Pick-lists and lookup columns and they are all pretty easy to build and dirt simple to maintain.
- Sorting and Filtering – I understand that Excel has the ability to sort and filter, but these functions in Excel involve hiding what you don’t want to see, particularly if you don’t want to see the same columns for every sorted and filtered set. Views in SharePoint are real views. Sorting and filtering in Excel is like putting virtual duct tape over certain rows and columns. But, if you want to make the point to an Excel fan-boy, build a view. Sit them down, and build a view right in front of them and dare them to do the same thing in Excel. Better yet, build three views in less than 5 minutes and sit there switching between them. Even better, put two Data View Web Parts on a page and use one to drive the sort and filter parameters of the other. Granted, that last one takes a bit of what some would call programming, but still…
“It’s easier to print from Excel” – OK, you may have me on this one, but that’s OK, I have a secret weapon – SQL Server Reporting Services. I know, there are ways to print from a Custom List, and there are List Print utilities. In fact, I think we own one of those utilities, but I like SSRS because my coworker can slap Excel upside the head with a report from that service.
When people push against moving to SharePoint from Excel, don’t argue the details, don’t try to convince them with logic and don’t make fun of them – bury them. Find every single thing they like about Excel and show them the superior thing in SharePoint. Trust me; it won’t be hard to do.