Joining Excel Worksheets

In Tableau, you can join tables created in Microsoft Excel. For example, if you have an Excel worksheet containing 10 fields that describe order information, you can join 5 additional fields to the first worksheet from a second worksheet in the same Excel workbook, and then analyze all the columns as a unified whole.

When you join two tables, both tables must contain a field that corresponds to a field in the other table. In some cases, there may be more than one set of these field pairs. The fields that link the two tables in a logical way can be thought of as the key fields. These fields do not have to have the same name, but they must contain the same information and should be of the same format/type. For example, one table might contain a field "State," and the other table contains the field "Customer State." The names are different, but both fields contain the names of states.

Step 1

Connect to the Sample - Superstore Sales (Excel) data source.

Step 2

For Tableau Desktop 7.0 and later, select the data source in the Data menu, select Edit Tables.
For Tableau Desktop 6.1 and earlier, select  Data > Tables.

The Tables dialog box lists the tables you are already connected to.

Step 3

The Tables dialog box lists the tables you are already connected to. Click Add New Table.

Step 4

Select the table you want to add.

Note: In most cases, Tableau automatically suggests a join clause based on the structure of your data. The join clause is shown on the Join tab of the dialog box.

Step 5

When finished, click OK.

Now, Tableau organizes the Data window by table so you can easily identify where each field is coming from.

Note: When you join Excel worksheets in Tableau, you are adding more columns to the data source. In essence, you are making the first data source "wider" by joining in new columns. The join feature is not designed for appending one worksheet table to the bottom of another. If you have so much data in Excel that it doesn't all fit on one worksheet, the best thing to do is to export your data to a database program.

The view shown below uses fields from both the Excel worksheets. It displays the returns for every customer and product with the return status indicated by the color of the mark.

If Table 1 and Table 2 have fields in common, and Table 2 and Table 3 have fields in common, you can join Table 1 and Table 3 to Table 2, but you cannot join Table 1 directly to Table 3. If the common fields have the same name, Tableau suggests a join; if not, you have to create the join yourself.

In this example, an Excel workbook contains three tables:

• Table 1, Orders, has four columns: Date, Order ID, Salesperson ID, and Total
• Table 2, Salesperson, has two columns: Salesperson Number and Salesperson Name
• Table 3, Branch, has two columns; Branch and Salesperson Name

Salesperson ID in Table 1 and Salesperson Number in Table 2 contain the same data, but have different field names.

Step 1

In a new Tableau workbook, connect to the Excel workbook join_example.

In the Excel Workbook Connection dialog box, Branch is highlighted because it is first alphabetically, but you can start with any of the tables.

Step 2

Under Step 2, select Orders$ in the list, and then select Multiple Tables.

Step 3

Click Add New Table.

Step 4

In the Add Table dialog box, under Select the table to add, select Salesperson.

Step 5

Select the Join tab.

Step 6

Under Add Join Clause, in the Schema Fields list for the Orders table, select Salesperson ID.

Step 7

Under Table Fields, select Salesperson Number.

Step 8

Between the two lists, in the operator list, keep the equals sign, and click Add.

Under Join Clause, a clause appears that equates Salesperson ID in the Orders table with Salesperson Number in the Salesperson table.

Step 9

Click OK.

Now you are ready to join the Branch table to the Salesperson table. This time, the field names are the same, so Tableau can suggest a join clause.

Step 10

In the Excel Workbook Connection dialog box, in Step 2 under Table Alias, select Salesperson$, and then click Add New Table.

In the Add Table dialog box, under Select the table to add, Branch is already selected (because it is alphabetically first, not because it is the last table remaining). At the bottom of the Table tab, the suggested join appears.

Step 11

Click OK to accept the suggested join clause.

Note: If you needed a type of join other than Inner, which is the default, you could change it on the Join tab. For this example, you can leave the join type unchanged.

In the Excel Workbook Connection dialog box, both joins are now listed.

Step 12

In the Excel Workbook Connection dialog box, click OK.

This creates a view with fields from all three tables.