Don’t forget to share this post

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.

In this article, 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.

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 Combine Multiple Excel Files Into One

Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master file

READ MORE
How to Combine Multiple Excel Files Into One

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. 1. Open your Excel workbook and select the whole table. Then, go to Table Design > Export > Export Table to SharePoint List.
Import And Export From Share Point To Excel Export Table to Share Point List
Import And Export From SharePoint To Excel - Export Table to SharePoint List
  1. 2. Enter the URL of your SharePoint site in the “Address” bar.
Import And Export From Share Point To Excel Share Point site URL
Import And Export From SharePoint To Excel - SharePoint site URL
  1. 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.
Import And Export From Share Point To Excel Create read only connection
Import And Export From SharePoint To Excel - Create read-only connection
  1. 4. Type in the “Name” for the SharePoint List. Optionally, you can add further information in the “Description” box. Then, click “Next”.
Import And Export From Share Point To Excel Name and Description
Import And Export From SharePoint To Excel - Name and Description
  1. 5. Review the data types per column in case you need to make any changes. Once you’re ready to export, click “Finish”.
Import And Export From Share Point To Excel Finish to export
Import And Export From SharePoint To Excel - Finish to export
  1. 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.
Import And Export From Share Point To Excel Check Excel in Share Point
Import And Export From SharePoint To Excel - Check Excel in SharePoint

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. 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.
Import And Export From Share Point To Excel Select Share Point list
Import And Export From SharePoint To Excel - Select SharePoint list
  1. 2. Open the “query.iqy” file and click on “Enable” if you get the “Microsoft Excel Security Notice” alter.
Import And Export From Share Point To Excel Enable
Import And Export From SharePoint To Excel - Enable

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

How To Use Excel Power Query for Beginners?

With Power Query, you can import data from different sources and prepare the data for analysis. Here's how to use Power Query in Excel.

READ MORE
How To Use Excel Power Query for Beginners Promotion

How to import a SharePoint list to Excel automatically?

PowerQuery 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. 1. Open your Excel spreadsheet and go to Data > Get Data > From Online Services > From SharePoint Online List.
  2. 2. Enter the URL to your SharePoint site. Click “OK” to continue.
  3. 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.

  1. 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”.
  2. 5. Once you finish, “Save” and “Load”. In the Power Query Editor, go to Home > Close & Load.

Now that you’ve set up your PowerQuery 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 PowerQuery, Power BI, or Power Automate. However, note that automating with PowerQuery requires you to have the Enterprise Plan. This is how you can quickly update a SharePoint list from Excel using Power Automate.

  1. 1. Launch Power Automate and go to “Templates” on the left-side menu.
Import And Export From Share Point To Excel Templates
Import And Export From SharePoint To Excel - Templates
  1. 2. Type in “When a file is modified in OneDrive, synchronize the changes to SharePoint” to quickly access the template.
Import And Export From Share Point To Excel Type in template name
Import And Export From SharePoint To Excel - Type in template name
  1. 3. Make sure you are logged in to all applications to proceed. Click “Continue”.
Import And Export From Share Point To Excel Continue
Import And Export From SharePoint To Excel - Continue
  1. 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”.
Import And Export From Share Point To Excel Fill in flow details
Import And Export From SharePoint To Excel - Fill in flow details
  1. 5. Test the flow to check that it runs correctly by clicking on the “Test” button to the far right.
Import And Export From Share Point To Excel Test flow
Import And Export From SharePoint To Excel - Test flow

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.

How to import and export Excel data with Layer?

Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you can:

  • Manage Access: Give spreadsheet access on a tab or cell level to relevant stakeholders.
  • Collaborate: Automate tasks, set deadlines, and communicate on top of your files.
  • Review & Track: Consolidate input, track changes, and restore previous versions.

Sign up to get started with Layer right now or schedule an onboarding call so we can help you set up and share your spreadsheet flows.

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 PowerQuery 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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published May 25 2022, Updated May 30 2022