This chapter shows how to connect Tableau to Microsoft Excel Files. Furthermore, how to use the excel sheets as Tableau Data Source with an example.
Steps involved in Connecting to Excel Files in Tableau
Before we start, let us see the data present in the Excel file. If we see the below screenshot, it is just a regular .xlsx file holding two sheets or two tables.
Below screenshot will show the Data present in Customers sheet
Below screenshot shows the Data present in Department sheet
Next, Double click on the Tableau desktop to open. Once it is open, it looks like below screenshot
First, Under the Connect section, Please select the Excel Option to connect Tableau to Excel files
Once the Excel Option is selected, a new window will open to select the Excel file from our file system. For now, we are selecting the CustomersAndDept.xlsx file as shown below
Once we complete, the below screenshot will appear. Please understand the following option before start creating a report
- Workbook: Excel File we selected from our file system.
- Sheets: This section displays the Sheets or Tables present in the Excel source. We have two tables, so it is displaying those two sheets (Customers and Department). We have the search bar under this section, and it is handy for a large number of sheets. For instance, If we have 30 or 40 sheets, use this to search for a specific table
- Drag Sheets Here: We have to Drag Table(s) from Sheets to this Section. Tableau will only use the tables present in this area. It is something like Dataset.
- This region shows the data present in our Dataset
NOTE: CustomersAndDept is the default data source name (Excel Filename) assigned by the Tableau. Please change this default name to a more meaningful unique name
We can add the sheets to Region 3 in Multiple Ways. As the name suggests, either we can Drag the Customers Table from Sheets region to 3rd region or else double-click on the required table will automatically add
TIP: Tableau allows us to add multiple tables using Joins
Once we dragged the Customers sheet, the Preview region will display the data present in that sheet.
If you observe the below screenshot, there is a message showing Data Interpreter is On
Data Interpreter: If Tableau discovers the format of any column data in our selected sheet is difficult to analyze, Tableau will pop up a message asking us to Turn On Data Interpreter. By turning Data Interpreter on, we can normalize the data
Once we finished, click on the Sheet 1 tab to design the report.
- Data: This will display the list of currently connected data Sources. We have only “One” at this time; otherwise, it displays all the data sources available.
- Dimensions: Columns with string data will place under the Dimensions section
- Measures: Columns with Numeric data or Metric values will place under the Measures section
- It is the area where we design our Tableau reports by dragging Measures and Dimensions
From the above screenshot, we are only seeing the data present in Customers table though we have Customers and Department tables in our Excel file. If we want to add a Department table, Click the back button on Top and Join the Department table with the Customer table.