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:

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.

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.
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.

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):

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.