Importing List Data from Excel

Quick reference

• 00:04 We've already seen that SharePoint has many different templates that you can use
• 00:08 to build lists.
• 00:09 But there is going to come a point where you've created a list in
• 00:13 another application, maybe an application like Excel, and
• 00:17 you want to import that list into SharePoint.
• 00:21 So let me show you an example.
• 00:23 So I've just switched across to Excel, and you can see that I have a list of
• 00:27 information here and this is the master list of employees.
• 00:31 I've got column headings, employee ID, first name, last name, location,
• 00:35 department and their extension number.
• 00:37 And this data set has been formatted as a table in Excel.
• 00:42 How do I know that?
• 00:43 Well when I click in the data, if I take a look up at my ribbons,
• 00:47 I now have a tab called Table Design.
• 00:50 If I click outside of my table, that tab disappears and
• 00:53 if your data isn't in a table, you'll also not see that tab.
• 00:57 But don't worry too much if you're not an Excel guru, or
• 01:00 even if you know very much about Excel.
• 01:02 What I'm trying to illustrate here is how simple it is to import data into
• 01:06 SharePoint.
• 01:07 So this is the data set that we're going to import and
• 01:10 create a brand new list out of.
• 01:12 So let's minimize down Excel, and I'll show you how you can do that.
• 01:17 Now like most things in SharePoint, this process does differ depending on whether
• 01:21 you're using the modern or the classic experience.
• 01:24 So I'm going to show you how you can import using both.
• 01:27 Let's start out with the classic experience.
• 01:29 If you're using the classic experience, you'll need to import using an app.
• 01:35 And if you remember if we click this new drop down, we can access all of our apps,
• 01:38 those lists and libraries from here.
• 01:40 Now if you are using the classic experience,
• 01:43 you won't have this menu running across the top.
• 01:45 So what you need to do is go to the cogwheel in the top right hand corner, and
• 01:50 you have an option here to add an app.
• 01:53 And this is going to take you into a very familiar looking screen.
• 01:56 We've already used this numerous times.
• 01:58 And what we have in here is a template to import a spreadsheet.
• 02:03 Now I can find this very quickly simply by clicking in the search box at the top.
• 02:07 And if I type in import and search for that,
• 02:10 it's going to pull back the template that matches what I'm looking for.
• 02:15 So I'm going to say yes, I want to import a spreadsheet.
• 02:20 Now I need to give my SharePoint list a name.
• 02:23 So I'm going to say list of employees.
• 02:29 Again I could give a description I'm not going to, and
• 02:33 then I just need to click Choose File to import that spreadsheet.
• 02:37 So that is how you would do it using classic view.
• 02:41 Let's now take a look at how we do this using the modern experience.
• 02:45 I'm going to click back on home.
• 02:46 What I can do from here is click that new drop down again and go to list.
• 02:51 And in the modern experience, we have a few different options.
• 02:55 And you can see right at the top we can create a list from a blank, from Excel,
• 02:59 or from an existing list.
• 03:00 So I think we all know where we're heading to here.
• 03:03 We want to create it from an Excel spreadsheet.
• 03:06 So let's click on from Excel.
• 03:08 We can then browse and find the file that we want to upload and
• 03:11 this is the one that I'm using here, master list employees and click on Open.
• 03:16 You can see now it's uploading that information, and it loads
• 03:19 it into a windows that I can just check to make sure everything looks correct.
• 03:24 So it's selected the correct table, I can see the data from the Excel spreadsheet
• 03:28 listed below, and at the top here,
• 03:30 we have the different data types contained within that particular column.
• 03:35 So what you need to do is go through and
• 03:37 make sure that these data types correspond to the data below.
• 03:41 So if I scroll across single line of text, yes, this all looks correct.
• 03:45 And then in the final one, we have number.
• 03:48 So I'm happy with how this looks.
• 03:51 So I'm going to say next, and I can now name my list because you can see by
• 03:56 default, SharePoint gives this list a very long and messy looking name.
• 04:03 So I'm going to call this Master List of Employees.
• 04:08 Again, I could add a description.
• 04:10 I'm not going to, and
• 04:11 then I'm being asked do I want to show this list in my site navigation.
• 04:16 Now I could deselect that if I wasn't too fussed about that, but I do.
• 04:19 So I'm going to leave that ticked and click on Create, and there we go.
• 04:24 It's imported that data within a few clicks.
• 04:27 Now one thing you'll notice that I didn't do is when I was naming this list,
• 04:32 I put spaces in the name.
• 04:33 And I've done that on purpose just to show you what the URL looks like when
• 04:36 you do that.
• 04:38 If you take a look at the top here,
• 04:40 can you see that we now have %20 %20 wherever there is a space.
• 04:45 And whilst you may not care about that,
• 04:47 it does end up making your URLs look a lot messier.
• 04:51 If I was to send this as a link to somebody, it ends up being very long and
• 04:55 kind of ugly looking.
• 04:56 So that is the reason why ordinarily, I create the name with no spaces and
• 05:00 then go back in, and change the way it's displaying to the users.
• 05:04 Also note that because I select it to add to site navigation,
• 05:08 I can now see my Master List of Employees showing in the Quick Launch menu.
• 05:13 So very simple to import data in from an outside source.

Lesson notes are only available for subscribers.