# Datatable - Apply conditional formatting to all columns

**URL:** <https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407>\
**Category:** Dash Python\
**Created:** [February 11, 2019, 2:03pm UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407 "2019-02-11T14:03:13Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![dhove](https://avatars.discourse-cdn.com/v4/letter/d/779978/32.png) [@dhove](https://community.plotly.com/u/dhove)\
**Post date:** [February 11, 2019, 2:03pm UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/1 "2019-02-11T14:03:13Z")

</div>

This should be very simple, but I am completely new to Dash, so I am not sure of the syntax yet.

I’m trying to use conditional style formatting on a datatable to make all negative numbers appear in red. Below is an example of what I want to do, but only done on one column. I would like to do this for all columns. Also the column headers (in this case years) might change over time, so I would like to avoid hard coding any column names.

```
import dash
import dash_table
import dash_html_components as html
import pandas as pd
import numpy as np
from datetime import date

app = dash.Dash( __name__ )

sColNames = [str(x) for x in range(date.today().year-6, date.today().year+1)]
df = pd.DataFrame(data = np.array([[1.5,0.0,-1.5]]*7).T, index=range(3), columns=sColNames) # Example dataframe

dtTest = dash_table.DataTable(
        id='TestTable',
        columns=[{"name": i, "id": i} for i in df.columns],
        data=df.to_dict("rows"),
        style_data_conditional=[
        {
            'if': {
                'column_id': '2015',
                'filter': '2015 < num(0.0)' # Here I would like the same condition applied to all collumns not just "2015"
            }, 'color': 'red',
        },],)
app.layout = html.Div(children=[dtTest])

if __name__ == ' __main__':
    app.run_server(debug=True)

```

I looked at [https://dash.plot.ly/datatable/style](https://dash.plot.ly/datatable/style) but couldn’t make the examples work in this case.

Any suggestions are much appreciated

---

<div class="post-metadata">

**Author:** ![Marc-Andre](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@Marc-Andre](https://community.plotly.com/u/Marc-Andre)\
**Post date:** [February 11, 2019, 6:27pm UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/2 "2019-02-11T18:27:03Z")

</div>

@dhove There’s currently no way to express this as a single style expression.

You could generate the condition for each column (no explicit column id dependency) and apply the result like so:

```auto
styles = [{
    'if': {
        'column_id': str(x),
        'filter': '{} < num(0.0)'.format(x)
    },
    'color': 'red',
} for x in range(date.today().year-6, date.today().year+1)]

dash_table.DataTable(..., style_data_conditional=styles)

```

 ![image](https://us1.discourse-cdn.com/flex024/uploads/plot/original/2X/7/77ec6f9b35b2e1b50f75233c1d8f30ef732e596e.png)

---

<div class="post-metadata">

**Author:** ![dhove](https://avatars.discourse-cdn.com/v4/letter/d/779978/32.png) [@dhove](https://community.plotly.com/u/dhove)\
**Post date:** [February 12, 2019, 7:35am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/3 "2019-02-12T07:35:41Z")

</div>

I think that is a neat solution, and it works perfectly! So that is great, many thanks 🙂

Using format like this also seems nice and flexible. I’ll remember this for future formatting.

---

<div class="post-metadata">

**Author:** ![threemonks](https://avatars.discourse-cdn.com/v4/letter/t/b9e5f3/32.png) [@threemonks](https://community.plotly.com/u/threemonks)\
**Post date:** [July 23, 2019, 12:18am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/4 "2019-07-23T00:18:03Z")

</div>

I tried Marc-Andre’s suggestion from Feb 11, but it seems to be formatting the entire column data red, instead of just the negative ones.

Any idea what I could be doing wrong?

Thanks

---

<div class="post-metadata">

**Author:** ![Marc-Andre](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@Marc-Andre](https://community.plotly.com/u/Marc-Andre)\
**Post date:** [July 29, 2019, 7:39pm UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/5 "2019-07-29T19:39:52Z")

</div>

@threemonks Do you have a code example and a screenshot of the result and the difference vs. the expected behavior? Will have a look, thanks.

---

<div class="post-metadata">

**Author:** ![threemonks](https://avatars.discourse-cdn.com/v4/letter/t/b9e5f3/32.png) [@threemonks](https://community.plotly.com/u/threemonks)\
**Post date:** [July 30, 2019, 2:22am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/6 "2019-07-30T02:22:26Z")

</div>

Here is the code:

```
   return dash_table.DataTable(
        columns=[{"name": x, "id": x, 'type': 'numeric', 'format': FormatTemplate.money(0),} for x in dataframe.columns.tolist()],
        data=dataframe.to_dict('records'),
        filter_action="native",
        sort_action="native",
        page_action='native',
        page_current= 0,
        page_size=page_size,
        style_cell={'whiteSpace': 'normal', 'textOverflow': 'ellipsis'},
        style_data_conditional=[ {
                'if': {'column_id': str(x), 'filter': '{} < num(0.0)'.format(x)},
                'color': 'red',
            } for x in dataframe.columns.tolist()
        ],
    )

```

See attached screenshot of the table display, all values in the columns product\_cost and revenue are shown in red, though I expect only cells with negative values in red, others in black.

![Untitled](https://us1.discourse-cdn.com/flex024/uploads/plot/original/2X/7/7dc858f02e714f7667ec5ccdbe5d8efe9f416b62.png)

---

<div class="post-metadata">

**Author:** ![Marc-Andre](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@Marc-Andre](https://community.plotly.com/u/Marc-Andre)\
**Post date:** [July 30, 2019, 6:06pm UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/7 "2019-07-30T18:06:11Z")

</div>

@threemonks Thanks for the example. The table’s props have changed a bit since February, especially during the 4.0 migration. The example code below uses `dash==1.0.2` (`dash-table==4.0.2`) and while not an exact match for what you had above, should be equivalent.

```auto
import dash
from dash_table import DataTable, FormatTemplate

app = dash.Dash( __name__ )

app.layout = DataTable(
        columns=[{"name": x, "id": x, 'type': 'numeric', 'format': FormatTemplate.money(0),} for x in ['a', 'b', 'c', 'd']],
        data=[
            {'a': '123', 'b': '234', 'c': 0, 'd': 456},
            {'a': '124', 'b': '235', 'c': 346, 'd': 457},
            {'a': '125', 'b': '236', 'c': 347, 'd': 458},
            {'a': '126', 'b': '237', 'c': 348, 'd': 459},
            {'a': '127', 'b': '238', 'c': 349, 'd': 450},
            {'a': '128', 'b': '239', 'c': 340, 'd': 451},
        ],
        filter_action="native",
        sort_action="native",
        page_action='native',
        style_cell={'whiteSpace': 'normal', 'textOverflow': 'ellipsis'},
        style_data_conditional=[ {
                'if': {'column_id': str(x), 'filter_query': '{{{0}}} > 125 && {{{0}}} < 458'.format(x)},
                'color': 'red',
            } for x in ['a', 'b', 'c', 'd']
        ],
    )
if __name__ == ' __main__':
    app.run_server(debug=True)

```

Gives me:

 ![image](https://us1.discourse-cdn.com/flex024/uploads/plot/original/2X/2/29df7ea538fdf2102bc471ab1196d2da15c0c388.png)

Few things to note:

- conditionals nested `filter` is now `filter_query` for consistency
- columns were referenced as `columnId`, it’s now `{columnId}`
- `num(value)` no longer exists and some common conversions are attempted automatically instead

---

<div class="post-metadata">

**Author:** ![threemonks](https://avatars.discourse-cdn.com/v4/letter/t/b9e5f3/32.png) [@threemonks](https://community.plotly.com/u/threemonks)\
**Post date:** [July 31, 2019, 12:14am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/8 "2019-07-31T00:14:34Z")

</div>

That works. Thank a lot!

---

<div class="post-metadata">

**Author:** ![digli-dang-dang](https://avatars.discourse-cdn.com/v4/letter/d/f6c823/32.png) [@digli-dang-dang](https://community.plotly.com/u/digli-dang-dang)\
**Post date:** [December 25, 2019, 7:33am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/9 "2019-12-25T07:33:41Z")

</div>

> [@Marc-Andre](#):
>
> ‘if’: {‘column\_id’: str(x), ‘filter\_query’: ‘{{{0}}} \> 125 && {{{0}}} \< 458’.format(x)}, ‘color’: ‘red’, } for x in [‘a’, ‘b’, ‘c’, ‘d’

Hi. If I have to add multiple if statements, what should I do? I tried to add a _comma_ after

> for x in [‘a’, ‘b’, ‘c’, ‘d’]

like in the following code:

```auto
style_data_conditional=[ {
                'if': {'column_id': str(x), 'filter_query': '{{{0}}} > 125 && {{{0}}} < 458'.format(x)},
                'color': 'red',
            } for x in ['a', 'b', 'c', 'd'], 

         // add new code block
       'if': {'column_id': str(x), 'filter_query': '{{{0}}} > condition'.format(x)},
                'color': 'blue',
            } for x in ['a', 'b', 'c', 'd']

        ],

```

But it does not work! Also, I changed { } at many places to put two conditional blocks, but the code does not work!

---

<div class="post-metadata">

**Author:** ![marlon](https://avatars.discourse-cdn.com/v4/letter/m/8c91f0/32.png) [@marlon](https://community.plotly.com/u/marlon)\
**Post date:** [January 14, 2020, 1:49pm UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/10 "2020-01-14T13:49:41Z")

</div>

> [@digli-dang-dang](#):
>
> I tried to add a _comma_ after

Instead of comma, it should be **+**.

```auto
[ 
{'if': {'column_id': str(x), 'filter_query': '{{{0}}} > 125 && {{{0}}} < 458'.format(x)},
                  'color': 'red',
            } for x in ['a', 'b', 'c', 'd'] 
] + [
{'if': {'column_id': str(x), 'filter_query': '{{{0}}} > condition'.format(x)},
                'color': 'blue',
            } for x in ['a', 'b', 'c', 'd']
]

```

This is like combining 2 lists.

---

<div class="post-metadata">

**Author:** ![digli-dang-dang](https://avatars.discourse-cdn.com/v4/letter/d/f6c823/32.png) [@digli-dang-dang](https://community.plotly.com/u/digli-dang-dang)\
**Post date:** [January 15, 2020, 12:02am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/11 "2020-01-15T00:02:47Z")

</div>

> [@marlon](#):
>
> - [{‘if’: {‘column\_id’: str(x), ‘filter\_query’: ‘{{{0}}} \> condition’.format(x)}, ‘color’: ‘blue’, } for x in [‘a’, ‘b’, ‘c’, ‘d’] ]

Thank you very much! Let me see if it works.

---

<div class="post-metadata">

**Author:** ![TTYL](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@TTYL](https://community.plotly.com/u/TTYL)\
**Post date:** [April 5, 2020, 11:37pm UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/12 "2020-04-05T23:37:38Z")

</div>

I signed up for a forum account just to thank you for the help with this, was facing the same problem and couldn’t find documentation anywhere. Marlon, you are a lifesaver!!! 🙏🏻 🙏🏻

---

<div class="post-metadata">

**Author:** ![andrewmarco](https://avatars.discourse-cdn.com/v4/letter/a/ac8455/32.png) [@andrewmarco](https://community.plotly.com/u/andrewmarco)\
**Post date:** [April 11, 2020, 5:20am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/13 "2020-04-11T05:20:18Z")

</div>

Hi. How would i use this code to format a negative float inside parentheses?

I tried this:

```auto
style_data_conditional=[ {
'if': {'column_id': str(x), 'filter_query': '{{{0}}} < 0'.format(x)},
                        'format': Format(sign=Sign.parantheses),
                } for x in ['Quantity']]

```

Do you know how to do this without converting to string? If i need to convert to str, what is best way to do so?

It should like this

```auto
df = {'Quantity': '-50;)
Out: (50.0) 

```

---

<div class="post-metadata">

**Author:** ![lawrenced](https://avatars.discourse-cdn.com/v4/letter/l/3da27b/32.png) [@lawrenced](https://community.plotly.com/u/lawrenced)\
**Post date:** [April 24, 2020, 8:10pm UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/14 "2020-04-24T20:10:29Z")

</div>

Hi All,

Im having the same issue where this is formatting every value in the column rather than individual values. dash-table ver 4.6.2 code as follows

```auto
p.layout = html.Div([
    html.Div([
    dash_table.DataTable(id='xx',
             columns=[{'name': i, 'id': i, 'type': 'numeric'} for i in list(dict[0].keys())],
             data=dct_asia,
             editable = False,
             # virtualization= True,
             # style_cell={'maxWidth': 100}
             style_data_conditional=[
                 {'if': {'column_id': 'delta_m'}, 'filter_query': '{delta_m}<t 0', 'color': 'red', }],

```

any help much appreciated

---

<div class="post-metadata">

**Author:** ![andrewmarco](https://avatars.discourse-cdn.com/v4/letter/a/ac8455/32.png) [@andrewmarco](https://community.plotly.com/u/andrewmarco)\
**Post date:** [April 25, 2020, 6:59am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/15 "2020-04-25T06:59:25Z")

</div>

Not sure if this will work but give a try:

```auto
style_data_conditional=[
    {'if': { 'filter_query': '{delta_m}<t 0'},
    'color': 'red' }]

```

---

<div class="post-metadata">

**Author:** ![chriddyp](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/chriddyp/32/16855_2.png) [@chriddyp](https://community.plotly.com/u/chriddyp)\
**Post date:** [May 2, 2020, 1:17am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/16 "2020-05-02T01:17:20Z")

</div>

Thanks for the feedback everyone! Our documentation was definitely lacking here. I’ve rewritten this documentation in a new conditional formatting chapter here: [https://dash.plotly.com/datatable/conditional-formatting](https://dash.plotly.com/datatable/conditional-formatting)

I hope this clears things up! If you have a new question and have consulted the documentation, please create a new thread 🥂

 ![image](https://us1.discourse-cdn.com/flex024/uploads/plot/original/2X/2/2ba41493fd1bb6d80a5d0ee62f4c5534d9a457a2.png)

---

<div class="post-metadata">

**Author:** ![chriddyp](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/chriddyp/32/16855_2.png) [@chriddyp](https://community.plotly.com/u/chriddyp)\
**Post date:** [May 2, 2020, 1:17am UTC](https://community.plotly.com/t/datatable-apply-conditional-formatting-to-all-columns/19407/17 "2020-05-02T01:17:33Z")

</div>