1 2 Previous Next 18 Replies Latest reply on Jul 15, 2017 12:31 PM by Michael Johnston

    always turn off crosstab formating

    Joe Mako

      I just got the attached dialog.

       

      Is there a way to always turn off the formatting of the crosstab export?

       

      The formatting that you currently apply is not useful.

        • 2. Re: always turn off crosstab formating
          Andy Cotgreave

          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....

           

          Andy

          • 3. Re: always turn off crosstab formating

            Massive +1 from me on this.

             

            The export to Excel is very weak.

            • 4. Re: always turn off crosstab formating
              Joe Mako

              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.

              1 of 1 people found this helpful
              • 5. Re: always turn off crosstab formating

                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.

                • 6. Re: always turn off crosstab formating
                  Dan Cory

                  Try Edit > Copy > Crosstab instead.

                  3 of 3 people found this helpful
                  • 7. Re: always turn off crosstab formating
                    Joe Mako

                    Dan,

                     

                    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.

                    • 8. Re: always turn off crosstab formating
                      . Mcnike

                      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? 

                      • 9. Re: always turn off crosstab formating
                        Joe Mako

                        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.

                        1 of 1 people found this helpful
                        • 10. Re: always turn off crosstab formating
                          Andy Cotgreave

                          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

                          • 11. Re: always turn off crosstab formating

                            I enthusiastically second that.

                             

                            Improved Tableau Server export to Excel would be a massive win.

                            • 12. Re: always turn off crosstab formating
                              . Mcnike

                              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.

                              • 13. Re: always turn off crosstab formating
                                Lisa Lacroix

                                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.

                                • 14. Re: always turn off crosstab formating
                                  Hayley Mallen

                                  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.

                                  1 2 Previous Next