In the previous chapter, we explained the history of Microsoft Lists and reviewed the core functionalities that are available. In this chapter, we will look at the different ways we can create our own lists. We can do this manually by starting with a default, empty list, and adding additional columns as needed. Alternatively, we can reuse existing list structures and create new lists based on Excel spreadsheets or other lists. Finally, we will review how to maintain data in lists.
This chapter will cover the following topics:
- Personal lists and team lists
- Creating a blank list
- Adding and maintaining columns on a list
- Creating a list from Excel
- Creating a list from an existing list
- Maintaining list data
Personal lists and team lists
Before we look at how to create lists, first, we need to establish the different scopes that they can be set up in. When you create a list, you need to decide whether the list should be made available to a small scope of people or just yourself, or whether there is an existing group of people that should be allowed to access and possibly contribute to it.
For the first case, you would have to set up the list so that it's stored in the My Lists space, which creates it within your personal OneDrive account in Microsoft 365. Initially, only you will be able to access the list and its contents until you share it with others.
However, often, you already have an existing group of people that collaborate on a SharePoint site or within a team, and you want to create a new list that all those people have access to. In such cases, you would have to create your list within that existing space and make it available immediately to everyone with existing access. Both these cases will be considered in this chapter, and further information around how to share a list with more people will be provided in Chapter 4, Collaborating on Microsoft Lists.
An important thing to note is that you will only be able to create lists in your own OneDrive account, as well as in SharePoint sites where you were given the rights to do so. This is the case when you are added as a member or owner of a SharePoint site through a Microsoft 365 Group, for example, which also happens when you get added to a team in Microsoft Teams. For sites that you have no access to, or sites that you were only given view rights to, you will not be able to create a list.
Creating a blank list
When it comes to creating a new, blank list, there are different starting points. You can create a new list from the home page of a modern SharePoint site, from the Lists app in Teams, or from the Lists app in the Microsoft 365 app launcher. In the following subsections, each of these options will be explained. However, as there are many similarities between the three different options, including common functionalities, steps, and options, they will only be explained once; any differences will be highlighted.
Creating a new list from the Microsoft Lists app
As the Lists app in the Microsoft 365 app launcher provides the most flexibility, we will look at what benefits we get from creating a list there. Once you've navigated to the Lists app, you will see a New list button at the top of the page. Clicking on it provides you with the Create a list dialog, which provides various options you can choose from:
There are three options for creating a new list, and the list templates we explained in the previous chapter are at the bottom of the dialog. To create a new list not based on one of the templates, you can start from the following entry points. You can either start with a blank canvas or, depending on any already existing data structures you may have, use an existing spreadsheet containing data or another list as a base:
- Blank list: This option allows you to create a new, empty list without any specific structure or data in it. It is used as the standard starting point in most cases. While this option provides you with the simplest initial setup, it also gives you the greatest flexibility in terms of how to set up your list.
- From Excel: If you already have a structure set up in an Excel spreadsheet, likely containing some existing data, then this is the best option for creating a new list while using it. You would choose this option, for example, when you are already tracking information in a spreadsheet and want to convert it into a list to make use of additional functionality. This option will be covered in detail in the Creating a list from Excel section of this chapter.
- From existing list: Finally, if you already have an existing list that you want to duplicate, this option allows you to do so. For example, if there is an asset tracking list in use that another department is also interested in, you could use this option to create a new copy. We will explain this in the Creating a list from an existing list section.
- Name defines the display name of your list. It is recommended that you choose a unique and descriptive name right at the beginning, as the list name is the first thing that people usually see before they see a description or data. Thus, the more specific you are here, the easier it is for people to understand the purpose of the list.
- Description allows you to provide further information about the purpose of your list. As an example, if you have an "Asset Tracking List" to manage your physical IT assets, an appropriate description could be "This list is used to manage the life cycle and allocation of all physical IT assets, including notebooks, speakers, and others."
- Choose a color is one of two options that helps you define a logo for your list. You can choose from 12 different colors to use as the background color for the logo.
- Choose an icon is the second option for defining your logo. As with the colors, there are 12 different choices available to you, including a calendar icon, an airplane icon, and a tracking chart icon. The icon you select here will also use your selection from Choose a color as the background color to provide you with a preview of the final logo.
- Save to is an option where you will need to make a very conscious choice:
Here, you decide where your list will be created, a choice that cannot be changed as easily without additional migration or copy and paste effort compared to the other options. You can choose to save to My lists, which will create a personal list in your OneDrive account that you can share later with others. Alternatively, you can select from a range of Recent sites, which includes regular SharePoint sites as well as sites connected to Microsoft Teams. Selecting one of those will create the list in this site, and any existing access rights to the site will also apply to your new list.
- Site navigation is an option that only appears if you have selected a SharePoint or Teams site under Recent sites in the Save to field. It allows you to define whether your list should be displayed in the left-hand navigation of the site where you want to create the list, and thus be easily accessible to site visitors.
In this chapter, we will use the following scenario for our list:
Your company does not use any software system that allows you to track and manage your annual leave. You want to use Microsoft Lists to keep note of any leave requests that you made, their status, and when the leave is scheduled for. You are planning to call this list Leave Tracker, and you will not share it with anyone else initially, so you will create it within My Lists.
Once you click Create, the list will be created in your selected location, and your browser will navigate to it:
As you can see, it only contains the default column called Title, which is available in every list, and no data has been added yet. Adding additional columns to your list will be covered in the Adding columns to a list subsection.
As we mentioned previously, you can change some of your list settings again once you've created the list. When you are viewing the contents of a list, clicking on the name of the list in the top-left corner will make a small dialog appear with familiar options:
This allows you to change the name and the logo of your list later if needed.
Creating a new list in a SharePoint site
If you want to create a new list in a SharePoint site directly, you can do so by navigating to your site's home page or Site contents page and selecting List from the New dropdown. The dialog that appears will show the same Create a list options and templates as in the Microsoft 365 app launcher. However, selecting Blank list will show you a slightly different next step:
Once you have entered all the required information and clicked Create, the list will be created within the site and you will be redirected to it.
Creating a new list in Microsoft Teams
The third option for creating a new list is available in Microsoft Teams. Usually, there are already people collaborating within Microsoft Teams and leveraging the flexibility of creating and using dedicated teams and channels to focus on specific work. The Lists app can be added to a team and is then available in all the channels within this team. You can create a new list for and within a selected channel, and anyone who has access to the channel can then work on data within that list.
Once the tab has been added, you have the option to either create a new list or add an existing list to be displayed within this tab. If you choose to add an existing list, it should be noted that the list does not have to reside in the current team and its dedicated SharePoint site; instead, it can be a list that has been created in another SharePoint site:
Once you click Create a list, a familiar view will be shown in the tab, providing you once again with the options to create a blank list, create one based on an Excel spreadsheet or a duplicate of an existing list, or select from predefined templates:
Once the list has been created, members of your team can work with it directly within Microsoft Teams by accessing it from the Channel's tab.
In this section, you learned how to create a new blank list from the Microsoft 365 app launcher, a SharePoint site, and from within Microsoft Teams. Next, you will learn how to configure your list according to your needs by adding an appropriate structure for your data.
Adding and maintaining columns on a list
Columns are used to provide structure for lists and store individual pieces of data. Now that you have created your first list, you will want to add some additional columns to your Leave Tracker so that you can track information properly.
There are various column types that can be used, all of which will be shown in more detail in Chapter 3, Microsoft Lists Core Features. In this chapter, we will use a subset of them to explain their purpose and usage. To continue with the Leave Tracker list, we are going to add the following columns with the corresponding column types:
- Start Date: A Date and Time column that will be used to track the beginning of when you take leave
- End Date: A Date and Time column that will be used to track the last day of when you take leave
- Days: A Number column where we manually provide the number of days in the leave period
- Leave Type: A Choice column to track whether it is Annual Leave, Medical Leave, or Others
- Status: A Choice column to track whether it is Planned, Approved, Cancelled, or Taken
Adding columns to a list
To add a new column, open your list. At the top of your list is a header bar showing various columns that are available. How you can control the visibility of these columns, and how you can manage different views to provide various viewpoints of your data, will be covered in Chapter 5, Creating Microsoft Lists Views.
Selecting Add column provides you with a list of the available column types that can be used for your new column:
Once you've selected any of these types, a new pane will open on the right-hand side of your browser, giving you even more fields that you can fill in and provide information about the column that you want to create.
The first three fields are always shown by default:
- Name asks you for the name you want to use for your new column.
- Description allows you to provide further information about the purpose of your column.
- Type lets you decide on the column type you want to use. The type you chose initially is selected here, but you can still switch to any of the other types during the creation process. Please note that once you have made a selection and created a column, it is not always possible to switch the type. While some types allow you to change to another type after column creation, such as from Single line of text to Multiple lines of text, other types, such as Person or Group, are not as flexible and are locked after creation. Due to this, you should be clear about the type of data you want to store in your column from the beginning and make a conscious choice.
When you choose a Type, the bottom section of the pane gets updated dynamically with additional configuration options. We will look at all the column types and their respective options in Chapter 3, Microsoft Lists Core Features. For example, for the Date and Time column, you can decide whether it should be a date-only field, whether the time should be included as well, and whether the column data should be displayed in a friendly format. Friendly format means that instead of having the date shown in your regular locale, such as 3/2/2021, a more appealing format will be used and might say "4 days from now" or "March 2," as an example. In addition, you can define a Default value to be used for any new items, and you can set the column to be optional or mandatory:
As we mentioned previously, different types offer you different configuration options. The Choice column type, for example, allows you to define different available and valid choices, and even a supporting color for each that will be used as the background color to highlight the value. That way, you not only let people who view your data see the different values, but make it more appealing to them and help them differentiate between these values easily. If you use a Choice column to keep track of projects, you can easily create a Red, Amber, Green (RAG) status column to let project managers select the corresponding status value, such as on track, while also providing a visual clue about the value itself:
Editing and deleting columns from a list
Often, you will find yourself in a situation where you need to modify your column's settings. This could be changing your Single line of text column to a Multiple lines of text column, switching the Friendly format option of your Date and Time column on/off, adding an additional option to a Choice column, and so on. In other cases, you will want to delete a column completely, such as when it does not capture any relevant information and you do not require it or any previously captured data anymore.
To make any such changes, you need to access your list's settings. This can be done by clicking on the gear icon in the top-right corner of Office 365 and selecting List settings while you have your list open. While the menu that appears when you click on the Gear icon is context-dependent, which means that, in a SharePoint site, you will see different additional options compared to what you do in the Microsoft Lists app, the List settings option will appear in both cases:
On the List settings page, you will find a dedicated section for the columns of the selected list. All the columns are listed with their respective types, and it is specified if a column is mandatory:
Clicking on a column's title will bring you to the column's settings page. This page shows all the available type-specific properties and provides you with information about any potential type changes. As we mentioned previously, not all types can be switched to another type once a column has been created, and such an option may not be available:
In the Additional Column Settings section, you can update the column's available options, similar to when you created the column initially. For the Days column, which uses the Number type, you can specify Number of decimal places as 0 so that only whole numbers are accepted, and even provide a minimum and maximum allowed value:
At the bottom of the page, you will find three buttons to complete your desired action. You can click Cancel to discard any changes, click OK to save all the updates that you've made, or click Delete to remove your column from the list permanently.
Deleting a Column
When you delete a column from a list, all the information that was entered previously will be removed as well. If you want to retain the data but do not want the column to appear anymore in your list's public views, you can simply hide the column from any such locations.
Deleting a column and the corresponding data is a process that cannot be reversed. Once you've deleted it, the column and the data cannot be restored anymore.
In this section, you learned how to set up your list with a structure for your data, and you also know how to add, edit, and delete columns. This way, you can create and maintain lists according to your requirements easily. However, there are also other, more efficient ways to create lists from an existing structure, all of which will be covered in the next two sections.
Creating a list from Excel
Often, you won't want to create a new list when starting from a blank canvas, as you may have already defined the data structure and potentially even have existing data. A lot of corporate and personal information resides in Excel spreadsheets. In some cases, you may want to take this information from an existing spreadsheet and put it into a list, in order to make use of additional functionalities such as formatting columns or creating a workflow to respond to changes.
If we have a spreadsheet where we keep track of our annual leave, we will likely have the structure of our information already defined, and will also have some data already available in this spreadsheet:
As we saw previously, Microsoft Lists can be created from such existing Excel spreadsheets. Generally, it is highly recommended to review and clean up the data inside your spreadsheet before you perform any imports. Any data that exists in the table you want to import from your spreadsheet will be added to your list as new items. But if you do not want to import everything, performing a cleanup in Excel beforehand is easier than doing the cleanup afterward in your list.
When you start the process of creating a new list, you can select From Excel as a starting point. The dialog that appears allows you to either upload a local file or select an existing file from the current site's Documents library:
Once you have selected your spreadsheet, the import process will start by examining the file for any existing tables. If no tables are available in your file, you will be informed about how to format your data accordingly to make use of the import functionality:
If tables are found in your document, the import process will ask you to select the table to be used. If there are multiple tables in your spreadsheet, you need to define which single table should be used. You can also review the identified column from the selected table and define which column type to use, respectively. Depending on the format of the data in your spreadsheet, different options are available.
For example, a column that has been formatted as a date or as a number in Excel can be set up as text, number, choice, date, or currency in your list:
If desired, you can also choose to exclude columns from your table by selecting Do not import as the column type. It should also be noted that not all column types can be imported. Complex types, such as Person or Group, can't be imported easily, so they can't be selected. When you import a spreadsheet, you should consider this limitation and plan for it accordingly. Further configuration and manual data imports might be required.
Once you've confirmed the column configuration, you can define a name and description for your list and complete the process. The list will then be set up with the selected columns, and any available data from the spreadsheet will be imported into it:
Instead of creating a blank list and adding columns manually, you now know how to leverage the From Excel creation functionality to easily and quickly set up a new list based on an existing spreadsheet. As a lot of corporate data is often stored in Excel spreadsheets first, this method is extremely useful for converting such spreadsheets into a list in a convenient manner.
With that, we have seen how to create our own lists by starting from scratch and adding relevant columns as needed, and we have also looked at how to create a list based on an existing Excel spreadsheet. The next step is to learn how to duplicate a list that already exists.
Creating a list from an existing list
In many cases, you have an existing list for a specific purpose, but you may also have some interest in it from different groups. For example, the Leave Tracker list, which we are using in this chapter, is something that other colleagues or even teams could be interested in. Instead of setting it up the same way repeatedly, it is more efficient to create a copy for new audiences whenever it's needed.
In the New list creation dialog, once you've select the From existing list option, you need to decide which list you want to duplicate. You can do this by selecting the team or site where the list has been created, followed by selecting the specific list:
Afterward, the familiar options dialog where you can define the list's name and other properties, among which is the destination location, such as a different site or My list, will appear again. Once you click Create, a copy of your list will be created in the selected destination, including all the previously created columns.
This method is useful when an existing list has been deemed valuable and should be used in other locations as well. Instead of setting up the same list structure again or using a third-party tool to create a copy, this out-of-the-box functionality provides you with a convenient way to create duplicates of a list.
Maintaining list data
Regardless of whether you've created an empty list or imported a list with data from Excel, the next step is usually to add some information to your list, followed by updating any existing information.
Adding items to a list
Depending on the column type, the individual controls are rendered differently. For example, for simple text fields, a normal text area will be shown, where you can enter required information. Where possible, more user-friendly controls are used. For date fields, date pickers are shown, allowing users to either enter the date manually or select it from the date picker control. Clicking Save will create the new entry, whereas clicking Cancel will close the dialog without storing anything that was entered.
Another way to add information is to use Edit in grid view mode, as you will see in the next chapter. While this mode is mainly used to edit existing entries, you can also use the Add new item row at the bottom of your list to create a new entry and add relevant data directly into the cells shown:
One benefit of doing so is that items are rendered directly on screen. As an example, for a Choice column with entries that are different colors, you will be able to see these background colors when you're entering your data, something that is not available in the regular New entry form.
Editing items in a list
To edit an item, you can click on the item's Title value, which opens a preview pane. Here, you can edit the column values individually, or you can click on Edit all at the top to open the full edit form. The edit form can also be accessed by clicking on the ellipsis of the item and choosing Edit:
It is also possible to edit items individually or in bulk in Edit in grid view mode. One of the benefits of doing so is that you can easily update a column for multiple items with the same value. You can select multiple cells within the same column by selecting the first item to be selected, pressing the Shift button, and finally selecting the final item to be selected. All the cells for the items between these two will be highlighted, and you can paste a value into all of them in one go. Alternatively, if you want to copy the value from the first cell into the cells below, you can select the first cell by clicking on the small square in the bottom-right corner of it and dragging it down across all the cells to be filled with the selected cell's value:
Any changes that you've made to items and cells will be saved immediately in the grid view, as well as in the preview pane. Only in the full edit form are you required to click on Save to save your changes.
Deleting items from a list
The last action we want to cover is deleting items from a list. When an item is no longer required, you can right-click on it and select Delete either from the menu that appears or from the options menu on top of the list. If there are multiple items you wish to delete, you can also select all of them first by clicking on the checkbox in front of each relevant item, and then selecting Delete:
Items that you delete will be moved to your site's recycle bin, where they will remain for the duration of the retention period that has been set up. The default duration for this is set to 93 days, during which you are still able to restore deleted items when needed.
A list without any data in it does not provide any value. In this section, we have shown how to maintain entries in your list by adding new items, editing them, and also deleting them when they are no longer required. This equips you with the knowledge to utilize your lists efficiently and to start using them within your organization.
In this chapter, we went through the various available processes for creating a new list and explained the differences between them. We also added additional columns to our list and looked at how to manage and maintain list data.
In the next chapter, we will review the available out-of-the-box Microsoft Lists features that can help you set up, manage, and use your lists effectively.