SharePoint Custom list vs. Excel

Quite often, I am asked by my clients and blog followers, whether or not it makes sense to transition information from Excel to SharePoint custom lists. As with many things in life, the answer is “It depends.” Let me list the advantages of both, which hopefully will help you make the right decision.

I assume you have an idea of what Excel is. For those of you who are millennials, Excel is this cool app, we, adults used before Facebook was invented. To better understand what the SharePoint list is, read this article. To learn how to build a custom list yourself, check out this recent post. OK, we should now be in good shape to proceed. Let’s start!

Advantages of SharePoint Custom Lists

Ability to create many different views

The major advantage of SharePoint lists is that you can create many different views, where you can sort, filter or group by certain metadata fields. The views can be created by both Admin and End User. I explained and provided examples of some of the cool views you can create in this post.

Nice Filter Option

Custom List metadata allows for some amazing filtering capabilities. Searching for info in a custom list becomes almost identical to shopping online – just click on the filters pane, and check off the fields you are searching for This post explains your options. By the way, the filter is available on both document libraries and custom lists.

Ability to set unique permissions

Another thing you can do, though might be a bit painstaking, is set unique permissions on each row in the SharePoint list. In Excel, if you can open it up, by default, a user has access to all records (rows) in a table. With SharePoint Custom List, you can set unique permissions for each row, hiding it from the view. I documented how to do it in this post.

Ability to set Item-Level Permissions for the list

Similar to the above, you can set item-level permissions for the list, meaning you can prevent those who did not create an entry (row) from seeing or editing it. Might be useful when you use Custom List functionality for something like a Helpdesk ticketing system or Vacation Request form. If you want to learn more about item-level permissions, check out this article.

Ability to integrate with Flow and PowerApps

Where the modern SharePoint Custom list stands out – is its ability to integrate with other Office 365 tools, like Microsoft Flow and PowerApps. With those, you can convert your static SharePoint list into a business process workhorse or a cool mobile app.

Ability to export information to Excel

If at the end of the day, you need to have the SharePoint data to be available in Excel, you can easily export it using the Out of the Box option. I also wrote a separate post on the same topic before.

Versioning

Another differentiating factor, in favor of custom lists, is versioning. Any change you make to the row is recorded, and you can always see what the change is,  who did, and when. You can also restore a previous version of the record too!!! How cool is that? You can get familiar with versioning by reading this post. Please note, that in SharePoint Online, versioning is automatically enabled only on document libraries. On SharePoint custom lists, you have to enable it manually.

Advantages of Excel

Ability to manage complicated formulas

SharePoint custom list is not a replacement for Excel and will never be. Excel has many great advanced features, like formulas. While you can do simple calculations in a SharePoint list, nothing matches the sheer power of Excel formulas.

Ability to color code things

Simple color-coding or color-coding based on certain conditions is a piece of cake in Excel.

In the SharePoint list, you have to use advanced coding to achieve the same (as of the writing of this post).  Microsoft recently announced basic color code capabilities on a SharePoint list as well that will be available out of the box, though I do not expect them to be as robust as those in Excel.

To learn more about column formatting in SharePoint, read this.

Ability to utilize Pivot Tables/Pivot Charts

Of course, if you need to use your data to construct Pivot Tables and Pivot Charts, Excel is your only choice! If you have your data in the SharePoint list, you would need to use Power BI to achieve the same.

Ability to easily tackle large amounts of data

Just like with document libraries, SharePoint Custom lists suffer from information overload (known by its official name as the 5,000 item view limit issue). You can have more than 5,000 rows in a SharePoint list, but to display the data, you would need to prep your list with proper column indexing and filtered views before you create or load the data. You have none of this to worry about with Excel.

Click here to read more about the 5,000 view item limit. This one, specifically, talks about SharePoint lists.