How the EXCEL?! / Tableau

How the EXCEL does Tableau work?! The Humans’ Guide to Vulcans

Humans are mystifying creatures, as Spock can surely tell you.  Much like humans, Excel is quite baffling.  What started life as a simple spreadsheet program grew to take on a life of its own in the BI space.  Excel is probably the most widely used program for data analysis.  But, it’s also the most confusing if you want to get down to brass tacks.  Where else do you have a program where you can enter data, connect to data, use 901 plugins, export out to PowerPoint, and run some VBA or macros.  As the program grew, it went from snowball to avalanche very quickly.  In Star Trek terms, you could call Excel a human.  At least Spock would.

Let’s look at this:

  1. At its base level, Excel starts with a sheet.  You can use that sheet as a data source (by typing in OR connecting), you can reference other data sources, or you can make a macro (I know I’m missing options).  We could go on, but this just highlights one thing: a plethora of options, yes, but also the ambiguous nature of Excel.  As Spock would say, HIGHLY ILLOGICAL!
  2. In the hands of a novice user, Excel acts as a basic data dump & presentation tool.  Generally, people enter data into it the way they want it presented when first starting out.  The order to this is generally HIGHLY ILLOGICAL!
  3. As some point, we learn to organize data a bit better (hopefully) or we enterprise it up a bit with things like cubes.  In the days of yore, we needed cubes. Or at least, that’s what they told us.  These days, there’s better options and we can safely label cubes HIGHLY ILLOGICAL!
  4. When we analyze data, we have to have a clue of where we’re going when working in Excel.  It takes loads of time to make stuff, let alone make it repeatable, reusable or, dare I say it, the responsibility of others.  HIGHLY ILLOGICAL!

Excel might look something like this:

Excel to Tableau - Excel at a Glance
Lots of options, no order to the chaos.

What about Tableau?

Like many vulcans before it, Tableau attempts to bring logic to the equation.  The heart of this starts with the data source.

Excel to Tableau Data Source Required

The Tableau Data Source

The data source is simply magic.  Within a datasource, you get fields and columns.  But, you get more than just that…a data source also lets you:

    1. Join items.  Pre-10, there’s limits on what you can join.  10 is coming soon.  Bust out the party – it’s logical here.
    2. Create calculations that live within it.  So, you can make a source, do your 900 calculations, and SHARE.  I know, scary, right?  Sharing is caring.
    3. Make it easier for others to understand what the EXC**L you did.  Yes, you can annotate and comment to your heart’s content and…ready for this…it stays with the source.  For anyone who has gotten a workbook with multiple lines of GETPIVOTDATA, ROW, INDEX, SUMIF, and the works, you’re probably searching for your socks.Formatting at data Source
    4. Set formatting (down to the field level).  No, really.  This is where those of us who have come from Excel may struggle.  Let’s face it, we got locked in the Sheet box.  You can set it at the source and forget about so many things.  And, as always, you can override it later in the worksheet, if you wish.
    5. Create rolled up extracts off it.  In the days of yore, we made these manually, but in Tableau, you can do this off row level if you need to.

To do much of anything in Tableau, you need a data source.  If you ask Spock, this makes sense.  You need data in order to analyze it.  Tableau also likes it to be somewhat logical.  More proof that Tableau is really a Vulcan (I think the ears may be a dead giveaway).

The Tableau Worksheet

Tableau’s worksheet makes use of your data source or sources.  If you have a couple, you can do scary things like blend.  Your call.

Worksheets look most familiar to Excel users.  They resemble pivot tables.  Drag, drop, and enjoy, right?  That’s a start.  But unlike Excel, you can make a ton of these and smile about it.  Here’s the thing: worksheets are not the end of this process.  You get your data, you make worksheets, and at the end, you make a dashboard (or story points presentation if you want to be difficult).  And, guess what?

