Three and a Half Ways to Conditionally Format Tables (including the headers) in Tableau

One of the most commonly asked questions on the Tableau forums is about conditional formatting of tables. There are a few blog posts out there addressing it, but this is my attempt to cover the three ways I know of doing it. They are progressively more horrendous, but chances are it’s going to be the last one that you want. Sorry about that. One thing I have to stress is that Tableau is not a spreadsheet application — expecting it to act like Excel will make your life miserable, and complaining that it doesn’t act the same is like expecting your toaster to boil water instead of using the kettle: it probably could boil water, kind of, but people might get hurt (this may be stretching my analogy about the effects of using Tableau for these tables).

Before I dig in, there are a couple of other options that do part of what you might be looking for, and I’d like to suggest those first. It’s not an obvious piece of functionality that if you create a table using [Measure Names] and [Measure Values], you can colour each Measure independently by clicking on the dropdown of the Colour pill and selecting “Use Separate Legends”. This is the “half” of the title. You’ll get a layout something like this — you can subsequently format certain columns to range from black to black (i.e. no formatting), and only have selected columns with a colour applied. The limitation with this method is that each value can only have a format applied using the value. You couldn’t, for instance, colour Sales values according to the Discount applied. However, if you just want to highlight a column with positive or negative values, this is the way I’d go.

If that gets you what you need, you’re welcome, please leave.

Using Separate Legends for four Measures in a table

Right, off we go. In ascending order of complexity, my actual three methods are:

Leveraging Header Shading
This involves creating an alternating index that highlights values that meet a condition. It is limited to basic highlighting, e.g. emphasising customers who have spent more than a certain amount, or categories that fail a test condition. It also contains a risk, in that the highlighting will be reversed if your first row of data meets the condition. However, this is the only method that will allow you to keep independently sized columns for your Dimensions.

Use of a Scaffold Table
This requires an additional (few rows) data table to add to your source. If you can’t make any changes at all to your data, this may not be the option for you. This method, and the placeholder technique, force all columns to have the same width, so if you have Dimension values like, “Thin Layer Chromatographic Analysis” and Measure values like “2”, then you’re going to have a lot of white space in your table.

The Placeholder Technique
This method is probably the most versatile — no additional data required, and completely configurable, but incredibly clunky, with high maintenance requirements, and limitations like headers and measure values all having columns the same width. You will also have some white space at the bottom of the table that you can’t get rid of, which can create a slightly distracting layout on a dashboard.

Header Shading

Before shading a header, I’d like to try one more alternative (another bonus option, I hear you cry, surely that makes 4, not 3.5!) to pointing out, for example, customers who may need attention. Using Tableau’s Superstore dataset, I’m going to mark customers who haven’t been spending enough. The standard way of colouring will only highlight Measures (left), but you can create a discrete Measure to place a marker next to rows of interest (right):

Marking rows versus highlighting values

This method uses a nifty trick using number formatting. You can use any HTML character in a number format, so I’ve created a calculated field:

// Replace the 1000 with a parameter value, if you like
SUM([Sales]) - 1000

// Add this to your Columns shelf and make it Discrete
// Format the number using a custom format:
   "";◀

This gives you a negative value for anything below your threshold. You can then use a resizable column that you can place to the left or right of your existing Dimensions, though to place it at the left and maintain your original sort order you will need to adapt, slightly:

Using a formatted number to place an icon within a row of data

Right, the actual highlighting part. A relatively brief pros and cons section for this first one:

Pros

• This is the only option that will allow you to resize your Dimension headers as normal

Cons

• The formatting can get reversed
• It really is just for highlighting intermittent headers, you can only have one colour.

Using the [Sales Compared to 1000] field created above, we can create a condition where we add 1 to a sequence number every time the values, running down the table, cross the threshold (i.e. the value on consecutive rows goes from >1000 to <1000, or vice versa).

Use an Index to alternate colouring of headers, then hide the Index column

