Benefits of SharePoint over Excel for Program Management
The method in which a project is managed has a significant impact on the effort required to track and report on the work.
On a recent project that required input from hundreds of employees throughout the client’s organization (both business and IT), we looked at various ways for collecting and storing information. The project consisted of creating a list of artifacts that needed to be modified and a place to store those artifacts centrally once produced. Our options:
A similar program had been managed several years ago using Excel. The program management team distributed a template to the business and IT owners for input. The program managers collected and consolidate the data into a master spreadsheet which was then available on a shared drive in read-only format.
Edits and updates went through a single owner who was responsible for maintaining the master file. The physical artifacts were stored in a shared folder with all files using a consistent naming convention and a UniqueID.
- Control of data entry
- Availability of ‘master’ file and artifacts to users
- Excel was widely used and familiar to all users
For this effort, we proposed using a SharePoint List. A List in SharePoint includes rows and columns very similar to Excel, but has many additional benefits. It is constantly accessible to entitled users who can add and modify data and create custom views. Data entry is tracked and versioned so that users can see specific details of all changes and re-load prior versions.
SharePoint also allows documents to be attached directly to their respective rows. This easily enabled sortable metadata to be assigned. Users had the ability to upload documents of various types (ex. doc, .xls, pdf, etc…) and then filter their documents. For example, show all documents where ‘Lazy Man’ is the IT Owner.
- Users can enter and modify data directly to the list
- Data is always up-to-date and multiple users can work on the list at the same time.
- Changes are tracked in ‘Version History’
- Custom views can be created (including custom columns) for each user
- Data can be exported to excel for additional reporting needs
- Artifacts and meta-data on artifacts are stored together
A happy ending for all with SharePoint.
Users commented on how much more efficient the system was to enter and retrieve data. Information was consistent, centralized, yet customizable.
This resolved several problems that users mentioned had occurred on the prior project:
- Delays to Updates – Being reliant on a single person to update the master file created delays (often, and for multiple days) and users had a hard time trusting that the version they were using contained all up-to-date data.
- Template Issues – As the template changed (ex. additional columns were added) users still working on the older version provided data in a format that needed to be modified prior to importation into the master file. There were also instances where users removed data validation fields and where users did not provide ‘required’ data.
- Inconsistent Data – Artifacts and the meta-data about those artifacts were stored in different locations. To know the owner of a certain artifact one would need to review the Excel list. To resolve this, meta-data was added to the artifacts (as SharePoint was used to store the files), but that data quickly did not match what was stored in Excel. Keeping these two lists in sync required significant work and was often ignored for weeks at a time, causing issues with reporting.
Significant time and effort was saved this time by implementing a solution that better suited the needs of the project than the previous iteration of Excel.