Creating tables in Tableau with Excel style conditional formatting is a common ask of Tableau developers. I understand many visualisation purists don’t like tables but sometimes it is the best way to show the data. After all a heatmap is a table with colour!
Those experienced in Tableau are aware it’s not Excel and the table style reporting in Tableau, although constantly improving, still isn’t great.
Measure Names / Measure Values is the best technique to build simple tables in Tableau. However this technique does not work for more complex table formatting. For example you may want to add some form of conditional formatting to your Tableau table or add a dimension between measures.
The “placeholder” technique is the standard way to create a more advanced table in Tableau . Create a field with the formula MIN(0) to use as a placeholder to put other text, colour, etc.
Each instance of this field on the Columns shelf represents another column of the table.
A simple example could be you want to see a table with Sales and Profit but with the Profit column coloured by whether it made a profit or a loss and no colour within the Sales column. (I know this could also be done using Measure Names / Measure Values and Use Separate Legends – but what if you wanted to colour Profit by YoY Change instead, for example?)
To begin, using a Superstore data source, put 2 Placeholder field pills on Columns.
The Mark Type can be Text to begin – although I will switch this to Square shortly to start to add colour.
Add the Profit pill to the Text shelf of the first placeholder mark and the Sales pill to the other.
Choose any dimension to put on Rows. For this example I have used State.
Next is to add some colour and tidy this up. Change the Mark types to Square. This enables us to colour the whole cell. Adding colour to a Text mark type only alters the colour of the actual text. It is generally more impactful to colour the entire cell.
This may cause some of the text to vanish in the table. Resolve this by simply altering the size of the text – click on the Label box for all marks on the Marks card. Font size 8 fixes this issue for me; as an alternative you could also extend the row height.
To show whether there was a profit or loss in the Profit column simply drag SUM([Profit]) to the Colour box of ONLY the first placeholder mark. Alter the colour scheme as you wish; I have used a default red-blue colour scheme with only 2 steps to show whether in profit or loss.
Next step is adjust the formatting to make this look more like a table. We want to remove the 0 line; remove the square from Sales; adjust the titles and increase the size of the Profit square to fill the entire cell.
- To remove the zero lines in Tableau using the menu bar: Format – Lines:
- Sheet – Zero Lines = None
- Columns – Grid Lines = None
- To remove the square from Sales simply set the mark type for that placeholder pill back to Text. Those with very sharp eyes may notice the font formatting no longer picks up from the Label formatting, now the SUM([Sales]) field should have the text format set to be a consistent size with the Profit.
- To alter the column titles right click on the axes titles and Edit Axis. Set the titles and set all Tick Marks to None.
- To increase the size of the Profit square find the Size option for the profit placeholder mark and drag the size out to the far right.
This causes 2 problems, the first very visible. The label has gone! To resolve click the Label and “Allow labels to overlap other marks”.
The second problem is less visible but obvious when checking the output. The colour is taking up more space than the square. Although the mouse in the image below is hovering over District of Columbia the tooltip is showing Colorado. Arizona had a loss but the pill is coloured as a profit; while District of Columbia made a profit but is coloured as a loss.
There are 2 solutions to this:
- Alter the mark type to Gantt – which is covered in a later article
- Duplicate the dimension field, add the duplicate to Rows and uncheck “Show header”. I have no idea why this works but it does correct the overspill of the colour from the square.
That gives a final output as follows:
Improve this further by right aligning the numbers and moving the headers to the top.
Moving the headers to the top is a little fiddly; each column in the table needs to be a dual axis. For the above table that would mean 4 placeholder pills on the Columns shelf.
Adding the extra placeholder pills does initially make things look untidy but can be easily cleaned up. Format the upper axes the same as with the earlier lower axes. It is likely the second axis of each column is also appearing, either duplicating the text or showing Abc in the same place as the numbers. To clean this you could:
- Alter the axis size; for example fix the placeholder axis to be displayed with a fixed -1 to 1 width and the axis you don’t want displayed with a fixed size of 2 to 3 (so it is effectively hidden)
- Set the new placeholders to text, create a new calculated field to return a zero length string (i.e. formula = “”) and put that in the Text area of the new placeholders. This returns nothing so can’t pollute the view. Note the placeholder mark type may also need to be altered to Text.
Altering the alignment of the Square mark type is simple. Set the alignment in the Label of the mark.
Right aligning the Text mark type is more complicated – the simplest option is to alter the text mark types to Square…although that adds additional steps…the size of the Square should be adjusted to fill the cell (the same as above) and also the Colour of the Square mark can be set to white.
Eventually you end up with the end result.
This technique is not my favourite but sometimes it’s the only way to produce the conditional style formatting a client may demand.
The negatives of this technique are:
- Performance – the load time of tables built like this can be slow, especially when dealing with a larger data set(s) and multiple columns
- Fiddly – this simple example alone is quite fiddly; more columns only makes it more fiddly
- White space – there’s excessive white space at both ends of the table
But remember sometimes it’s the only way so isn’t all bad!
Download the workbook of the above, including the Measure Names / Measure Values technique and the Gantt technique from Tableau Public