Online tableau training, tableau tips, & video tutorials
How to Align Text Like Finance Reports in TableauPreview
Match the formatting of your P&L statement in Tableau
Analysts who are used to building text tables in Excel often get frustrated with Tableau’s crosstab formatting limitations. Learn two ways to make text table formatting more flexible so you can match the financial reports you have around the office.
Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you two tricks that allow you to align your reports like traditional finance reports. What I mean by that is, it never fails. Whenever I’m working with a finance client, they really want to align the text in different ways. They’ll have a different number of spaces for things like the original metric, subtotals, totals. But Tableau doesn’t allow that flexibility to have those different indentions based on the row number in a dataset. Well, I’m going to show you two ways to get around that, so you can make your Tableau text tables look just like your finance reports.
The first one is a real easy one. Actually, they’re both real easy. But let’s just say that we’ve got a field. You can do this with any dimensions. So any row names that you are using, this technique is going to work. Just so that I can share it and you can follow along, I’m just using the Sample Superstore dataset. And we’ll focus on the Sub-Category dimension. I’m going to double-click on that dimension, which by default, will add it to the Rows Shelf.
And the first trick to creating these different indentions, like on a traditional finance report, is to simply edit the aliases of them. So let’s say, every sub-category that starts with the letter A, we want to have five spaces. To change that, you can simply right-click on each one and click Edit Alias.
And I will bump this up to 1, 2, 3, 4, 5. Click OK. Accessories is now indented five spaces. I’ll do that for Appliances. 1, 2, 3, 4, 5, click OK. I’ll do that for Art. I’m just editing the aliases, bumping them up to five spaces.
If that’s not fast enough for you, there is another place you can edit the aliases, which is by right-clicking directly on the dimension from within the Data pane, and clicking Aliases there. This allows us to, instead of right-clicking on each one individually, we can just highlight it within this interface. Let’s say for the sub-categories that start with B, I’ll bump it up to 10 spaces, so 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.
I’ll do that for Bookcases, as well, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. I must not have gone quite far enough. There we go. We’ll call that good for now. Watch what happens when I click OK; those two sub-categories that start with a B are now indented 10 spaces. The first are indented five spaces.
You might be wondering or thinking to yourself– and that’s bugging me, because I see that I did have that right actually. I thought I might be able to count to 10. So those are lining up pretty good now– five indentions, five spaces for everything that starts with an A, 10 for everything that starts with a B.
So what I was getting to– you might be wondering, well, that was really nice and clever. But if I go to use this sub-category dimension somewhere else, I don’t necessarily always want it to have those indentions. I have a trick for that, as well.
All you have to do is duplicate the original dimension, and the aliases are independent from each other. So here’s the copy version. If I edit those aliases, I can actually clear all the aliases at once by clicking on this button. It’s back to the original, names with no spaces.
If I click OK and add this to the view as a second column, you’ll see that the first column was my dimension with the aliases, those leading spaces. The second is a copy. It has no spaces. So I can use this both ways, with spaces and without spaces.
The second way to do this is to simply put it within your underlying dataset. So take a look at this Excel table. In column A, I’ve got whatever my line items are called. And they have the leading spaces with them. So all I would have to do is then connect to that data source.
That’s an Excel file. It’s called Table with Text Alignment. So you just make your data source have this space in the underlying data. Here’s what it looks like when I’m connecting to it in Tableau Desktop. I could go to a new sheet and drag my Cell Text to Rows, and it will come over with those leading spaces. So two different options for you, but what we just did is we created those custom indentions, like a lot of finance folks like to see it on their reporting.
This has been Ryan with Playfair Data TV – thanks for watching!
Related video: Connecting to Data in Tableau
Related video: 3 Ways to Smooth the Excel Transition
Related video: Storytelling Tip – Know Your Audience
Related blog post: Ten Tableau Text Tips in Ten Minutes
Related chapter: Innovative Tableau – Chapter 49 – Format Text Tables Like Finance Reports
- How to Dynamically Format Numbers in Tableau
- How to Conditionally Format Individual Rows and Columns in Tableau Like You Can in Excel
- How to Allow Users to Choose Dimensions and Measures in Tableau
- Exercise: Sales by Category, Region, Segment, or Ship Mode
- Using Tableau’s INDEX() Function for Easier Top N Filtering
- How to Make Tableau Histograms with Aggregate Calculated Fields
- How to Make Dynamic Tooltips in Tableau
- How to Add an Image to a Tableau Tooltip
- How to do Automatic Anomaly Detection in Tableau
- How to Make Rounded Gauges in Tableau
- How to Add a Tooltip to a Dimension in Tableau
- Two Ways to Make a Tableau Timeline when Events Overlap
- 3 Creative Ways to Use Transparent Sheets in Tableau
- How to Automatically Change Number Units in Tableau
- How to Drill into a Single Row in Tableau
- A Formatting Trick for Tableau Scatter Plots and Maps
- How to do Sheet Swapping with Tableau Layout Containers
- How to Use Measure Names in Tableau Calculated Fields
- How to Use Control Sheets for Better Parameter Actions
- How to Deduplicate Joined Rows in Tableau
- How to Add Vertical Connecting Lines to Tableau Slope Graphs
- How to Align Text Like Finance Reports in Tableau
- How to Show Top N Lists in Tableau Tooltips
- How to Flexibly Format Decimal Places in Tableau