Tips for Formatting Text Tables in Tableau

Unlock complete flexibility for Tableau crosstabs

Learn how to take Tableau text tables beyond the defaults by (1) creating tables with more than 16 columns (even prior to version 2020.1), (2) formatting table columns independently, and (3) conditionally formatting individual cells like you can in Excel.

Hi, this is Ryan with Playfair Data TV, and in this video, I’m going to be providing several tips for formatting text tables in Tableau. Tableau has gotten a lot better at text tables over the years I think due to popular demand, so here’s the current state of the default table in Tableau as of Tableau 2020.1. There’s actually some things that I really like about the default tables in Tableau. One of the main things I like is the text is left-aligned while the numeric values are right-aligned.

And there’s not too much redundant data ink on here or non-data ink. So if you’ve seen some of my other videos on how to design different visuals in Tableau, a lot of my design tips are tied to the concept of maximizing the data-ink ratio. That’s a term coined by Edward Tufte in his 1983 book, The Visual Display of Quantitative Information.

But it essentially states, for all the data on the view– or I’m sorry, all the pixels on a view, we want as much of it as possible to be dedicated to data. And Tableau actually does a really good job with that. There’s a few extra lines and shading in here. So this is a– my first tip is definitely take it or leave it, but just to point out that there is some– very much some formatting flexibility in Tableau that you can access just by right-clicking anywhere in the view and clicking Format.

And one thing that’s standing out to me is I might clean up the shading on this. That’s on this Shading tab, and there’s something called Row Banding. That light gray, it’s OK, and again, this is a case-by-case basis, but you could turn that off to clean this up just a little bit. So I click None there, None for the header. That’s already significantly cleaner. Now if you’ve got thousands and thousands of rows, you might want some of that banding in there, but that’s just one choice you’ve got.

Also, those lines are a little bit heavy relative to what’s on there now. You’ve got some choices there as well. You could change the row dividers and column dividers maybe to a dotted line. Just gives them a slightly less heavy feeling. It’s still there, it still separates the categories, but just a little bit lighter feel. You can also toggle some of these headers on and off. I usually typically turn off the headers for my dimensions. So you can do that just by right-clicking and hiding the Field Labels is what those are called.

So that was the first thing, just using Tableau’s inherent formatting flexibility to clean up a table. But what I’m going to show you next is how to unlock complete flexibility in terms of formatting as well as usability for a Tableau text table. One of the reasons I’m showing you this is not just for the design purpose of this, but one of the biggest frustrations I’ve observed for new Tableau users is text tables. They– almost every time the first thing they try to do is use Tableau like they use Excel, but they are working with the misconception that Tableau is creating Excel-like spreadsheets.

It’s very important to know that this text table on the view right now is not a spreadsheet, in that we can’t click on an individual cell, change its value, or click on an individual cell and conditionally format it. So that’s a big point of frustration for people that are so used to using Excel, but text tables in Tableau are essentially a, quote-unquote, “visual.” I don’t consider it a visual, but it’s a visual of that underlying spreadsheet data. It’s just a visual in a tabular form.

What I’m going to show you how to do here is make it so that it acts more like a spreadsheet. We can change both its formatting and we can dynamically conditionally format certain cells. Tableau, as of version 2020.1, has expanded the number of rows and columns that you can use in a table. Prior to that version, after you got past six columns, it would start concatenating column names together. You could in the past bump that up to 16. You can now bump that up all the way to 50.

And if you’re in a situation where you need more than the default six columns, you can do that by clicking Analysis in the top tab– top navigation, hover over Table Layout, and click Advanced, and that’s where these options are. You could bump up this number. Again, prior to version 2020.1, you can only bump that up to 16. You can now bump that up to 50.

But I’m going to show you a trick that has always allowed you to do this. So I’m going to start a new table on a new sheet. I’ll call this my Flexible Table. And it uses a series of placeholder fields to essentially create fake columns. I use this so often that I typically create a calculated field out of it and I just call it Placeholder. And the formula is typically the aggregation MIN for minimum and the number 0. This Placeholder field is simply creating a new measure which value always equals 0.

If I were to drag that onto the Columns Shelf, it will create a x-axis that equals the value 0. But the important thing is I’ve created a column. So if I were to duplicate that Placeholder field, I could create more and more columns. So our default table had six things on it, I could create five more copies of this Placeholder field and create six columns in this table. So I will use my favorite shortcut to do that, which is to hold the Control key while I click on the Placeholder field and drag it right next to itself. And I’ll do that five times. So there’s 1, 2, 3, 4, 5 columns, and a sixth column.

