Tableau’s ability to Export to Excel is really useful, and has saved me many hours down the years. This functionality is currently still lacking in PowerBi (sorry Jack but you know its true) compared to Tableau.
Quite often you will export your Tableau data to Excel to send somebody an extract of the data, maybe that person doesn’t have Tableau or the data is part of a much bigger piece of work and doesn’t warrant a whole Tableau Workbook getting sent. Sometimes these datasets will be large and can be quite a faff to sort out in Excel once it has exported. So here are a couple of tips to help reduce those woes.
1. Export to Excel function
This is the option under Worksheet > Export > Crosstab to Excel in the toolbar at the top. The advantage of this is that it will replicate the format you have in your Tableau table in Excel, which ultimately should reduce the time spend faffing at the other time. The downside however is the time it takes to actually export the data, and this increases dramatically with the size of the dataset. To show you I made a super simple view using the Superstore dataset, showing category, sub category, product name and sales. 1850 rows in total.The export took 25 seconds to complete and the format is not what I want, as I would like the end user to be able to apply filters on category, sub category and product name.
So the temptation would be to select all of the cells, unmerge them and start clicking and dragging cell A2 down until I get to the next value, and so on to the bottom of the table.
This is a massive pain and takes ages…however there is a quick way of doing this that doesn’t involve clicking and dragging.
First you unmerge all the cells in the worksheet as before, but this time set a filter up, filtering out everything except for (Blanks).
Then in cell A3 (or whatever the top-left empty cell is) and type the formula =A2. Then simply copy that formula down to the bottom of the table, the idea being that once the formula passes each new value it will pick that up and copy it down, and so on. Do that for each column that has blanks in (substituting the column reference each time) and you will have a full table, ready to be used by somebody else.
2. The CTRL+A, CTRL+C, CTRL+V
The ACV method is a no brainer for when you have data which is just one measure deep. Anywhere in your worksheet, select all (CTRL + A), copy (CTRL + C), open your Excel document and paste (CTRL + V). Easy, right?
Using my same 1850 rows I get this into Excel in under 5 seconds and in the nice flat format that I require, no fancy formulas needed.
However this method falls down when you have more than one measure involved or if you have dimensions in columns, as Tableau will pivot the data so that there is only one measure column. For example I’ve now added Quantity, Profit per Order and Discount to the view.
The export till takes under 5 seconds, but the format is not user friendly like it was when it was just one measure.
As you can see Tableau has brought in Measure Names and Measure Values as column headers.
Obviously I can now pivot this data in Excel to get it back to how I wanted it before, but this adds precious time to my analysis, especially as I need to format the pivot or copy the data back out of the pivot!
In summary, the Crosstab to Excel method is good for when you have tables which have multiple columns such as more than one measure, or more than one dimension, but it takes a while to export and you may need to do some formatting to get it right.
The ACV method is a whole lot quicker to get into Excel in the first place, but any more than one measure, or if I have dimensions in columns within Tableau, then I need to pivot the data and that is a faff too.
Overall, I only use Crosstab to Excel for small datasets which are more than one measure/dimension wide, for everything else I use the ACV method.