How to Color and Size Entire Tableau Table Rows

Encode Entire Tables Including Headers by Color and Size

This video shows you how to highlight entire text table rows including the headers associated with the text marks. You will also learn how to use a set control to choose which rows are being colored and sized for extra emphasis.

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you how to color and size entire table rows in Tableau, including their headers. By the end of this video, you’ll be able to make something similar to what you see on the screen now. Notice here’s the default table. But as I start to make selections using this set control, the entire table row, including its header, is being both colored and sized for an added emphasis on what has been selected.

So that you can follow along using the Sample Superstore dataset, I’m going to throw together a quick crosstab that looks at the first three measures. So I’ll click Discount, hold Control and click Profit, hold Control and click Profit Ratio broken down by the Sub-Category dimension. So I will hold Control and click Sub-Category.

I’ll go over here to Show Me and click the crosstab in the top row, very first option. I’ll make this a little bit bigger so we can see it. And first I’m going to show you why this tip is needed and why it’s going to give you a nice bit of aesthetic polish here. I’m going to create a set from my sub-categories to give me something to put in the set. And if anything is in the set, I want to color it one way. Everything out of the set will be colored a second way.

The easiest way to do that is to right click directly on the dimension that you want to create the set from, hover over Create, and choose Set. And for now, I’ll leave it empty. I will click OK. I’m going to right click on that set and show the set. But notice that by default, that show set option is grayed out. This is a nuance of using a set control in Tableau. The set has to be somewhere on the view in order to show the set control.

Well, that’s fine because I was going to use this anyway to color the rows. So before I show the set control, that just means I need to drag Sub-Category Set to the Color Marks Card. We don’t see anything change. We have a different color, but everything is out of the set. Because remember, when we created that set, we left it blank. So everything is classified the same way. Yes, the color changed from a dark gray to a lighter gray. But now I should be able to right click on that Sub-Category Set– this is the important part of this– and now I can show the set. I did not have that option available to me before.

So I’ll click Show Set. Now if I add the first dimension member to the set, we should see both a classification of In as well as Out. So far, we only have Out. If I click Accessories to add that dimension member to the set, we now have In. And so that we can see these a little bit better, I’m going to remap the colors, maybe choose a brighter red for those that are in the set.

So why we need this tip– notice that was a nice effect. As I add additional dimension members to the set, their marks are being colored, but their headers are not. So essentially, anything that’s on the Marks Card will be encoded by that in and out of set field. And that’s why those numbers are colored either red or gray. But the headers technically are not in the set, and that’s why they’re not colored the same way.

They’re not being encoded like their marks. It almost looks like it’s broken.

I think Tableau is trying to help us here by maintaining consistent formatting, but I think that this crosstab would look a lot nicer if we were able to encode the entire row. That would make it look more intuitive to the user in my opinion. So that’s what I’m going to show you how to do here. I’m going to just undo to get us back to the beginning here.

And the trick to this tip is we need to create– I call it a scaffold table. I use this technique all the time. But what I’ll do is go ahead and put my dimension on the view as the rows, but for the columns, I’m going to put in four placeholder fields, one for each column in the data. The first column will be for the Sub-Category dimension, the second column will be for Discount, the third for Profit, the fourth for Profit Ratio.

The easiest way to add these placeholder fields is to do it in the flow of the analysis. You can do that by just double clicking on the Columns Shelf as I just did. I always do MIN(0). So that’s the entire formula. I use this so often that I typically make a calculated field. You could also do that, just make a calculated field. The whole formula would be MIN(0). Then you would place that calculated field on the Columns Shelf four times.

As a little bit of a shortcut, I just did it in the flow. That creates one column. My very favorite shortcut in Tableau is to hold the Control key while you click on a pill. That allows you to create a duplicate. So if I do that for MIN(0) three times, I’ve just created my– table scaffold is what I call these.

The default mark type is Bar, so I need to change the mark type for this entire table to be Text. And now whatever I place onto the Text Marks Card for each respective column is what will be displayed as the mark. So for the first column or the first Marks Shelf with my first placeholder field, I will put the Sub-Category dimension onto Text. I’ll navigate to the second column and put Discount onto Text. Third column– Profit on Text. And just to get there a slightly different way, I’ll click the fourth Marks Shelf and drag Profit Ratio to Text.

Now I’m going to go back to the All Marks Shelf and drag that Sub-Category Set onto the view, except I undid too far, so let me quickly remake that just by right clicking. Hover over Create, choose Set, and click OK. I’m still leaving it empty for now. So there’s my Sub-Category Set. I will drag that to the Color Marks Card. Everything is Out because the set was empty.

Now if I show the set control and add a field to it, the row will be colored. Let me get that back to a brighter red. But you can already see why this is working. Now that first column is technically a mark. Yes, it is a dimension and not a measure, but it is a mark, has its own Marks Shelf. Therefore, it can be encoded just like the other three columns.

Now that I’ve got that working, I can actually hide the header altogether by right clicking on the Sub-Category dimension and deselecting Show Header. And actually, let me get that back for one moment, make these rows a little bit taller again, and then I will hide the header. All right. So now you can see why this works.

As I click through, I’m adding different dimension members to the set, and they’re getting color coded all the way across. That’s already a nice effect. But I also want to show you how to size the entire rows, including their headers, because I think this adds even more nice polish to this and just provides a subtle extra emphasis of what’s being highlighted.

