Since Microsoft Excel is a very commonly used tool for analyzing data, Tableau makes it easy for the users to connect to Excel files.
To use an Excel file as a data source, let's use the sample file that comes with the Tableau installation. Unless you have customized your Tableau installation, you should find the Sample – Superstore Sales (Excel).xls file when you navigate to My Documents | My Tableau Repository | Datasources.
Once you have identified the presence of the sample Excel file, perform the following steps to connect to the Excel file:
- From Tableau's main screen, click on Connect to Data as shown in the following screenshot.
- Under the In a file option, select Microsoft Excel as the connection option.
- Browse and select the file
Sample - Superstore Sales (Excel).xls. - Tableau will determine the number of sheets in the file and provide an option to import a single worksheet (also called tabs or sheets) or multiple worksheets.
- Select the Orders sheet and hit OK.
- Use the Connect live option to get the data loaded as is from the Excel file.
- You'll see that Tableau determines the field types based on the available data.
When Tableau connects to Excel files, it provides options for connecting to a single worksheet or multiple worksheets. Custom SQL commands can also be written to access data from multiple worksheets of an Excel file. In this recipe, we connected to a well-formatted worksheet from the sample Excel file. As was the case with plain text files, Tableau determines the data types of the fields using Microsoft's Jet Engine Driver.
Although we used a well-formatted Excel file for this recipe, we know that analysts spend a lot time cleaning and manipulating data before any analysis. Before connecting to Tableau, we have to make sure the Excel files are formatted according to what Tableau is expecting as a data source. The Preparing Excel Files for Analysis article in the Knowledge Base section at the following link provides more information on how to prepare Excel files to be used in Tableau for analysis:
http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis