In this article we will show you, How to connect Tableau to Microsoft Excel Files and how to use the excel sheets as Tableau Data Source with example.
Steps involved in Connecting to Excel Files in Tableau
Before we start, Let us see the data present in the Excel file. If you observe the below screenshot, it’s just a normal .xlsx file holding two sheets or two tables.
Below screenshot will show you the Data present in Customers sheet
Below screenshot will show you the Data present in Department sheet
If you haven’t started the Tableau yet, 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
Once you selected the Excel Option, a new window will be opened to select the Excel file from our file system. For now, we are selecting the CustomersAndDept.xlsx file as shown below
Once you are done, below screenshot will be appeared. Please understand the following option before you start creating report
- Workbook: Excel File we selected from our file system.
- Sheets: This section will display 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 very useful for large number of sheets. For instance, If you have 30 or 40 sheets then you can use this to search for specific table
- Drag Sheets Here: You have to Drag Table(s) from Sheets to this Section. Tableau will only use the tables present in this area. This is something like Dataset.
- This region will show the data present in our Dataset
NOTE: CustomersAndDept is the default data source name (Excel File name) assigned by the Tableau. Please change this default name to more meaningful unique name as per your requirements
We can add the sheets to Region 3 in Multiple Ways: As the Name suggest, Either we can Drag the Customers Table from Sheets region to 3rd region or else simply double-click on the required table will automatically add
TIP: Tableau allows us to add multiple tables using Joins
Once you dragged the Customers sheet, 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 you are 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 will display all the data sources available.
- Dimensions: Columns with string data will be placed under the Dimensions section
- Measures: Columns with Numeric data or Metric values will be placed under the Measures section
- This is the region where we design our Tableau reports by dragging Measures and Dimensions
From the above screenshot you can observe that, We are only seeing the data present in Customers table though we have Customers and Department tables in our Excel file. If you want to add Department table, Click back button on Top and Join the Department table with Customers table
Thank You for Visiting Our Blog