Ryan Sleeper
Highlight tables help smooth the Excel transition by introducing the preattentive attribute of color to a crosstab. Learn how to make this chart even better with these formatting tricks and ideas for making Tableau highlight insights in your data.
Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you three different ways to make highlight tables more engaging. Highlight tables are one of my favorite chart types because they help us smooth the Excel transition. A lot of my stakeholders in a corporate environment have been using Excel for decades. And this chart type helps you kind of take that next step into becoming visual for the first time.
You still get to see the raw data, but you’re getting the benefit of the pre-attentive attribute of color to help you process that data. To get started– and if you’re not familiar with this type of chart type– I’m going to create a quick highlight table using three fields. Order Date– I’m going to hold Control and click Sub-Category. I’m going to hold Control again and click Profit. So I’ve got three fields selected. I’m going to go over here to Show Me.
And the third option on the first row is to create a highlight table– going to click on that. I will change the fit of the view to entire view, just so that we can see these data points a little bit better. But this is a highlight table. It looks like a crosstab or text table, but there’s color in the background to help us process this. Because I used Profit as my measure, I’ve got a diverging color palette up here.
Orange is on one end of the spectrum for negative numbers. Blue is on the other end of the spectrum for positive numbers. Again, I love this chart type. This is already a huge improvement over a text table. But now I’m going to show you a couple ways to make this even more engaging. The first tip I’ve got for you is to use some of the formatting that’s already available to you out of the box in Tableau.
One of the things that I don’t love about these labels on the view is some of them are black and some of them are white. What’s happening is Tableau is trying to help us by looking at the hue of the color below the label and delivering the best contrast. Once the hue gets to a certain darkness, Tableau will flip that label to be white. If the hue is lighter, it makes the label black just so we can see it better on that background.
But most of the time I’m using hues and colors that are dark enough, where I almost always like to switch those to all white. And in fact, I think it almost looks like a mistake when you don’t do that. We see a mix of black and white. And the end user might think that that coloring means something. We don’t want to confuse them.
So if I want to switch all these labels to white, you can do that in a couple ways. First is to click on the Label Marks Card. And click this option under Font. And if use this dropdown, I can change the color of those labels to white. If that doesn’t work for you, you can click on this ellipsis, which will open a little word processor, where you can highlight the value and then change the color of the text there.
So two ways to change those labels. But as you can see, they’re now all white. Another thing I like to do– and this is true of any chart for me, where the marks are touching. Highlight tables is one of those examples. And what I mean by that is if you look at either the rows or columns in this highlight table, the marks are right next to each other. They’re literally touching. You also see this on a filled map, a unit chart, just to name a couple of other examples.
But whenever that is the case, I like to add a white border around those cells. And you can do that by clicking on the Color Marks Card. And there’s an effect called Border. And if I click this dropdown and choose white, you’ll see a white line throughout that highlight table as the borders– just adds a little bit of professional polish. My second tip for you is to limit your use of color.
Some of the grays in this view aren’t really providing much value. I can’t even tell if the values are negative or positive. It’s because we’re using this diverging palette. And by default, it runs the full spectrum. So we’ve got dark orange on one end of the spectrum, dark blue on the other end, and then we’ve got every color in between. And once you near zero, the cells start to turn gray. And they’re not providing a lot of value.
This tip’s a little bit controversial. This is a case-by-case basis, so please don’t feel like this is a blanket statement. But I typically care most about whether the values are positive or negative. And I’m actually losing that insight by using the full spectrum, because those numbers that are closer to zero– they all look a similar shade of gray. So I don’t know if they’re positive or negative right away.
You can change that by double clicking on the color legend. It will open up this dialog box, where you can remap colors. And one of the things I typically like to do, when I’m using a diverging color palette, is to check this box for stepped colors and bump this down to two. That limits the colors. And because this is diverging and we’ve got a center of zero, what this setting does is it makes all of our positive values one color and all of our negative colors a different color. It’s that simple.
So we’ll immediately be able to see which values are positive and negative. Anytime you see the Apply button in Tableau, you can click on it to preview the change before you accept it. Looks good. I’ll go ahead and click OK. And again. Just a little bit of a disclosure here– we lost some of the insight, because now we can’t tell the intensity of the positive or negative value. But this would be a good alternative choice if you care more about knowing what’s positive versus negative, and not necessarily the magnitude of how positive or how negative it is.
Anyway, my third tip is to take this a step further. So that’ll be kind of a moot point. What I’m going to do in this next example is color those cells by different thresholds. We’ll say that our goal is to get to $10,000 of profitability. And we’ll also say that we want to call attention to the cells that were negative, and we’ll leave everything else as “Other”.
So we’re going to make a calculated field to make Tableau do some of the work for us here. It’s going to immediately boil up some insight. It’s going to show us what sub-categories and years we hit our goal, which sub-categories and years we are unprofitable. And it’s going to gray out everything else, because those aren’t as important to us. So I’m going to start a calculated field. I’ll just call this Highlight Table Color for now.
And I’m going to put in that logic. We’re going to say, if SUM of Profit is greater than or equal to $10,000, then “Goal Met”. Another scenario we’ve got, or that we want to kind of boil to the top, is if it was not profitable. So for that one, we’ll say, ELSEIF SUM of Profit was less than 0, then “Unprofitable”. And then everything else we’ll just throw into bucket called “Other”, so ELSE “Other”. There’s the formula. I’m going to click OK.
And now, instead of coloring these cells by SUM of Profit, I’m going to color them by our newly created Highlight Table Color calculated field. But watch what happens when I drag this to Color. We see just a little square, and we lost our labels. What’s happening is this highlight table color is a discrete dimension. Earlier we had a continuous measure.
Well, one of the consequences to this being discrete like this is our sizing is a little bit different. It’s not going to draw a continuous axis and fill the entire cell. I can click on the Size Marks Card to make those squares larger. But you’ll see right away that this doesn’t look quite right. What’s happening is I made those squares so large that they’re overlapping and spilling into other cells.
Well, there’s a little hack for this to correct it. What we’re going to do is add a another discrete dimension to both the Columns Shelf and the Rows Shelf. That’s going to change our table structure just enough to get these squares to fill their respective boxes. To do this, just double-click on the Columns Shelf. And I like to type just two quotation marks. And in fact, I use this so often that sometimes I create a calculated field for it that I just call, “Blank”.
But that’s the entire formula– quote, quote. This is basically just an empty dimension. And if I click Enter, you’ll see it got cleaned up a little bit. What happened was it added a discrete dimension on the Column Shelf. So it got cleaned up from left to right. But I need to add that same blank dimension to the Rows Shelf. And when I click Enter this time, you’ll see this coming together.
It’s now– we’ve changed the table structure, so that those squares fill their respective cells. They’re no longer spilling into other rows or other columns. So this is a way to kind of fake Tableau out a little bit. The only negative side effect is we’ve got this dimension called, quote quote. But we can hide that by right clicking and hiding the field labels for columns, and also same thing on rows. Just to clean that up a little bit– the end user won’t even know that that’s part of the view.
Lastly, to clean this up, I might remap the colors. Instead of having Other be orange, I’ll change that to a shade of gray, and click OK. And now this is a much more useful highlight table to me. We’ve got our raw data. That’s helping my end users that are accustomed to looking at raw spreadsheets of data. But I’m also making Tableau do some of the work for us by boiling some of these insights up to the top– things that I want to focus on.
If it was above $10,000 in profitability, the cell is colored blue. If that particular sub-category and year combination was unprofitable, it’s colored red. And the things that I’m not as interested in are colored gray.
This has been Ryan with Playfair Data TV – thanks for watching!