You might have noticed in your peripheral vision that as I duplicated that Placeholder pill, it was adding other columns over here– I’m sorry, other Marks Shelves over here. What’s important about that is those can now be edited independently of each other. So let’s see, maybe I will put my Category and Sub-Category fields on here like they were originally. And actually, those created my first two columns, so I’m going to drag the first two Placeholder fields away.

So my entire default table had six columns, but the first two were dimensions. I just added those as the rows and columns, but I’ve got four more Placeholder columns which I can edit independently. And why I’m introducing this tip and why it’s closely related to that columns limit that I just showed you is I could create 50 of these Placeholder fields. So even if you’re not on the latest version of Tableau and your company takes a long time to update, you can get around that column limit by using this special placeholder trick.

But we’re going to work with this smaller table for now and just pretend we’ve got these six columns. Because each one of those measures get their own set of Marks Cards, they can be edited independently of each other. So I could change the mark type on all these to Text, but drop in different text for each different column. So the first column was Quantity, I could drag Quantity to Text. The second column was Sales, I could drag sales to Text. Third column was Profit, fourth column was Profit Ratio. Just make sure I had that right.

And these are now completely independent. So I can conditionally format numbers based on whether there’s a positive or negative change. I can even use a combination of mark types. If I wanted to have a bar chart or a square or circle marker to indicate certain changes, I could do that. Other columns could be text. These are now completely independent of each other. So we’ve just unlocked completely flexible formatting for our text tables.

One of the drawbacks with this approach is that you get this center alignment. So I’m going to show you how to fix that so that we’re back to having left-aligned text but right-aligned numbers. First thing I’m going to do is change the mark type from Text to Gantt Bar. And you can already see the alignment coming into place. I’m going to fit the Entire View so there’s enough room for all those labels to show. But these are now all aligned perfectly, and they are by default right-aligned to that Gantt mark.

First thing I’m going to do if I want to flip the alignment so that they appear to be right-aligned, I’m going to click on the Label Marks Card and change this to be Left. So they are left-aligned relative to each Gantt mark, but they appear to be right-aligned now within my table. Also, if I want to move those marks to the right, I can just change the axis range on these Placeholder fields. By default, it’s always showing the number 0, but if I right-click on the axis and click Edit Axis, I can change that range to whatever I want by clicking this Fixed radio button.

So if I looked at maybe 0 to 10, let’s see where this moves on the view. Well that’ll get me right-aligned now because this number’s 0, so maybe I should actually do 0 as the end but negative 10 as the beginning. Now you can see this really coming together. And I just did that for the first column just to illustrate the difference, but you can obviously roll that out across the entire table and everything would be right-aligned. So this is extraordinarily flexible. Let’s just pretend I had done that for the other columns, and once I’m ready to use this, I would just show– I’m sorry– right-click on the header and deselect Show Header to hide that header.

The only drawback with this approach in my mind is we lose the column headers. So notice on the default table, we’ve got the names of the fields there at the top, but on our new flexible table, we lost those headers. And that is a drawback, but we’re going to make lemonade out of lemons here and I’m going to show you an advantage to having to recreate those headers.

What I’m going to do is duplicate our table just so I can get those Placeholder fields back in place, and let’s say I’m going to clean up the table by just removing everything. And this time, I need this text to be at the top. So I am going to remove Category and Sub-Category and add two more Placeholder fields. Actually, I will just use my shortcut– hold Control and duplicate that twice. Again, these are still independent of each other.

So for the first two, I’m going to change the mark type to Text, and I’m just going to type in the name of this field. I need to get this back to be an Automatic range. I’m going to just type in the name of the field in the flow here. So the first column was Category. So if I– I just double-clicked on the Marks Shelf, typed in the string of ‘Category’, and click Enter, by default, that field is placed on the Detail Marks Card, but now if I drag it to Text, that is what will be displayed as the text.

I’ll do the same thing for Sub-Category, which was the second– I forgot my tick marks– Sub-Category, click Enter, that gets added to the Detail Marks Card, I’ll drag it to Text. And you could roll that out across all six column headers, but here’s where some of that flexibility adds value. Instead of simply showing the text, I can make these column headers themselves dynamically formatted or conditionally formatted in a couple of different ways.

