Join Gini von Courter for an in-depth discussion in this video Export a list from Excel, part of SharePoint 2016 Essential Training.
- [Voiceover] My favorite SharePoint data, is data that already exists some place else. That I don't need to retype, that I don't need to copy and paste. That I can simply import or connect to from some fabulous place like Microsoft Excel. I'm going to show you how you can take a list of data in Excel and publish it into SharePoint as a list. I could also take this list and pull it into SharePoint from Excel using an app. But let's see how it looks when you start this process in Microsoft Excel.
First, let's fire up Excel, and I'm going to open a workbook that already exists called meeting rooms. And this is a list of meeting rooms and their capacities. In column A we have an ID, column B, the name of the room, the location, type, and then the capacity given different room layouts. Classroom style, banquet setting, round tables, and reception which is basically stand up. The first trick is, this information has to be formatted as a table.
So just select any cell here on home tab of the ribbon, in the styles group choose format as table, and whatever format you want, it really doesn't matter, because we're not so much dressing this up, as we are changing it to a different style. It's no longer a range of cells, this actually has some different properties and you know that because when you click in it, you get another tab called table tools and it is the design tab.
Here, in the external table data group, we have an export button and if you click, notice export table to SharePoint list, I'm loving this. When I click, I have a two step here, almost as if we were in Texas. First it wants to know where I want to publish my table, and I can either copy the URL from the browser, or I can enter it if I know what it is. Which I actually do it's www.landonhotel.net /BH for Berrnal Hill.
The next thing I can do is I can create a read-only connection to this new SharePoint list. And if I do this, it does something pretty interesting. What it does is after the list is published, if someone modifies it in SharePoint, I can open this Excel spreadsheet, and refresh it, and I will get the information that was entered in SharePoint. Let me say that again because it's actually very, very cool. If I wanted people to be able to enter information in SharePoint that I could pull into Excel for an analysis, clicking this check box is a great way to do this.
All I want to do is take this table, and publish it in Excel, so it's available. And let me be clear if you used a former version of SharePoint, for example SharePoint 2007, or 2010 even, you had the ability to be able to publish from Excel to SharePoint, and simply refresh Excel to republish. That is no longer the case. Once I publish this to SharePoint, I have two possible outcomes.
One, without the check box checked, is that now there's a copy in SharePoint that's totally separate from the copy that I have here in Excel. So if I add meeting rooms in Excel or if someone adds them in SharePoint, there's no communication between these two programs. With the check box enabled, I can no longer change this in Excel. All I can do to modify this list is change it in SharePoint. However, if it has been changed in SharePoint, I can refresh the list and get that new data here in Excel.
I simply want to copy this. Now, what name will this table have when it's a list in Excel? This is going to be meeting rooms, but remember, I'm creating a URL, so I might wanna say something like MTG rooms. This might also be a place that an underscore wouldn't be a bad idea. So this is a list of, we might even want to say that this is the official list of meeting rooms and capacities at Landon Hotel Bernal Hill, next.
I'm being prompted to log in, that's not going to surprise me at all, and you may be prompted to do that too. Even if you aren't normally because we're having an application that's local communicate with our server. In the second step, notice that it has each of the columns, and it has the data type listed. You'll want to make sure that these are correct. So that, for example, classroom, banquet, and reception are numbers. If you were going to publish a list that you wanted people to be able to fill in information in SharePoint so you could pull it back in Excel for analysis, you would always want to provide at least one row of data, because this information that these classroom, banquet, and reception columns are numbers.
Well ID, name, and location and type or text is actually generated based on this first row of data. If this is wrong, you can't fix it on the other side easily, so what you'd want to do is cancel, fix your first row of data so that it models what it is you would like to see in terms of data types. I'm all done, so I will now click finish. If you want to try this, all you need to do is generate a two row table, one row of column headings, and one row of data.
And you can check out and see how this works by exporting my table to SharePoint as a list. Notice that SharePoint is telling us that the table was successfully published and we can view it, by clicking here. If I click this hyperlink, there we go. Beautiful. Wasn't that easy? I love this. Notice also, that I have the ability if I wish to go in here and edit this, make any other modifications that I wish, I like this.
And I can't stop editing the list, it drops me in an editing mode, but let's do one more thing to this before we are done. Let's go to the list tab on the ribbon, let's go to list settings, and let's clean up the list name, description, and navigation. I need to decide if I want this on the quick launch. For right now, I'm going to say yes. I might change my mind and remove it later, it depends on how often people use it.
But right now, I'd like to be able to have on the quick launch, a reasonable list of everything that people are using on the site. So I'm going to click save. And let's now go back home. There are our meeting rooms, and again, very easy to create this by exporting my list from Microsoft Excel. One more thought about this type of interactivity between SharePoint and Excel. Remember, that I said we were able to either export a table from Excel to SharePoint, or I could be in SharePoint and pull the information in from Excel using an app, and I want to show you where that is.
If we go to site contents, and choose add an app, on the second page of apps the very last one listed here is called Import Spreadsheet. And it works with Microsoft Excel or another compatible program. I tend to take Excel data into SharePoint in exactly the same way I showed you, for two reasons. The first is that when I import the spreadsheet, I'm not going to be prompted to create a lasting connection in the same way I will when I use Import Spreadsheet.
The second thing is, that the Import Spreadsheet app has a lot of moving parts. And it's not unusual for this app to be one of the apps that does not work as well in the earliest releases of any version of SharePoint. That's been true in 2010, 2013, 2016. And if it doesn't work, it will simply give you an error. That point you'll wait for a patch from Microsoft, it will take care if issues between Office, and Excel, and SharePoint. And that's really where some of us are right now, as the 2016 version of SharePoint is just coming out.
So right now, the Import Spreadsheet app does not work for all users of SharePoint 2016, and Microsoft is fixing it. It will work by the end of this month for everyone. Let me tell you though why you might want to use this Import Spreadsheet app. Because the data that you want to bring in from Excel is not formatted as a table, and it's not your spreadsheet so you can't format it as a table. You need instead to be able to simply select a range of cells. And in that circumstance, the Import Spreadsheet app is your best way to bring Excel data into SharePoint as a list.
- What is SharePoint?
- Understanding SharePoint roles
- Searching SharePoint sites
- Editing, saving, and sharing documents
- Using OneDrive for Business for file storage
- Working with libraries and list apps
- Creating custom and dynamic views
- Changing file, item, and list settings
- Using the SharePoint social features, including your newsfeed
- Creating site collections and sites
- Working with app parts and web parts
- Displaying images and media
- Integrating SharePoint 2016, Office 2016, and Office 365
- Customizing search in SharePoint
- Adjusting SharePoint permissions
- Creating content types and document sets
- Using SharePoint site templates