Export Excel Lists To SharePoint And Connect Them

hi I'm Peter come stream of Kallstrom dot-com business solutions and this demonstration I'll go ahead and import this list to SharePoint so that we get all the data and also I will display how to connect these two to each other these two lists that I have one here at customers list and run a country list so I'm gonna export both those two into SharePoint and then connect them and make a rub a nice display of that and SharePoint so the first thing I'm gonna do is I'm gonna insert a table here or the same thing can be done here format at the table and I'll just select any layout there occurs and it select that my table has headers this works well as long as the table has no empty columns or empty rows so then the next step I'm actually gonna remove all the formatting here of it but I still want to retain it as a table and I see that it is a table in the eyes of Excel and that this table tools tab of the ribbon is visible and that ribbon gives me this export feature export table to SharePoint list so I'm gonna do that and I'm just gonna insert the URL I have the URL here already this happens to be a site on SharePoint comm which is office 365 but this would work both and if you had an internal website or office365 of course I'm not going to create a read-only connection at this time I'm just gonna export this into the lists called customers and then I'm gonna go and click next and then I'll have a preview of how this is gonna be rendered in SharePoint there we go you see all the information is actually transferred there but I do have a couple of problems here that there are multiple lines in some of these customer name etc and also in phone number can change that here but once we're in SharePoint I will indeed change that so I'm going to finish this and now Excel is adding rows to my SharePoint site do you see that one rather fast but the table was successfully published and may be viewed etc so I'm gonna click on this link now and go out to the website and there we go the customers list it's opened down in there in a second unless you see this opens up now in the datasheet view which is nice but I'm gonna change the views later so the first thing I'm gonna do now is notice that the contacts first name can't be sorted and it can't be filtered upon because it's a multiple row and the same thing with a lot of fields here and of course I do want them to be sortable so what I'm gonna do is go into the list settings and find the fields that are available and this list and as you see several of them are multiple lines of text so I'm going to go into customer name and if I first change them to plain text click OK then go into them again you'll see that I can actually change multiple line field to a single liner field so I'm going to do that a couple of times now back and forth here contacts first name it's a plain text and again on there contact last name the same thing so this gets a bit repetitive of course but um it's better than entering the data again of course just a few more clicks now there we go now I have the city and the phone number to do the same thing with there we go and the city again and finally the phone number two clicks on that to play it first to plain text and then to single line all right there we go I'm gonna click OK on that so there I have all my customers now and the second problem I see here is that my customers list is actually not visible in the quick launch I can change that over here title description navigation and put it in the quick launch under you see the customer send up there I'm also gonna go ahead and click create a new view I can do that here great view I'm just gonna do a standard view because I want this standard to be there and I want a standard to be a non datasheet view list I'll make that the default and I'll click OK that worked out just fine and now as you see this is the default view now I'm gonna go back to excel and do the same with the countries list first of all I'm gonna insert a heading right clicking on that and just write in country there and now I'm gonna format this as a table - it's fine and has headers beautiful and then I'm gonna export that to SharePoint on the same site and I'm gonna call this countries and do next it's just gonna take a few seconds and then oh that's a single line so that's good we don't have to do that again there we go now we have that list also and I'll do that go ahead and click them that to go do the same thing with that I'll change it to a non datasheet view and I'll put it out on the quick launch so I'll go into the list settings and change the navigation to make it display on the quick launch say that now we have the countries there I'm also gonna create a view in this case I'm gonna create a more compact view since there's only one it will be a standard view and made the default and I'll call it the country's best yes that's fine and I don't want have any type and I am catchment side of just the country item there and I'm gonna be and make it a of the style and boxed that will be fine and I click OK there we go as I said I wanted to connect these two now I have the customers on the countries and the customers list I already have one field here with the country right but that's not connected to the countries less than anyway now so I'm gonna go ahead and go over to the last tab here and create a new column and I'm gonna call that country look up and it's gonna be a lookup based on information already on this site and that's gonna be the countries list and of course I'm gonna pick the country field of that and I'm gonna add that to the default view now to get this information from the one column to the other I'm just gonna go into datasheet view and here you have the countries list there on the country look up and look up as you see is it's connected to the countries list beautiful but what I want to do is just copy this information over there and I'll select the entire column right click on it select copy and then right-click and paste over there there we go then I have all the information there looks great managed to get an empty row there and I'll go ahead and delete that I now know and now I don't need the country calling me or I just need the country lookup so I'm gonna go ahead and move the country single-line feel there which I don't need so I'm gonna go in quick comment and then just delete now the final step is actually connecting these and I will do that on a new page I'm just gonna create a new page here on my SharePoint 2010 site I'm gonna call that them country grace customers so in that page now I'm just gonna go in and insert some webparts and first i'm gonna insert the countries list and let's see listen libraries here we go and i have a country slice there and i'm just gonna add that there this webpart selector dialog might look a bit different but depending on what SKU of sharepoint you have and etc if you have silver rights but that's the same thing so i'm gonna go ahead and add the other one also the customers list again on the insert tab I click on the in the webparts button there and I'm gonna add the customers they're from the distant library selection there now in order to connect these two so I can actually click a country up here and get the appropriate customers a bit further down I'm just gonna go ahead and go into edit mode of the country web part here now you see this is kind of out of the picture here so there we go now I'm gonna go into edit mode of this web part and once I'm in edit mode of the web part I can go ahead and apply a connection between these two that the webpart task pane will appear to the right here but I'm actually not going to use that now I'm just gonna use the this little selector here and I'm gonna provide the row from the countries list to the customers list and now a small wizard will appear which will allow me to connect these two there we go I'm gonna select the column and the countries list to the filter customers and of course the only call them available in countries is country but in the customers list there's lots of columns there so I'm going to select the country lookup and then we're gonna go next um that's the end of that so now you see I get these little selectors here and first of all we're gonna go ahead and save the page still loading there we go there we go no it's saved save and close and now I can go ahead and select the counter country here so let's select Sweden for example and you'll see at the bottom here we have all the Swedish customers select Switzerland I get the Swiss ones except two etc so quite a bit of functionality there showing both how to import data from Excel and how to create some new views and to how to connect to web parts to each other thank you for watching this demonstration

Channel: kalmstrom.com

Views: 106,828

Keywords: Excel lists, Connect SharePoint lists

Id: vay-J97xl44

Length: 10min 18sec (618 seconds)

Published: Sun Dec 04 2011