How to use Microsoft 365 Lists to organize Excel data you must track or share

Microsoft 365 users can now use Microsoft Lists to track data.

If you work in Teams or SharePoint, you’re probably familiar with the concept of lists. This feature made its way to Microsoft 365 in 2020, but many users still aren’t using the app. Lists are a way to collaborate with others when managing work or tasks. You can create and share lists that help you track almost anything.

Lists are also a great way to collaborate, and you can use Microsoft Lists to create data entry forms for others in your organization. It’s quick, easy and infinitely easier to implement than Excel user forms or even Microsoft Forms. In this tutorial, I’ll show you how to create a Microsoft Lists list from Microsoft Excel data. You can download the demo file for this tutorial.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

Lists is available with Microsoft 365 on Windows 10 and 11. We’ll get data from an Excel file, but Lists doesn’t limit you to Excel — you can upload data from other spreadsheet software. You’ll need a Microsoft Work or Home account to get the full benefit of Lists. Personal accounts aren’t as robust. Lists has been around for a while now, but it’s possible that your interface graphics might not match mine. Don’t worry about it if that happens.

How to create a list in Microsoft 365 using Excel data

Why lists instead of Excel? Lists lets you control the way you see the data. Once the data is in Lists, you can view it as a grid, a card or a calendar. You can set rules that notify you when something changes. Lists really show their potential when you use them with Power Automate, Power Apps and even Power BI. You can easily replace user forms and Microsoft Forms with Microsoft Lists as long as users are in your organization.

When using existing data in a Microsoft Excel file, there’s not a lot to do to prepare. However, you can’t think like an Excel user; you must think like a Lists user. Microsoft Lists assumes that the leftmost column is a Title data type — it’s the default column. Depending on how you use your Excel sheet, its structure might not reconcile with Lists’ structure. If you can move columns around at the source, the .xlsx file, you can save yourself a bit of work in Lists.

You can work with a file of your own. If you choose to work with your own data, the leftmost column will be the Title data type by default.

There are three ways to create a list in Microsoft Lists: From scratch, by template and from Excel. The route for all three is the same, up to the point where you must choose Excel as the data source:

1. Launch Lists from inside your Microsoft 365 account, as you would any other Microsoft 365 app (Figure A).
2. Click the Microsoft 365 apps launcher and choose Lists. If you don’t see Lists, click All Apps and then select Lists.
3. If you haven’t used Lists before, the app will probably prompt you to install the latest upgrade. If you upgrade now, you might need to relaunch Lists when done.
4. Click New List at the top of the page (Figure B).
5. On the resulting page (Figure C), you can choose one of three ways to create the list.
6. At this point, you part ways with the steps for creating each list. To get data from Excel, click the From Excel tile.
7. You can upload from your local system or OneDrive. The demonstration .xlsx file on my system is in Downloads (Figure D). Locate and select your file, wherever it is. Lists uploads the file to the OneDrive library. Lists does a great job of delimiting your data correctly (Figure E). If the Upload File button is dimmed, you don’t have permission to create a list from Excel. Ask your administrator to change permissions.
8. The first control will display the Table objects in the .xlsx file. In our case, there’s only one, Sales. Use the dropdowns above each field to set the correct data type. Leave the Title data type for the Region column. Date is Date and Time, and Amount is Currency (Figure F).
9. Click Next. In the next window, add a description, choose a color and pick a location to save the list (Figure G).
10. Click Create.

Figure A

Figure B

Figure C

Figure D

Figure E

Figure F

Figure G

Figure H

As you can see in Figure H, Microsoft imports and formats the Excel data.

To enter new data, click New and Microsoft Lists will open a data entry form. Click Share to share the list with others in your organization.

Clicking Export allows you to export the current list data to .csv and .xlsx format. Using Automate, you can set a reminder or create a rule that notifies you when specific conditions are met. Integrate lets you send the file to Power Apps, Power Automate and Power BI.

Stay tuned

Microsoft Lists assumes the leftmost column is the default column and automatically assigns the Title data type, which is impossible to change. This isn’t a bug; it’s the way the app works. In a future article, I’ll discuss this behavior and show you how to deal with it.