forums

Use these forums to ask questions and discuss Tableau.

Using Excel crosstab as a data source.

Posted August 20th, 2008 by Chris Gerrard

in crosstab data source Excel Data Sources and Performance

I'm frequently in the position of needing to analyze data in Excel that is in the form of cross-tabs.
This usually results from someone extracting data using one of the "Big-BI" tools into Excel.

I've worked out a way to get the Excel data into row-structured data in a csv file, but it's not very tidy and requires some data munging and scripting.

It seems that Tableau ought to be able to recognize cross-tab data, since it's simply the logical inverse of asking Tableau to provide the cross-tab for data in an existing visualization.

Can Tableau do this, and if so, can anyone reveal the secret?

If not, I'd be happy to share my method, warts and all.

Chris Gerrard

Add new comment

Comments

On August 20th, 2008 mcristia said:

Chris,

During his Tableau Customers Conference breakout session on using Tableau with Excel, Tableau Software's Ty Alevizos mentioned a utility he developed that seemed to have this task as its focus. You might want to touch base with him for the specifics on how to get hold of the utility.

MANY BLESSINGS!
Peace and All Good!
Michael W Cristiani

On August 21st, 2008 Michael said:

Chris,

Ian Hergert of Tableau Software provided me with an Excel add-in solution in April which works very well in reshaping the data. Unfortunately, this site does not let me attach a *.zip file nor an *.exe file, hence I am unable to upload it here.

If you can provide me with an email address, I shall forward the file to you. Alternatively, you may contact Ian directly.

Regards
Michael Choudhury

On August 21st, 2008 Chris Gerrard said:

Hi Michael,

Thanks for the offer.
My email address is Chris@Gerrard.net

Regards,

Chris

On August 21st, 2008 James Baker said:

Here's the add-in.
(It's labeled 3.6, but is actually Tableau-version agnostic, and should work with Excel 2000-2007.)

There's one installation step to note - after installation, go to Excel's Options dialog, select "Add-Ins", hit the "Go..." button to "Manage Excel Add-ins", and there click the check box for Tableau. THEN you should be able to see "Reshape Data", etc in your Add-Ins ribbon/menu.

Attachment	Size
Setup-Tableau-Add-In-3.6.exe	329.88 KB

On August 21st, 2008 Chris Gerrard said:

Dang.
That's startlingly cool. I've just been relieved of tons of tedium. This is going to be big. Very big.

Thanks.

On August 23rd, 2008 Ian Waring said:

Awesome - saves a lot of dog work here also. Thank you.

Subscribe to the comments on "Using Excel crosstab as a data source."