That's a shame. Joe's right, the formatting isn't always great, and disappoints a lot of our users. We make big claims that Tableau's great because we can quickly create reports, and you, the user, can export the data to Excel. WHen they see the output, they're often a bit let down....
Massive +1 from me on this.
The export to Excel is very weak.
1 of 1 people found this helpful
If anyone else wants to remove the formatting Tableau applies when you export to crosstab, here is some simple VBA code that I use:
Sub CleanTableauCrosstab() Dim singleCell As Range, mergedRange As Range, mergedCell As Range ActiveSheet.Copy After:=Worksheets(ActiveSheet.Name) For Each singleCell In ActiveSheet.UsedRange If singleCell.MergeCells Then Set mergedRange = singleCell.MergeArea singleCell.UnMerge For Each mergedCell In mergedRange mergedCell.Value = singleCell.Value Next mergedCell End If singleCell.Style = "Normal" Next singleCell End Sub
This will duplicate the sheet, go through all used cells, and if merged, it will unmerge (filling in the value to all cells in the merged range), and then set the format for all cells to "Normal".
I consider this an example of Tableau trying to be too helpful, and as a result I have to do additional work to undo what Tableau thought was a good thing to do.
A possible better way to approach this is to give the user the option to retain formatting, or to not format. Have it be a message box that falls under the category of "Help -> Show Messages Again" type of dialog.
Nice Joe... very nice.
With Export to Crosstab, I think Tableau has tried to meet two needs that don't completely overlap:
Need 1: A nice export to Excel that duplicates the formatting seen in the table in Tableau.
Grade: C- at best... my issues with this are covered here: http://goo.gl/w7rdD
Need 2: A completely non-formatted version of the data displayed in Tableau. This can be used as an input to other analytical work.
Grade: C. This could easily be bumped to a B or an A if you could choose which measures were included in the export.
3 of 3 people found this helpful
Try Edit > Copy > Crosstab instead.
Thank you very much, you just made my day!
I apologize for missing that menu option, and creating an unneeded complex alternative, but hopefully the VBA code proves useful for anyone else looking to unmerge and fill cells in Excel.
Our people use both the formatted crosstabs and the unformatted crosstabs. 90% though prefer the unformatted crosstab because they turn on Auto-Filters. Auto-Filters do not work on the formatted crosstabs that Tableau currently creates.
Think about it this way, they see the relationships in the visualizations, but often need to dig through and act upon the underlying data. Maybe Tableau can make an "Export Crosstab Data to Excel" option and with Auto-Filters switched on?
1 of 1 people found this helpful
For reference, the option:
Edit -> Copy -> Crosstab
produces different values than
File -> Export ->Crosstab to Excel
The Copy command will only copy as the value is displayed, while the export command will export the full value and then format it. So if your value was 12.34567 and your format in Tableau had it show only two decimal places, the copy command would put just 12.34 into your clipboard, while the export command will put the full value 12.34567 in to Excel, and then format the cell in Excel to round to two decimal place, and removing the formatting in Excel will display the full value.
While we're on this topic, the difference in behaviour when exporting data from Tableau Server is a bit of a mish mash. Only a CSV gets exported, whichever option you choose, which can be a pain for users. We would rather see much more functionality on the copy/export of data from within desktop and server
I enthusiastically second that.
Improved Tableau Server export to Excel would be a massive win.
I think is the appropriate thread to which to file the following request:
When I use the Edit >> Copy Data option (or when I hit Ctrl +C) on a crosstab in order to paste the data into a spreadsheet, the order in which the columns are pasted is the original order in which I placed the dimension and measure pills onto the Row shelf. Even if I've rearranged the pills on the shelf, I still get the original order. Can you fix this? I don't need the original order, I'm expecting my columns to be pasted over in the order in which I have them right now in Tableau.
I second that! Having to rearrange the columns in Excel is a pain. I would love for the product to keep the same column order after copy or export as is shown in my Tableau view.
Hi Joe, I'm not familiar with VBA but have managed to successfully replicate this across my cross-tab, with one slight glitch - My unique identifier converts from text to number for the copied rows. As I have some identifiers that begin with a '0' I am unable to convert all text to number as this will omit the leading 0's. Is there a variation that could be applied to the code which would copy the new rows in text format? If you have any tips you could share it would be fabulous, failing that I'm going to shove a whole bunch of unnecessary extra fields into my cross tab to force it to display the info i need on one row without merging the key fields.
For quite an expensive bit of software, I am finding it doesn't quite do all the things that one would logically expect which is unfortunate so I hope the Tableau team address this in future revisions.