SharePoint List Conditioinal Formula - If any fields are blank in a row

Frequent Contributor

is there a conditional formula or json i can use that would highlight rows which have a blank field?  Or I can create a column with a status and have it upate (Complete vs Incomplete) depending on if every field is filled out or not?

3 Replies

@VeeExcelLearn you can use json formatting.

Select column settings > format this column



Select Conditional formatting


If the column is equal to [leave type or value empty] - then apply selected formatting. 



Similarly you can do the same for a view:

When you open column formatting , select Format View followed by Conditional Formatting.



Then you can create the condition based on multiple values




But if I add a column I have to keep on adding conditions? Is there a way to do it in a way that is fail safe? If any item in a row is blank trigger a "INCOMPLETE" flag. Does that make sense?
You can't do that with JSON- as columns are added you would need to update the formatting. It looks like you could do this with Power Automate, but it takes a little work.
When an item is created/modified, get the list columns
Put the column names in an array, then use a foreach to check to see if any of the columns are empty.

This would allow dynamic additions of columns and always check them. A couple of notes:
- Consider adding a trigger action for items that aren't INCOMPLETE - I could see scenarios where an item has was complete at the time, but then once a column is added, it will be INCOMPLETE again if it gets modified.
- Alternatively, if an existing item should be marked INCOMPLETE when a new column is added, you probably to run this on a timer to check all list items.