How to Conditionally Format Individual Rows and Columns in Tableau Like You Can in Excel

Ever wanted to use Tableau like Excel by modifying individual cells on a spreadsheet?

This video explains the difference between Excel’s spreadsheets and Tableau’s crosstabs; then shares one of my favorite hacks that allows you to conditionally format in Tableau like you can in Excel.

Hi, this is Ryan with Playfair Data TV. And in this video I’m going to show you two of my very favorite techniques in all of Tableau. The first is a hack that allows you to use Tableau just like you would use Excel.

The reason I love this trick is because I find that a lot of beginners with Tableau that have been doing most of their reporting in Excel try to use Tableau like Excel. But they’re very different tools mainly because Tableau doesn’t actually make spreadsheets. It makes text tables and cross tabs, but it doesn’t make spreadsheets in that you can modify individual cells. That’s not the default behavior of Tableau.

But this hack is going to show you how to do that. And we’re going to use this trick to build one of my favorite dashboard elements. I often like to build a little three row widget that shows– on the top row, it’ll show the current performance. On the second row, it’ll show the comparison performance that can be either a prior period or sometimes it’s a goal if you’ve got that type of data. And then on the third row, it compares those to each other on a 100 point index score.

So I’m going to show you how to do all that in this video. This hack involves a special calculated field that I often create. And I just call it Placeholder. So I’m going to create a calculated field. I always call mine Placeholder. And the entire formula for the placeholder is the aggregation MIN, open parenthesis, the number zero, and a close parenthesis.

This simply ensures that whenever I use this placeholder field, the value will be 0. Also, because it’s quantitative, it will be classified as a measure. So I’m going to click OK. We’ve got a new measure down here called Placeholder.

The trick to getting this to work is to put this placeholder field onto the rows shelf. That’s what’s going to create are three different rows for this dashboard element we’re going to eventually make. So I’m going to put the placeholder field onto the rows shelf.

From here I’ll use my favorite shortcut in all the Tableau, which is to hold down the Control key while you click on a pill. And that creates an exact duplicate of that pill. I’ll do that one more time. Hold the Control key, click on the second placeholder field, drag it next to itself to create a third row.
Just like with our dual axis maps and dual axis combination charts now that we’ve got more than one measure on the rows shelf– and this also works for the columns shelf– they each get their own marks shelf over here. And what’s important about that is we can edit those independently of each other.

So on the first row I can have not only a different value, but also a different mark type. So I can change the mark type to text. I’ll also change the mark type for the second row to text. And I’ll change the mark type for the third row to square. And I’ll click on the size Marks card. And drag this to the right so that that square fills all available space.

So at this point we’ve made this little skeleton for the element we’re going to make. There is a empty cell in the first row, an empty cell in the second row, and an empty square on the bottom row. We’re now going to populate these with our values.

I went ahead and created these calculated fields off-camera, but I’ll show you how they’re made. We’re pretending that the current performance is May and the comparison performance is April. But just to give you a quick look under the hood it’s a fairly simple formula. It’s just saying if the month of order date is 5, which is May, then show sales. So I’ve isolated May sales. And then I did the same thing for the comparison metric.

Now that I’ve got those, I’ll populate the cells on the first row only. I’ll put the current performance on the Text Marks card. Make this a little bit bigger so you can see it. On the second row, I’ll put the comparison performance, April sales.

And then for the third row, we need to make one more calculated field. I’ll go ahead and share this. It’s not just exclusive to Tableau. This is more of an analytical calculation that I like to do in a lot of my analyzes. It’s a 100 point index score. The reason I like this so much is for two reasons.

It A, normalizes data across different data types on a 100 point scale. So you can compare currencies to ratios to decimals to integers. And they’ll all be normalized on a 100 point scale. 100 point scales also make the math very easy. Anytime you’re above 100 you outperformed the comparison. Anytime you’re below 100 you underperformed the comparison.

And because it’s in units of 100, you can do the math by just subtracting 100 to see how much you increased or decreased. For example, an index of 120 would be a 20 point improvement. An index of 90 would be a 10 point decline.

The formula for this calculation, which I’ll call in this case May vs. April index, is the current performance. So in this case, May sales divided by the prior performance in this case, April sales. I’ve already aggregated those two things in the calculated fields below so that’s good for now. But just to ensure that happens first in my calculation I’ll go ahead and wrap that in parentheses. And then you close this by multiplying it by 100. So that’s the entire formula. Current performance divided by comparison performance times 100 will get you a 100 point index. I’ll click OK.

I’m going to add that as the label on the third row. And any time I have a calculated field I like to start by QA-ing it. So I’ll just open the calculator on my PC. And just do the math to ensure Tableau properly computed that index score.

So we’re looking for 155,029 divided by 137,762. And we’re going to multiply that by 100. And we get 112.5. Perfect. Now that I know it’s working I can use that in the future. I’ll also maybe go ahead and change the format of that number. Typically, with 100 point index scores I do not use decimals. So I’ll navigate to this third drop down for numbers and change it to Number custom with no decimal places.

I can also align that in the center of the box. Maybe make it a little bit bigger because that’s really kind of the prime number. Also with these index scores I typically like to color this box based on whether it was a positive improvement or a negative decline.

This little element– one of the reasons I like it so much is it’s kind of a gateway for people that are stuck on just looking at raw Excel spreadsheets just seeing the raw numbers. Those are still provided, but we’re going to sneak in a preattentive attribute of color. We’re going to color this box based on whether the performance improved or declined.

To do that I’m going to replicate the appearance of this May vs April index. Right now it’s only on the Label Marks card. I’m going to hold Control and also move it to the Color Marks card. We see a color legend appear. It’s only got one color on it. It’s because we only have one value on the View. So there’s a couple of more changes we need to get this to work how we want.

I’m going to edit these colors A couple of ways to do it. You can click on the Color Marks card. You can click this down arrow and click Edit colors. My preferred method is just to double click on the color legend. With these 100 point index scores I like to choose a diverging color palette and set the step size to 2. This will just show one color if it’s a positive value or a value above 100, and a second color if the value is less than 100.

Last thing we need to do is set the center. Because our center is something other than zero, we have to click on this Advanced button. Click Center and set it to the 100. Now any time the index score is above 100 it’ll be color-coded blue. Any time the index score is less than 100 it’ll be colored this dark orange color. Click OK.

There’s a few more things we could do, but that’s essentially it. I can de-select show Header on these placeholders. Those are irrelevant. I could format those zero lines a little bit, but this is essentially it.

One more tip for you if you’re not sold on the 100 point index score, I’ll show you how to very easily modify this calculation to create a percent change instead. So the first part of it is actually the same. Current performance divided by comparison performance. But instead of multiplying it by 100, you’d change this to minus 1. And that’ll get you a percent change instead of a 100 point index score.

But there you have it. This is one of my favorite hacks and one of my favorite widgets in Tableau.

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