Exporting a Tableau viz data into Excel (html Excel button)

Rolling out Tableau to 500+ users is challenging, especially if your users are coming from old BI tools like BOBJ or Cognos. The first question most of them will ask is: where is the Excel download? Yet Tableau does not have an Excel download option, only csv. Yes, you can open csv in Excel, but unfortunately, csv is problematic when you are dealing with an international audience as dots and commas get misinterpreted when you open the file in Excel. This is bad as it messes with the numbers, which is the reason why users are downloading files in the first place. There is no direct Excel download from Tableau that I know off, so I built one.

OK, so how do we do this?

With Javascript of course!

Here is a great guide to build a web button to download any Tableau data from any viz into a csv (text) file: themarkscard

Yep, I know, amazing. Again, many thanks to themarkscard for this.

I did few changes to the initial code:

  • I moved from the csv format to a direct Excel workbook (.xlsx)
  • Added the possibility to define entire columns as value, so Excel will read them as numbers and will allow math calculations on them
  • Replaced null values from %null% to 0

The result looks like this, try clicking the button below:

And the Excel looks like this:

Excel Tableau pivot example

I have put all the script variables that need to be updated by you (the tableau developer), so you can easily to adapt this button to your case. You can see below the rows that need to be changed by you, only 2 changes to be applied.

[js]
//Add here all columns that you want to see as numeric in Excel (if not they will be showing as text and will need number conversion)
intCols = [‘SUM(Quantity)’]
//Viz url
vizUrl = “https://public.tableau.com/views/getData_Demo/getData_Demo?:embed=y&:display_count=yes”
[/js]

Next step, very important additional feature for our Excel users

OK, so the user can now download the raw data, but this is still a pretty basic Excel. Also, I am still producing a lot of crosstab reports in Tableau (they look like pivot tables), so users downloading this expect a table not a raw data format. There are 2 ways I can think of solutioning this:

  1. We rebuild the table using javascript. Cool, but will be a lot of code (and the issue is that code needs to be maintained, so less is better)
  2. We build the pivot table in Excel and we ask the users to replace the raw data with the new downloaded data every time
  3. We do .2, but we host the Excel file somewhere on the intranet and we update the raw data ourselves on downloading using javascript

Of the 3, I think option .3 is the best one, followed closely by .2. Option .2 is obviously not very efficient as users need to do some (very quick) data crunching themselves. Anybody wants to work on option .3 please let me know! I think it would be pretty cool to get an updated script that can do this. I might take another stab at this at some point too.

This is the solution in Excel using data from the above dashboard. You can try yourself to update the “data” sheet using the fresh download from the viz above. I am linking to the spreadsheet file below again for your convenience.

Excel Tableau pivot example

As for me, I am going to put this into production soon. It will be a matter of training and change management to explain users how to replace their data sheet with the fresh download, but it won’t be a huge challenge (also I will put a link to the Excel file on top of each of the dashboards, so they don’t have to remember where to find it).

Philosophical debate

If you came here for the technical solution, you don’t need to read this.

It is quite clear that this solution can go very far. We can build charts, pivots, fancy vlookup tables, all in Excel, then we can ask the users to just refresh the data sheet and wow factor is secured. However, Tableau is an online tool and the idea is to stay away from local files and, especially, spreadsheets. I think this idea has legs and we need to move towards that, especially because we are more and more on the web and on the web it makes just a ton of sense to embed Tableau dashboards instead of asking users to download files. This is an interesting debate, but reality check – if the majority of the users ask for an Excel download, you better give it to them, otherwise even the adoption of Tableau itself will suffer.

Thanks for reading

If you have questions or comments, please use the section below, I am always interested to hear feedback or fresh ideas.

3 thoughts on “Exporting a Tableau viz data into Excel (html Excel button)

  1. Great article! I am currently trying to accomplish this for our users. Do you have your code available for us to see the changes you made? I’d love to see how you did this.

      1. Yes. Pretty cool.

        My use case has changed a little so I’ll be creating a custom toolbar with clickable icons to trigger the javascript API. The following will work for me now:

        function exportPDF() {
        viz.showExportPDFDialog();
        }

        function exportImage() {
        viz.showExportImageDialog();
        }

        function exportCrossTab() {
        viz.showExportCrossTabDialog();
        }

Leave a Reply

Your email address will not be published. Required fields are marked *