What is Pivot in Tableau?
Pivot in Tableau refers to transforming a dataset from a wide format to a tall format to evaluate the data concisely. Pivot helps you to convert a dataset spanning across multiple columns or fields into a limited number of fields to make your data analysis efficient and easy. Tableau pivot enables you to perform different pivoting options, which include:
- Pivot rows to columns
- Pivot columns to rows
- Use wildcard search to pivot fields based on a pattern match
A sample pivot in Tableau looks like the one provided below. As you can see, the columns or fields in the 1st table are transformed into 2 new pivot columns representing the pivoted data.

Key Takeaways
- Pivot in Tableau enables you to transform a dataset from a wide format to a tall format to evaluate the data concisely.
- Pivot in Tableau helps you to convert a dataset spanning across multiple columns or fields into a limited number of fields to make your data analysis efficient and easy.
- With Tableau Pivot, you can perform different pivoting options, which include
- Pivot rows to columns.
- Pivot columns to rows.
- Use wildcard search to pivot fields based on a pattern match
- You can create pivots with Tableau Desktop, Tableau Prep, or Tableau Public tools.
- As an alternative to pivot in Tableau, you can use some of the methodologies such as using joins, calculated fields, using if-else or case statements to achieve similar results.
How to Create a Pivot in Tableau?
You can create a pivot in Tableau with Tableau Desktop, Tableau Prep, or Tableau Public. We have provided a step-by-step guide for creating a pivot with Tableau Desktop and Tableau Prep.
Note: Steps are the same for both Tableau Desktop and Public.
#1 – with Tableau Desktop
To create a pivot with Tableau Desktop, follow the instructions outlined below:
Step 1: Connect the data source with Tableau using the File Navigator. For the database, use the database credentials to connect with the underlying tables.
Step 2: Once you have successfully imported the data files or connected with the database, in the grid, select the fields or columns you want to pivot.
Step 3: Click on the drop-down arrow next to the column name and choose the Pivot option.

It will create new columns, Pivot Field Names, and Pivot Field Values, replacing the original columns in the data as shown below.

#2 – with Tableau Prep
To create a pivot in Tableau Prep, follow the step-by-step instructions outlined below:
Step 1: Connect with your data source using Connect to Data in Tableau Prep.

Step 2: Drag the table to the Flow pane. The underlying dataset can be viewed under the Preview section.

Step 3: Click on the + icon next to the table and select Pivot.

Step 4: Drag a field to which you want to apply pivoting to the Pivoted Fields pane. Tableau Prep will add the new pivot columns along with their values, as shown below.

You can use the pivoted data to create data visualization in Tableau Desktop.
Examples
In this section, we will use different examples to help you understand the Pivot in Tableau. This will help you understand how to use pivots in real-life situations in your data visualization using Tableau.
Example #1 – Pivot Columns to Rows
In this example, we will create a step-by-step guide for applying pivot columns to rows using the Mobile_OS_Market_Share dataset in Tableau Desktop. The dataset contains the market share of different mobile operating systems across different years, highlighting the usage of different OS.
To apply pivot columns to rows in Tableau, follow the instructions outlined below:
Step 1: Use the Tableau interface to import the Mobile_OS_Market_Share dataset. Once imported, you can view the data in the Data Source tab.

Step 2: Once the data source is set up, navigate to the data grid. Select the number of columns on which you want to pivot from columns to rows. Right-click on the selection and choose the Pivot option from the context menu.

Now, you can see the new columns Pivot Field Names and Pivot Field Values created and added to the data source as shown below.
These new columns replace the original columns you had selected in the previous step to create the pivot. The Pivot Field Names contain the list of OS names that previously existed as original columns whereas the Pivot Field Values contain the values for the individual OS names.

Step 3: Navigate to a new worksheet. Drag Year and Pivot Field Values to the Columns shelf and Pivot Field Names to the Rows shelf. Tableau will create a visualization, as shown below.

Step 4: Right-click on the Year and change the Year field to Discrete.

Step 5: Right-click on the SUM(Pivot Field Values) and change it to Average, as shown below.

It will create a visualization showing the distribution of each OS across each year in the Tableau view.

Example #2 – Using Wildcard Search to Pivot
In this example, we will demo how to use wildcard search to pivot in Tableau Prep. We have used the Brands Sales AdSpent dataset which contains the advertisement spending across different brands for different dates along with the Gross Sales, Net Sales, and Total Sales measures.
To use wildcard search to pivot in Tableau Prep, follow the step-by-step instructions outlined below:
Step 1: Connect the Brands Sales AdSpent dataset with Tableau Prep using the Connections interface. Choose the Microsoft Excel option.

Step 2: Drag the table to the Flow pane. In the Preview pane, you can preview the underlying data, as shown below.

Step 3: Click on the + icon next to the Brand_Sales_AdSpend table and choose Pivot from the context menu.

