How the EXCEL do I format that?! 5 Tips for winning formats.

Formatting in Tableau is a secret superpower. Yet, with the myriad of options, it can get complicated. Looking to format like a pro and not wonder why that line is there? Look no further…

1. Defining format with Tableau

Tableau allows you to format so much, it’s almost dizzying. Beyond what you see in the marks card, here’s what you can control:

• Fonts
• Text alignment
• Shading
• Borders
• Lines

At a glance, these seem woefully limiting. There’s a lot tucked into this. Next, take a look at the marks card:

• Color
• Size
• Label
• Detail
• Tooltip
• Mark specific options (Path, Shape & Angle)

Marks are treated differently from general formatting. They’re the most obvious as far as controls, but also a key pain point, which we’ll cover.

2. Think in Levels or Grains

Tableau workbooks are rendered in XML. This is absolutely key to understanding how formatting rolls out. Nesting is the paradigm when it comes to Tableau formatting. If you’ve ever used mindmapping software, Notepad++, or any other tool that expands like a spider web, this is the paradigm you want to keep in mind.

a. Workbook level

As a newer feature, this is the “format all” button we’ve waited for. Go to Format / Workbook and it appears on the left. So far, you can set fonts and lines. When you set formats here, they become the general defaults for the workbook. If you’re copying things to a workbook where you’ve used workbook formatting to another where you haven’t, you’ll notice the style changes.

You can also crash any customizations your coworker has done if they’ve glitterbombed a dashboard. As you’ll learn, it’s very easy to bypass these defaults. And, if you apply them later, it resets everything.

Key tip: Use this at the start, rather than at the end. That will allow your exceptions to hold.

b. Data source

This is the least obvious level, but it’s where marks-centered formatting technically lands. Ever swap a data source and find all your carefully selected colors destroyed? Blame this.

Yet, data source formatting is also a superpower. Do you want certain dimensions to always be a certain color? Why, yes, you can make it happen and confound your coworkers with your abilities!

Things applied through the Marks Card care about the data source. While not technically formatting in Tableau’s parlance, it’s a common problem cited under formatting.

Key tip: Before you swap a data source, load everything first. THEN swap and your colors are more likely to hold (Thank you David Pires for this!).

c. Worksheet level

This is probably the most familiar level, and also the most confusing. At the worksheet level, there’s actually 3 choices.

The sheet level applies a rule to both columns and rows, with one very large caveat: that nothing in rows or columns says otherwise. Have gridlines on rows that you want to go away? Click that rows option and there they are!

Here’s the back story: Long before Tableau 10, we had less gridlines and more borders and shading and more bolded items. You can see what it looked like before by selecting Format / Workbook Theme and any option your heart desires. Tableau 10 moved the needle, based on a blend of research and the stellar designs of Kelly Martin. Someday, I should join Clubhouse and do a long chat like the cool kids on how she pioneered the modern Tableau dashboard.

What you really want is not more themes, but the ability to make your own. You can upvote this idea by visiting the forums.

Key tip: if you’re feeling punked by a rogue line, check either rows or columns, as these override sheet settings.

d. Field level

Ever go into an axis or metric and format? Oh yes, this is yet another level in the formatting funnel! And when you replace it with another metric, oh yes, the formatting goes right with it!

Key tip: the more you do at higher levels (worksheet and above), the less frustrated you’ll be later if you like consistency.

3. Inside vs outside

When in doubt, favor out. If it’s on a whim, it’s totally in.

The #1 formatting question revolves around the lines tucked in rows and columns. If you can’t find it and it’s only happening in one direction, you can almost always guarantee your formatting conundrum is down to the hidden rows and columns choices.

If it’s both directions and still mystifying, chances are high it’s hiding in the last option, Format Lines. Here hides all the fun and absolutely mystifying lines: grid lines, zero lines, trend lines, and yes, the demonic Axis Rulers. If you do a dark or inverse theme, you’ll see the rulers straight away. Evil…absolute evil.

If it’s inconsistent, you can also bet it’s a field level choice. You can also get to the fields here, just in case you only want to format one.

Key tip: exceptions always win. If you format inside (rows, columns, fields), they’ll ignore what you put outside.

4. The Super Secret Mobile Hack

I remember begging for sheet transparency. Then, silently, those kids released it with hardly any stated fanfare on their part. (We will not discuss the parade I held.) It works everywhere, even on maps (if you drop out the base layer).

What if you want different colors, depending on device? You know how modern sites roll – they go light on desktop, but dark on mobile. Up until now, we’ve completely ignored the layout pane available when designing dashboards. Why let sheets have all the fun when there’s dashboards?

In the layout pane, you can change colors, add borders (if you want to be hateful), and add padding (if you tile). You can also color assets like text, blank boxes, etc here.

Yet, here’s the super amazing trick we don’t discuss often enough: these settings are device-specific. You CAN recolor that dashboard for mobile right here IF you set the primary worksheet shading to none.

Think of worksheets like the stickers and these formatting options like paper below them. You can have fun with stickers all day, but you can also draw around the edges. If you have transparent stickers, you can do more with the paper.

Key tip: remember inside-outside, but also conditionals. Device designer and Layout formatting is at a whole different level.

5. Fix it Felix

Every now and then, things happen and it’s hard to trace back all the steps. You can use the sledgehammer (Format Workbook) or worksheets have a hidden fix-it button as well. Go to Worksheet / Clear / Formatting and whisk it all away. If it’s really bad, you can wipe out the sheet too, but I only recommend this if deleting feels too harsh.

You can also do this with dashboards, but it is a broad clean-out. And, yes, you can also format paste from a worksheet as another way to salvage viz gone bad.

To Infinity and Beyond

To gridlines everywhere, good night!