The power of SharePoint lists: 5 reasons why they are better than spreadsheets

A SharePoint list is one of the most powerful and flexible features of SharePoint, but its potential is often overlooked. One of the reasons for this is that lists are often regarded as a feature that only IT professionals or those with sufficient technical knowledge are able to exploit, but actually, SharePoint lists are not that difficult for power users and citizen developers to utilise.

Another potential reason that lists get side-lined is because they have been around for so long and are not really the focus of attention. SharePoint lists are far from being shiny and new, but used in the right way, they can deliver great value and play a key role in business apps and solutions. Read more about SharePoint lists and the key benefits and features, in our article: SharePoint lists: The Beginner’s Guide.

SharePoint lists vs Excel sheets

A SharePoint list is simply a collection of data that has some kind of structure to it: it is essentially like a table, a spreadsheet or a simple database. It can include many different types of information including numbers, text and even images.

Within organisations, information that is regularly updated by a number of people and also acts as a wider reference point is often managed on an Excel spreadsheet. Typically, this might be a list of suppliers, a list of employees who are qualified to give first aid or a list of business publications that an organisation subscribes too. Excel is a great solution that we all rely on, but there are times when a SharePoint list provides far more value for storing, managing and visualising information.

Lets explore five reasons why SharePoint lists can work better than Excel spreadsheets.

1 Ensuring there is one source of truth

When a team or a number of different users enter data into a spreadsheet, there can often be issues with version control. Even when Excel online is being used, users are still liable to save local versions onto their own drives. Sometimes, to avoid these issues, one person ends up being responsible for entering data into the spreadsheet, with different people emailing that person with updated information. This long-winded process is inefficient and prone to errors.

A SharePoint list avoids many of these issues; it is online so there is only ever one version and therefore one source of truth. Multiple users can update information as required without the danger of creating multiple copies.

2 Maintaining control over certain areas

Excel spreadsheets can get complex, for example with formulae in particular cells, or with various different formatting. People also want to sort data and potentially manipulate it within the spreadsheet. With multiple users involved, it can be easy for an individual to inadvertently delete a cell or make unwanted changes, maybe even ruining the spreadsheet or altering information that might not be noticed before problems arise down the line.

Part of the problem is that it is impossible to assign permissions to different parts of a spreadsheet – people can either access everything or nothing. With a SharePoint list, you can designate permissions for each item in the list and protect any formulae or controlled lists, helping to ensure you do not lose valuable information or compromise the lists integrity.

3 Guiding users to enter the right data

With a SharePoint list, you can also nudge and guide users to provide the information you need by creating a form as a more user-friendly interface for data entry. Instead of adding information directly into cells on a spreadsheet, users can be presented with explanatory text, help options, drop down lists, validation criteria, custom error messages and so on.

While some of these features are available on a spreadsheet, there is no comparison when it comes to easily creating a form to help users and ensure you can control the data that is entered into the SharePoint list.  Here, values in drop down lists and validation criteria can be targeted to different AD groups and even reference other SharePoint lists, allowing you to manage sophisticated data collection processes online with a level of control that is largely lost when you use a spreadsheet.

4 Better data integrity

The combination of permissions, forms, targeting, and data validation which can be applied to different parts of a SharePoint list, along with the ability to collate one source of truth, means that, overall, you can manage data integrity better via a SharePoint list than an Excel spreadsheet. This is also supported by audit trails for each item in the list with an edit history for every single item in it, as well as related version history.

5 Integration with Microsoft 365

A SharePoint list also has the ability to easily integrate with the rest of the Microsoft 365 suite in ways that can deliver more value than Excel. For example, it is very easy to integrate a SharePoint list with Power BI to feed custom reporting, as well as trigger workflows defined within Power Automate based on values that are entered into the SharePoint list. It is also straightforward to present the data in visually appealing ways in your SharePoint intranet.

When you start to combine these elements, a SharePoint list can be at the centre of a business app or solution, where it provides an easy place to maintain dynamic information that in turns powers a data visualisation layer with Power BI, and a data processing layer with Power Automate. Find out more about integrating SharePoint lists with other components within the Microsoft 365 suite in our article: Should I synchronise SharePoint lists with SQL server database?

Use cases

There are numerous use cases for SharePoint lists, often involving the intranet. For example, a  SharePoint list is perfect for keeping an often-changing list of suppliers up to date, with different functions adding information, and the results available for everybody to view on the corporate intranet. Another key use case might be maintaining a central catalogue of apps used throughout the enterprise.

A potential more sophisticated use case that could be powered by a SharePoint list relates to managing the reopening of offices during the pandemic. Here, employers will need to keep a record of everybody who attends an office in person for the purposes of any track and trace processes in case anyone tests positive for coronavirus. In this circumstance, a SharePoint list can potentially provide:

• A place to manage a dynamic set of data with numerous people regularly adding information
• The potential to provide multiple views of the data – by location, division etc.
• The ability to provide overall reporting of working patterns through a Power BI dashboard
• The ability to have a check-in form with approval workflow so that employees require approval of plans to visit the office, providing a simple structure through which to manage strict limits on numbers
• The potential to extend this form to a PowerApp available on a mobile device
• Having one source of truth for all this critical information with audit trails etc.

Here, a fully working business solution is seen with a SharePoint list right at its  centre.

We love SharePoint lists!

OK, we know it sounds a bit geeky, but here at Content Formula, we really do love SharePoint lists, and you should too. They are simple to use and provide an excellent way to keep control over and present dynamic collections of information that are frequently updated by a wide group of people. They can also power various Microsoft 365-based apps and solutions. If you’d like to discuss how you can use SharePoint lists in your organisation, then get in touch!