Column formatting is a nice way to customize your modern lists views. But it requires that the columns used in the JSON are referenced in the view.

In this workaround you need a calculated column present in the view. It doesn’t need to be the column you are formating.

For this  example, create a column ‘Flow Status’ and a column ‘Color’. The purpose is to show the color in the column status.

Go to the formating panel of column Flow Status

and paste the folowing JSON

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"style": {
"border-top": "none",
"background-color": "=[$Color]"
},
"children": [
{
"elmType": "span",
"style": {
"font-weight": "bold",
"color": "black"
},
"txtContent": "@currentField"
}
]
}

If the column color is present in the view, the formating is correct

But, if you hide the column, it not shows the color correctly

So the trick is to make a reference to the Color column without showing it in the view. To do that, the easiest way is to make that reference in the calculated field with a condition that never happens.

The column Flow Status calculated field has a simple formula

=[_Hidden_FlowStatus]

what we need to include is a condition that never happens. For example, put in the formula the condition if 1 equal to 2 then show Color else, show Flow status.

IF(1=2;color;_Hidden_FlowStatus)

This way, the view have reference to the column color and show the format correctly


19 Comments

Erwin Koens · October 22, 2019 at 8:43 am

Hi. Cannot get this working. What is the name of the calculated column? Is the calculated column displayed?

Wim · November 7, 2019 at 8:42 am

Hi, Can’t get this working. Only works when you load loading the calculated view from a view where all required columns are present. Loading the trimmed view with only calculated column after hard refresh does not give the values from the referenced columns.

    Valter Lima · November 7, 2019 at 10:46 am

    Hi,
    You need to put the required columns in some of the calculated field.
    In my example, the required field is Color, and i put it in a calculated field in a condition that never happens:

    =If(1=2;Color; _Hidden_FlowStatus)

    did it helps? If not, please post your example whit the required and calculated fields.

Joseph · December 5, 2019 at 10:06 pm

I also could not get this solution to work. The workaround I ended up using was not hiding the columns I didn’t want to show, but instead using JSON formatting of those columns to make them appear blank.

    Valter Lima · December 6, 2019 at 11:59 am

    Its a way of getting things done, but did you try to put that collumn in a calculated field? That way you have the needed reference to the collumn and if you put it in a condicion that never happens it doesn´t appear in the view, for example 1 = 2. then you use this calculate field to show a collumn that you already need to show in the view.

Anthony White · April 29, 2020 at 1:42 pm

Like others posting here, I don’t think this works…

Tina · May 5, 2020 at 4:16 pm

I have a calculated field [Last Updated] that I am formatting. Formula: =TEXT(LastUpdatedDate,”d-mmm-yyyy”)

In my view, I am formatting the [Last Updated] column with:
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “div”,
“attributes”: {
“class”: “=if(toString([$LastUpdatedDate]) == ”, ”, if([$LastUpdatedDate] <= (@now +2592000000), 'sp-field-severity–warning'))"
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}

This only works if I am showing [LastUpdatedDate] in the view. The field is there in the formula, but it doesn't work. I even tried the IF(1=2…). Any suggestions?

Detlef · February 2, 2021 at 7:27 pm

This is an exceptionally clever workaround for the modern pages limitation on column formatting conditionals. Readers may be getting stuck just picturing what is being rearranged.

In a nutshell – take ANY of your existing list fields and duplicate it by making a calculated copy. Example: Name and Name_Copy, or State and State_Copy. The field that you choose has to be one that you want to display. It doesn’t have to be one that you plan to format, but it is convenient.

The calculation for the calculated field [Name_Copy] starts as just [Name]
The IF statement is literally only there to mention -another- field [Color], that we never want to display at all. We just need it referenced so that it loads with the view.

I flipped the IF statement around a little, just so that it would be a little clearer in my own mind:
[Name_Copy] —> IF(true,[Name],[Color])

[Color] will never be used, since true will never equal false.

Remove [Name] from your list view and replace it with [Name_Copy]. Nothing should have changed, but we can now make references to [Color] in other conditional statement.

    Valter Lima · March 16, 2021 at 5:55 pm

    Thanks for the feedback! You resumed very well!!

Luis Montoya · February 10, 2021 at 11:07 pm

this workaround works no more, it seems MS has updated something in sharepoint and referenced additional columns values are not brought to the JSON formatting

    Valter Lima · March 16, 2021 at 5:52 pm

    I just checked in my tenants and is still working.

    Grateful ExcelUser · December 3, 2021 at 5:40 pm

    This is a brilliant solution and solved a problem that was driving me crazy.

    I had 2 fields: Name and URL. I wanted a 3rd column, “Link” that would show the name and let you click and take you to the URL, then hide the 2 original Name and URL fields. However, Sharepoint was forcing me to keep Name and URL unhidden if I wanted to have that 3rd Link column.

    This solution worked – I created the 3rd column as a calculated column with the formula
    =IF(1=2,URL,Name)

    I then used the following JSON in the column formatting
    {
    “$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
    “elmType”: “a”,
    “txtContent”: “[$Name]”,
    “attributes”: {
    “target”: “_blank”,
    “href”: “=[$URL]”
    }
    }

Jesper Simonsen · June 10, 2021 at 9:24 am

My 10 cent on this smart work around is to put the fields you want to use for column formatting in the condition part of the IF statement, this way it work more reliably and you can add as many fields in there as you want.
So if you need field1, field2 and field3 you do something like this:
=IF([field1]=”test”&[field2]=”test”&[field3]=”test”&1=2,[fieldNotDisplayed],[fieldDisplayed])

Gabriel · October 7, 2021 at 4:48 am

Hello! Great tips, I made some read-only cells with another tip you have over there. Well, now I’m searching for a way to show or hide columns to an user or group of users in a list. I’m not looking for the conditional formulas used in the form (I replaced the form with my own form made in PowerApps), I’m looking for a workaround to hide columns to a users. Scenerio: Group 1: Managers, Group 2: Recruiters. The list have 10 columns with HR info. Managers can view all columns and Recruiters only can view 5. Is this possiible? Thanks a lot

ctenden · December 2, 2021 at 10:42 pm

The Calculated Field should be If(1=2,Color,_Hidden_FlowStatus) not =If(1=2;Color;_Hidden_FlowStatus)

JR · January 10, 2022 at 3:54 pm

This is positively brilliant, thank you so much!

[Tips & Tricks] View-Formatting With a Column not present in the view – DevScope Ninjas · February 21, 2020 at 4:07 pm

[…] workaround used in this post is the same used in the Column-Formatting With a Column not present in the view, but with a different example that can be more easily […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: