Please wait...

Conditional Formatting

Video versionA video overview of conditional formatting in wpDataTables

Example of table without conditional formattingWhen conditional formatting (highlighting) is needed

When you are working with large data sets, especially with numeric data, it is hard to quickly find and estimate the amount of cells with the values you’re looking for. Imagine you’d need to quickly figure out looking at this table for which companies revenue for February and March was below $30.000. If even possible, this would probably take an ‘ordinary’ person an hour or so, and could certainly cause some mistakes.

Same table with conditional formattingHow highlighting makes life easier

Now take a look at the same table, where all values below $30.000 are highlighted in red, all between $30.000 and $60.000 are colored in yellow, and everything above $60.000 is green.

That what conditional formatting is: it allows you to highlight or change the cells, rows, or even columns based on the content of cells.

Configuring conditional formatting rulesHow to set up conditional formatting / highlighting for a table

wpDataTables conditional formatting

Let’s see how we can define the conditional formatting rules.

First you should go to the column settings by clicking on “Column settings” button in the column header wpDataTables column settings or by clicking “Complete column list”  button wpDataTables complete column list from where you can open column configuration for each column. When column settings panel is opened, you should go to “Conditional formatting” tab.

To start defining conditional formatting rules for column just click “Add rule” button. You will see a popup dialog. Each column can have unlimited amount of formatting rules (just bear in mind that each rule takes some time to render, so it’s better to keep them at a reasonable amount). When you click the “Add rule” button, a new rule row will be added.

  1. Comparison rule selector – How to compare?“. Basically, which operation to use in the condition. For numeric and date columns this can be “less than”, “less than or equal”, “equal”, “not equal”, “greater than or equal”, “greater than”. For string columns this can be “equal”, “not equal”, “contains”, “does not contain” for fuzzy comparisons.
  2. Comparison value – “What to compare to?”. Value to be used in the rule’s comparison operation. I.e.: “if cell value is greater than 5“, “if cell value is equal to ‘abc‘”, etc. Uses a datepicker for date columns for easier interaction.
  3. Formatting operation – “What to do when a match is found?”. Which kind of formatting to apply if the the cell matches the condition defined in 1. and 2. Has following options:
    • Set cell color. Changes the current cells color to one defined in rule.
    • Reset cell color to default. If any previous conditional formatting rule defined a cell color, it’s going to be reset.
    • Set cell content. Override the actual cell contents with some custom HTML. Useful to e.g. replace company names with logos, or status messages with icons. Please note that sorting and filtering will be applied before the conditional formatting, so basically if you choose to set cell content to “zzz” when it’s actual value is “aaa”, it’s still going to be on top when you sort because sorting is done against the original data set.
    • Set cell CSS class. Adds a defined CSS class (or several classes if entered separated with a space) to cells which match the conditional formatting rule. Useful for custom highlighting (custom font, font size, alignment, etc.)
    • Remove cell CSS class. Removes a given CSS class (or several classes if entered separated with a space) from a cell, if it matches the rule.
    • Set row color. Sets the whole row color to a provided one if a cell matches the rule.
    • Reset row color to default. If any previous conditional formatting rule defined a row color, it will be reset.
    • Set row CSS class. Adds a CSS class (or several classes if entered separated with a space) to all cells within this row if a cell in this column matches the rule. Useful for custom highlighting (custom font, font size, alignment, etc.)
    • Remove row CSS class. Removes a given CSS class (or several classes if entered separated with a space) from all cell within the current row, if cell content in the current column matches the rule.
    • Set column color. Sets the whole column color to a provided one if one cell (within visible range for tables with server-side processing) matches the rule. This operation has the highest priority and will override other color-setting operations if they are defined.
    • Set column CSS class. Adds a CSS class to all cells within this column (within visible range for tables with server-side processing) if at least one cell in this column matches the rule. Useful for custom highlighting (custom font, font size, alignment, etc.)
  4. Operation value. Color for highlighting (defined with a colorpicker) for coloring operations, or a value for defining CSS classes, or resetting cell values.
  5. Delete button. Clicking the “X” button will delete the whole row.
wpDataTables conditional formatting

Please bear in mind that each next rule has higher priority than the ones above it, so it can override them. This means, e.g. if you defined a rule to color all cells which are greater than 3 in red, then define another rule for same column to color all cells which are greater than 2 in yellow, the first one will never be applied, as the second one is going to always override it and color everything above 2 in yellow. However, same can be used to define combinations. For example see how we configured the rules for the table above.

  1. Color all cells where values are less than 30000 in red.
  2. Color all cells where values are greater than or equal to 30000 in yellow.
  3. Color all cells where values are greater than or equal to 60000 in green.

As you see, rule 3 overrides rule 2 in the given range, but this is what we need, this way we defined the rule “between” 30000 and 60000.

To play around with a table with Conditional Formatting in our Sandbox, click here or here to tweak the back-end configuration.

Today placeholder

For “Date” and “Datetime” columns it is possible to set “%TODAY%” placeholder so cell value will be compared with today’s date. You can find “%TODAY%” placeholder button at the bottom of the date and datetime pickers.

wpDataTables conditional formatting
wpDataTables conditional formatting

Let’s imagine that today’s date is 05/06/2016, and if we define “=” for “Comparison rule selector” and “%TODAY%” placeholder for “Comparison value” then all cell where date is equal to 05/06/2016 will be included in this condition as you can see in “Exam date and time” column at the example on the left.

Never miss new features!

Join 2000+ newsletter subscribers

Never miss notifications about new cool features, promotions, giveaways or freebies - subscribe to our newsletter! We send about one mail per month, and do our best to keep our announcements interesting.

We never spam or disclose your address to anyone.