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

The New Computer Age: Infrastructure and the cloud

Most business managers don’t know it but this is very close to the truth. IT teams across the world live in day to day hope that their servers and networks hold together and pray that the disaster recovery schemes will be adequate in the event of catastrophe. IT people are highly professional and, of course, their server networks do deliver. Massive and expensive failure is surprisingly rare, provided that the IT department gets what the IT department needs in terms of budget. But, where budget is restricted, where the IT manager fails to make the business case for upgrade, then performance and security start to become threatened. In addition, a complex IT infrastructure brings with it the penalties of restrictive practise where a department head can’t get the software, services or technology required for a lean department because what is needed will not fit with the current IT infrastructure. So, what’s the answer? Sole Trader - Read more SME - Read more En...
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