How to Import Data from Excel into SharePoint

There are a few ways to import data from Excel into SharePoint – learn when to use each method based on your needs.

We previously shared how to export data from SharePoint into Excel. But what if you need to import data from Excel into SharePoint? Importing your Excel data into SharePoint enables a ton of extra functionality.

Here’s a common example:

Maybe a project manager on your team created a huge spreadsheet to track data on a project such as permits or materials. Over time the spreadsheet has gotten out of control. Keeping information up to date and sharing it with stakeholders is a pain.  So you’d like to add some additional functionality like workflows based on field changes. You also want to share it somewhere and set permissions for who can access and edit data.

Putting this spreadsheet into SharePoint would allow you to do all of the above and more.

So what is the best way to import data from Excel into SharePoint?  That answer depends on the contents and complexity of your Excel data. Below are two common methods – and one bonus method.

Method One: Use the Import Spreadsheet App in SharePoint

The Import Spreadsheet App is a useful tool for basic spreadsheets. The tool creates the list with column titles in SharePoint for you and automatically imports your data.

But be careful. The Import Spreadsheet App works best when your data is a simple list with single text field data. It can get easily hung up when trying to import a list that mixes multiple data types across columns and even in a given column. Why? Because by using this app, you are letting SharePoint make the call as to how to handle the column data types.  This can lead to unexpected results.

For example, the Import Spreadsheet App often stumbles on single-line text types in date type columns or vice versa.  So if one of your spreadsheet columns includes dates and times, the tool will make that entire column a date/time data type when importing it to SharePoint. But if some of the fields in that column have text entries (e.g. “NA” or “TBD”), you will start getting errors when editing and viewing the list. And then you have the tedious chore of troubleshooting that list in SharePoint.

Don’t get me wrong, the tool can be a useful timesaver for simple spreadsheets. However, if your spreadsheet is more complex, you might spend more time fixing errors after the import.

Method Two: Create a Custom List

A better approach to import data from Excel into SharePoint – especially complex data and spreadsheets – is to use the Custom List App. This allows you to create the list the way you want it. This process takes a little more effort upfront, but saves you headaches later.

Here’s how it works:

Step 1 –First, you will need to go to Site Contents and Add an App to enable the Custom List App.

Step 2 – Select the Custom List App.

Step 3 – Define the Columns Title and Types to represent your spreadsheet in your Custom List.

Step 4 – Create a view that matches the columns in the order of your spreadsheet.  You can create a Datasheet View or Standard View and switch to Datasheet or Quick Edit.

IMPORANT: Make sure to include all the columns in your view that are required or the import will not work!

Step 5 – Highlight the data from your Excel file and copy it (Control-C). Then paste (Control-V) the data in an open row in the Edit View of the List.

Step 6 – Make sure all of the items paste without errors. Any errors will show as red circles to the left of the row. When done, click “Stop editing this list.”

Presto, the above steps are a copy-and-paste method to import data from Excel into SharePoint.

Method 3 – Set up an automated process to import data from Excel into SharePoint 

Here’s a bonus method for you. Did you know it’s possible to have a list update automatically from Excel into SharePoint? There are many options to automate importing Excel data into SharePoint.

Get a free consultation to learn more.

Want more tips and tricks for construction project management professionals?

Our new Tips from the Field series features handy information for construction project managers. Subscribe to our newsletter to get our latest tips and tricks delivered directly your inbox each month.

And be sure to check out last month’s installment to learn how to easily reorganize your files in SharePoint.