Beyond Excel – SharePoint

Analysts have been using spreadsheet for a long time; spreadsheet is getting more powerful in every upgrade but our demand is even higher. Excel 2013, another major upgrade is around the corner; what is your Excel wish list? Recently, I discussed with a group of financial analysts about what they want to see in Excel 2013. They are power Excel users and want Excel to do more for them. It is not surprised that they put together a long list but I am only going to summarize the most popular items.

• Data analysis
• Data visualization
• Data storage / integrity
• Data extraction
• Security
• Collaboration
• Programming
• Control

Excel 2013 customer preview is currently available for download. As usual, it comes with a lot of enhancements. However, Excel 2013 does not address all our needs and our offices may not upgrade to Excel 2013 for another few years. So meanwhile, what can we do to address our needs? How can we effectively improve our efficiency and work smarter? Well, your organization may already have a solution, i.e. SharePoint.

I asked my colleagues if they ever used SharePoint; apparently, most of them have used it but mainly for documents storage. SharePoint is an excellent content management system so it is great idea to use it as an electronic library. However, it is more than just an expensive electronic library; it has many great features for data engineer to transform data into insights. Let me show you some useful SharePoint services.

Version Control

Figure 1 Version Control (from microsoft.com)

Version control allows us to build a version history and keep all previous versions with version information, e.g. when an Excel workbook was created, changed and even comments to describe the changes. It is handy to have access to all previous versions; we can view or compare previous version or even restore to any previous version when we hit a dead end and like to drop all changes. SharePoint automatically maintain all versions and there is always only one file to handle.

Excel Services

 

Figure 2 Excel Services (from microsoft.com)

Excel Services extends Excel from desktop into server infrastructure. It manages security and does all calculations on the servers. Once we upload the workbook to SharePoint, anyone with the required permission can access the whole workbook or pieces from the browser. We still have to use Excel to create workbook but SharePoint allows us to distribute and share the workbook in a more control and secured environment.

PerformancePoint Services

 
 

 

Figure 3 PerformancePoint Services (from microsoft.com)

 PerformancePoint Services allows us to create dynamic, powerful and reusable dashboard; we can create sophisticated KPIs and scorecards that have drill down and up capabilities. One of the handy analytical tools that I like to use is Decomposition Tree; we can easily visualize the root causes of a business problem.

 

Workflows

 
 

Figure 5 Workflows (from microsoft.com)

SharePoint provides a set of pre-configured workflows that automate a set of common business processes in a production environment, e.g. collecting signatures, feedback, or approvals. We can also build our own customized workflows that tailor made for our specific business processes, e.g. workflows that automatically collect business planning workbooks from each business unit, approve them and consolidate into a single workbook. All these business processes can be done with minimum coding in SharePoint.

Conclusion

SharePoint has many powerful features. It allows us to work together on a secured, automated collaboration platform and it grants us critical business insights via interactive dashboards and scorecards. There are many other SharePoint features that I haven’t covered in this article, e.g. Access Services, Visio Services, PowerPivot for SharePoint, Power View…etc.

I often say Excel is a very handy Swiss army knife because it can handle many analytical tasks. SharePoint moves Excel to another level, an enterprise level that often requires tighter control, better collaboration and higher efficiency. Talk to your IT department; it may already be installed and all you have to do is just use it!