IF 
    LOOKUP(SUM([Sales Compared to 1000]),-1) < 0 AND 
           SUM([Sales Compared to 1000]) >= 0
    OR 
    LOOKUP(SUM([Sales Compared to 1000]),-1) > 0 AND 
           SUM([Sales Compared to 1000]) <= 0
THEN
    PREVIOUS_VALUE(0) + 1
ELSE 
    PREVIOUS_VALUE(0)
END

The table above has normal shading applied, so all of the odd numbers will receive a header shading. The downside, you might already have noticed, is that Tableau always starts with an unshaded row, so if your first customer had low sales, this breaks down but you would, at least, still get the same rows marked differently to higher sales. This is not the most versatile of options, but for simple highlighting it can be a quick implementation. To use it in tables with more dimensions, just use an LoD to get the detail you need, but bear in mind, you have to highlight all Dimensions (because you’re highlighting the customer and all of their sub-Dimensions):

Use an LoD to avoid highlighting customers with low sub-category sales

Using a Scaffold

I’m putting the pros and cons up front, as some of these may be deal breakers for you. For the scaffold, the pros and cons below are in bold where the point is specific to this method.

The Pros

• Relatively simple to set up, but does require a relationship (or Blend) with your existing data source.
• You can have columns in any order. Click and drag to re-order, the same as you would for any Dimension
• You can format the value of a column based on the value in another column.
• Colouring multiple columns in a similar way is easy.
• This is possibly better suited to re-ordering columns, more than conditionally format them.

The Cons

• All of your columns will be the same size. Long text fields and integer numbers will be given the same amount of space.
• The text alignment of all cells will be the same.
• You can only use Discrete values for colouring cells.
• You need to add all of the header columns as well, so that the display is at the right level of detail.
• Number and date formats will need to be constructed as strings

To get started with this method, all you need is an Excel spreadsheet with a single column called “Column Number” (or whatever you want, really). It should contain a sequence of numbers, up to the highest number of columns you expect to have in any table in your workbook. Create a relationship with your existing data, using a value of 1 on both sides:

Full Outer Join in a relationship

To create the basic table, you need two calculated fields: one for your column headers; and one for the values in the table. Note, you can re-use this scaffold to create as many tables as you like, each requiring a pair of calculations to create the column headers and the calculations for the values in each column:

// [Conditional Columns 1]
CASE [Column Number]
    WHEN 1 THEN "Region"
    WHEN 2 THEN "Ship Mode"
    WHEN 3 THEN "Quantity"
    WHEN 4 THEN "Sales"
    WHEN 5 THEN "Customer Name"
END

// [Conditional Values 1]
CASE MAX([Column Number])
    WHEN 1 THEN ATTR([Region])
    WHEN 2 THEN ATTR([Ship Mode])
    WHEN 3 THEN STR(SUM([Quantity]))
    WHEN 4 THEN STR(ROUND(SUM([Sales])))
    WHEN 5 THEN ATTR([Customer Name])
END

Notice that you need to convert all of your values to be strings, this would include dates, or you’ll get this kind of error:

You cannot mix the output type of a CASE calculation

Your basic table will look like the table below. Bear in mind, you will need to add your Dimension fields (Region, Ship Mode and Customer Name) to the Rows shelf, to get the correct level of detail. If you don’t, the outer join we created will give you asterisks instead of your values (try it and see). However, all of your true Dimension columns are hidden.

From there, you can start formatting. The simplest formatting is the whole row, based on your condition. For that, we can just use our previously created field:

To colour the background of cells, I usually use a Bar, with a MIN(1) pill on the Size button. There are other ways. You may also notice I’ve added a pill to the Rows with a value of “”. When using Bars to colour the cells, you will lose a level of the row borders (I don’t know why, possibly something about the render order of chart elements). Adding this field gives you an additional level so you can bring the borders back.

You can colour each column independently, but the formatting all has to be applied from one field. Just to remind you, our column names are now the values in a Dimension, and the same thing has to be used to colour all of the values in a Dimension. If we create a calculation [Cell Colour]:

