Selected cell in spreadsheet excel
Image: PixieMe/Adobe Stock

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. You can download the demo file for this Excel tutorial.

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 How to use Microsoft Lists to organize Excel data you must track or share.

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

We’ll base a Microsoft Lists list on this Excel data set.

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

Select the Excel file.

Figure C

Microsoft Lists does a good job of delimiting the data.

Figure D

Share the list with others.

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

Figure E

Your list is ready to use, but don’t make any changes yet.

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

Select the Microsoft list ExcelSyncList.

Figure G

Export to Excel.

Figure H

Choose the export settings.

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

Microsoft Lists exports the list to an Excel query.

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

Add a new record to the list.

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

Excel adds the new record added to the list.

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

Import the web query .xlsx file.

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

Import the entire list into Excel.

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.