How to sync Microsoft Lists with Microsoft Excel

A shared Microsoft 365 Lists list is more valuable if you can use it to update source data in Microsoft Excel.

The article How to use Microsoft Lists to organize Excel data you must track or share is an introduction to sharing Microsoft Excel data with other users via a list prepared by the Microsoft Lists app. As is, there’s no connection between the list and Excel. You can’t update the source data in Excel by updating the list. Nor can you update the list by updating the Excel data. For many, that might be a problem.

Fortunately, creating a connection between the list and Excel isn’t that difficult. In this tutorial, I’ll show you how to create a simple list based on Excel data and then create a temporary link between the two via a web query.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system. Earlier versions won’t support this connection. If you need help creating the list following this article’s instructions, read the above linked article.

How to create the list in Microsoft Lists

Lists are very easy to create, even though there are many steps. In this example, we’ll create a list based on the Excel data set shown in Figure A. You can use any Excel data you like, but I’m keeping it simple on purpose, so as not to distract from the technique. If you’re using the demonstration .xlsx file, save it to OneDrive and remember the location.

Figure A

To create a Microsoft Lists list from the Excel data shown in Figure A, do the following:

1. Make sure the Excel file isn’t open — you can’t load data from an open file.
2. Launch Lists from inside your Microsoft 365 account by clicking the Microsoft 365 apps launcher and choosing Lists. If you don’t see Lists, click All Apps.
3. If prompted to upgrade, do so. You may need to relaunch Lists when done.
4. Click New List at the top of the page.
5. In the resulting page, click the From Excel tile.
6. Click Upload File. If it’s dimmed, call your administrator.
7. Locate the demonstration .xlsx file on OneDrive, select it and click Open. Lists will upload the data to the OneDrive library.
8. If you have more than one Table in the Excel file, select the appropriate table from the Select a Table From This File dropdown. Excel does a good job of delimiting your data, as shown in Figure B. Leave the Title data type for the Region column. Change Date to Date and Time, and Amount is Currency (Figure C).
9. Click Next.
10. In the next window, add a description, choose a color and My Lists as location to save the list (Figure D).
11. Click Create.

Figure B

Figure C

Figure D

Figure E shows the resulting list ready to share and use. With a list in place, let’s create a link.

Figure E

How to link the list to Excel

Once you have a shared list in SharePoint, you’re ready to make the connection. To do so, return to OneDrive and use the App Launcher to launch the SharePoint site that contains the list you want to sync with Excel. Then, do the following:

1. Click My Lists in the left navigation pane and then click ExcelSyncList (Figure F), the list you created in the last section.
2. From the Export dropdown choose Export to Excel (Figure G).
3. If prompted, confirm the operation by clicking OK.
4. In the resulting dialog, click Open and then click the Open File link. I’m using Microsoft Edge, so this process might differ a bit with a different browser.
5. If prompted to enable data connections, click Enable.
6. In the resulting dialog, select Table in the Select How You Want to View This Data In Your Workbook option.
7. In the Where Do You Want to Put the Data section, choose New Workbook (Figure H).
8. Click OK.

Figure F

Figure G

Figure H

At this point, the connection isn’t with the original Excel workbook. Instead, Excel creates a connection to a web query, as you can see in Figure I. This means Excel doesn’t make the changes automatically.

Figure I

Now, let’s add a record to the list and watch it update the web query. To do so, return to the list and click New to add a new record, as shown in Figure J. Then, click Save.

Figure J

To update the web query, return to that file in Excel, click the Data tab and then click Refresh All in the Queries and Connections group. Figure K shows the updated records. Save the updated web query as an Excel .xlsx file in OneDrive and close it. You’ll need to remember where you saved this file later.

Figure K

Open the original Excel workbook — the demonstration workbook — to update it via the web query that you just saved as an .xlsx file. Then, click the Data tab and then click the Get Data option in the Get & Transform Data group. From the resulting submenu, choose From File and then choose From Excel Workbook.

In the resulting dialog, locate the web query that you saved as an .xlsx file, as shown in Figure L, and then click Import.

Figure L

In the resulting dialog, click Query_Table, and then click Load. Excel lists two data sets because the demonstration .xlsx file contains a Table and a normal data range.

As you can see in Figure M, Excel imports the entire table into Excel. It doesn’t append the new record, which would be more efficient and convenient.

Figure M

It seems like a lot of work, but that’s because we started from scratch. Once the pieces are in place, you need to only update the web query and then import into the source Excel file. Using Power Automate, you can automate most of this process. This isn’t truly a dynamic link, but for now, it’s the process we have.