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 fileREAD MORE
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.
- 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” alter.
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 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. 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 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. 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.
How to import and export data to Google Sheets?
Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.
Using Layer, you can:
- Share & Collaborate: Automate your data collection and validation through user controls.
- Automate & Schedule: Schedule recurring data collection and distribution tasks.
- Integrate & Sync: Connect to your tech stack and sync all your data in one place.
- Visualize & Report: Generate and share reports with real-time data and actionable decisions.
Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!
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.