Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's Office 365 Admin Blog > Posts > Creating a SharePoint list from an Excel spreadsheet
November 17
Creating a SharePoint list from an Excel spreadsheet

Last week a friend of mine asked a fairly innocent question, "Can I import a spreadsheet into SharePoint?" My answer to almost all SharePoint questions is, "Of course you can, and it'll rock!" Of course this is no exception. I thought I'd write up a quick blog post to show some of the ways it rocks.

I started out with this spreadsheet:

Pretty simple. Of course he could have just uploaded this spreadsheet to a document library, but then anyone that wanted to add vales would need to have Excel installed to open it. Plus there would be the added complexity of check in and check out to deal with, which aren't necessary for this. Instead, we're going to import this spreadsheet into a list in SharePoint. To do that go to the home page of the site where you want to create the list and click Site Actions > Create like below:

 

Under Custom Lists choose "Import Spreadsheet."

You'll get have to enter a list name and choose the XLSX file you want to import. This will fire up Excel and you'll get to choose which parts of your spreadsheet you want to import. In this example I lassoed the relevant information:

Click okay and let SharePoint work its magic. SharePoint generates a list like this:

SharePoint took pretty good care of this, and even used the header row for the column names. Pretty cool. But it made some general assumptions about the data. It added everything as a single line of text as shown here:

This doesn't work well. Fortunately you have a lot of flexibility. Let's go in and change the birthdate field to a date field. Go into the list settings and change the type.

These settings look appropriate for the data in the column. When I hit okay I got this dialog box:

Being the adventurous sort, I hit OK. Here's what was waiting for me:

Of course there was not a big pink question mark, but the Birthdate was missing for Harry. How come? If you look back to my original spreadsheet you'll notice the value for Harry's date was 12/31/174. Since it's not a valid date, SharePoint kicked it to the curb. Even though we lost a value, it was understandable and I considered it a success. I decided to try the Rank column and see what SharePoint could do. It made sense to make it a choice value. I opened of the list settings and changed the Rank column to a choice type. Look what SharePoint did for me:

It went ahead and populated the existing values into the dropdown choices. All that for no extra cost. Sweet.

Hopefully this blog will help if you want to move some spreadsheets into SharePoint lists.

tk

Comments

Thanks

Was wondering how that worked.
 on 12/2/2008 12:30 PM

share point

Many thanks! It worked perfectly on my blog
 on 1/1/2009 2:22 AM

Can I add new records from Excelsheet in the previously created list using "Import SpreadSheet" under Custom List

Hi,
I have created one list using "Import SpreadSheet" under Custom List. At the time of list creation, I have 10 records in the ExcelSheet.
Now I added another 10 records in the Excel Sheet.
Can I import this updated sheet in the previously created List?
Please let me know if you have any thoughts on this.

Thanks in advance.

S
 on 3/9/2009 6:57 AM

Yes you can

Hi S

copy the new rows from within Excel and open the old list in Sharepoint with Actions, Edit in datasheet, paste there the new rows, save the sheet...et voila!

Markus
 on 3/25/2009 9:41 AM

Importing more complex spreadsheets

Many users I work with have complex spreadsheets - multiple worksheets, calculations, even color coding.  Are there best practices about moving this type of data to SharePoint via custom lists and maintaining links, calculations, etc? Thanks.
 on 3/31/2009 9:52 AM

Chaitanya Sagar,Excel Expert- Spread Sheet-Share Point

Thanks for the post. It had made my job easier. Integrating spreadsheet and share point is producing great efficiencies in my work.
http://www.p2w2.com/chaitanya_sagar/index.php
 on 4/8/2009 12:04 AM

Can I change the linking column aftter the import

I have performed an import and only one of the columns is the edit/view item url - how can I change this link to either another column AND/OR add in another linking column?

Thanks
 on 4/8/2009 8:59 PM

One Problem

What if "Import Spreadsheet is not available  from the Custom List Menu?
 on 5/1/2009 11:23 AM

Excel header list not importing

I performed all the correct actions, however, when I select import of the spreasheet, the import feature creates column headings in the Excel Spreadsheet.  When it imports into SharePoint, I get Column 1,aolumn2,etc. as headers not the headers in Excel.  Why is this?  How can I correct it?  I formatted the 1st Excel row as Text and the other rows as General.

Thanks in advance for assistance.
 on 6/3/2009 2:43 PM

Defining Excel Column Settings before importing to a SharePoint list

I successfully imported an Excel spreadsheet to SharePoint creating a custom list.  Somewhere in the import process, SharePoint defined the column settings as either mulitple or single lines of text. I am unable to edit those defined as "Multiple lines of text".  I need to change these to "Single lines of text", however, this isn't an option.  Is there a way to define the column settings in Excel (multiple or single lines of text) prior to importing the spreadsheet to SharePoint?  What criteria defines the column settings when using the "Import spreadsheet" function?


 on 7/28/2009 5:47 PM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person

Twitter


Want a message when I reply to your comment? Put your Twitter handle here.

Attachments

 

 SysKit