Simply put, and contrary to popular belief, Excel is not designed for storing tables of data!
Excel is a spreadsheeting tool, intended for data analysis.
Do you know what IS designed for storing tables of data?
And SharePoint lists! Which are essentially database tables anyway.
Rather than store tables of data within Microsoft Excel workbooks, which you then store in your SharePoint document libraries, you may want to consider storing the data within a SharePoint list instead…
You may need to utilise Excel-specific functionality such as pivot tables, complex functions, and cross-worksheet calculations – if that’s the case, we can still use Excel to analyse data which we store within a SharePoint list… more on that later.
So let’s have a look at some of the key benefits of storing your data in a SharePoint list…
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 resent 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 formulas.
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!
The process described above – and much, much more! – is covered in our SharePoint 2013 Power End User course. Full details can be found on the 3grow website