Column Formatting In SharePoint Online - No Coding

Scenario

 

You have a list in SharePoint with generic columns like Status, Due Date, Assigned To, Actual Hours, etc. You might want to highlight the data from each of these columns depending upon its value.

 

Solution

 

Well, back there in SP 2010/2013, you would have done this using XSLT, SharePoint Designer, JSLink, Content editor web part, JavaScript, and many other ways.

 

For SharePoint Online, it is just a configuration exactly like an MS Excel file.

 

Depending upon the type of the field, the configuration is provided in SharePoint Online.

 

No Code solution

 

If the column type is Yes/No, Choice, or Date, then you just need to configure the colors; no need of JSON code.

 

JSON code solution

 

For all other column types like Single line of text, Person and Group, you need to construct the JSON for formatting but that is also readily available at Microsoft documentation. You just need to update the logic as per your need.

 

How to format Yes/No, Choice, Date columns

 

Open your SharePoint Online List >> Select the Yes/No column which you want to format >> Column Settings >> Format this Column

 

 

For the choice of Yes/No and Date columns you will find the default template already present. You need to select that template >> Preview the result >> hit Save button.

 

 

If you want to modify the default colors applied >> Click on Edit Template >> as shown below you will get option to select color for each of the values.

 

 

Those are the default color shades provided in design view to select >> choose as per your wise for both the values >> Once saved that’s how the formatting looks.

 

 

Let’s look at how Choice columns formatting options look  >> Select any choice column >> Column setting >> Format this column

 

 

And once you click on Edit Template that’s how it appears with all the choice values >> Choose the color for each of the choice options and hit Save/Apply.

 

 

Let’s see what’s there if you click on Advanced mode in the above screen. Just to highlight, those are two important locations in JSON which tells you what class is being applied for a particular value. These classes are already present in default CSS which is part of Office UI Fabric.

 

Ok, so how do you put your own class or your own background color in this JSON? We will see in a while.

 

 

For the Date/Time column below, three categorizations are provided for formatting by default. You can  modify this JSON using Advanced mode to fulfill your own requirement

 

Before Today, Today, After Today

 

 

Formatting using JSON

 

In the above steps, we saw how we can use default templates and format the column. Yet, what if we want to apply our own color/style – how and what to modify in already generated JSON?

 

Example - I wanted to apply different background colors to Yes/No values than the one which was applied with the default template.

 

So, click on Advanced mode. Use the JSON provided below and update the color code/values as per your need. In the default JSON, the class name is being provided conditionally, so I have applied the style conditionally.

 

{
  "elmType": "div",
  "style": {
    "padding": "0 4px",
    "color": "white",
    "background-color": {
        "operator": ":",
      "operands": [
        {
          "operator": "==",
          "operands": [
            "@currentField",
            true
          ]
        },
        "Red",
        {
          "operator": ":",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "@currentField",
                false
              ]
            },
            "Green",
            ""
          ]
        }
      ]
    }
  },
  "attributes": {
  },
  "txtContent": "=if(@currentField==true,'Yes',if(@currentField==false,'No',''))"
}

Going further, you can show icons as well depending upon the values. Use the below JSON for formatting your Choice/Text column with icons. You need to modify the choice or Text values as per your scenario. Choose the classes from the provided options.

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField == 'Done', 'sp-field-severity--good', if(@currentField == 'In Progress', 'sp-field-severity--low', if(@currentField == 'On Hold', 'sp-field-severity--warning', if(@currentField == 'Not Started', 'sp-field-severity--severeWarning', 'sp-field-severity--blocked')))) + ' ms-fontColor-neutralSecondary'"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(@currentField == 'Done', 'CheckMark', if(@currentField == 'In Progress', 'Forward', if(@currentField == 'On Hold', 'Error', if(@currentField == 'Not Started', 'Warning', 'ErrorBadge'))))"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

Below are some example of classes and how they format your column values. Try using them in your list view. It will put a nice impact on the end users.

 

 

Hopefully, this article has helped you to understand the column formatting options in SharePoint online. Refer to Microsoft documentation for more details.