Import and export from SharePoint to Excel (& vice versa)

SharePoint is a Microsoft online platform that most companies use as an internal website or intranet, i.e., a cloud storage and management system for all business-related data, including your Excel documents. Although you can use OneDrive to share an Excel file, SharePoint unlocks features for more advanced collaboration. Workbooks saved there also support Excel co-authoring, so several people can edit the same file at the same time.

In this post, you’ll understand what a SharePoint list is, how to import data from Excel into a SharePoint list, and how to export from SharePoint to Excel.

If you want to import data into Excel, Sheetgo, an AI-powered data automation platform, can help. With Sheetgo, you can easily share specific sheets or ranges from your main Excel file with others, filter data, and even split it into multiple files. You also have control over edits and changes before merging everything back into your master file stored in SharePoint, giving you full oversight of your data.

Install Sheetgo today to start managing, automating, and scaling your processes across your spreadsheets and workflows with AI.

What is SharePoint?

SharePoint is a web-based collaboration platform developed by Microsoft. It combines various aspects of document management and storage, intranet portals, extranets, and social networking tools into one integrated package. SharePoint is designed to help businesses manage workflow and collaborate more effectively with colleagues, partners, and customers. By utilizing this platform’s features, organizations can streamline their processes, create virtual workplaces and securely share information.

SharePoint is available to businesses of all sizes, from small startups to large corporations. It provides users with an easy-to-use interface for accessing documents, tracking tasks, and communicating with others. With SharePoint, organizations can quickly and easily share files, track projects, assign tasks and hold discussions.

As a result, employees can work more efficiently, complete tasks faster, and improve collaboration between teams. SharePoint also provides users with a range of other features, such as analytics tools, enterprise search capabilities, document libraries, and mobile access. These additional tools make it easier for businesses to manage their data, stay organized and collaborate from any location.

How to use SharePoint?

SharePoint can be used to store, manage and share documents with colleagues. It also provides a platform for collaboration on projects so that teams can easily work together on tasks in real-time. SharePoint allows users to create custom webpages, such as intranets and extranets, which can be used for team collaboration or client interaction. Users can also access SharePoint from their mobile devices, making it easier to stay connected and collaborate on the go.

In addition to the features mentioned above, SharePoint also offers a range of other tools for managing documents, including document libraries, lists, workflows, and discussions. These features make it easier for users to organize and manage their data, resulting in improved efficiency and collaboration.

What is a SharePoint list?

A SharePoint list is mainly a data container, similar to an Excel table with rows and columns. These lists can ‌keep all types of detailed information on your clients, employees, products, or services.

SharePoint lists allow you to set up columns according to the data format, i.e., text, number, dates, currency, etc. You can attach photos or hyperlinks to make your list as detailed as possible.

Let’s first start by creating a SharePoint list by importing the data from Excel.

How to export Excel data to a SharePoint list?

To export Excel data to a SharePoint List, you need to make sure of three things:

• You have formatted the data as a table.
• You have named the table.
• You have named the columns.

This is how you can export an Excel table to a SharePoint list.

1. Open your Excel workbook and select the whole table. Then, go to Table Design > Export > Export Table to SharePoint List.

2. Enter the URL of your SharePoint site in the “Address” bar.

3. Tick the box “Create a read-only connection to the new SharePoint list”. This allows the new data to show up in your Excel table when you add or refresh your SharePoint list. Excel keeps this link as a data connection, which you can refresh from the Data tab. Our guide to Excel connections explains how they work.

4. Type in the “Name” for the SharePoint List. Optionally, you can add further information in the “Description” box. Then, click “Next”.

5. Review the data types per column in case you need to make any changes. Once you’re ready to export, click “Finish”

6. Go to your SharePoint site to check that it has exported the table correctly. It will appear under the “Activity” group, as shown below.

Now that you know how to export an Excel table to a SharePoint list, let’s see how you can do the reverse process.

How to export a SharePoint list to Excel?

To export data from a SharePoint list to Excel, follow these steps.

1. Go to the SharePoint site and click on the SharePoint list you just created to open. Go to Export > Export to Excel. This will export your list as a “query.iqy” file.

2. Open the “query.iqy” file and click on “Enable” if you get the “Microsoft Excel Security Notice” alert.

If you only need a static copy, choose Export to CSV from the same Export menu and import the CSV into Excel, or turn it into an Excel file with our free file converter.

Let’s see how you can perform these same actions automatically using Power Query.

How to import a SharePoint list to Excel automatically?

Power Query is an automation feature in Excel that allows you to perform repetitive tasks automatically. Unlike the read-only connection set in the previous method, this will give you more control over which data in the SharePoint list you would like to import or export.

However, to import or export a SharePoint list in Excel using Power Query, you will need to subscribe to an Enterprise Plan.

1. Open your Excel spreadsheet and go to Data > Get Data > From Online Services > From SharePoint Online List.

2. Enter the URL to your SharePoint site. Click “OK” to continue.

3. Select the SharePoint list to import. Then, a preview of the list should appear on the right.

Please note: All SharePoint lists assign a unique identifier to each column. This ID column is created automatically on SharePoint. Simply edit according to preference to exclude any unwanted column.

4. Select the columns to import by pressing down the Ctrl key and right-click on any of the selected headings to “Remove Other Columns”.

5. Once you finish, “Save” and “Load”. In the Power Query Editor, go to Home > Close & Load.

Now that you’ve set up your Power Query connections between SharePoint and Excel, you can quickly update your SharePoint list from Excel.

How to automatically update a SharePoint list from Excel?

To automatically update a SharePoint list from Excel, you can do so manually by simply clicking on the “Refresh” button under the “Table Design” tab. If you want Excel to do this automatically through a workflow, you can do so using Power Query, Power BI, or Power Automate. However, note that automating with Power Query requires you to have the Enterprise Plan. This is how you can quickly update a SharePoint list from Excel using Power Automate.

1. Launch Power Automate and go to “Templates” on the left-side menu.

2. Type in “When a file is modified in OneDrive, synchronize the changes to SharePoint” to quickly access the template.

3. Make sure you are logged in to all applications to proceed. Click “Continue”.

4. Select the folder where you have stored the Excel file to trigger the “When a file is modified” action. Then, select the SharePoint folder where you saved the list. Once you finish, click “Save”.

5. Test the flow to check that it runs correctly by clicking on the “Test” button to the far right.

Additionally, Power Automate may ask you to make a change in your Excel file to trigger the flow. You can also create an automatic trigger. However, this requires a more complex setup which you can learn in more detail here.

Conclusion

SharePoint has proven to be a great platform to store and manage data for any organization. Moreover, it provides powerful features to reach advanced levels of collaboration across various departments within a company.

In this article, you have learned how SharePoint and Excel integrate, so you can export and import data manually and automatically using the Power Query tool. You simply have to consider that the features offered for Office 365 are more limited in the Business Plan than in the Enterprise Plan.

If you’re interested in unlocking the full potential of Office 365 tools and creating your own workflows between apps, read Power Automate & Excel: examples and use cases.