My husband Mark gave me some data on gasoline components in an Excel spreadsheet. The spreadsheet has several sheets each with multiple tables. I opened a connection to the spreadsheet then clicked on the box to use Data Interpreter.
The Data Interpreter did a pretty good job of finding the various tables. The tables are named using the sheet name and range and now appear in the left side pane under the Excel file name. The only problem is that Data Interpreter splits tables where a column is given a different color cell fill. Once I dragged a table into the right hand side data canvas, I could edit the data range to include all the columns in the table, but wish I could just do this in the left hand data pane. I had five tables to include, so dragged them all into the data canvas. Tableau automatically creates joins, which in this case is not helpful. I made changes to cell ranges where needed, and could now see the correct ranges in the left had pane. I then converted one of the tables to a union and selected Edit Union from that table's drop down menu. I then dragged the remaining four tables from the left hand pane to the box containing the first table name. Lastly, I removed all of the joined tables.
The final result is a single table with Sample numbers across the top (as appears in all of the tables) and the components down the side. Additionally, Tableau adds columns for the sheet name and table name (sheet name + range).