How to color and evaluate table data based on conditions in Confluence?

[#1]

Native Confluence features allow you to customize your table. But if you want to achieve something similar to MS Excel conditional formatting, you can use the Table Transformer macro from the Table Filter and Charts for Confluence app. Let's have a look at how you can achieve it.

The Table Transformer macro allows you to alter your table however you like with the help of SQL queries. It means that the possibilities are nearly endless.

In this example, we have a table that a marketing team can create to see how much profit consultations, webinars, guides, and brochures brought to a company over each quarter. Conditional formatting can come in handy if you want to highlight the months, depending on whether you achieved the sales goal.

Let's start with a simple one-column table and break down quarterly profits coming from selling consultations.

SELECT 

T1.'Quarter', 

FORMATWIKI("{cell:bgColor=" +  

CASE WHEN T1.'Consultation' <= "300" THEN "#FA7E70"

     WHEN T1.'Consultation' > "300" AND T1.'Consultation' <= "700" THEN "#FFE784"

ELSE "#8FCA7D" 

END

+ "}" + "$" + T1.'Consultation' + "{cell}") 

AS 'Consultation' 

FROM T* 

• Lines 1-2: select the first column with the same title.
• Line 3: indicates cell background color changes.
• Lines 4-6: add conditions - if consultation sales were below 300$, the cell is red; if they were between 300$ and 700$, the cell is yellow; and if the sales exceeded 700$, the cell is green.
• Lines 7-9: close the formatting query, add the dollar sign to each number, and specify the column heading.
• Line 10: specifies the selected table.

The result looks like this.

If you have a table with more columns, duplicate the query and use the right conditions according to the data in your table.

Hope we inspired you to try out new tools and get more creative with your Confluence tables! Let us know what you think in the comments.