Tableau Crosstab: Your One-Stop Guide

Tableau is well-known for its easy-to-create and cool visualizations. Ever since I took Data Visualization in Tableau, I've loved using Tableau to present my findings.

But did you know that Tableau can also do interesting things with tables? This is great news because, when we need to report the exact figures more than anything else, showing data in text can be the best option. So in this article, I'm excited to show you one specific thing: Tableau crosstabs. Let's take a look.

What is a Tableau Crosstab?

A crosstab (also called a cross table or text table) is simply a way of displaying your data in a grid format. It's essentially a table view where the rows and columns show aggregated values.

Crosstabs main function is to convey the exact figures, but they can be customized and built on with visual aids to also show comparisons and place numbers within context. With customizations and visual aids, we can create complex business intelligence reports, including balanced scorecards, to report business metrics and KPIs.  All of this will make sense as we work through our example.

Steps to Create a Crosstab in Tableau 

Creating crosstabs in Tableau is easy and straightforward, as it solely depends on the software’s interactive user-friendly interface. In the following steps, we will be going through the steps of importing data and creating a simple crosstab.

Import data into Tableau

Let’s use Tableau’s Superstore Sales dataset. To download the file, go to Explore sample data sets from the opening page, and download the 'Superstore Sales Excel' file.

Downloading Superstore Sales data. GIF by Author.

When downloaded on your device, you can import the data by choosing Microsoft Excel from the Connect pane on the left-hand-side and finding where the file is located.

As you will be able to see, the file has three sheets. For this article, we will use the Orders data. Drag the sheet from the left side and drop it in the upper middle part of the Data Source page.

Importing data into Tableau. GIF by Author.

Then, move to Sheet 1 from the bottom tabs to start building our crosstab.

Create a basic crosstab

To create a crosstab, we simply need to drop a dimension into the Rows shelf, another into the Columns shelf, and add a measure in the space in between.

Let's try it for ourselves: For the Rows, we will choose the State field, which represents the state from which the orders were made. For the Columns, we will choose the Category field, which represents the categories of the Superstore products.

For the measure, let’s choose Sales. Now, to add a measure to a crosstab, you drag and drop it right in the view, in the space where the default text, 'Abc', is shown. Or else, you could put it on the Text card in the Marks box, as shown in the GIF below. And there we have our first crosstab!

Creating basic crosstab. GIF by Author.

We can add other dimensions on either of the axes to increase the level of detail. For example, we can add the Sub-category field to the Columns shelf. This will separate each category in our crosstab into its respective sub-categories and detail the profits accordingly.

Creating a basic crosstab with two dimensions in columns. GIF by Author.

Note that the fields in the Rows and Columns shelves follow a hierarchical order. What is placed on the left has a higher hierarchy than what is on the right. So, the order of the fields on the shelves is important.

Customizing the Tableau Crosstab 

Creating crosstabs is easy, but the challenge comes in formatting. Like all visualizations, for the crosstabs to be readable and insightful, deliberate choices of formatting need to be taken.

Formatting the crosstab

Crosstabs formatting can be generally split into three parts:

• Formatting the overall worksheet
• Formatting headers
• Formatting the measure values

Let’s do some example formatting for each. First, let’s access the formatting pane, through which different general formatting options can be done. There are multiple ways to access the pane. Let’s do it by:

1. Going to Format menu in the toolbar at the very top.
2. Choose Fonts to start with.

As we can see the formatting pane has options for fonts, alignment, shading, borders and lines, and for each we can specify if we want to change the respective formatting for the whole sheet, rows only, or columns only.

How to access the formatting pane in Tableau. GIF by Author.

Formatting overall worksheet

We can do two formatting changes for the whole crosstab, change the font type and clear out the default alternate row shading in the table:

1. In the Sheet options of Format Font, we change the Worksheet font.
2. In the Sheet options of Format Shading, we change the Row Banding color to None for both Pane and Header.

Overall formatting for the worksheet. GIF by Author.

Formatting headers

For the formatting of the headers, meaning the names of the states and the categories, we can simply make these bold. To do so, we simply change the Header font to be bold.

Crosstab headers formatting. GIF by Author.

