Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

This question came up from one of the attendees where I was leading a Tableau Workshop and it’s not the first time either! After showing a demo of creating a crosstab, they asked:

“Can we colour a specific column or row in a crosstab?”

So, here it is …

How to Colour Your Crosstab

Below we have a crosstab, where it shows sales by the region and sub-category. The attendees wanted to know if you could highlight a specific region as an end user may be interested in that particular area.

Formatting options do not allow you to choose a specific column or row in the crosstab, so we would use a calculation in this case. As we would like to colour the Region dimension, we should specify the dimension within the calculation. My below calculation assigns a value of 1 whenever the region is South; any other region would have a 0 assigned:

By bringing this field onto Color on the Marks card, you’ll see a colour legend come up:

By default, it will colour the text and aggregate this field by SUM. It is currently counting how many records there are for the South region in every sub category. This isn’t an issue because we know that anything identified as 1 and above is in the South region. If you would like to, you can change this aggregation to min, max, median or average. Ultimately, it doesn’t matter because it will return values that are either 0 or 1 and above.

Click on Color on the Marks card, change to Custom Diverging and put a white colour for the left option, then a darker colour on the right. Make it into a two-stepped colour, then click on the Advanced button and fix it to start from 0 and ending at 1. This means that anything identified as 1 or above will be the darker colour; anything under 1 will be white. Click OK:

Finally, on the Marks card change the type to Square so that the whole cell is coloured:

In our calculation, we specifically asked for the return values to be 1 or 0, which would make it into a continuous field. Making it into a continuous field allows us to have the filled square option that you see above.

We don’t have to stop there. We can highlight more than one column or more than one row. We could even try a combination of both! Just like in my example below, all you have to do is update the calculation and specify the requirements of another dimension. In my calculation, I want the view to colour the row when Sub-category is Bookcases:

In fact, why not make this even more flexible by adding in parameters so that the end user has the control? We can create the first parameter for Region, have it as a string type and populate the list by using Add from Field > Region:

Next, we repeat the steps and do the same thing for Sub-Category. Now that we have our two parameters, we can substitute these into our original calculation:

So, the end user can now use the parameters to control which dimensions they want highlighted in the crosstab. If you would like to implement this in your Tableau reports, you can download and use my sample workbook below to help you. The data source I’m using is the Sample Superstore, which is available from the Saved Data Sources in Tableau Desktop.