Skip to main content

O365: SharePoint List v Spreadsheet - which is best?

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 Lists

Keyword Search

Here'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. 



We can show you how, or we can do it for you.




Labels:

Comments

Post a Comment

Popular posts from this blog

O365: SharePoint Searching in Office 365 and Teams

 O365: SharePoint Searching in Office 365 and Teams See also -  Other Office 365 Posts Earlier posts have discussed the huge advantages of centralising documents, files and communications in SharePoint and Office 365. One of the strongest functions in Office 365 is the ability to search all content so that locating data is easy. However, the search tools in Office 365 are not always obvious. Teams Search Teams is becoming really popular, not least due to the fact that it makes SharePoint very easy to deploy, use and control. At the top of each Teams General Tab is a search box This is multi function. Type / into the box and you will see a list of commands that will perform all sorts of Teams functions    The /Files option will return files you have recently accessed. Great, if the file or document you are looking for is actually in the recent file list. What if the information you require is in an older file, or in a different location? Location Search Also in Teams ...
Post a Comment
Read more

O365: Mobile Apps - the easy way

 O365: Mobile Apps - the easy way See also -  Other Office 365 Posts Anyone following these posts will now know how easy it is to create data stores in SharePoint for documents and files and for listing data, such as customer details. We've also looked at making this data easily findable, how to flag items for the attention of individuals and teams within the Office 365domain and moving work through the system in a controlled manner. We've also seen how we can access this content when away from the office via mobile apps for Teams, SharePoint and OneDrive. Occasionally the standard apps aren't really convenient and fail to lend themselves to mobile use. I'm thinking here of functions like stock lists, customer contact lists and the like, when the standard app delivers too much detail for a small screen, or where links and buttons are small and difficult to use. For these functions it would be better to deploy an app that is designed for fast, effective use on a mobile. ...
Post a Comment
Read more

O365: Getting the best out of SharePoint

O365: Getting the best out of SharePoint See also - Libraries , Lists , Linking List and Library Simplicity Anyone who has read my earlier posts on creating, populating and joining data containers like Libraries and Lists in Office 365 SharePoint will know how simple it is. Giving a little thought to what data or information you want to keep and what you will use it for in the future is the hard part. Once you have that, building the lists and libraries is as easy as creating a spreadsheet.  But, a SharePoint list or library is far more flexible and effective than a spreadsheet can ever be. Shadow IT This is the name given by IT departments to the practice of hiving off chunks of corporate data into silo data stores controlled and maintained by individuals. A mobile phone contacts list is a great example. The same data might be held in a dozen or more separate phones and the data might never be synchronised. Personal spreadsheet lists can fall into the same category. Data from any ...
Post a Comment
Read more