Office Integration with SharePoint

Many companies are still using older versions of Office. They often ask me why they should move to Office 2013. There are many new features in Office 2013. However, among the most important (and often overlooked) benefits is integration with Sharepoint. Therefore, this article is dedicated to features related to SharePoint, Office 365 and OneDrive usage.

Related Articles

Read the following articles for understanding the problem and solutions related to effective sharing and collaboration.

Your biggest enemy: CCs with attachments
Sharing = 1 copy in 1 place (SharePoint)
What exactly is OneDrive?
4 methods of using OneDrive for Business effectively

The need for Integration with Sharepoint

SharePoint has been there since many years. In spite of that, most users do NOT use it for storing files by default. Most users end up storing one copy on local PC and upload a COPY to SharePoint (or OneDrive). This defeats the purpose of sharing.

The primary problem is that users find it cumbersome to use SharePoint. They are very reluctant to move away from their comfort zone of File Menu and go to some web site to store files.

Microsoft understood the problem and solved it in an elegant manner in Office 2013.
Let us explore what has changed and why is it useful to us.

Integration is the key

Apart from Windows, Office has been the most longstanding product of Microsoft. All other products were created AFTER Office. Therefore, Microsoft has systematically and proactively made sure that all products integrate with Office.

Office creates documents and SharePoint manages the interaction with multiple users and teams. Therefore, integration between them is very tight. But that integration was not visible from an end user perspective. For many activities you had to shuttle between an Office tool and SharePoint web page.

Now that has changed. The File menu – (technically called BackStage) is now fully capable of managing SharePoint, OneDrive and related components in a surprisingly seamless manner. Let us explore the benefits one by one.

Registering SharePoint and Office 365 sites

ALL Office products understand Office 365 out of the box. No special effort is needed. Immediately after installing Office tools, you can login using the File – Account option and add one or more Office 365 sites, OneDrive for Business and OneDrive (earlier known as SkyDrive). Also notice that social media applications also integrate easily into the same engine.

Saving files directly to OneDrive or SharePoint

Once registered the Team sites and OneDrive for Business automatically appears in the File Save (Save As) menus of ALL Office applications. This is very convenient and powerful.

Here is a nice psychological boost for users. They are used to going to file – save – my documents and then think further. Now even before Computer (local storage), the OneDrive and Team sites are appearing – making it extremely compelling to save the files directly to the server or Office 365.

Of course, in order to click on those cloud locations, the users must be aware of the benefits of storing things on SharePoint compared to storing those on local PC. If this education has not been given, nothing will work. Most users will continue to store locally and worse still – some may have two copies – one local and one server based.

Notice the Other Web Locations which shows other SharePoint sites used recently. Each folder can be pinned once so that it automatically appears in the list next time onwards.

Adding Places

Specific external places like Document Libraries or other shared storage locations can be added using this option.

Integration with consumer version of OneDrive ensures that personal or externally shared documents are also managed exactly in the same way – without creating a local and a cloud copy.

Apart from this there are many features of SharePoint exposed through File Menu itself making it extremely easy to manage the sharing lifecycle.

Sharing

When it comes to various options to share the document, SharePoint and Office 2013 really shine.

Invite people to view / edit documents

Earlier we had to go and change the file level permissions using a non-user-friendly Permissions page in SharePoint. Now with SharePoint 2013, that dialog has become much simpler and easier to use. Office brings that dialog right into the Share option of File menu.

This eliminates another reason why people got irritated with SharePoint usage – frequent trips to web page of the SharePoint location.

This dialog allows you to add multiple people – and in case of Office 365 – even external people. Choosing Edit or View options is a good way to feel confident that you are controlling the access even though the file may have been stored on a remote server.

Often we forget which document is shared with whom. This dialog shows that information as well – so that sharing is not a confusion based decision – it is an informed decision.

Sharing Link

Consumer versions of OneDrive, Google Drive, DropBox, etc. have made people familiar with the concept of a View link and an Edit link. This feature is also available right within File Menu in Office 2013.

Lync Integration

In all these dialogs, wherever there is a person context, hovering the mouse there will open the Lync interaction dialog and show the Lync status as well. This opens up new ways of working together, clarifying things quickly, eliminating long mail trails with a quick instant chat or application sharing session.

Browser View Options

Many people equate SharePoint with other vanilla solutions for cloud based storage. Of course that is not a fair comparison. SharePoint offers many brilliant features which are only now being noticed and being used – after a decade of underutilization. One such feature is to distribute and publish Excel based reports using SharePoint.

