Conditional Formatting in Table Tool
Conditional formatting is a very effective way of pointing out values in analyses that should be looked at particularly closely. In Excel, many people use this option very intensively - the result looks like this, for example:
However, even though this is very easy to implement in Excel, there are good reasons to create reports that are needed regularly with Alteryx. The report shown above could look something like this created using Alteryx:
But how do you get from the data supplied to this report? Of course, there is no COPY-PASTE here, but a workflow in which data is loaded and prepared.
To output the report, we use the Table Tool, which can also be used as a module in more complex reports. It might look something like this.
Data is loaded from a file, a few calculations are performed (in our example only a total row) and then the report is generated. Of course, the report should look exactly like our Excel template, i.e. it should also highlight the "anomalies".
Here, the Table tool helps us to design the report accordingly, i.e. to select columns, change their order or adjust headings. And there are a number of column-related formatting options that we can use for our report.
If we select one of the columns under "Per Column Configuration", we can access the column rules of the selected column with "Create ...", i.e. specific formatting rules that only apply to this column.
When opened for the first time, a new rule is automatically created, which then only needs to be filled with content (i.e. specific instructions).
I would recommend changing the "Rule Name" immediately. Here I have adapted the name of the rule to the target colour - so it follows the rule with which the cells are to be marked whose values are particularly bad. Of course, "Rule 1", "Rule 2" etc. are also possible, but who remembers after a few weeks what is hidden behind "Rule 5"?
In the next step you decide when to apply the rule (i.e. the formatting) - always or only in certain cases. So there is no difference between "general formatting" and "conditional formatting", but the column rules are used for both cases. If a column should always have a certain font or colour, this is also specified here - in this case, the rule always ("Apply Always") is applied. In our case, we can select "When" (i.e. use a simple condition); if this is not enough, there is also the option of using a formula as a condition.
With a simple condition, as used here, we must then select the column to which it should apply (3), specify the type of comparison (4) and the value to be checked against (5).
For the example, we choose the deviation column, "<=" and "-25" as the limit value.
Important: Not only numerical checks are possible, but also checks for a specific text.
Then it must be determined what should happen when the condition is fulfilled. A wide range of options is available here - font type and size, colour, but also prefix and suffix, for example.
In our case, we select "Background Color" and then specify the color using the corresponding selection mask.
Recommended by LinkedIn
After you have confirmed the selection, we see our first rule again in the summary. When we exit the rule, the Table Tool no longer shows "Create" for the column rules, but now "Edit" - a rule already exists.
The result meets our expectations: The cell that shows a variance of -40.5% (i.e. <= -25) is highlighted in red. So it seems to work.
Important: The condition does not have to refer to the column that is to be formatted! We could have colored the first column, for example.
Then we can continue. Let's just add a condition that covers the next range - the deviation up to -10%. Then we follow the same procedure as before - adjust the name of the rule, select the column to be checked, enter the comparison operator and the limit value and select the color.
This time, however, it does not work quite as intended - now two fields are highlighted in light red, and the formatting already set in the first rule is "destroyed" again by the second.
The reason for this is that the rules are simply executed in the order in which they are listed, regardless of whether a previous rule has already been applied. Of course -40.5 is smaller than -25 - but it is also smaller than -10! The formatting of the last applicable rule therefore overwrites all preceding rules.
Important: Only the specified formats are overwritten. If we had chosen "bold" in addition to "red", but only specified "light red" in the second rule, "bold" would have been retained.
Of course, it is simpler and more practical to use the "correct" order directly. With "Up" and "Down" the rules can be moved. Ranges (i.e. "value between -10 and -25") can alternatively also be checked with a formula.
But even so - after changing the order, it works as planned.
Now only a few more rules are missing, then the formatting looks like in the Excel sheet.
But stop - the totals line was still bold in the original,
For this purpose, there are the "Row Styling Rules". Basically, they work like column rules, but they refer to one row. We can either define them (a specific row number) or identify them via the label "Total" - which is more flexible.
Important: If the rules of column and row formatting do not match, the column rule has priority - unless it is defined differently in the row rule!
Anyway, our report now looks like the Excel template!
By the way, the Table Tool also offers a whole range of other formatting options that you should definitely have a look at.
In this way, a "semi-manual" report can be quickly and easily converted into an automatic process that is always repeatable and remains scalable.