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

Knowledge - The Ultimate Resource

Knowledge – the ultimate resource? I can’t argue with that. As a writer I’m in the business of providing knowledgebase elements. I take my own experience and that of others, I then turn it into some form of written document that may be understood and acted upon by the intended audience. Quote from Wikipedia - Human-readable knowledge bases are designed to allow people to retrieve and use the knowledge they contain, primarily for training purposes. They are commonly used to capture explicit knowledge of an organization, including troubleshooting, articles, white papers, user manuals and others. The primary benefit of such a knowledge base is to provide a means to discover solutions to problems that have known solutions which can be re-applied by others, less experienced in the problem area. · The most important aspect of a knowledge base is the quality of information it contains. The best knowledge bases have carefully written articles that are kept up to date, an excellent information ...
Post a Comment
Read more