Excel To SharePoint In Two Ways

Exporting Excel data to SharePoint gives many sharing advantages. The transfer can be done in several ways, but the major question is if you want to take the extra trouble and use Access, or if you can do with a simpler method.

In the SharePoint Online from Scratch series I have created tutorials on both methods. I also discuss benefits and drawbacks with Excel and SharePoint Online from a sharing aspect. Most of them are applicable to other SharePoint editions as well.

Excel vs SharePoint
There are several advantages of having data in a SharePoint list instead of an Excel table

• The data is easier to share in SharePoint.
• When you enable versioning in SharePoint, you can see and restore earlier list versions.
• SharePoint´can send you a message when the list has been changed.
• You can search data in SharePoint.

SharePoint also has some drawbacks, and the most important is the 5000 items limit on SharePoint lists. This limit cannot be changed if you are using SharePoint Online. I have heard that the limit will be removed in SharePoint 2016, but for now you must be aware of it when you transfer data from Excel to SharePoint.

Excel Export button
The easiest way to move items from Excel to SharePoint is to export an Excel table to a SharePoint list by using the 'Export Table to SharePoint List' button in Excel. This method is quick, but it has some serious drawbacks. Some of them can be helped by a "clean up" of the SharePoint list that is created in the export.

Other issues, like lack of control and the use of list columns instead of site columns in the SharePoint list, are more difficult to correct when the export has been done. I discuss those problems with the Export button method in a separate tutorial.

Use Access
If you want to import data from several Excel files to SharePoint, I recommend using the Access method instead of the Export button in Excel. The data is of value to your organization and the result will be better, so it is worth the extra work.

In my tutorial I explain step by step how to do, and once you have learned the process it is not slower than using the Export button and then cleaning up the list – and you will get a more powerful result!

With Access you can decide exactly which Excel columns you want to include in the SharePoint list and how data should be distributed. You can also use an existing list with site columns for the import of Excel data.

Where to edit
Another advantage of the Access method is that changes can be made in both Access and SharePoint. When you enter data in Access it is saved to the SharePoint list as soon as you move to another row. When you enter data in SharePoint, the linked Access table will be updated next time it is opened or refreshed.

When you have exported the Excel data to SharePoint via the Export button, items can only be updated in SharePoint. Changes in SharePoint are saved back to Excel, but if you edit data in Excel the changes are not saved to the SharePoint list.

I hope my tutorials on Excel data in SharePoint will help you make sensible decisions on how your organization should share information and move data from Excel to SharePoint.

By Peter Kalmström
CEO and Systems Designer
kalmstrom.com Business Solutions