Are you tired of updating data manually in both SharePoint and Excel? Meet the revolutionary solution: two-way synchronization. This smart process lets you say goodbye to the repetitive task of transferring data back and forth. Let’s find out how to connect as well as automatically export the SharePoint list to an Excel table and vice versa using two way synchronization.
Table of Contents
How Two Way Synchronization Between SharePoint List and Excel Table Works?
Businesses need effective data management and real-time communication to succeed in today’s fast-paced environment. The two-way synchronization of Excel tables and SharePoint lists is one technique to accomplish this. This procedure enables smooth data interchange, ensuring that data is accurate and current on both platforms.
With two-way synchronization, any edits you make in your SharePoint list will instantly be mirrored in your Excel table, and vice versa, as the Excel and SharePoint lists get connected and synced. Now, let’s see how two way synchronization between the SharePoint list and Excel table works:
1. First, download the SynchronizeWSSandExcel.xlam add-in on your PC.
2. Then, go to the Downloads folder in your File Explorer app and Extract the SynchronizeWSSandExcel.xlam add-in.
3. Now, open the Microsoft Excel app on your PC and launch the desired sheet.
4. Click on File > Options from the left pane.
5. Select the Add-ins tab from the left pane.
6. From the Manage: drop-down field, select the Excel Add-ins option and click on Go…
7. Then, click on the Browse… option and Open the extracted SynchronizeWSSandExcel add-in to add it to Excel.
8. Now, click on the target table in your sheet and switch to the Design tab from the top.
9. From the top ribbon bar, click on the Publish and allow Sync option.
10, Then, enter your SharePoint details in the following field boxes connect Excel to SharePoint list:
11. After entering the required details, click on the Publish option.
12. Now, after making the desired changes in your Excel sheet, right-click on the target table and select Table > Synchronize with SharePoint to manually sync the table.
It is how to connect and synchronize the SharePoint list and Excel data.
How to Export Excel to SharePoint List?
You can easily export the data if you have an Excel sheet that is organized with columns that correspond to the desired format in your new SharePoint list. By following these easy steps, you can export this entire data from Excel.
Method 1: Enter Data Manually in SharePoint List
It is simple to export data from Excel to a SharePoint List manually. This approach uses step-by-step procedures to ensure smooth data transfer between the two platforms.
1. Open your target Excel file containing the table you want to export.
2. Go to your team site in SharePoint Online.
3. Click on List to create a new list and give it a name and description.
4. Add columns with the same names and data formats to the new list.
5. Click on Edit in grid view in the SharePoint list.
6. Select target data from an Excel column and Copy it.
7. Paste the copied data into the appropriate SharePoint column.
8. Click on Exit grid view to finish the data transfer.
Method 2: Export Data Directly
Directly moving data from Excel to a SharePoint List will streamline the export procedure. Simply follow these steps to lessen the need for manual copying.
1. Open the target Excel sheet and enter the desired data you want to export to SharePoint Online.
2. Select the entered data and click on the Insert > Table option from the top.
3. Keep default values and click OK from the popup in the Create Table window.
4. Select a column name and select the Design tab.
5. Enter the desired table name.
6. Click on Export >Export Table to SharePoint List…
7. Enter your SharePoint team site address.
8. Provide a name for the table.
Note: It will become the SharePoint list name.
9. Enter a Description (e.g., Import Excel to SharePoint list) and click on Next.
10. Define data types for each column and click Finish.
Note: Excel prompts for Office 365 authentication (username and password). If correct, you’ll see a success message with a link to the list.
11. Open the link in a web browser to view the exported list.
Also Read: Explain Workbook Protection Types in Excel
Method 3: Export Excel Data When Making a New SharePoint List
You can often import data directly from an Excel file while creating a new SharePoint list. Follow these steps to enhance control and achieve synchronization in both directions.
1. Open your SharePoint Online team site.
2. Click on the Settings gear icon and select Site contents.
3. Then, click on New and select the List option.
4. Click on From Excel on the new page.
5. Enter a name for the new list.
6. Click Upload file and choose the target Excel file.
7. Click on Next to continue.
8. Check column types for consistency and click on Create to finish.
How to Export SharePoint List to Excel Automatically?
Follow these steps to export data from a SharePoint list to Excel automatically.
1. Open the SharePoint list you want to export.
2. Click on Export to Excel. Excel will automatically open with a data connection to the SharePoint list, populating the Excel sheet with the list data.
Note: Your list will be exported as a query.iqy file.
3. If the Microsoft Excel security notice appears, open the query.iqy file and click on Enable.
4. After the SharePoint list is in Excel, save it in the desired format (XLS or XLSX).
5. Click on the Data tab from the top and select the Queries & Connections option.
6. Click on the Refresh icon > three-dotted icon > Properties option.
7. Select the checkboxes for the following options:
- Enable background refresh
- Refresh every [desired time]
- Refresh data when opening the file
- Refresh this connection on Refresh All
8. Lastly, click on OK.
How to Import Excel to SharePoint?
To effectively import Excel data into your SharePoint setting, follow these methods:
Method 1: Using Import Spreadsheet Feature
It has always been a bit tricky to import Excel to SharePoint. By using the Import Spreadsheet option, you can easily upload the Excel sheet directly to the website. The steps are as follows.
1. Open your SharePoint Online team site.
2. Click on the Settings gear icon > Add an app option.
3. Now, search for the Import Spreadsheet app.
4. Locate and click the Import Spreadsheet app.
5. Enter a name for the new SharePoint list.
6. Add a description to the list.
7. Click Browse and select the target Excel file to import.
8. Click on the Import option to proceed.
Method 2: Using Quick Edit Feature
You can instantly import the data from the Excel spreadsheet into the list you previously made using the quick edit option in a SharePoint list. Here’s how to do this.
1. Log in to your SharePoint site.
2. Create a new SharePoint list or select an existing list.
3. Create columns similar to your target spreadsheet.
4. Click on Add Column and choose desired metadata options.
5. Click on the Quick edit option.
6. Copy a row from Excel and Paste it into the relevant list row to the SharePoint list.
We hope you have understood how two way synchronization between SharePoint list and Excel table works. Now, you can save time, reduce errors, and streamline your data management workflow. Let us know your thoughts in the comments below, and explore our website for more insightful content.