Microsoft Excel will always hold a special place in my heart. Learning how to automate excel through VBA is what made me interested in coding and eventually lead me to Tableau. A common struggle for Tableau users is recreating specific elements of Excel, especially the drill-down functionality of a Pivot Table.
However, analytics does not live in a Tableau bubble. Most of us will encounter projects that involve some Excel fans. In those cases, here are some options for mimicking a Pivot Table. Each option has different pros and cons so it’ll be up to you to figure out which option is best for your users, your Tableau developers, and your data.
Option 1: Built-In Hierarchy
- Design Difficulty: Easy
- End-User Friendliness: Medium
- Similarity to Pivot Table: Medium
Link to Instructions: Tableau: Create Hierarchies
As always, directions are encouraged to help your end-user understand how to drill-down. Some end-users, might not know they need to hover over the field label to find the drill-down icons (+/-).
Option 2: Dynamic Drill-down
- Design Difficulty: Medium
- End-User Friendliness: High
- Similarity to Pivot Table: Medium
Link to Instructions: How to Build a Dynamic Drill-down Table in Tableau
This can also be a great option if you’d like to limit the options of lower levels based on prior level selections or if you need to limit options based on usernames. Also, this opens the door for all of the cool UI functionality with parameters (ie custom buttons).
Option 3: Click to Drill-down
- Design Difficulty: Medium-High
- End-User Friendliness: High
- Similarity to Pivot Table: High
The most common functionality people are trying to mimic from a Pivot Table is the click to drill down functionality. Click to investigate is arguably the most user-friendly option and helps to encourage Tableau’s “Click to Discover” mentality. However, the built-in functionality (Set Actions) only allows for two levels of data (ie Category & Sub-Category, not Category, Sub-Category, and Manufacturer).
Check out the full Tableau workbook with the different Pivot Tables on Tableau Public: Pivot Table Options.