The first way I’ll show you is if I’m ever wanting to just call attention to a certain column– let’s say I’m making this nicely formatted text table and I want to eventually take a screenshot of it, put it in a PowerPoint presentation or an email. And let’s say we just want to for some reason highlight the Sales column. So that would be the second column of numbers in this table.

The first thing I’m going to do, instead of changing the mark type to Text for the remaining four column headers, I’m going to change the mark type to Square, click on the Size Marks Card, and make those squares larger so that they eventually fill all of the space on each column. So I did Shape, it needs to be a Square, and I’ll do Square one more time– two more times. Square one more time now for the sixth and final column.

And if I had put this back into Standard view, maybe make this a little bit skinnier– that was a little too skinny– you can start to see why this is going to work for me. So we’re essentially just making column headers out of blocks at this point. All right, we’ll call that good.

So we’ve got these four different squares as the mark type now for the column headers for each of my four measures. They’re still completely independent of each other. So for the first one, I could make that maybe a light gray, but for whatever I want to call out, I can make it orange or a brighter color. So I’ll make everything that light gray except for the one that I want to call attention to.

I can also add the column header names as labels. So if I wanted to call attention to sales– I’ll just do the first couple here. So the first column was Quantity. I could type Quantity. That adds it to the Detail Marks Card, but I can then drag it to Label. And maybe I want to center that so that it looks like the first two.

You could do that for Sales as well. So Sales gets added to the Detail Marks Card, drag that to Label, and I will center that. And I might as well do the rest. So we’ve got Profit and we’ve got Profit Ratio. Now that I’ve got all the column headers in place, again, I no longer need their headers. You can hide those by right-clicking on a header and deselecting Show Header, and those will all go away for you.

To bring this together, we need to float these together on a dashboard. Well, you can also use Tiled, but Floating will give you a little bit more flexibility in aligning them with precision. So ‘Flexible Table 2’, those are actually my “Table Headers”. I’ll give that a better name so I can go find it when I add it to a dashboard. I’ll drag my Table Headers onto the view, hide its name, maybe make it fit the width, and make this a little bit longer.

I’ll do the same thing with– actually, I’ll make this fit Entire View. Do the same thing with my Flexible Table. Drag that onto the view. This one’s a little bit tricky to set up the first time because you’re going to need to make sure that these are aligned. So let’s see, our width of this was 775. I’ll try that to start with over here on my table. And this was at 124 pixels in on the x-axis, and I will just reduce its location on the y-axis to get it to line up with my column headers.

So you can hopefully kind of see where this is going. I’m going to hide some of this stuff to clean it up, maybe make this taller, and change this alignment a little bit. I’m just trying to get these to line up a little bit better. There we go. So for time, work with me just a little bit. I didn’t clean this table up nearly as much as I could have, but I’ve cleaned up the Category column, the Sub-Category column, and the Quantity column. I’ve also customized that header to call attention to a certain column in the underlying table.

So that’s approach number one. Because we can use a combination of mark types and they can all be conditionally formatted independently of each other, we can use that to call attention to a certain column in a table. Another even perhaps more practical purpose would be to use those column headers to illustrate some change in the business. Again, these four blocks are colored independently of each other. So for each one of those, I could navigate– I’m just working with Sales at the moment, which is the fourth column. So I’ll click the fourth Placeholder field. That opens up the fourth Marks Shelf. And maybe instead of coloring it by– just coloring it orange to highlight it, I’ll type a formula here in the flow.

We’ll say Current Sales is greater than Comparison Sales. By default, that gets added to the Detail Marks Card, but if I drag it to Color, whenever that is True, it gets colored one thing; whenever it is False, it gets colored a second thing. For False, I probably want that to be red to illustrate a negative performance. And before I recorded the video, I set up these parameters that just allowed the user to choose a Current Month and a Comparison Month.

So February compared to January is False, we had a negative month-over-month change in Sales. If I bumped this up to March, we have a positive change, so True should probably be colored blue. That looks good. But the point of this is now if I go back to my dashboard, those column headers will dynamically change based on their period-over-period performance.

So I really like that. Not only did we just unlock complete flexibility with how we format a table, but we made something more practical that is kind of forcing our users that are kind of stuck in that spreadsheet mentality to leverage the preattentive attribute of color to at least know where they should start an analysis.

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