Column Formatting SharePoint: Severity Levels

The Microsoft website has a detailed description on how to Use column formatting to customize SharePoint.  In particular, it shows how to apply formatting based on the value in a field using a CSS class.  Unfortunately, the article does not list the severity levels available.  A little poking around at the style sheets uncovers the following severity codes:

  • sp-field-severity–good
  • sp-field-severity–low
  • sp-field-severity–warning
  • sp-field-severity–severeWarning
  • sp-field-severity–blocked

Also of note, the example code for that section does not properly reflect the necessary code to reproduce the example.

Status field with done colored green, blocked colored red, and in review colored orange

"class": "=if(@currentField == 'Done', 'sp-field-severity--good', if(@currentField == 'In progress', 'sp-field-severity--low' ,if(@currentField == 'In review','sp-field-severity--warning', if(@currentField == 'Blocked','sp-field-severity--blocked', ''))"
}

If you read the JSON, the example overlooks the Has Issues condition.  The JSON should actually read:

"class": "=if(@currentField == 'Done', 'sp-field-severity--good', if(@currentField == 'In progress', 'sp-field-severity--low' ,if(@currentField == 'In review','sp-field-severity--warning',  if(@currentField == 'Has issues','sp-field-severity--severeWarning', if(@currentField == 'Blocked','sp-field-severity--blocked', ''))"

As well, the icon section leaves out Blocked. It should be:

"iconName": "=if(@currentField == 'Done','CheckMark', if(@currentField == 'In progress', 'Forward', if(@currentField == 'In review', 'Error', if(@currentField == 'Has issues','Warning', if(@currentField == 'Blocked','ErrorBadge','')"

Excel: Banding alternate rows, even with conditions

Excel tables have a nice little feature that lets you band alternate rows to help visually read the data.

However, to use this, you have to convert the data to a Table.  Since this is not always practical, there is an alternative using Conditional Formatting.

The formatting rule is:

=MOD(ROW(),2)=0

This applies the formatting you choose only to those rows that when divided by 2 have no remainder, i.e., even numbered rows.  This is especially useful in preventing the shading from go all haywire when the user deletes a row.

But what about conditional formatting on larges swaths of cells that hampers banding?

Easy enough. You can combine formulas to make the conditions still work with banding.

The formulas are as follows:

=AND(C11="Sales",MOD(ROW(),2)=0)

=C11="Sales"

=MOD(ROW(),2)=0

Quick tip: The banding with condition formula needs to be above the condition-only formula, with the standard banding formula at the bottom of the conditions list.

This gives you conditional shading that is continuous with the underlying banding across the full data set.