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 was introduced in Microsoft 365 in 2020, but many users are still not using the app. Lists are a way to collaborate with others to manage work or tasks. You can create and share lists to keep track of 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 fast, simple, and infinitely easier to implement than Excel user forms or even Microsoft Forms. In this tutorial, I will show you how to create a Microsoft List 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 get data from an Excel file, but Lists doesn’t limit you to Excel – you can upload data from other spreadsheet software. You need a Microsoft Work or Home account to take full advantage of Lists. Personal accounts are not as robust. Listings have been around for a while, but it’s possible your interface artwork doesn’t match mine. Don’t worry if that happens.
How to create a list in Microsoft 365 using Excel data
Why lists instead of Excel? Lists give you control over how you see the data. Once the data is in lists, you can view it as a grid, map, or calendar. You can set rules that will notify you when something changes. Lists show their full 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 exist in your organization.
Using existing data in a Microsoft Excel file doesn’t require much preparation. However, you cannot think like an Excel user; You have to think like a Lists user. Microsoft Lists assumes that the leftmost column is a Title data type – this is the default column. Depending on how you use your Excel spreadsheet, its structure may not match the structure of lists. If you can move columns at the source, the .xlsx file, you can save yourself a bit of work in lists.
You can work with your own file. If you want to work with your own data, the leftmost column defaults to the Title data type.
There are three ways to create a list in Microsoft Lists: from scratch, by template, and from Excel. The path is the same for all three, up to the point where you need to select Excel as the data source:
- Launch Lists from your Microsoft 365 account as you would any other Microsoft 365 app (Figure A).
- Click the Microsoft 365 Apps launcher and select Lists. If you don’t see Lists, click All apps, and then select Lists.
- If you haven’t used Lists before, chances are the app will prompt you to install the latest upgrade. If you update now, you may need to restart Lists when you’re done.
- At the top of the page, click New List (Figure B).
- On the resulting page (Figure C), you can choose one of three ways to create the list.
- At this point, you separate yourself from the steps for creating the individual lists. To get data from Excel, click the From Excel tile.
- You can upload from your local system or OneDrive. The .xlsx demonstration file on my system is under Downloads (Figure D). Locate and select your file wherever it is. Listen uploads the file to the OneDrive library. Lists does a great job of correctly delineating your data (Figure E). If the Upload File button is grayed out, you don’t have permission to create a list from Excel. Ask your administrator to change the permissions.
- The first control shows the table objects in the XLSX file. In our case there is only one, Sales. Use the drop-down menus above each field to set the correct data type. Leave the data type Title for the Region column. Date is date and time, and amount is currency (Figure F).
- Click next. In the next window add a description, choose a color and a place to save the list (Figure G).
- Click Create.
As you can see in it Figure HMicrosoft imports and formats the Excel data.
To enter new data, click New and Microsoft Lists opens a data entry form. Click Share to share the list with others in your organization.
If you click Export, you can export the current list data to .csv and .xlsx format. Automate lets you set a reminder or create a rule that notifies you when certain conditions are met. With Integrate, you can send the file to Power Apps, Power Automate, and Power BI.
Microsoft Lists assumes the leftmost column is the default column and automatically assigns the bibliographic data type, which cannot be changed. This is not a mistake; This is how the app works. In a future article I will discuss this behavior and show you how to deal with it.