5 min read

Working with data on sharepoint

By Mads Bender on 27 January 2023

Personal experience working with data in larger projects with SharePoint.

More and more of our daily operation centers on data, and the aspect of sharing data is of great value. As a business professional you most likely use Microsoft office problems in your daily operations, and if you work on projects, you are likely to also use SharePoint to manage the sharing of documents.

In a recent project I was using share point to share data, working with Microsoft offices applications. Here I ran into a huge limitation to this way of working and my suggestion to all for working around this. I needed to get data from data sheets on SharePoint and manipulate them, I did that in a local excel sheet, here the first limitation is you can only copy paste a small amount of data from online excel to your desktop client. Thus, for this I copied when the data was within limits otherwise, I would open in excel client and copy paste between 2 excel workbooks. HOWEVER, copy paste between excel online and your excel desktop client does not keep the proper formatting, and here all by date formats where lost and only some of the data points ended up with a date format.

So how do we work around this, personally I see very little value in working on the online application, yes you work on the server however it is a less powerful application compared with the desktop version. The only reason why I use the online version, is because SharePoint opens the desktop application. So how do we change this?

Change from online office to local client as default for opening office files.

Picture1To change SharePoint from opening office files (word, excel, ppt…) in the online application to the local client we need to update the settings in SharePoint.

First go to your documents and press the settings icon it is on the right in the menu bar (the gear icon).

Next go to library settings.


Go to More library settings:


Go to Advanced settings:


Scroll down until you find the section on Opening Documents in the browser here set the bullet to Open in the client application. Now office files will open in your client application instead of using the online version for this project:


This setting is library specific, therefore you will have to update it on all the libraries where you want this to be the default behavior of SharePoint.

If you always want this behavior on a project and you are site administrator, you can update the root/home for a project, you do this by going to Home -> Settings -> Site Settings -> Site Collection Feature -> Open Documents in Client Applications by Default -> Activate.

I hope this is valuable to more out there, please contact us if you have any question.


Written by Cittros team

Subscribe for our insights