How to keep leading zeros in Tableau Exports to Excel/CSV

Have you ever tried to export a table from Tableau to Excel, only to realize your fields with leading zeros were losing those leading zeros? Pretty frustrating!

Don’t worry, I’ve figured out a simple trick that will allow you to keep your zip codes and account numbers in their proper format so end users can VLOOKUP to their heart’s content (without ever learning how to format cells to include leading zeros themselves).

The trick works like this: If you create a calculated field that is formatted like an Excel formula, when the extract is opened by Excel, the cell will display the results of the formula (instead of just the formula text itself).

For example, the excel formula to make sure a value in cell A1 is padded with 0s up until 5 characters is this:

=text(A1,”00000”)

If the value in A1 was 123, and you put that formula in cell B2, you would see “00123” as the result.

How it Works

To make this work with Tableau, you’ll want to create a calculated field that is formatted like this:

'=text(' + [field_name] + ',"00000")'

Pretty neat!

The drawback is that this obviously doesn’t look great in a Tableau viz itself.

The simplest solution is to just uncheck Show Header – the field will be hidden on the table, but will still appear in the export. You may have to live with a solution that has one field shown and another hidden, and have both fields appear on the export, but that is easier than having only one field missing the leading zeros.

You’re probably realizing that this method isn’t only for leading zeros – there’s all sorts of data viz black magic you could conjure up using Excel formulas in Tableau exports. Feel free to experiment and go wild, but in my experience, less is more. Bringing that level of complexity into a data viz solution usually isn’t worth the effort.

Still, now that you know this exists, it’s fun trick to have in your toolkit that can really wow your clients.