In part I, we reviewed the benefits of SharePoint lists. Let’s now see what Excel has to offer.

1/ Calculations, data analysis and charting
We must not forget that Excel’s primary purpose is to maintain… spreadsheets! Excel will allow you to use advanced formulas, perform calculations across rows, draw charts, use parameters for what if analyses, and generate PivotTables/PivotCharts.
This is not available OOTB in SharePoint lists. Note that SharePoint 2003 offered the OWC Web Parts, but they are discontinued.

2/ Standalone file, offline access
As the content lives in an Excel file, it can easily be taken offline, on your desktop or for sharing with users who don’t have SharePoint access. For example, simply checking out the Excel file will allow you to get a local copy on your desktop.
SharePoint lists only offer this through an external application, for example Outlook or… Excel!

3/ Excel Services (if deployed in your environment…)
Excel Services leverages the power of Excel on SharePoint, and allows users to access the content of a spreadsheet through a Web interface. The benefits: performance (no need to open Excel) and cross-platform (makes the spreadsheets accessible to people who don’t have Excel).
It also brings an additional security level, making the difference between viewer (can only see what is displayed on the Web page) and reader (has read access to the whole spreadsheet).

A couple comments:
– the choice Excel vs. SharePoint list is not exclusive, there are bridges between the two options. Some Excel spreadsheets can be imported to a SharePoint list, and most SharePoint lists have an “Export to Excel” feature. There is even a free add-in for Excel-SharePoint synchronization:
http://msdn.microsoft.com/en-us/library/bb462636(office.11).aspx
– It’s not all black or white. For example SharePoint also has some charting capabilities: you can take advantage of free Flash or Silverlight tools, or apply color coding to your lists.

To finish, let’s put this into practice with a few examples.

Action items for a core team
This is a perfect scenario for SharePoint. A project team can create a team site, and manage action items in a tasks list or an issues list. The list content will be displayed through multiple views:
– by user (“My assignments”)
– by due date and/or by priority
– by category
– etc.
SharePoint will notify team members of task assignments. For each item, SharePoint will keep track of the history (for example the resolution steps, or successive comments while the task is in progress).
Workflows can be used to send reminders or include an approval process.
Action items can be connected to other lists in the team site, for example an issues list or weekly meeting minutes.

Analysis of customer calls
In this scenario, a product team analyses customer feedback collected from a call center.
Though we are in a collaborative mode – the product team will discuss the results and the steps to address issues – the analysis itself is not. It is usually prepared by an analyst before being shared with the community.
In this case, dropping the calls in a SharePoint list doesn’t really help. Excel on the contrary will offer convenient PivotTables and PivotCharts with drill-down capabilities.

A team roster
A SharePoint contacts list will be more interactive than an Excel list. It can integrate with communication tools, like Outlook, or an IM tool (including presence information). Being Web based, it can link to other Web based applications, like a company directory or a social network, to display additional information about each individual.

Working with vendors
The scenario: after tests or a quality control on a delivery, the team identifies a list of issues and works with the vendors to resolve them.
Though we are in a collaborative mode, allowing external partners to access the company’s SharePoint creates additional constraints.
For long term relationships, it can be worth the trouble, as the extended team gets all the benefits we saw in the first example.
For a one time project, exchanging Excel files through e-mail will be more straightforward. But then we get the usual pitfall: multiple versions of the spreadsheet, a source of confusion and inconsistencies.

This second part concludes the technical review. We have not tackled the human factors yet, and this will be the object of part III.