How to Combine Multiple Sheets in Tableau

Bringing data together from different sheets is one of the most common tasks you'll perform in Tableau, and thankfully, it’s much simpler than you might think. Whether you're trying to combine sales data from different months or enrich transaction data with customer information, Tableau has a few straightforward ways to get the job done. This guide will walk you through the three primary methods for combining multiple sheets: relationships, joins, and unions.

First, Understand Your Data's Structure

Before you start dragging tables around, it's helpful to know how your data should be combined. Are you trying to stack similar datasets on top of each other, or are you trying to merge different datasets side-by-side using a common field?

• Appending Data (Using Unions): This is when you stack rows from one sheet on top of another. This method works when your sheets have the exact same columns. A perfect example is consolidating monthly sales reports (January Sales, February Sales, etc.) into a single master sales table.
• Merging Data (Using Relationships or Joins): This is when you add new columns to your dataset from another sheet based on a shared field, like a "Customer ID." For example, you might merge a Sales sheet with a Customer Details sheet to analyze sales by customer location or demographic.

Understanding this distinction is the key to picking the right method for your analysis.

Method 1: Combining Sheets with Relationships (The Recommended Way)

Relationships are Tableau's default and most flexible method for merging data. Instead of creating a single, rigid table, relationships tell Tableau how your tables are connected. Tableau then intelligently brings in data from the necessary tables as you build your visualizations, often leading to better performance and more accurate results.

Imagine you have two sheets in an Excel workbook:

1. Orders: Contains OrderID, CustomerID, OrderDate, and SaleAmount.
2. Customers: Contains CustomerID, CustomerName, and State.

Your goal is to analyze sales by state. You'll need to relate these two tables using the CustomerID field.

Step-by-Step Guide to Creating a Relationship

1. Connect to Your Data: In Tableau, select "Connect to Data" and choose your data source (e.g., Microsoft Excel). Navigate to and select your file.
2. Drag Your First Sheet: Once connected, you'll see the available sheets in the left-hand pane. Drag your primary sheet, Orders, from the pane onto the canvas. This is often called your "fact table" because it contains the events or metrics you want to measure (like sales).
3. Drag Your Second Sheet: Next, drag the Customers sheet onto the canvas. Tableau will automatically detect the common field (CustomerID) and create a relationship, which looks like an orange line or "noodle" connecting the two tables.
4. Configure the Relationship (If Needed): You can click the noodle to configure the relationship settings. Tableau is usually very good at identifying the correct fields, but here you can change them if necessary. You can also adjust the cardinality (e.g., one-to-many) and other performance options, but the defaults are great for most situations.
5. Start Analyzing: That’s it! Now, go to a worksheet (Sheet 1). You'll see fields from both the Orders and Customers tables available in the left-hand data pane. You can now drag State to Columns and SaleAmount to Rows to build your visualization easily.

The beauty of relationships is their flexibility. Tableau keeps the tables separate and only pulls in the data it needs based on what fields you're using in your view, preventing data duplication issues common with traditional joins.

Method 2: Combining Sheets with Joins

Before relationships were introduced, joins were the standard way to merge data in Tableau. A join creates a single new table by combining the columns from your original tables based on a shared field. While relationships are now preferred, joins are still useful, especially when you know you need a fixed, singular table before starting your analysis.

There are four main types of joins:

• Inner Join: Only includes rows where the join key (e.g., CustomerID) exists in both tables.
• Left Join: Includes all rows from the left table and only the matching rows from the right table.
• Right Join: Includes all rows from the right table and only the matching rows from the left table.
• Full Outer Join: Includes all rows from both tables, regardless of whether there's a match.

Step-by-Step Guide to Creating a Join

Let's use the same Orders and Customers tables as our example.

1. Connect to Your Data: As before, connect to your Excel file.
2. Enter the Physical Layer: Drag your Orders sheet to the canvas. To create a join, you must open the "physical layer." Do this by double-clicking the Orders table on the canvas.
3. Drag Your Second Sheet to Join: Now, drag the Customers sheet to the right of the Orders table. Tableau will display a Venn diagram icon, allowing you to configure the join.
4. Configure the Join: Click the Venn diagram icon. In the dialog box, select the join type (e.g., Inner). Then, choose the common field from each table under "Join Condition" – CustomerID in this case. Tableau will automatically set this if the column names are identical.
5. View the Results: Below the canvas, you can see a preview of the new, single table created by the join. It now contains columns from both sheets. You're ready to start building your sheet and analyzing the combined data.

Method 3: Combining Sheets with Unions

A union is used for appending data - or stacking rows from two or more sheets. This only works effectively if the sheets you’re combining share the exact same column structure (i.e., the same column names and data types).

Imagine you have three separate sheets for quarterly sales: Sales_Q1, Sales_Q2, and Sales_Q3. Each sheet has the same columns: OrderID, Product, and Amount.

Step-by-Step Guide to Creating a Union

There are two primary ways to create a union: manually or with a wildcard search.

1. Connect to Your Data: Connect to your data source as usual.
2. Start the Union (Manual): Drag the first sheet, Sales_Q1, onto the canvas. Then, drag Sales_Q2 directly below the Sales_Q1 table until you see a message that says "Drag table to union." Release to create the union. Repeat this for Sales_Q3.
3. Use a Wildcard Union (Automatic): A much faster way, especially if you have many files, is to use a wildcard search. Instead of dragging a sheet, double-click "New Union" from the left pane. Once the dialog box opens, drag a sheet like Sales_Q1 into it. Then, change the file name search in the configuration from a specific file to a pattern using an asterisk (*). For our example, searching for Sales_Q* will tell Tableau to automatically find and union all sheets that begin with Sales_Q.
4. Review and Analyze: After creating the union, Tableau will generate two new columns: Path and Table Name. These fields are helpful for identifying which rows came from which original sheet, allowing you to use the sheet name as a dimension in your vizzes (e.g., analyzing sales by quarter). Now, you have one consolidated table to work with.

Which Method is Best: Relationships, Joins, or Unions?

Here’s a quick summary to help you decide:

• Use Relationships if: You're merging data with different levels of detail (e.g., daily transactions and annual customer goals). This is Tableau’s recommended approach for most merging scenarios due to its flexibility and performance benefits. It preserves the original tables and prevents data duplication.
• Use Joins if: You have a specific need to create a single, fixed table before you begin analysis. Joins are more rigid but can give you more explicit control over the structure of your combined data source.
• Use Unions if: You are stacking sheets that have identical column structures, like combining monthly reports into a yearly total.

Final Thoughts

Mastering relationships, joins, and unions unlocks the full analytical power of Tableau by allowing you to work with complex, multi-sheet datasets. Selecting the right method comes down to understanding your data's structure and what you're ultimately trying to analyze - are you adding more context with new columns (relationships/joins) or compiling a comprehensive dataset with more rows (unions)?

While combining sheets within a single source like Excel is a great start, the real challenge often comes from unifying data across many different platforms. That's why we built Graphed to help. Instead of wrestling with files from Google Analytics, HubSpot, Shopify, and Facebook Ads, you can connect them all in one place and let AI handle the hard work of joining and reporting. Simply ask a question in plain English, and Graphed builds the real-time dashboards you need, saving you countless hours of manual data wrangling.