SharePoint Form Conditional Formulas
SharePoint Lists *are* Microsoft Lists, and in these lists you can create conditional formulas, so that certain fields hide and show according to conditions that you define. This is just out-of-box in SharePoint, without any other product, no Power Apps needed.
Here’s how it works. In this example, I’ve created a list using the travel request list template, in SharePoint Online in Microsoft 365.
In your list, click the New button at the top, as if you were going to fill out a new form.
Click Edit columns.
The first thing to notice about this next screen, is that it allows a quick way to uncheck boxes to hide or show fields, or drag them up and down to rearrange the way they show on the form. For example, I have a column/field here called “Approved?” and I don’t want anyone to ever change it when they’re filling out the form, because I have a flow that is changing it based on approval process. So that’s a good reason to completely hide a field from the form. Also notice here that if a column is required, it will be grayed out. You can’t hide it on the form if you want it to be mandatory, basically.
Hover your mouse over the fields, click the ellipsis next to a field, and you’ll notice that there’s an option to Edit conditional formula.
This means that you can use conditions to hide and show fields. Instead of having to choose whether a field is always showing or always hidden, you can say under what circumstances you’d like that column to be hidden. In this example of a travel request form, I don’t need the “Estimated airfare” to be displayed until I’ve chosen an airline, and I don’t need the “Estimated hotel cost” to be display until I’ve selected a hotel.
The hyperlink on this little screen is very helpful, and will show you exactly how to write the condition formulas. Learn to use conditional formulas in a list form. There is also a list of field types on that site, that aren’t supported by this functionality.
In the documentation, you’ll find that all of the formulas are IF statements that result in a TRUE or FALSE.
TRUE = the column will be hidden
FALSE = the column will show
Basically, their documentation shows that each column type has a slightly different syntax. In my example above, I want to show the estimated airfare field only if an airline has been selected. Airline is not blank. This one isn’t listed in the documentation, but here’s how it goes:
=if([$Airline] == ”,’false’, ‘true’)
When using these formulas, the system names for fields will need to be used. So for example, if the column you’re referencing is called “Estimated hotel cost”, that’s not what you’re going to type in the formula. To get the system name for a column, go to the list settings, scroll down to the columns, and click on that column. Then look at the URL in the address bar, at the Field=
So my column is really called EstimatedHotelCost
If I wanted to only show a column if the estimated hotel cost is greater than zero: =if([$EstimatedHotelCost]>0, ‘true’,’false’)
If I have an “Approver” column, but the travel request only needs an approver if it is greater than $1,000, then I could do something like:
=if(([$EstimatedHotelCost]+[$EstimatedAirfare])>1000, ‘true’,’false’)
At the almost end of this video, at 53:12, I did a demo of this functionality:
What a great post and awesome feature.
One question, I know that this can be done in powerapps, can you hide field based on if a person is creating a new item or not? Use cases is a form where you only fill out a few questions and someone else comes and fill the manager section.
Yes,
If(TheNameofYourFormControl.Mode=New, true, false)
Put that in the visible property.
@Daniel, if you’re asking how to do this in an SP form vs. PowerApps, here’s what I’ve done: I created a hidden date column (StartDate) and then a Flow populates it after the list item is created. A conditional formula hides several fields in the form until that StartDate has a value in it, effectively making those additional fields available on submit: =if([$StartDate] != ”,’true’,’false’)
It could be another type of column (I don’t think there’s anything magical about date), but for our process the StartDate was already in play and convenient. I think for future lists I may set a standard for myself and create a hidden choice column like “formStatus”. Based on the status, fields can be shown or hidden by using a flow to change the status at appropriate times in the process.
Laura,
May you please give more details about the formula If(TheNameofYourFormControl.Mode=New, true, false) ?
Where can I find the name of the FormControl ? As is I have an error “invalid formula”
Click on your form in your PowerApp, and look at what the name of that control is. By default, it will be called “Form1”.
I had totally missed this, and it rather useful. Many thanks Laura – I’ll even do a mini session on it at our User Group (crediting you, of course)
Brilliant and important capability that my (onprem only) organization won’t get immediate access to. Thanks for another simple and incredibly useful post, Laura!
Awesome! Thanks!
Question: Is there a way to conditionally require the field that is displayed, since ‘required’ fields can’t be conditionally displayed?
No, not at this time.
Pingback: SharePoint List Form – Form Editor | @WonderLaura
Hi Laura,
I have tried using this referencing “date” type column using the Microsoft recommended script – =if([$StartDate] >= Date(‘4/6/2020′),’true’,’false’), however can’t make it work for date column that was created by user. It works only for default fate field (Created, Modified). The internal column is correct (only one word).
Have you tried using this for date columns?
In the settings for your date column, change the setting instead of date only, to date and time. See if that solves the problem.
What is the formula when the condition depends on Metadata column. I’ve tried $xxx.label but is not working.
I don’t know, I’ve never tried it.
Is there to do this by identifying the current user or more usefully by identifying if the current user is a list admin or site member?
Nope.
Hi Laura,
Is there a conditional to make a field read-only on the SharePoint List form?
Nope.
@Preston, I’ve been faced with a similar challenge and managed it this way (if I’m reading your question correctly):
1. Create a calculated column directly referencing the column you want to “conditionally” make read-only
2. In the Form formatting, set your Condition to “show” the calculated field when you want the “read-only” field visible; test it to make sure it works
3. Copy the text from the Condition you just created, and Paste it into the condition for the *original* field; just reverse the ‘true’ and ‘false’ statements
I use this method to show an empty date field, but once it’s got a date in it, the fillable field goes away and the calculated field (read-only) becomes visible, essentially locking the user out from making changes to it. My condition is just testing to see if the date is not null =if([$StartDate] != ”,’true’,’false’)
Can you add more than on conditional formula to a column? If so, how do you separate them. I have two IF statements I need to use for one column.
There may be a way to do that, but Microsoft doesn’t make it clear. The syntax examples on their site don’t show if there is a way, either. I don’t know.
how would you code an OR? e.g. if status = ‘Active’ or ‘Cancelled’ then Blue else Black
I don’t know.
From Kunj Sangani at C#Corner:
&& Logical AND operator
|| Logical OR Operator
! Logical NOT Operator
Used in a formula:
=if([$Airline] == ” && [$TicketPrice] == 0,’false’, ‘true’)
=if([$Status]==’Active’ || [$Status]== ‘Cancelled’, ‘true’, ‘false’)
Hello Laura,
Thank you, for all the posts that you have made until now. They have been really helpful.
I have a question. I want a condition for one column based on the length of another column.
Is there a way to do it? I have tried len(columnName) formula but it doesn’t work. I didn’t find any formula online for this requirement.
I don’t know.
Can we perform this formatting on document libraries forms?
No, at least not yet at this point. Not sure if they plan on adding that.
Is is possible to hide columns on the NEW form but not the EDIT form?
No, not with this method. It can be done in a Power App, though.
@Nick Warrens, you have to use Flow to update a hidden column, so there is some complexity…but it can be done (and I use this in many of my SP Lists to simplify the submission process). See my comment above for more details: https://wonderlaura.com/2021/02/12/sharepoint-form-conditional-formulas/#comment-136329
Thanks Laura, stumbled across this in a quest to get some guidance on this topic, and amongst the plethora of articles/Youtube videos I found, yours gave the key information on how to get the system name for the column in question. This saved me tonnes of time so thanks very much!
Hi Laura
Thanks and congratulations for your job.
I modified a form, since I can no longer see the attachments !! Do you have an idea.
Thanks in advance. Cordially
If you’d like to reset the form, you can go to list settings -> form settings, and set it back to the SharePoint default form. Or you can just add the attachment control / card back to your form control in the Power App.
Hi Laura Can i hide Columns that missing Information , as we have a list with lots of column and some columns dont have information
so them we want to hide ? and show just columns that have information?
Regards Tommy
Yes, you can always remove any column from a view. You can learn basics like that in my free SharePoint Beginners plan here: https://www.iwmentor.com/pages/plans
Hi Laura, I have a column that counts the number of days from a due date to the present date (days over due) is there a way to stop the count when a completed date is input into another column ?
Thanks in advance
How is your formula counting that number of days?
I have a hidden column (todays date), Type of information is set to date and time and default value is set to todays date. I also have a column labelled “target date completion” which just has a date entered. I used a 3rd column labelled as Days past due, this column information is set to calculated and the formula I input is =DATEDIF([Target Completion Date],[Todays Date],”D”). I would like to incorporate 1 more column labelled “Date Completed” and when the job is completed and a date is input in that column then the days past due would keep the number of days from the “target completion date, and date completed” I am sorry if this is confusing……
Setting a date field’s default to today, doesn’t mean that it dynamically changes itself each day. It means that the date field gets populated right when the user is filling out the form for the first time. Which means that your “today’s date” column will always just show the same date as the “created” system column. So, since it doesn’t dynamically change, the question doesn’t make any sense.
Hi, I have three columns that are somewhat dependent on each other. Column 1 called TypeTracker is what drives what other columns should display. Category is a choice column that only shows when Type Tracker is = to Activity. Then I have several Sub Category columns that are all choice as well. I want to have only the Sub Category column that relates to the selection in Category to display. Here is the formula I entered, but it is not working. Thoughts? =if([$TypeTracker]==’Activity’ && =if([$Category]==’Audit’,’true’, ‘false’). I am placing this rule on the Audit Column and just doesn’t work.
I’m sorry but I don’t know how to troubleshoot that code. I don’t know the syntax well enough, and each time I do it, it takes me hours to figure it out when I need to do different logic.
Michelle, this is way too late, but you’re missing a right parenthesis bracket.
Good rule of thumb for anyone working with longer nested conditional statements: count the number of left-hand parentheses, then count the number of right-hand parentheses and make sure they match. If not, figure out where it’s missing 😀
can we use Filter in Conditional formatting?????
I don’t think so.
Hi Laura, wondered if you could have a look at this for me. I have a sharepoint form that has a number of fields. 1 of the fields is a tick box that opens up multiple other fields, but i want this tick box to be only accessible to a few individuals. Now i figured out how to accomplish this for a single person. Here’s what i did.
So i created a column that is hidden from the form, I’ve called this ‘Current User’ and in there I’ve set the default value to be ‘=Me’. This returns ‘i:0#.f|membership|myname@mycompany.com’.
I then use this within the conditional formula for the tick box field like this…
‘=if([$CurrentUser] ==’i:0#.f|membership|myname@mycompany.com’, ‘true’, ‘false’)’
This works great, only i can see the tick box field, however i want this to work for about 4 other people and hide it from everybody else.
I’ve tried nesting if statements and using if(or but i’m getting a bit stuck with it. Do you think it can be done?
Sorry, i totally missed Josh’s comment above on January 11th, this looks like it’s worked.
All thanks to your website being here, it’s taught me loads, please keep up the great work!!
I don’t know. I usually just use Power Apps when the form gets more complex like that. Anything can be done in Power Apps.
Hi Laura, would you happen to know of a way to set the show/hide conditional formula via the REST API? I would like to set the formula for 180 columns and don’t want to do it manually. I have created a Power automate flow which can create SPoint list columns, set column properties, rename etc. This would be most help if I could set this aspect via Power automate (REST api) as well. Thank you for taking time to read my question, Josh
I’m not a dev, so I don’t know.
Hi. I am wondering if more complex functionality can be supported. For example, if filetype = folder, don’t apply expiration date?
I don’t think so, it seems extremely limited. For anything more complex, I’d use a Power App instead.
Hello. This is useful. I have a question. How to make the hide/show field a mandatory field when it is showing?
You can’t. I’d use Power Apps if I needed to do something more complex like that.
Thank you, Laura. I will try to use Power Apps
Hi Laura,
My situation is unique, I have an IF statement that is working but now a new thing popped up. I have to make another IF within this statement conditional. Here is the scenario
=IF([modified]=””,”in process”,IF([modified]=”place”,”in process”,”Complete”)) This one works fine
Now the new request I have
=IF([modified]=””,”in process”,(IF([modified]=”place”,AND(IF([Date]=”DATE”,”in process”,)”Complete”))) This is giving me a syntax error and I tried many permutations but nothing is working any help will be greatly appreicated.
Sorry I don’t know how to troubleshoot that one.