A crosstab is a text table that displays the relationship between two or more variables. When exporting your view using the Crosstab to Excel option, you may notice that the output does not resemble the crosstab view that you created in Tableau. For example, a common outcome is the last column of data that is displayed vertically in Tableau, displays horizontally in Microsoft Excel like in the example below.
In general this issue occurs because Tableau does not have a second variable to reference during the export of the crosstab. In the above example, the view is comprised of only dimensions on the Rows shelf. In this case, Tableau treats the far-right dimension as a second variable that it can reference, which produces results that you may not expect after exporting the crosstab to Excel.
To resolve this issue, you can do one of the tasks listed below.
- Create a second variable: Create a calculated field that contains just two single quotation marks. Right-click the calculation and place this field in the Columns shelf. This field now functions as a placeholder field that Tableau can treat as a second variable. Refer to Solution 1 in the attached workbook.
To hide the empty column created by placeholder field right-click the field in the view and select Show header to remove the default selection. This will prevent the column from displaying in the Excel file. For the example above, you can create a calculated field called “Placeholder Sales Field”, and place it on the columns shelf.
- Move a field to the text shelf or convert to a measure: If possible, add or convert a field to a measure or move a field to the Text shelf. For the example above, you can move the Sales field to the Text shelf. Refer to Solutions 2 and 3 in the attached workbook.
- Copy summary data into Excel: Alternatively, instead of exporting using the Crosstab to Excel option, you can copy and paste the summary data into Excel. To do this, select Analysis > View Data, click the Summary tab. Select the entire table, and click the Copy button. Go to Excel, and paste the data into a worksheet. Refer to Solution 4 in the attached workbook.