Tableau is logical, so in a worksheet, you can:

  1. Blend data.  If you have multiple sources, you can blend them on something logical.  Tableau hates chaos, so there’s a limit to how many sources you can blend at once.  It’s logical, but frustrating if you’re used to human chaos.
  2. Create window calculations.  Right click on a pill, select one of the options, and keep right on trucking through.  It’s really that easy.  Or, kick it up a notch and edit it or write your own.
  3. Make it easier for others to understand what the EXC**L you did.  Captions are really handy here, as is ‘Describe Sheet’ (Control + E) if you like an absurd amount of documentation.
  4. Set formatting.  You can use marks, but you can also ALSO format the daylights out of the worksheet.  Tableau Public is a great way to get some ideas.  You can also override any formatting you’ve set at the data source level at the worksheet level (such as number formats and sorts).
  5. Create modified sources.  This is probably one of the oldest tricks in the books, but make a table of data how you need it, export to Excel, and suck it back in.  This is MOST helpful when you’ve done some data masking in Tableau and need to get some help.  Here’s what NO ONE taught me when I was new (or if they did, I didn’t listen): Make a view with everything you need, copy it as a table, alias what you need hidden, export it, connect it to Tableau, and change the source.  Save as a different file, remove the original source, and send for help.  Seriously, it CAN be that easy.

Logical

Are you seeing a theme?  Tableau likes logic.  More proof that Tableau is really a Vulcan.

The Tableau Dashboard

This is where the magic really happens.  Tableau (dramatic picture, remember) lets you combine all your worksheets (plus things like text, images, and web pages) to make something superb.  And by superb, I mean logical.

Dashboards are the opus of this:

  1. Combine worksheets to make something meaningful.  This can be across data sources, as long as something matches.  Think things like ‘Region’ or ‘State’ or dates.
  2. Use actions to filter – they’re like calculations, but easier!  You can use charts to filter.  And you should.  They’re more fun than other filter options.  And crazy effective.
  3. Make it easier for others to understand what the EXC**L you did.  You can do this within the dashboard (hover tip instructions) or by floating info out into the margins of the dashboard.
  4. Set formatting.  Yup, you can format here too.  And make some pretty amazing things.
  5. Export data from sheets within.  Probably seems like cheating to list it here, but for end users, they usually don’t see your sheets.

At a high level, Tableau looks like this (we could go down the rabbit hole of exceptions, but I’m going high level):

Nested Vulcan logic
Nested Vulcan logic

The transition can certainly be jarring.  Recognize it, explore, ask questions, and realize it’s only logical that this is a journey.  Viz long and prosper.

spock-logic

2 thoughts on “How the EXCEL does Tableau work?! The Humans’ Guide to Vulcans

  1. Hi Bridget,

    Great post, as always I really like what you write but for the 1st time I disagree with the stance.
    I may be a lone wolf but I don’t quite subscribe to the whole Excel bashing that often abounds in the Tableau community. It’s not that I love Excel, I don’t and I wouldn’t go back to codding endless macros if someone paid me. Where I differ is that I don’t think the comparison is fair, not quite comparing apples with oranges but close enough.

    Excel was first made available in September 1985, that’s almost 31 years. You can’t blame it for having a few wrinkles and dark patches under the eyes from partying too much.
    VBA was first introduced in 1993 and a major shift to Excel happened when Microsoft released Windows 95, that’s 21 years ago. In comparison Tableau was founded in 2003 and IPO in 2013. Tableau is barely a teenager, albeit an extremely talented one.
    The way I see it, Tableau identified a market opportunity early on building on Excel’s failures. Microsoft is notorious for poor integration of their different products and that’s noticeable from their SQL offering to their new BI flagship product Power BI.

    However I see Tableau’s success in the similar way I see Tesla electric and driverless cars. Identify the pain, correct it and take it a step further.
    A few good examples of where Tableau did this was:
    • Unions
    • Cross data source filters
    • Custom territories
    o In the case of custom territories I kept asking Tableau for more data (a la Excel mind-set) and they came back with a brilliant way of letting the user build upon their existing dataset.

    To me these examples are what makes Tableau so great, not that Excel does things bad. I don’t want Tableau to be great because, Excel, Qlik or any other tool are rubbish.
    I want Tableau to be great because they continue to invest in a product that’s innovative and has their users at the forefront of their development. Always striving to keep their moto “Help everyone see and understand their data” going.

    David

    1. David, I love your points and how you bring them to the table. I started this series more as a way to help Excel users understand the transition from Excel to Tableau, but as you highlight, may have went a bit off the rails with my Spock meme. I definitely agree, they’re fruit from different different trees and thank you for this reminder.
      PS – love the history highlight and I may steal this (with credit) in the future.
      PPS – for others – go read his blog, it’s wonderful!

Leave a Reply

Your email address will not be published. Required fields are marked *