IF MAX([Column Number]) IN (1,3,5) THEN
    IF MAX([Customer Less than £1000]) THEN
        "Small Customer"
    ELSE 
        "Big Customer"
    END
ELSEIF MAX([Column Number]) = 2 THEN
    MAX([Ship Mode])
ELSEIF MAX([Column Number]) = 4 THEN
    STR(FLOOR(LOG(SUM({FIXED [Customer Name]: SUM([Sales])}),10)))
END

The first part colours three different columns based on whether the customer on that row has spent less than £1,000. The Ship Mode column is coloured based on the value in that column. Our remaining numeric column cannot be coloured with a gradient palette — you could use STR(SUM([Sales]), but you would have to associate a colour with every single value in your dataset, which is only fun if you’re a contractor on an absurd salary and no-one knows what you do. For the rest of us, create a field that categorises your SUM([Sales]) values into something more discrete. For this exercise I’ve used the order of magnitude of the customer’s sales. Take a look at the table below — spoiler, it’s gorrrrrrgeous.

A conditionally formatted table illustrating why Tableau don’t make this standard functionality

The main thing of note here is the legend. Because the data in our table has been ‘restructured’ such that all of the values are now part of one Dimension, all values are in the same Legend. If you want separate legends displayed on a dashboard, you’d have to build other worksheets, or text boxes, to split them out.

Okay, you made it this far. I hate the placeholder technique, so I’m only going to show you this once — don’t ask again.

The Placeholder Technique

At this point, I feel the need to remind you that Tableau is not a spreadsheet application, and is not designed to display large, conditionally formatted tables. With that in mind, let’s list some pros and cons, again. Those in bold are specific to this method:

The Pros

• Numeric fields can have number formats applied as normal.
• Columns can contain compound fields, e.g. £1,000 (+7%).
• You can format the value of a column based on the value in another column.
• Text alignment is more flexible, but not a matter of just selecting “Left” or “Right”.
• You have ultimate flexibility in formatting each column using either discrete or continuous values (make them as ugly as you like).
• You can have a mixture of normal Dimensions, and ‘Dimensions’ that you want to conditionally format (but the normal Dimensions must be at the left).

The Cons

• You lose column headers, they must be re-added manually as the title of a chart axis.
• Columns will all be the same width.
• It is time consuming to create these tables. Large tables are not recommended.
• The load time of your worksheet will be the slowest with this method.
• Reordering columns once you think you’re done will remove all previously applied formatting (including the Measure value) to that column.

So, now that you’re here, firstly, sorry, I’ll try to make this as painless as possible. Let’s start with a table we’ve kind of seen already:

A basic table by Region, Ship Mode and Customer Name

In this table, if you plan on highlighting an entire row, then you probably want to have all headers on all rows, i.e. the Region column above would contain “Central” on every row. If that is the case, then there is a step you can skip (that’s the only pain I can take away).

The beginning
This is the place you come back to once you’ve finished creating a column, somewhere further down the page (because you have to do these steps for every. single. column).

• Create a placeholder
  For now, remove the Measures from the table and, in the Columns Shelf, right click and select New Calculation. Type MIN(1.0) and hit enter.
  On the Marks card, change the Mark type from Automatic to Text.
  This is your first placeholder. That all felt very easy.
• Populate the placeholder
  Drag the Region field to the Text button of your Mark. This will put the Region value on every row.
  You can use the FIRST() table calculation to select which rows should contain header values. Since it doesn’t make a huge amount of sense to highlight a blank, I’ll leave this here, but you probably want to skip this step: If you want to replicate the normal table layout (i.e. only display “Central” once), then you’ll need a table calculation to specify that only the first row of a Region should be populated:

Creating a table calculation to populate only the first row of a Region

I have ignored that step, and will display all values on all rows. Your table should currently look something like this:

The beginning of a placeholder based table.

• Format your placeholder value
  Change the Mark type to Bar, with a size of MIN(1.0). If you haven’t used this technique before, right click on the Marks card and select New Calculation, type in MIN(1.0) and hit enter, then click on the detail button to the left of the pill and change the selection to Size. This pill is not related to the MIN(1.0) pill on your Columns shelf, we’re just using two MIN(1) tricks at once, go us! Change the range of your x-axis to be fixed at 0.0 to 1.0, and then click on the Label button to set your text alignment.
• Format your table “cell”
  From the menu, select Format → Lines, then remove everything — zero lines, grid lines — and leave a nice clean background. If you find that some of your rows are missing the data value, click on the Label button and select “Allow labels to overlap other marks”

You should now have a table that looks something like this, I’ve just coloured the column by Region name, but at this point you can start colouring things by whatever Measure you want. If you try to colour things by a Dimension that is at a lower level of detail (e.g. City, in this table), then you will get some dodgy layouts:

One, somewhat formatted header column in a table

Almost there, we’ve nearly finished our first conditionally formatted column. As an aside, if you don’t need this column conditionally formatted, it’s probably still easiest to get your text alignment how you want it using the method above, and just making the background white.

If you haven’t noticed already, we’re going to create a bunch of bar charts that all look the same size, and just have different labels, so our column headers are all going to be an x-axis label.

• Create a Column Header
  Create a second MIN(1.0) pill on the Columns Shelf, and make it a Dual Axis with the first. Tableau will try to be helpful and make assumptions about what the Mark type should be and how it should be formatted. Remove all pills from this new series’ Marks card. You’re going to have a lot of Pills with the same name, so the easiest way to get to the right one is to click on that Pill in the Columns shelf. Change the Mark type to Polygon. Yep, none of this makes sense, but as we have no data with which to draw a polygon, this is the easiest way to make the value blank (rather than Abc). You should now have a table that looked exactly like it did earlier, with one key difference, there is now an axis displayed at the top of the table as well.

A table that now has two axes displayed. Yep, we’ve made no progress.

• Set the column header
  Right click on the top axis and edit it. Replace MIN(1.0) with your column name (Region). Switch to the Ticks tab and select None. Do the same for the bottom axis, but change the title to be blank. You may be tempted to right click and de-select “Show Header”, which you are more than welcome to try. Sad times.
  Make the top header as short as you can, so that the white space at the bottom of your table is minimised (sorry, you can’t get rid of it).
• To make your life minutely easier later (and to make myself a liar about having lots of things with the same name), you can give your pills a name. Right click on the first MIN(1.0) Measure and select Edit in Shelf. At the start of the value, type //Region Footer then press Shift-Enter to add a line break. This keeps your calculation, but now the pill is labelled with a name (if you haven’t edited your x-axis yet, it will also replace MIN(1.0) with the name you just specified). If you’d like the calculation to be a proper calculation in your list of fields, you can ctrl-click and drag the pill to your Data pane — Tableau will create the calculated field for you, with that name, and replace the Pill on your Columns with the calculated field. Personally, I wouldn’t clog up by calculation list with a whole bunch of these.

Giving a pill on a Shelf a different name

ctrl-click and drag an in-line created field from a shelf to the Data panel

And that’s it! One, whole formatted column. Now you can go back to the bit where I said, “Come back here” and do it all over again for your second column.

Repeating that process for each required column will give you a layout that ends up looking something like this, a series of paired columns with independently applied conditional formats. In the image below I’ve just highlighted all columns except one with a discrete marker, and the Sales column with a continuous colour:

A final, 5 column conditionally formatted table with formatted header columns

A few final things to note:

• The Columns shelf in the image above is displayed as large as it will go. With more columns, it gets very difficult to work with.
• Note the white space at the bottom of the table — this will appear in your dashboard.
• To get horizontal line separators, I’ve added a “” empty Dimension, otherwise the lines won’t appear (this is to do with using a Bar for the background, not the MIN(1.0) pills)

Just reading this far is an achievement. Well done. Believe me, it was traumatic to write, tables aren’t what Tableau is designed for, so the inevitable, “Can we have a table with all the data in it as well?” makes me very sad.