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

Microsoft Teams

Microsoft Teams – Not only but also... What is Teams? Chat, Meetings, Calling, Collaboration | Microsoft Teams That’s the legend that Microsoft uses to head their Google listing. And it does those things really well. But -  There’s more . Teams also delivers a gateway to the fabulous world of SharePoint file storage, SharePoint and Office 365 index and search and Office 365 file security. All without the need for technical knowledge or extensive configuration. All well and good... What exactly does that mean? When you create a new team, simply click the ‘Join or Create a Team’ box at the lower left of the teams window, you launch a new team that has all the necessary attributes. This includes a mailbox, chat, call and video meeting area and a SharePoint file space with a basic library. Move shared documents, of any type, into the library and they are shared with other team members (the share facility is configurable to exclude individuals in the team who do not have sufficient righ...
Post a Comment
Read more

O365: Getting Really Useful Data into SharePoint Libraries

O365: Getting Really Useful Data into SharePoint Libraries See also - Blogs on  Library ,  List ,  Linking ,  Teams ,   Invoicing ,  Workflow ,   Bulk Invoicing  and  Spreadsheet Data A really useful SharePoint Library structure will give you something like this -  Just typing the words 'Credit Card' into the SharePoint search box returns a list of items relating to credit card across the entire Office 365 site. Security Don't worry about users accessing documents that are restricted. If you create or place a document into a Teams Document Library, it will be available to members of that Team by default. However, each document can be locked down to no access, view only or edit. You can also stop sharing any document, or alternatively share only with the members of the team you select. Users with whom the document has not been shared with will not be able to access it. Being More Specific   That's fine if you know which Credit C...
Post a Comment
Read more

O365: Create a SharePoint-On-Line Library

O365 is a tremendous resource. It can serve a business better when SharePoint on-line libraries are used as a repository for files and documents on which collaboration is required.  Instead of circulating documents for input by different users, simply save them to a SharePoint library where selected personnel can access them for update. Creating a library is a simple process which needs no technical expertise greater than the ability to use a computer. For this exercise we will create and start to populate a library for storing invoice documents.  Select the Site  An O365 domain can contain many sites. Libraries and Lists within a site can be made to interact with one another so data can be readily shared across the site and recycled. For example, if we choose to create a Customer List within a site and we populate the list with customer names and addresses we can allow other libraries and lists within the site to access and use the name and address data. So, the first jo...
Post a Comment
Read more