It requires a very simple formula. It’s a calculated field, so I’ll left click on this down arrow in the top right corner of the Data pane, click Create Calculated Field. And I’ll call this Mark Size. And this formula is going to have two outcomes, so I will use the Immediate IF statement, wrap it in parentheses. And what I will put after the parentheses is Sub-Category Set.

The sets, one of the advantages to them– and we have another video here that goes in depth on six or seven different ways to use sets. But they’re kind of– I think of them as a really elegant way to write IF-THEN logic because that one field is saying quite a bit. It’s saying if a dimension member is in that Sub-Category Set, then treat it this way. Otherwise– so if it’s not in the set– treat it a different way.

And all that logic is wrapped up in that one field. So this is basically saying if you’re in that set– and that’s its own kind of logical statement– and with this immediate IF statement, you type a comma, and if this criterion is true, whatever you put after the first comma is what will be applied to it. So it’s essentially so far saying if a dimension member is in that set, what do we want to do to it?

I’m going to say I’m going to size it, or I’m going to attribute a value of 3 to it. With immediate IF statements, you then type a second comma, and whatever you type next is what will be the outcome if that criterion right here is false. So if it’s not in the set in this case, I’m going to attribute a value of 1. And then these close with a parentheses. And that’s the entire formula.

I will click OK and drag that to the Size Marks Card for all of the columns. So I navigated to the All Marks Shelf, dragged the Mark Size field to Size. And now you can see we’re getting a different size dependent on whether the dimension member is in that set or it’s not.

But there’s one very critical nuance to this. Because I didn’t force an aggregation in this Mark Size calculated field– because that would have given me a mix of aggregates and non-aggregates– I’m seeing a default aggregation of SUM. And so while I’m getting a different size depending on whether they’re in the set or not, the sizes are inconsistent. I want to change that aggregation from the default, SUM, to Minimum. And now as you can see on the size legend, we only have two sizes. And these are always in– they’re always relative to each other. That’s what’s great about this technique.

So if you’re in the set, you are now three times bigger than if you are out of the set. And just to give you an example of how this works, if I continue to add some dimension members to the set, they’re each exactly three times bigger than those dimension members outside of the set. I think that’s just a really nice technique.

One more thing I’ll show you on this video to give you a little bit more context. The one drawback, in my opinion, if I’m being candid, is that we lost that nice crosstab alignment where typically, you want your words to be left aligned and your numbers to be right aligned. As much as I like this extra consistent encoding where the entire row is both colored and sized by whether or not it’s in that set, we did lose some of the advantage of having a table and what I would consider best practice with that alignment.

If that’s driving you crazy, I do have a solution for that. The one drawback to this solution– there’s kind of always pros and cons of different approaches in Tableau– is we can no longer size the marks. We can still color them, including the headers, but we can no longer size them. So there’s a little bit of a tradeoff. But if you want to change the alignment, what you can do is change– first of all, I’m going to get rid of my Mark Size on Size because that will no longer work.

And what I’m going to do is change the mark type for all four columns to Gantt Bar. So I’l choose Gantt Bar. You can already kind of see why this might work– because now our mark type is not Text, which by default gets a center alignment. Our mark type is Gantt Bar, and now all the labels that you’re seeing are– I accidentally just stole my thunder and revealed why this is working, but all the text that you see on the view is no longer a Text mark type. It is a label of the Gantt marks if that made any sense.

But now that we’ve got that nice alignment, we can manipulate that placeholder value to get the exact alignment that we’re looking for. So for example, if I wanted to move these Sub-Category labels further to the left, I could edit the axis for that first column and maybe make it something like minus 1 to 9. And because that Gantt mark will always be at 0, just by manipulating the axis range, I can move the words left and right. They’re already left aligned, but I just moved them further to the left because my axis scale now starts at minus 1 and goes all the way to 9.

For these others, maybe I want to change the label alignment to be left instead of right. So I’ll do that first on all three of these. And I will change their axis scales to go from minus 9 to 1. Instead of minus 1 to 9, this would move everything over to the right, so minus 9 to 1. Let me do that for the others. Minus 9 to 1 and minus 9 to 1. All right.

So you can see. I’m going pretty fast here, but I can also make the table skinnier. You can mess with this alignment. You can alter the axis scales to really format this however you’d like. To get the color back, I’m going to go back to the All Marks Shelf and add my Sub-Category Set again. Actually, it’s already on there. What am I talking about? I just don’t have any dimension members in the set.

So let me go add that just to show you that this is still working. Notice now the marks are being colored, and the marks are the Gantt marks. So those Gantt bars are turning red, but the words are not. Well, there’s luckily a very nice feature in Tableau that provides an easy fix for that. Just click the Label Marks Card, click on the Font dropdown, and choose Match Mark Color. And now you can see that the labels are colored the same as the Gantt bars.

One more thing. I no longer actually want to see the Gantt bars, but I don’t want to get rid of the color on the labels. The trick to that is to click on the Color Marks Card and reduce the opacity of the marks all the way to zero. That will make them just disappear, but the labels will still be intact.

So if I just take this slider all the way to the left, the Gantt mark goes away. My labels are still colored based on whether or not they’re in the set, and we’ve got the more traditional alignment. From here, obviously, you could clean this up pretty significantly. I’d probably add some row dividers. But at a minimum, what I would do is hide this header. That number is completely arbitrary and meaningless. It’s just forcing us to line that label– or that mark, rather– up at 0 every single time. It means nothing to the user. So at a minimum, just make sure you hide that last header by right clicking and deselecting Show Header.

This has been Ryan with Playfair Data TV – thanks for watching!