Learn how to import Excel spreadsheets into SharePoint Lists and Microsoft Lists and what to do to troubleshoot the feature when it doesn’t work as expected.

Microsoft Lists and SharePoint Lists

This tutorial will focus on Microsoft Lists. It’s the most recent iteration of a low-code or no-code database that Microsoft offers. Microsoft Lists were first released September 2020 and have been intermittently updated ever since.

They have the almost the same exact functionality as a SharePoint Online List. Some of the older and on-premises versions of SharePoint Lists will work differently and come with their own set of troubles and challenges.

Creating a New List from an Excel Sheet

Theoretically it should be really easy to create a new list from an Excel Spreadsheet. When you go to Microsoft Lists site, click on + New List and it will prompt you to create a blank list, from an existing list, or From Excel. Choose From Excel.

Next, you will be prompted to Select a Table from an Excel Worksheet.

Excel Tables are a specific feature in Excel. It’s different than selecting a regular cell range. An Excel table will give your columns Field Names that can be referenced within Excel.

To create a table in Excel, open the excel workbook, and go to: Insert > Table after selecting your data range. Once its successful, your rows and columns will become formatted. Name your Table so you can more easily reference it if you’re uploading a workbook with multiple tables in it.

Next, Save the file. Go back to Microsoft List, open the file and after a few moments you should be able to view your new imported list. If the import is successful, you will see a new populated list.

File Already in Use Error During Import

If you receive an error that says the file is already in use while you try to upload the Excel file, check the following.

• Doublecheck that you do not have the file open on your desktop
• If saved on a network drive, ensure that someone else doesn’t have it open
• Sometimes network files get hung up, either reboot your computer or copy the file to your desktop.

How to Append Data to an Existing SharePoint List

If you already have an existing SharePoint list and want to quickly add more data to it, click the Edit Grid View button at the top left of the list.

Once you click it, there will be an Exit Grid View button, and the list becomes entirely editable.

This view lets you Copy and Paste

As long as your data columns line up. Copy your data from Excel and Paste it into the List. Copy and Pasting Excel into SharePoint Lists will be the primary way to fix or avoid a majority of problems.

Note: Try clicking near the circle in the top left of list before pasting

Note: Using the copy and paste method will still require you to create all of your columns. You can either do it manually, or you can try and upload a file. Usually if it errors out, it will still create your column headers for you.

Common Errors When Uploading Excel Files to SharePoint Lists

When trying to upload Excel files into a SharePoint or Microsoft list you might run into some of these errors. Microsoft does not necessarily make the reason of the error obvious with the messages that appear. It may take some trial and error to troubleshoot.

• Data Type Mismatch – This error will occur when the data type of the data you are pasting or importing doesn’t match with the column. For example, if you try to paste Text into a Numeric column, you will get an error. Update this by editing the column and defining a different Type.
• Reserved Column Names – SharePoint and Microsoft Lists will by default reserve specific column names. If you try to use these column names in your table, your upload will fail. You can try to copy and paste into the SharePoint list to avoid this.
• Permissions – Make sure you have permissions to create, edit, and add records to a SharePoint list.
• Data Validation – Going a step beyond Data Types, you some columns can be setup with formulas and data validation. Make sure that these settings are either turned off or your data matches the requirements.
• Date Formats – Data formats hold a special place with data validation. Make sure that dates are in the correct format before copying and pasting or uploading them into your dataset.

To check permissions, and which columns are active or required go to:

Settings (Cog Wheel) -> Library Settings -> More Library Settings

From this screen, you can modify whether or not columns are required, change data validation settings and make many other important changes.

Edit Column Settings from Grid View

If you go to Edit Grid View, Click on the Top of Column and you can Edit Column Settings. These settings will let you modify data types, define data validation rules, formatting and make other necessary changes.

Click on the column header and you can rename it.

Note: If you don’t find the options you need here, check out the additional Settings available under the Microsoft List settings. This is where you can control permission settings and determine if fields are required or not.

After clicking edit, you will have an Edit Column menu on the right side of the screen to establish data types, names, and formatting options.

Conclusion

If you need help working with SharePoint or other parts of the Microsoft Power Platform contact us today! Pop! Automation specializes in helping companies automate their processes, building out automated financial reports in Power BI, and can help with a wide variety of corporate automation needs.