Notice that if we want to do a formatting that is different for states than for categories, we can change the Header option for Rows and for Columns separately.

One of the other customizations on the headers that usually need to be done is to take out what Tableau calls Field Labels, and, in our case, these are basically State and Category words at the top of the table.

To remove those, we can right-click on each and choose Hide Field Labels for Rows or Hide Field Labels for Columns.

Hiding field labels. GIF by Author.

Since we differentiated the headers with bold font, we can now clear out the grey line under the category names. This option can be found under the Sheet options of Format Borders, named as Row Divider. It will be enough to change the line of the Pane to None, and the line of the Header will be None as well.

Clearing Row Divider. GIF by Author.

Formatting measure values

Measure values, or the numbers at the intersection of the two dimensions, can be formatted in two ways, through the formatting of the field, and through the formatting of the Text Mark.

For example, assuming that profits are in US dollars, it would be better to add a dollar sign. This can be done from the formatting of the field. To do so:

1. Go to the SUM([Sales]) field in the Marks, and right-click on it.

2. Choose Format… The formatting pane will appear on the left.

3. Click on the drop-down arrow right next to Numbers under Pane > Default.

4. Choose Currency (Custom), and set the decimal places to zero.

Formatting currency values. GIF by Author.

Meanwhile, if we want to give the measure values a different appearance (font type, color, alignment), we can click on Text under the Marks. We will have the ability to edit the alignment, orientation, and other things.

If we click on the three dots, we would be able to change the font properties, as well as rearrange how the different measure values will appear. For now, we can do just a minor change, which is to set the font color to a slightly lighter black.

Formatting Text. GIF by Author.

Note that any change in the formatting in the Text Marks will superimpose the formatting set from the formatting pane.

Using filters and parameters

Like any other visualization in Tableau, crosstabs can be further customized with filters and parameters.

Crosstabs with filters

Suppose we are not interested in showing all US states in our crosstab. We can filter our table to include only the regions of interest.

Simply drag the Region column from the Measures pane on the left, and drop it in the Filters area. Then, we get to choose the regions of interest. Let that be 'Central' and 'West' for now.

If we want to give the viewer the ability to control the filter, we can right-click on the Region field in the Filters area, and choose Show Filter. The filter control will appear on the right side.

To customize the shape and functionality of the filter control, we can click on the small downward arrow, next to the filter control title, and choose the type of the control we want. Here, we choose Single Value (list) to transform the filter control into a dropdown menu. If we want the viewer to select more than one region at once, we can choose Multiple Values (list), as an option.

Adding filters to crosstabs. GIF by Author.

Crosstabs with parameters

Filters are great, but they have their limitations. What if, for example, we want to give the viewer the ability to do more complex filtering and customization to the crosstab, like the ability to toggle between the top 5, top 10, and top 15 states according to total sales? Here is where parameters come to play.

If you are new to Tableau parameters, you can read through this introductory tutorial to have an overall grasp on the concept. But for now, let’s say they are like filters that are customizable by the viewer, and therefore make our views more dynamic and responsive to the viewer’s needs.

To create the needed parameter in our scenario, let’s clear out the filters, and do the following:

1. Drag and drop the State column into the Filters box.
2. In the Filter window, click on the Top tab.
3. Choose By field.
4. Leave everything as is, but open the dropdown menu of the Top value and choose Create a New Parameter…
5. In the upper part of the Parameter window, write a descriptive name for the parameter. Let it be Top N States.
6. In the lower part of the parameter window, set Allowable values to List.
7. Enter possible values that you want to give to the viewer to choose from, and how they will be displayed. For instance, to show top 5 states, the value would be 5, but it is better to be displayed as Top 5.
8. Click OK in the Parameter window, and another OK in the Filter window.
9. You will see our Top N States as a pillar in a new section for parameters, at the bottom of the measures pane. Right-click on it, and choose Show Parameter to show its control for the viewer.

Adding parameters to crosstabs. GIF by Author.

Advanced Customizations to Further Enhance Your Tableau Crosstab

As you will see in this next section, Tableau crosstabs can be enhanced to help highlight numbers, compare between values, and add context.