Locked lesson.
About this lesson
Import a contact list that's stored in an Excel spreadsheet into SharePoint as a list.
Exercise files
Download this lesson’s related exercise files.
3.10 importing-list-data - Exercise.docx60.9 KB 3.10 importing-list-data - Exercise solution.docx
59.3 KB Master_List_Employees.xlsx
11.2 KB
Quick reference
Importing List Data from Excel
SharePoint has many list templates to choose from. However, what if we have a list stored in another application like Access or Excel? Both of these applications structure list data in the same way with column headings and records and so they can easily be imported into SharePoint.
When to use
We import list data into SharePoint whenever we have a list stored in another application like Excel.
Instructions
List data can be imported into SharePoint in both the classic and modern experience in slightly different ways. In this example, we are going to import a master list of contacts from an Excel spreadsheet.
Importing data in the Classic Experience
- Click the Cog icon and select Add an app.
- In the search box, type 'Import' and press Enter.
- Select the Import spreadsheet template.
- Name the list 'Master List of Employees'.
- Add a description (optional).
- Click Choose File.
Importing data in the Modern Experience
- Click the New drop-down and select List.
- Click From Excel.
- Click Upload File.
- Browse to find the file to import and click Open.
- Check the data types are correct for each column of data.
- Click Next.
- Name the list 'Master List of Employees'.
- Add a description (optional).
- Select Show in the site navigation.
Hints & tips
- Ensure Excel data is contained within an Excel table prior to importing.
- 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.