KNOWLEDGE BASE

Analyzing Related Data Across Multiple Excel Tabs


Product(s): Tableau Desktop
Version(s): All
Last Modified Date: 16 Aug 2016

Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.


Consider a Microsoft Excel workbook that contains multiple worksheets, with each sheet representing data for a particular geographic region. Suppose you want to combine the data from these sheets into one, so that you can compare figures across regions. Initially, you might consider using a join query. However, what you might be looking for instead is the result of a type of union.

A join adds columns to the data. Therefore, in this scenario, a join would not produce the result that enables you to analyze data across regions. However, a UNION query appends distinct rows, and UNION ALL returns all rows. In this case, as long as you have the same column headings on all worksheets, you can use a UNION ALL query.

Performing a union, instead of copying and pasting the data into one big Excel worksheet, also allows you to maintain the worksheets separately, but analyze them all as a single table in Tableau.

Use custom SQL to create the UNION ALL

The steps below show a UNION ALL query using an Excel workbook that contains two worksheets for regional sales data. Each sheet represents a different region, in this case East and West.

Step 1 

Open a new workbook, and then do one of the following;

  • For Tableau Desktop 8.3 and earlier, click Connect to Data > Microsoft Excel.
  • For Tableau Desktop 9.0 and later, click Excel.

Step 2 

In the Open dialog box, do one of the following:

  • For Tableau Desktop 8.1 and earlier, find and select your Excel workbook, and then click Open.
  • For Tableau Desktop 8.2 and later, find your Excel workbook, and click the drop-down arrow next to Open, and then select Open with Legacy Connection.

Step 3 

For Tableau Desktop 8.1 and earlier, in the Excel Workbook Connection dialog box, select Custom SQL.

For Tableau Desktop 8.2 and later, drag the New Custom SQL table from the left pane to the join area.

Step 4 

In the custom SQL field or dialog box, copy and paste the UNION ALL query similar to the one shown below, using your worksheet names.

In this example, the worksheets do not include a region field, because each sheet represents a region. So in addition to the union, you create a column for Region in the Tableau view, plugging in the worksheet name as the value.

SELECT *, 'East' AS [Region] 
FROM [East$] 
UNION ALL 
SELECT *, 'West' AS [Region] 
FROM [West$]

Notes

  • If your worksheets include a Region column, you can simply use SELECT * FROM [SheetName$].
  • If your worksheet names have spaces in them, you need to include single quotation marks around the name in your custom SQL query.

    For example, for the sheet name Eastern Region, you would type ['Eastern Region$'] as part of the query.

Step 5 

Click OK. The connection consists of a single table that contains all of the data from both worksheets.

Step 6 

On the sheet, from the Dimensions pane, drag Region to a shelf, and then drag other fields to create a view.

The view shows how the regions compare.

Note: If you add sheets to the Excel workbook in the future, you will need to modify the Tableau data connection.

Important: The UNION ALL query is processed every time Tableau queries the data source (that is, any time you drag fields around the view, use a filter, and so on). This could take a long time depending on the size of your Excel file. You can avoid these issues by taking an extract. Select Data > Extract.

 

Did this article resolve the issue?