It does not just store the Excel files, but also renders them on a pure browser based view. This way, the person viewing the report does not even need to have Excel installed on their device. It works with all browsers and all devices – making it a very compelling feature for delivering reports without sending mails and attachments.

While storing Excel workbooks to SharePoint, we can control which part of the file will be visible on the browser. Often workbooks have few output items like charts and Pivot Tables, and multiple raw data sheets with intermediate calculations. Therefore it makes sense only to show the desired report elements and hide the unwanted data.

This can be done easily using the Browser View Options in the Info menu of File menu. You have three choices. Show Entire Workbook – which is the default.

Alternatively you can show specific sheets – useful if you want to show the final output elements seen on a per sheet basis. This is also useful for exposing Power View reports because it is always a separate sheet.

Finally there is an option to choose specific named items like Tables, Pivot Tables and Charts. This is the most suitable and elegant option. If you have to show Power View report, this option is not appropriate.

Even though the report is shown on a browser page, interactivity is still maintained. In older versions of SharePoint, only filtering was active. Now even the structure of the Pivot Table can be changed on the browser view. Of course, by default it does not change the underlying Excel file.

Email as link vs. attachment

One of the best features I like is not even technical. Under Sharing . various methods are made available. The primary benefit of using SharePoint is that the document always remains in a single, central copy. Many users have still not realized this benefit. Therefore, Microsoft has smartly used the extra space available in the menu to market the idea of SharePoint based link sharing. Have a look.

The moment users understand that by sending an attachment they are losing complete control of that file, SharePoint adoption will zoom up!

Share a Link is the obvious choice and it is explained extremely well in a simplistic language without using any technical terminology.

Instant Presentation using Lync

Lync integration allows for immediately sending the file using Lync – but not NOT as an attachment – as a link. This saves you the trouble of going to Lync, starting a session, sharing an application and so on. This works with all Office tools.

Instant presentation using web (Word and PowerPoint)

This is a special feature available for Word and PowerPoint. This allows you to deliver a presentation using a special Microsoft hosted service called Office Presentation Service. Using this you can show the document or deliver a presentation to external parties by creating and sharing a temporary link. They can view but not edit the document. Scrolling in a Word document or Slide show will be automatically synchronized. Up to 50 people can participate. Audio must be managed through a separate bridge. This is a very powerful and lightweight way of presenting to customers or external parties.

Send by Lync

This option appears if the file is locally stored. In this case it starts a Lync session and attaches a copy of the file.

Version History

SharePoint manages major and minor versioning very well. This feature is also exposed in the File menu under the versions button.

This allows you to open any backdated version of the file for auditing or comparison purposes.

Check Out

This is used when you want exclusive editing of a file. This option is also available in the Versioning dropdown making it much simpler to manage check in and check out actions.

Protection (IRM)

If IRM is implemented (or available through Office 365), these options appear in the File – Protection menu.

Templates delivery

Till now whatever we discussed was about saving and sharing documents. Even creation of documents can be managed from SharePoint. Custom templates should be stored in SharePoint based document libraries and then mapped as a drive. This drive should be configured as a Workgroup Templates Path (Group Policy) to deploy and maintain enterprise wide templates with very little effort.

Similar approach is useful for distributing company-wide Building Blocks for Word.

Blog

SharePoint based blogs can be directly managed from Word by choosing the Blog template and registering the SharePoint server.

Group Policy

Group Policy for Office has very powerful options for creating additional places in the File Open / Save dialogs as well as backstage. We will discuss it in a separate technical article. This article is mainly for end users and hence I don’t want to complicate it.

Power Query and SharePoint Lists

Other than documents, data integration is also seamless between SharePoint and Office. Power Query accepts SharePoint list as a source for data. Alternatively, an Excel table can be exported as a list to a SharePoint library. Using Business Connectivity Services, LOB data can also be exposed to Power Query using SharePoint.

For the sake of completeness, let me also mention Data Connection Libraries, which allow you to define user oriented data sources on SharePoint. This eliminates the need of configuring ODBC / OLEDB drivers on each user desktop.

Synchronization with Outlook

This was always there but has become more seamless and powerful with Office 2013.

Access Integration

With Access 2013 it is extremely easy to create and manage multi-user data oriented applications right from a desktop. The database is created and synchronized on SharePoint and all users can interact it using pure browser based forms or Excel like Quick Edit functionality.

What more?

This article covered the new features of Office 2013 only from the point of view of Office 365, OneDrive for Business and SharePoint integration. I will discuss more new features in future articles.

***