O365: SharePoint List v Spreadsheet - which is best?
See also - Blogs on Library, List, Linking, Teams, Invoicing, Workflow and Bulk Invoicing |
| The image shows the same data displayed in different applications. On the left is the information imported into a SharePoint List and on the right is a table representation in Excel |
What's The Difference?
Let's assume you are running Office 365 and you may even have Teams launched. You need to provide your users with access to a table of data, the sort of thing that has traditionally been circulated in spreadsheet format.
Only now, you have SharePoint Lists as well.
So, which is the best way to store, maintain and distribute the data?
In both apps you can sort and filter the rows and columns, you can add new columns that read original columns and perform arithmetic, mathematical, statistical and text manipulation on the content of the original.
In the SharePoint List linking to other lists and libraries is easier and in Excel sharing by copying the entire list is easier.
I had a recent experience with a client who deployed a 33,000 row read only Excel Spreadsheet in Teams for operatives to reference data. Users launched the file in Teams and the search function in Excel worked seamlessly. But, the same could be said of a SharePoint List.
Data Security
In most cases the choice will be one of preference and that will be influenced by the intended use to which the data will be put in the future.
In the case of the 33,000 row spreadsheet described above, this was deployed as read only and changes to the data would be performed and validated by a small team in order to ensure that changes were always accurate. Once changes were made the entire spreadsheet would need to be replaced in the Team file store to make the changes available to all users.
If the data had been contained in a SharePoint List the users who were able to edit the list could be locked down to the same small team, but the changes would become available immediately.
Data Import/Export
There are three ways to get data into either Excel or SharePoint Lists, these are manual entry, bulk entry (usually copy/paste), or programmed import. These are largely the same for each application.
Export from SharePoint Lists to Excel is by the single click of a button and the resultant spreadsheet can be shared by email or archived in just the same way as a data list stored originally in Excel.
Data Manipulation
Formula in Excel and SharePoint lists are very similar -
 |
| In Excel The formula returns exactly the same result in Column1 as |
a similar formula does in SharePoint ListsKeyword SearchHere's the first place that SharePoint Lists scores significantly over Excel. Everything stored in a SharePoint List is crawled and indexed and becomes available for the SharePoint search engines deployed in Teams, Office 365 and SharePoint. A SharePoint search for keywords where the data is stored in a spreadsheet would return the file names and locations of the Excel files. To search in Excel, you would need to open the spreadsheet, invoke the search function and enter the search string. The search would be limited to the worksheet or workbook.  For data stored in a SharePoint list, just enter the search string in the search box in Teams or SharePoint and it will find the data you are looking for.  |
if there are multiple entries which match the search the data will be presented as a filtered result..
Let the Machine do the Work
The powerful search functionality in Office 365 is one of the most valuable facets of the platform. This is especially the case if you, or your operatives, have a business need to locate data urgently or at speed SharePoint search is effective across any data stored in the Office 365 cloud.
Comments