How to add data to SharePoint list

A while back, I published a post where I explained how to export SharePoint lists from SharePoint to Excel. Today, I am going to cover the opposite of this how to import Excel to SharePoint. Say, you have an Excel list/table and want to bring it to SharePoint [because SharePoint custom list kicks ass!]. How do you achieve this? Well, let me explain. You have a few options to choose from!

July 2020 Update READ THIS FIRST!

Below techniques describe the old way of importing Excel to SharePoint. If you are in SharePoint on-premises, below tips are the only options you have. However, if you are in SharePoint Online [Office 365], we now have a new and easier way to import Excel data, which would be the easiest and preferred option to use. Please reference this most recent post for instructions.

Option 1: Import using Quick Edit

The first option assumes that you already have a SharePoint list with all the corresponding columns, and just need to copy/paste the content from Excel to SharePoint. Here is what you need to do to achieve this:

1. Build out all the columns exactly as they appear in Excel
2. Make sure columns in a view are in the same order as in Excel [otherwise when you do copy/paste, you will get a mismatch]
3. If you are taking advantage of metadata [drop-downs], make sure to populate all the possible choices into respective columns [otherwise, when you paste, and pasted text does not match the drop-down choice, the row will be rejected]
4. Open your list in Quick Edit [Datasheet view for those in SharePoint 2010]
5. Copy the rows you want to copy from Excel
6. Paste into Quick Edit View
7. You will notice the rows being pasted one by one. You might need to give it some time if you have lots of rows. In case you get a mismatch in data being imported, you will get a red warning just like in the image below
8. When all data is successfully pasted/mismatches resolved, just click on Exit Quick Edit. You are all set!

Pros

• Allows to take advantage of metadata and different types of columns in the existing list [i.e., Text, Choice, Date, Currency, etc.]

Cons

• It takes time to set up the listand view before you can import/paste
• Might not be a good technique for importing large data sets
• Error handling capabilities are limited [if you have a row that fails, might take time to figure out the issue/field causing the issue and you might need to redo the whole import]
• Import will not work if you set up metadata drop-down using Term Store [managed metadata]. Click here to read more about this issue

Option 2: Import Spreadsheet Web part

Another option that is available to import Excel to SharePoint is to use a Web Part called Import Spreadsheet. These are the steps to follow:

1. Gear Icon > Add an App
2. Scroll down to Import Spreadsheet App. Click on it.
3. On the next screen, give your new app/list a name, then choose an Excel file. Click Import
4. You will now notice an Excel file open up with a pop-up window where you need to select a range of cells to import. Once you choose the range of cells in the pop-up, click Import.
5. The table will now be imported to SharePoint. It will go ahead and create a custom list with proper column headers and values.

Pros

• Relatively easy to use
• Does not require much prep time like Option 1

Cons

• Have to use the Internet Explorer browser for this option. If you use Google Chrome, you will get an error message like this
• While import does an excellent job of recognizing some field types [i.e., numbers, dates], some fields that you would have preferred being drop-down choices are imported as text

Option 3: Import directly from Excel

Another great option is to import data right from within Excel. This is what you have to do:

1. In Excel spreadsheet, select the data range and Format it as a Table
2. While still in Excel, highlight the table, then choose Export from the top ribbon, then Export Table to SharePoint list
3. From the pop-up that appears, specify the URL of the site you are importing to, then give the list a name, then hit Next. You can also [optionally] check the box next to Create a read-only connection to the new SharePoint list. What it does is make your Excel physically linked to SharePoint list. Any changes in the SharePoint list will propagate down to Excel when you refresh the data there.
4. On the next screen, you will see all the columns with corresponding recognized column types [Currency, Date, etc.]. Just click Finish
5. You should be getting a success message/pop-up.
6. You should now see your list created in SharePoint. It will default to Datasheet view, just click Stop to view it as a normal list.

Pros

Same as Option 2

Cons

• Same as with Option 2, some drop-down choices are recognized as Text.
• The list created is a classic list and not a modern one [for those who are in SharePoint Online]
• By default, the list opens up in Datasheet view. You always have to click Stop Editing to see a normal list