You can view the fields in the Fields pane. Similarly, you can view the Pivot Results for the underlying dataset.
Step 4: Now click on the Use wildcard search to pivot option in the Pivoted Fields section.

Step 5: In the search text box, type Sales and hit Enter.

Tableau Prep will show all the fields with Sales as the keyword. Here, we can see three fields with Sales text listed.
Step 6: Rename the Pivot1 Names column to Sales Category. Now, you can see the pivot data results in the Pivot Results section, as shown below.

Example #3 – Pivot Rows to Columns
In this example, we will demonstrate how to pivot rows to columns in your data source using Tableau Prep. We have used the Book Sales Volume dataset, which contains book sales details for different months across four regions: East, West, North, and South.
To pivot rows to columns in Tableau Prep, follow the step-by-step guide below:
Step 1: Launch Tableau Prep on your system and click on Connect to Data.

Step 2: Connect with your data source, Book Sales Volume. Drag the table to the Flow pane. You can preview the dataset in the Preview pane of the Tableau Prep.

Step 3: Click on the + icon next to Book Sales Volume. Select Pivot from the context menu.

Step 4: In the Pivoted Fields pane, choose Rows to Columns from the drop-down list as shown below.

Step 5: Select a field from the left pane. Drag it to the Pivoted Fields pane. In this case, we have dragged the Region field to the Pivoted Fields pane as shown below. You will see the distinct values of Month, Region, and Sales in the Pivot Results pane.

Step 6: Select a field from the left pane and drag it to the Field to aggregate for new columns section in the Pivoted Fields pane.
Here, we have dragged the Sales field to the Field to aggregate for new columns section. As you can see, Tableau Prep has applied a default aggregation type, i.e., SUM. You can change it to other aggregation types as well.
You will see the pivot results of the data in the Pivot Results pane, as shown below.

Example #4 – Pivot Using Custom SQL
In this example, we will demonstrate how to pivot using custom SQL in Tableau Desktop. We have used the Vehicle Sales dataset, which contains the different vehicle categories, sales volumes, and total number of vehicles sold.
To pivot in Tableau using custom SQL, you may refer to the step-by-step guide provided below:
Step 1: Connect with the Vehicle Sales access database file using the Tableau interface. To do so, click on Data – New Data source.

Step 2: Click on Microsoft Access on the left navigation and use the Browse option to provide the path of the filename. Then click on Open to connect with the file.

Tableau will connect with the Vehicles Sales table, as shown below.

Step 3: Right-click on the Vehicle Sales and choose Convert to Custom SQL option.

It will open the Convert to Custom SQL screen with SQL statements, as shown below.

You can see the Vehicle Sales table in the data pane.

Step 4: Right-click on the Vehicle Sales and choose the Edit Custom SQL Query option.

Step 5: Use the custom SQL query below to optimize the analysis of your data in Tableau. The below query does the following:
- Pivots the Sales Volume column header into a new column called Sales Dimension.
- Pivots the Total Number of Vehicles Sold column header into a new column called Sales Dimension.
- Pivots the Sales Volume and Total Number of Vehicles Sold values into a new column called Sales Measure.
Click on OK.

Tableau will pivot the data into the below columns, as shown below.

Important Things to Note
- Pivoting a voluminous dataset may have performance implications. It is recommended that you consider optimizing your dataset or using data extracts to pivot data in those cases.
- In some cases, pivoting data may result in data loss. Hence, be careful when applying pivoting data, especially where a field has possible null values.
- Provide a meaningful name when you apply Pivot in Tableau.
- For scenarios where you don’t get the pivot option, you may use the custom SQL option to apply Pivot to your dataset
Frequently Asked Questions (FAQs)
Yes, you can pivot data from Excel in Tableau. It works the same as other data sources.
To apply the Pivot in Tableau, you must import the Excel data file into Tableau and follow the steps outlined in the examples section.
To unpivot data in Tableau, follow the instructions below:
Step 1: Select the pivoted columns or fields in the data grid in Tableau
Step 2: Right-click on the data selection and choose the Remove Pivot option from the context menu.
Some of the common issues with pivoting data in Tableau are outlined below:
Reference to data fields – Pivot data replaces the original columns with new columns or fields which makes any references to the original columns invalid. This causes the existing fields as invalid i.e. red exclamation mark in the Data pane.
Population of null values in the grid – Tableau will display null values in the pivot fields if all the original fields are removed.
Pivot options in Tableau appear only when you choose two or more fields in CSV, Excel, Google Sheets, and .pdf data sources.
Yes, you can pivot data in Tableau Public. The pivot options and processes in Tableau Public are the same as in Tableau Desktop. The steps associated with pivot data in Tableau public are provided in earlier sections of this article.
Recommended Articles
Guide to What is the Pivot in Tableau. We explain step-by-step guide for creating a pivot with Tableau Desktop in different ways and points to note. You can learn more from the following articles –
Leave a Reply