SharePoint Online Column Formatting

By

SharePoint Online Column Formatting was announced at Microsoft Ignite 2017 and started to appear in SharePoint Online Tenants around October 2017. The SharePoint Online Column Formatting is very useful for applying conditional formatting to content allowing for data to be highlighted to end users. For example, highlighting overdue tasks, tasks assigned to the current user, or perhaps low stock availability within a custom list.

SharePoint Online Column Formatting provides users of SharePoint with a greatly missed feature. Going back to SharePoint 2010, conditional formatting was often applied using the SharePoint Designer 2010 tool. After SharePoint 2013 was released, Conditional Formatting then became difficult unless you were to write custom code or use a third party tool.

SharePoint Designer 2010 Conditional Formatting

SharePoint Online now offers two ways of working with the column formatting. You could use ‘SharePoint Online Column Formatting’ or use a Field Customizer which is part of the SharePoint Framework SPFx. Within this post, we’ll concentrate on the SharePoint Online Column Formatting. An example of some of the formatting capabilities are below:

SharePoint Online Column Formatting

SharePoint Online Column Formatting doesn’t just offer the ability to apply conditional formatting as far as changing fonts and colors are concerned, but also provides data formatting allowing for manipulation on the display of dates and numbers, as well as the ability to render data as Data Bars, Custom Icons, and also create custom actions. Microsoft have written a great article on this feature showing some examples of how you can apply column formatting in different scenarios. https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

 

Applying SharePoint Online Column Formatting

SharePoint Online Column Formatting is applied through the field properties page. To find the field properties page:

 

1. Navigate to the List Settings using the Site Actions Icon in the top right hand corner of your SharePoint page.

 

Applying SharePoint Online Column Formatting

2. In the Columns section of the List Settings page, click the hyperlink for the column that you wish to modify.

3. Scroll down to the Column Formatting field within the Field Properties.

SharePoint Online Column Formatting to display Emojis

4. Using JSON, you will be able to apply your column formatting. Below is some sample code that displays Emojis based upon where our eRally car finished within a rally course.

 

{

“$schema”: “http://columnformatting.sharepointpnp.com/columnFormattingSchema.json”,
“debugMode”: true,
“elmType”: “div”,
“children”: [
{
“elmType”: “span”,
“style”: {

“display”: “inline-block”,

“padding”: “0 2px”

},

“attributes”: {

“iconName”: {

“operator”: “?”,

“operands”: [

{

“operator”: “<“,

“operands”:

[

“@currentField”,

“2”

]

},

“Emoji”,
{
“operator”: “?”,
“operands”: [
{
“operator”: “<=”,
“operands”:
[
“@currentField”,
“3”
]
},
“Emoji2”,
{
“operator”: “?”,
“operands”: [
{
“operator”: “<=”,
“operands”:
[
“@currentField”,
“5”
]
},
“EmojiNeutral”,
{
“operator”: “?”,
“operands”: [
{
“operator”: “<“,
“operands”: [
“@currentField”,

“10”

]

},

“EmojiDisappointed”,

“Unknown”

]

}

]

}

]

}

]

}

}

},

{

“elmType”: “span”,

“txtContent”: “@currentField”

}

]

}

Applying Conditional Formatting with the Lightning Conductor

The Lightning Tools Lightning Conductor SharePoint rollup tool also allows you to apply Data Formatting and Conditional Formatting without the use of JSON code. The Lightning Conductor can be directed at a specific list, or an aggregation of lists based on the same list template or content type. Date Formatting, Currency Formatting, String Manipulation, and styling can be applied using the Lightning Conductor Display settings. The below example changes the background font, not just to the current column but optionally to the entire row. If the eRally car finishes in position 1, the background color changes to green.

SharePoint Online Column Formatting with the Lightning Conductor

After the conditional formatting has been applied, the results are applied to any list included in the content aggregation:

SharePoint Online Column Formatting Display in the Lightning Conductor

You can download the Lightning Conductor for SharePoint Online and SharePoint On-Premises and receive a free 14 day trial.

<Brett/>