Conditional formatting in WordPress tables

Video versionA video overview of conditional formatting in wpDataTables

Example of table without conditional formattingWhen conditional tables 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 number of cells with the values you’re looking for. Imagine you need to quickly figure out by looking at this table, which companies’ revenue for February and March was below $30.000. If even possible, this would probably take a human an hour or more to do it manually, and could certainly result in some mistakes.

The 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 all about: it allows you to highlight or change the cells, rows, or even columns based on the content of the cells.

Configuring the conditional rules to format tablesHow to set up conditional formatting / highlighting for a table

Add rule in conditional formatting

Let’s start by defining the conditional formatting rules.

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

To start defining conditional formatting rules for a column, click the “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?“. This is defined by which operation you want to use to set 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?”. This is defined by the value to be used in the rule’s comparison operation; i.e., “if the cell value is greater than 5“, “if the cell value is equal to ‘abc‘”, etc. Use a datepicker for date columns for easier interaction.
  3. Formatting operation – “What to do when a match is found?”. This defines the type of formatting to apply if the cell matches the condition defined in 1. and 2. You are given the following options:
    • Set cell color. This changes the current cell’s color to one defined in the rule.
    • Reset cell color to default. If any previous conditional formatting rule defined a cell color, it will be reset.
    • Set cell content. Override the actual cell contents with some custom HTML. This is useful if for example, you want to replace company names with logos, or status messages with icons. Please note that sorting and filtering will be applied before the conditional formatting takes place, so if you choose to set a cell’s 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 they are entered separated by 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 by a space) from a cell, if it matches the rule.
    • Set row color. Sets the whole row color to the color provided if a cell matches the rule.
    • Reset row color to default. If a 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 by a space) to all cells within this row, if a cell in this column matches the rule. This is 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 by a space) from all cells within the current row, if the 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 the visible range for tables with server-side processing) matches the rule. This operation has the highest priority. It will override other color-setting operations if they have been defined.
    • Set column CSS class. Adds a CSS class to all cells within this column (within the visible range for tables with server-side processing) if at least one cell in this column matches the rule. This is useful for custom highlighting (custom font, font size, alignment, etc.)
  4. Operation value. Color for highlighting (defined with a colorpicker), for coloring operations, or as a value for defining CSS classes or resetting cell values.
  5. Delete button. Clicking the “X” button will delete the entire row.
Rule options in conditional formatting
Rules example in conditional formatting

Please bear in mind that every “next” rule has higher priority than the ones above it, and can override them. For example, 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 rule will never be applied, since the second rule will always override it and color everything above 2 yellow. However, the same rule 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; in this way, we implicitly defined a rule for cell with values “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 a cell value will be compared with today’s date. You can find “%TODAY%” placeholder button at the bottom of the date and date-time pickers.

From version 2.2 you are able to use more placeholders to compare cell value: %LAST_WEEK%, %THIS_WEEK%, %NEXT_WEEK%, LAST_30_DAYS%, %THIS_MONTH%, %LAST_MONTH%, %NEXT_MONTH. You can find them next to the %TODAY% placeholder, and use them in a similar way.

Date rule in conditional formatting
Table layout after conditional formatting applied

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