SharePoint List vs. Excel Workbook

One of the very common questions users ask themselves when moving from a shared folder structure to a SharePoint environment is: “Should I keep my data in an Excel workbook in a library, or should I convert it to a SharePoint list…?”. Well, here’s my view on this subject…

If the Excel workbook contains complex functions, cross-sheet formulae or pivot tables, it is probably more appropriate to keep the data within the Excel file. However, if the Excel workbook contains more simple table-style data, it is likely that you will find SharePoint an easier tool to manage this information. Below is a list or reasons why this may benefit your organisation, therefore improving efficiency and productivity:

• Only one user at a time is able to modify the contents of each worksheet within an Excel workbook at any one time. Therefore, if you’re storing a list of products within this workbook, while Fred is editing the details of an existing product, Janet is unable to edit another product’s details, or add a new product. Once a table is imported from an Excel workbook into SharePoint as a list, each product becomes an item in its own right, therefore items can be modified independently of each other.
• SharePoint lists have the benefit of views. Although the contents of an Excel worksheet can be grouped, sorted and filtered, the interface is nowhere near as dynamic or intuitive as SharePoint lists.
• You can turn on version control within a library, but this will only tell you that the Excel workbook has been updated and by whom, but what it won’t tell you is what exactly has been updated. If the data is stored within a list with version control turned on, you can view the version history of each individual item, including who modified it, when it was modified, and which properties/metadata/columns were modified.
• It is far easier to present the contents of a SharePoint list on a page within your site – by simply adding the web part for that list to the relevant page – than trying to present the contents of an Excel worksheet. In situations where it is more appropriate to keep the data within Excel, you may consider using Excel Services to present the contents of an Excel workbook within a web part on a SharePoint page. There are many reasons why this is useful, such as allowing a user to dynamically modify the way the data is presented within a pivot table.
• Accessing the data within an Excel workbook simply takes longer. For example, to view the information you would have to navigate to the site, then the navigate to the relevant document library, open the required Excel workbook (this can sometimes be a slow process!), then navigate to the tab containing the data. To view the equivalent data within a SharePoint list, you simply navigate to the site, then to the relevant list, and voila!

The point of this article is not to say that Excel is a redundant tool now that you can use SharePoint to store the data. Excel certainly still has its uses – SharePoint is a tool for viewing, adding and editing the data in a convenient and slick shared location, whereas Excel is a great data analysis tool which allows you to produce rich charts, pivot tables and complex formulae.

If you’re really feeling adventurous, you could try storing the data within SharePoint, exporting it to an Excel workbook which is then in turn stored within a document library within your SharePoint site! This allows the data to be managed and maintained within the SharePoint list, but you still have the ability to perform rich analysis – as mentioned above – on the up-to-date copy of the data which is sitting within the Excel workbook… then you get the best of both worlds!

Russ Norton

3grow SharePoint Training, Australia

About Russ Norton

Russ Norton has written 2 post in this blog.

I'm General Manager and SharePoint trainer for 3grow Australia Pty Ltd, based in Melbourne. I've been teaching since 2003, and specialising in SharePoint since 2007. At 3grow we take pride in delivering quality SharePoint, Office 365 and Nintex training. Our courses are designed to meet the needs of all involved in the full SharePoint spectrum: administrator, end user executive, developer, designer.

• View all posts by Russ Norton →
• Blog
• Twitter
• Facebook