Export Excel lists to a SharePoint site and connect them
In the demo below kalmstrom.com CEO Peter Kalmstrom shows how to export
two Excel lists in Excel 2010 to a SharePoint site.
explains how to connect the two lists in SharePoint.
The possibility to connect lists in SharePoint is
very useful. On the kalmstrom.com SharePoint
intranet we have the Customers and the Purchases
lists connected, so that when a Customer is selected
all the Purchases of that Customer is shown below
the Customer details.
In the demo Peter uses a connection between
Customers and Country as example. The result makes
it possible to select a country and see the
customers from that country. These features are being
Format as table
When Peter has made a table of the list he gets the alternative Export under the Excel Table tab.
Export from Excel to SharePoint
The Excel list "Customers" is exported to a SharePoint list with the same name.
Change from Multiple row to Single row
Some of the fields in the new SharePoint list cannot be sorted or filtered, since they are of the multiple row type. Peter makes them single row instead.
Display the list on the Quick Launch
Peter adds the "Customers" SharePoint list to the Quick Launch.
Create a new SharePoint List View
The list was created as a datasheet view, so Peter creates a Standard view also and makes it default.
Create a new Lookup column in the a SharePoint list
When Peter has exported also the "Countries" Excel list to
SharePoint he creates a new Lookup column in the "Customers"
This new column will take its info from the "Countries" SharePoint list
Transfer data from one column to another
Peter transfers the "Country" column of the "Customers" SharePoint list to the new "Country Lookup" column. This is done in the Datasheet view by Copy and Paste. Then the old "Country" column can be deleted.
Create a new SharePoint page
Insert SharePoint Web Parts
Peter inserts the lists "Countries" and "Customers" into the new page.
Apply a connection between two SharePoint lists in a new page
In Edit mode of the "Countries" list Peter creates a connection to the "Customers" list
Now Peter can select a country and see the customers from that country. Welcome
to look at the demo and try it yourself!