Learn how to use conditional formatting to customize SharePoint or Microsoft List forms. Bascially you can show or hide columns based on conditional values of other columns.
Check the video below to understand how it works, you can also read the transcript below
Transcript
Hello and welcome all to this new series and also first video of the series where we will go through a Microsoft 365 topic. Today’s topic is related to Microsoft Lists and specifically how to show or hide columns based on conditional formatting.
Conditional formatting is a concept which has been introduce recently in Lists and has gained lot of popularity for formatting list column, views, coloring, text formatting etc. Microsoft recently has added lot of options around this but the I will be showing today how to hide or show columns in forms based on value of other columns.
For this example I have created a “Content scheduler” Microsoft list where I am storing when to publish content for my YouTube videos, I have added two items and my objective is to show column “Published Link” when status is “Ready to publish” OR “Published” because the link is not yet ready in other cases, very simple.
So let’s start with this. The same formula works in all 3 forms that is new , edit and view =if([$Status]==’Ready to publish’ ||[$Status]==’Published’,’true’, ‘false’)
You can see that I have here used OR parameter and similarly you can also use AND or NOT operator. It is important to put a logic which is applicable of handling all forms. It took sometime for me to get the OR operator right but finally it worked.
There are certain limitations such as below column types cannot be used in conditional formatting
Person columns with multiple selections
Multiple choice column
Time calculations in DateTime column
Currency columns
Location columns
Calculated columns
Managed Metadata columns
Before this feature was added in SharePoint online we had to rely on Power Apps or SPFx development which I think was an over kill for such a thing but now this is available OOB and this really adds value to Microsoft Lists.
So ya that’s it in this video if you have any questions or suggestions please reach out to me in Twitter, Linked in or in YouTube comments, please share the video and subscribe the channel if you like it.
Finally on 28th July 2020 Microsoft Lists have arrived in our tenants (10% targeted release), here is the icon and just click on it to access the Lists page.
Microsoft Lists iconMicrosoft Lists home page
What are the ways to create lists?
You can create a blank list as we use to do like custom list in SharePoint.
You can start with an existing excel which means that if you have excel you can use that create a list with same set of columns etc
You can pick any one of the templates to create a list.
You can also use an existing list of create a list which means you don’t have to start from scratch and any existing list can be used as template.
We will have a look at all these one by one but first let’s discuss templates.
What are the templates available and their usages?
Issue Tracker: This to track issues, similar template is available in SharePoint as well, look at the video below how to create and what are the columns etc. You will notice that the interface is same as SharePoint Lists and also settings page is same so the learning curve is pretty small.
Microsoft Lists Issue Tracker Demo
2. Employee OnBoarding Template: Look at the video to understand what columns it has, this can be used for onboarding new employees or onboarding emoloyee transfers from one location to another or department
Microsoft Lists Onboarding template demo
3. Event Itinerary template demo: A lists to track series of events such as who is the speaker, location, number of people allowed etc, look at the video for more details
Microsoft Lists itinerary template demo
4. Asset Manager Template: This template can be used to manage assets if new assets needs to be assigned, transferred or an inventory of existing assets. All required columns like Serial number, manufacturer etc are already available, look at the video for more details
Microsoft Lists Asset Manager template demo
5. Recruitment Tracker: This template can be used to track recruitment candidates, has all options to store candidate information, who is the requester, for what role etc., look at the video to understand more.
Microsoft Lists Recruitment Tracker template demo
6. Travel Request Tracker: This can be used to track travel requests, it has all the columns you need, workflow of course can be created using Power Automate, good use of the “Location” column as users can enter location where they are travelling so you don’t need to maintain a list, flight cost, hotel cost etc, look at the video for details
Microsoft Lists Travel Tracker template demo
7. Work Progress Tracker: This template can be handy if you are managing a team or you are tracking different tasks, this template has all the columns you need such categorization, priority, start date , end date etc, look at the video for more details
Microsoft Lists Work Progress Tracker demo
8. Content Scheduler: This template can be used to track launch of content in your organization, it has all the columns you need such as Content type, author, publish date, content link etc, view the video to get more details
Microsoft Lists Content Scheduler template demo
Create a blank list: Use the same steps as before just use the option, blank list, it is more like a custom list we have in SharePoint and we can create columns as we wish. View the video for more details.
As you can see the experience is very similar as before and we have both modern way of creating ways also classic experience available with us, if you want to use metadata type it is also available.
Microsoft Lists Blank List demo
Create a List using an existing list: Use the same options as before just use the option existing list and you will be able to create a list out of an existing list. Benefit of this is that whatever customization with respect to columns, formatting and views you have done will be copied over. View the video for more details.
Create Microsoft Lists using an existing List
Create a List using an excel: If you have data in existing excel and you would like to turn it into Microsoft Lists then it can be easily done, view the video below.
Some points before you do this:
There is a limit of number of rows for which data can be uploaded
Your excel sheet needs to have data in “Table” format else it will not work and will give a message for the same.
You can select multiple type of column type while uploading such as Single text, Multi text, Choice , Date time, Number , Currency depending on what data resides in the column or you can select not to upload data
When you upload the excel it will be first uploaded to OneDrive and then list will be created, later it can be found in OneDrive.
You have the ability to upload an excel from OneDrive as well.
I also encountered an issue while uploading, I was not abe to save List under “My Lists” as it gave me this error “The Site does now allow Site Designs” so I had to share it in one of the SharePoint sites and then it worked.
Your data along with columns will also be uploaded.
What happens when you create a list and how to create under SharePoint Site ?
If you will notice the List URL you will understand that the List App is actually running from your SharePoint My Site as we have our “OneDrive” running, a sample URL below so I believe all Lists are being created under your My Site. Check below URLs and you will understand. Anyhow our my sites are nothing but a SharePoint site so it makes sense to utilize them.
You have the ability to save list under a SharePoint site when you create a list, instead of My Lists select SharePoint Site this will create the List under SharePoint site, a link will be added in menu automatically and also you can find under the Site Contents.
Create Microsoft Lists Under SharePoint Site
Create a List from existing SharePoint List: Yes this is true you can create Microsoft Lists using existing SharePoint List, check the below video for details. Basically you will have to use “From existing list” option and you can select SharePoint sites and it will show compatible Lists whuch can be used.
Create Microsoft Lists using SharePoint existing Lists
How to create a Rule in Microsoft Lists: Rule is a great idea introduced by Microsoft which works similar to List formatting but has much more, check the video below in which I highlight a column and also add an exclaimation icon based on specific condition and that too only via few clicks.
Create Rule for Microsoft Lists demo
How to access Microsoft Lists via PowerShell?
You can access Microsoft Lists just like any other list and I hope all kind of commands work, check below the screenshot to believe it
Microsoft Lists access via PowerShell
Sharing options in Microsoft Lists: When you create Microsoft Lists under My Lists it is accessible only by you so if you want to share with others you need to share with other persons. When you create under a SharePoint site then it will be accessible by SharePoint site users as well.
Share options in Microsoft Lists
You can also share individual items as well.
Integration options: This is a big topic so I just want to say few lines as it has all the abilities for extending the functionality using Microsoft Graph, Power Automate, PowerApps
How to access normal SharePoint List in Microsoft Lists way:
If you want to see the new Microsoft Lists experience in any modern SharePoint list, go to the URL and append this query string after AllItems.aspx like this. AllItems.aspx?env=WebViewList
Credit goes to this tweet:
If you want to see the new Microsoft Lists experience in any modern @SharePoint list, go to the URL and append this query string after AllItems.aspx
Summary: This is a great decision from Microsoft to introduce Lists as a standalone App, it will provide a great value. It has huge capabilities which I have not been able to cover all things but I will do this in subsequent blogs.
Connect with me on Twitter or Linked In if you need more information.
Kislay Sinha 