KNOWLEDGE BASE

Analyzing Related Data Across Multiple Excel Tabs


Published: 02 May 2017
Last Modified Date: 02 May 2017

Question

How to UNION ALL to combine multiple Excel worksheets from a Microsoft Excel workbook where each sheet represents data for a particular geographic region, city or branch in order to compare figures across regions.

Environment

  • Tableau Desktop
  • Microsoft Excel

Answer

A join adds columns to the data, 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 on the Connect page that opens click Excel.

Step 2 

In the Open dialog box, find your Excel workbook, click the Open drop-down menu, and then select Open with Legacy Connection.

Step 3 

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.

Additional Information

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 making an extract. Select Data > Extract.
Did this article resolve the issue?