There may be an instance where you require the need to sync an Excel spreadsheet and a SharePoint list to make it easy for you to manage data. For example, the format of SharePoint lists may work better with what you require in terms of data management. Or you may require the formatting abilities of Excel spreadsheets. This blog will cover the steps required to synchronize an Excel spreadsheet with a SharePoint list.
Below is a guide on syncing an Excel spreadsheet with a SharePoint list. For this process, you will require Power Automate, a tool that allows users to effectively create flows to automate tasks for the Office suite and software outside of Microsoft.
- Firstly, open Power Automate.
- Now click on “Connectors.”
- From there, click on “SharePoint.”
- After that, select “When an item is created or modified.”
- Input your site address.
- Input your “List Name.”
- Now click on “Action,” followed by “Condition.”
- In the “Choose a value” section, select “Created.”
- In the second “Choose a value” box, select “Modified.”
- In the “If No” tab, select “Add an action.”
- From the “Action” tab, select “Update a row.”
- Fill out all the information.
- Click on “Save” in the top menu bar.
- Finally, run a test to make sure the flow works.
- Get Excel for just $6.99 per month from the Microsoft Store!
That’s it; once the steps above have been completed, you will have successfully synced your SharePoint list to your Excel equivalent document. There are some prerequisites to using this method; you need to make sure both the Excel document and the SharePoint list are uploaded to the same Site. This process will only work on the Digital version of SharePoint; a post-flow-export of the Excel file will not conduct a sync on the desktop version, only the digital version as the flow is linked to that specific file.
You also need to be aware that the “Update a row” action will only sync one row/column at a time; you will need to repeat that process for each row/column you have on your document. The document must also match with the list; otherwise, the flow will trigger a failed prompt on unassigned rows.
   |
Recommended SoftwareIn-depth step by step guide [with screenshots] – How to two-way synchronize an Excel spreadsheet with a SharePoint list
Below is an in-depth guide on the steps above; these steps include a more in-depth process and screenshots to help you better understand the process within the guide illustrated. The visual context in the form of screenshots helps users to understand what the process is and what they require to proceed ahead.
- Firstly, open Power Automate.
You can only open Power Automate in the browser; use your browser to find the Power Automate site and sign into the program. You need to use your Office 365 login credentials to access the program.
- Now click on “Connectors.”
In the left-hand menu, there will be an option for you to select “Connectors.” The connectors are essentially plugin controls to help automated flows within Office programs.
- From there, click on “SharePoint.”
In the connections section, you need to find the SharePoint option, and from there, you can now open it.
- After that, select “When an item is created or modified.”
This is the main trigger flow we will be using to help sync content between the list and Excel document. The main trigger flow will allow for users to then build the final automated flow surrounding the trigger.
- Input your site address.
Here, you need to input your site address; this will show automatically when you click on the dropdown; if it doesn’t show automatically, then it may not have synced correctly; try typing out the site URL or input from the browser when prompted.
- Input your “List Name.”
Once the site address has been inputted, now find the list you want to sync with your Excel file. In this example, I will use the “Test doc” list. This list has a series of columns that need to be synced with the Excel document.
- Now click on “Action,” followed by “Condition.”
In the next step, you need to use the “Action” tab, which is a move directly committed by you; in this case, your edit input on the test document. Select a condition for that input.
- In the “Choose a value” section, select “Created.”
- In the second “Choose a value” box, select “Modified.”
- In the “If No” tab, select “Add an action.”
- From the “Action” tab, select “Update a row.”
- Fill out all the information.
- Click on “Save” in the top menu bar.
- Finally, run a test to make sure the flow works.
That’s it; once you’ve done the steps above, you’ll have successfully synchronized your SharePoint list to its Excel equivalent. There are a few requirements for employing this method, including ensuring that both the Excel document and the SharePoint list are posted to the same Site. This method will only function on the SharePoint Digital version; a post-flow-export of the Excel file will not sync on the desktop version of SharePoint, only the digital version, because the flow is linked to that specific file.
You should also keep in mind that the “Update a row” action will only sync one row/column at a time; you’ll have to repeat the process for each row/column in your document. The document must also match the list; otherwise, the flow would fail on unassigned rows, resulting in a failure prompt.
Why sync SharePoint lists with Excel documents
A major reason people choose to sync between their list and Excel documents is for convenience; users don’t have to constantly export lists to open in Excel documents or the opposite. With sync enabled, they can simply input the information on one, and the other will be simultaneously edited.
Conclusion
Thank you for reading through our content; we hope the information above has provided you with all the resources you require to sync information between Excel documents and SharePoint lists. If there are any areas of the document you don’t understand or have any issues with the steps illustrated, please add a comment below, and we will address those issues.