Home >Tips >Import Excel table to SharePoint list kalmstrom.com site map icon Site map  

Two ways to import an Excel table to a SharePoint list

An Excel tutorial by Peter Kalmstrom

Excel iconIn the demo below the kalmstrom.com CEO Peter Kalmstrom shows two ways to import data from an Excel table to a SharePoint list.

As an example Peter uses a company contacts list for staff that is already present in Excel. This Excel list has a link to a photo of each staff member.

Peter uses Excel and SharePoint 2013 for his demo, but the two methods are avaliable in earlier versions too. For both methods he must first format the Excel list as a table.

The first method Peter shows is to export the table to a SharePoint list with the Export button in Excel. This method creates list columns in SharePoint.

Excel icon

The resulting list is not very user friendly, but Peter shows how to modify it so that it works well. However, with this method the photos will still be links.

Excel Data Bars


The second method is more complicated but also much more powerful. Now Peter begins in the other end, in SharePoint, and he also uses Access. That way he can control what columns will be used.

Site columns give several advantages, for example in searches, so Peter prefers them to list columns. The second method makes it possible to use site columns and also to display the photos directly in SharePoint.

This method can be used in the same way for CSV files.

These are the steps in the second method:
  1. Create a Contacts list in SharePoint.
  2. Modifiy the default Contacts list
  3. Add a Contact Photo column from Site columns
  4. Add a Picture library
  5. Upload photos to the Picture library
  6. Change the links in the Excel table Picture column so that they link to the photos in the Picture library
  7. Add the Departments to a new Department choice Site Column
  8. Add the Department column to the Contacts list
  9. Open the Contacts list as a table in an Access database
  10. Link the Access database to the Excel file by creating a linked table
  11. Create a Query Design that appends the linked list table to the Contacts list table
  12.  Run the Query
And here is the result:
 
Staff list in SharePoint



Also refer to
the kalmstrom.com Blog post about adding an Excel staff list to SharePoint




Learn more



---------------------------------------------------------------------------------------







Always the latest news in the kalmstrom.com blog



Follow kalmstrom.com on LinkedIn Facebook, Google+  or Twitter!