Anytime I have a meeting with a client to discuss ways I can help improve business processes, one thing is always mentioned as a major headache.
“We’re storing important data in spreadsheets”.
Every industry. Every Department. The same problem.
What has usually happened is this: you (correctly) notice that something isn’t being tracked (project tasks, hardware assets, new starters, etc.) and decide to start tracking it. So, you fire up Excel and thus begins your slow descent into madness. At first, the spreadsheet will be a big help. Stakeholders get emailed the file, get a view of the data they need and update it as required… and then it gets emailed to more people… and more people update it… and send it on to more people. Before you know it, there are 19 versions of the spreadsheet, they all have conflicting data, different columns added and one even has a formula that turns every fifth cell orange for no apparent reason.
We’re now at a point where not tracking this information was better. It will take time and effort for someone to organise this data into something usable. And seeing as you created the file, guess who gets to try and fix it?
What’s the alternative?
Well, I gave it away in the title, but SharePoint lists are usually a better alternative to an excel spreadsheet. Here’s a few reasons why:
Up to date data
If an excel file has been mailed around, it quickly becomes hard to tell if you have the most up to date version. With SharePoint, the data stored on the list at the time of reading is the most up to date version, avoiding any confusion.
Access Control
Rather than having to email and re-email a spreadsheet each time a new person needs to view it, SharePoint allows you to grant and (sometimes more importantly) revoke permissions to the file. You can also set different levels of access to prevent who can make changes to the list.
Simultaneous Editing
An excel file that is stored one a network drive or in some document libraries will be locked down to a single user while editing, leading to people creating alternate versions with different names. With a SharePoint list we have version control that allows for simultaneous editing and can track who has made changes and what changes they have made.
Out of the box templates
SharePoint has a large amount of different types of list templates as standard, which will suit different scenarios with little to no customisation required…
Future Customisation
but if you do feel the need to make changes, the possibilities are almost endless. You can change any fields in the list, customise views, add workflows or even use the list as the basis of SharePoint app to be reused or even sold.
So when should I use Excel?
In the interest of not seeming like a complete SharePoint shill, I have some very specific (or very obvious) scenarios to stick with your much loved spreadsheet creator:
This spreadsheet is only for you
Pretty straightforward. If you are the only person who will need to view or update this list, feel free to keep it in a spreadsheet.
You are using complicated formula, Pivot Tables and Power View
This is what Excel does best. Recreating this functionality isn’t really doable in a SharePoint List, so keeping this in Excel and using web parts to display the data is the way to go.
But do I really need it?
You may be saying that a lot of these points can be applied to storing the Excel spreadsheet in a SharePoint document library or that you don’t need any of these features. This is true, maybe Excel will be all you need a lot of the time. But for the sake of your own sanity, just keep these points in mind before you decide to create your next spreadsheet.
Author: Ian Jones, Software Developer, Aspira. www.aspira.ie