fbpx
from Excel to Tableau

The Excel data cruncher

Just like you, I’m an Excel data cruncher.  Love it.  I think Excel is great for collating and managing data, and as a data visualiser I use it regularly for preparing small and static data sets.  So why do we need to ponder a move from Excel to Tableau?

Because Excel strangles the opportunity to communicate deeper analysis; the types of analysis that extend beyond the pivot table and static Excel chart. 

Imagine presenting your analysis as a dashboard of interconnected visualisations, rather than a PowerPoint presentation where you’re clicking from one slide to the next.

When someone pipes up, “Can you show me how we performed last month?”, you move the pointer and up it comes.  You know you’re on a winner.  And they will too.

When knowing your data is important (and when isn’t it), Excel becomes inefficient at delivering the capabilities to recognise hot-spots and derive that deeper understanding of your business.

So this article isn’t about ditching our much loved spreadsheets and cruising into a utopian life beyond Excel; here you’ll be guided to overcome the typical barriers to make a smooth and enjoyable venture into the world of visual analytics, moving from Excel to Tableau.

Excel Pie Chart

The Excel pie chart.

A favourite for cutting and pasting into the annual report.

But not my favourite.

Pay attention to this term

There’s one term that doesn’t get enough air time in visual analytics, and it’s not AI or machine learning, it’s what’s known as a pre-attentive attribute.

When teaching visual analytics, pre-attentive attributes are one of the first topics I cover due to their simplicity and importance in visual analytics.

A pre-attentive attribute is anything that draws our attention.  A distinguished Tableau dashboard uses pre-attentive attributes to focus people on specific hotspots and trends in your data that are otherwise missed in a pivot-table or Excel chart. 

They also provide excellent means for transforming your data from a bunch of independent charts into a data story; one that’s oozing in communication potential.

Colour is probably the best example of a pre-attentive attribute, but there are many ways Tableau brings focus and clarity to areas of our business warranting further investigation.  Size, shape and texture are other attributes for quickly drawing our audience’s focus.

So when we visualise data in Tableau, we use colour for impact and not for decoration.  It’s a sensible general rule.

Pre-attentive attributes are a fascinating field of study.  Tableau provides a short guide to pre-attentive attributes in Why Visual Analytics?

Designing with your brain in mind by Elias Nordlinder is also a nice reference using Tableau examples.

The Tableau Treemap packs two pre-attentive punches.

Size – this shows the quantity of electricity generated.

Colour – this shows the change from the previous year.

So, Large Scale Solar is the ‘grower’, but it’s still small. 

Black Coal is the ‘dominator’, but it’s retreating.

Getting to know Tableau

In 2015 I was introduced to Tableau.  I was working mostly with spreadsheets and Excel charts at the time.  Justin Gilbert, Federation University’s Manager of Planning and Analytics (and a big Tableau fan) dropped it into my lap and said to try it, he’d seen it demonstrated and it looked promising.

I haven’t used Excel charts since, with the exception of generating the pie chart above.  I’m confident I’ve satisfied the itch for a further 6 years.

Last year I was asked for assistance to format an Excel chart.  Not only did I have difficulty remembering how to perform this, I realised how Tableau can completely transform our experience working with visual analytics and how more revealing and powerful analytics can be.

Taking the time to move from Excel to Tableau is worth the investment.  Presently I’m consulting to the ARTSA Institute (ARTSA-i) who are an Australian industry body for heavy vehicles (that’s trucks).  We’ve transformed their entire analytics environment from spreadsheets and printed PDF charts to Tableau interactive analytics.

Tableau Online provides ARTSA-i with central and secure cloud access, handing their members the latest market trends and hotspots on a platter.  Members can even save their filtered views, “I’m interested in new trailer registrations in Victoria”, making their next visit and analysis even quicker.

The same Treemap, but now we’ve introduced the States to make our dashboard.

We can now investigate in more depth.  Discover by hovering.

So, Large Scale Solar has grown everywhere, with sunny QLD dominating.

Looks like Black Coal is on the decline in QLD.

Think about how many individual charts we’d need to show all these combinations!

Before starting – some different ways to analyse

Tableau gives us the power to create the most stunning and sophisticated analyses.  But that doesn’t mean your next (or first) dashboard has to take on this persona.  Quite the contrary, most Tableau dashboards achieve their purpose by being simplistic in design, but revealing in outcome.

A recent dashboard I designed analysing survey data for a Victorian school springs to mind.  I bet we’ve all reviewed survey responses before; scrolling through each question in turn, and if longitudinal in nature, reviewing the bar against previous responses and looking for trends at every possible junction.

Can you imagine capturing your entire survey data within a single dashboard?  You’ll be quite popular with those who have to perform the analysis!

I achieved this using Tableau’s heat map to draw attention to questions with high variance from the baseline.  By hovering on the heat map, the specifics and trend of that question were revealed, removing the need to review each question in turn.  This not only sped up analysis, it ensured that any key findings were not missed.

So we need to be thoughtful about designing a Tableau dashboard before diving in.  This simple but important question will guide your development:

Are you designing for an outcome or for exploration? 

 

outcome

Designing for outcome.

Designing for an Outcome: This is where you need to report on progress against a target or baseline.  Try to keep to a single dashboard with a maximum of 2 charts.  Use BANs (Big A… Numbers) to communicate your findings, e.g. big bold 10% increase YTD text.

You can still provide filtering and a minor level of drill-down.  Any audience should be able to use this dashboard with limited training.  It should be intuitive.

These dashboards are great for providing progress updates, “Our membership base is 10% up on this time last year.”  My recent school survey dashboard fits into the Outcome category.  Most school staff would be able to extract the key survey outcomes with little or no introduction to the interface.

designing for exploration

Design for exploration and let your audience navigate to what’s important to them.

Designing for Exploration: Designing for exploration gives you more licence to be creative.  Your audience will want to dive deeper into the data to explore relationships between many variables.

Consider your audience before determining how detailed the dashboard needs to be.  The more sophisticated it is, the more challenging it may be to navigate.  I usually pitch exploration dashboards to a small group of users; those who can be guided and supported in achieving maximum benefit.

I recently created a dashboard to analyse waste composition for a Victorian local government, by combining views on collection routes, diversion rates (organics and recyclables vs landfill) and seasonal trends.  This dashboard is pitched at a small number of users and will drive their future strategy for landfill minimisation.

Ready to get your hands dirty?

Hands dirty

5 steps to move from Excel to Tableau

If you’ve come this far, you’re wanting to take the jump to Tableau, or needing some soothing reassurance that your Tableau journey is going to be ok.

Here are five steps to set you or keep you on your journey.  This is not a how-to, but rather a what-to-do.

 

1 Download and play

The journey starts with familiarisation.  Start with a simple data set; Tableau comes with a sample set but even better if you use one that you already work with.

You can either download the trial version of Tableau Desktop (currently 14 days) or for longer access, download Tableau Public.  The Tableau Desktop trial is fully featured and expires after 14 days.  Tableau Public can be used forever, but is only suitable for training as you cannot save your work unless publishing it to the Tableau Public domain (don’t do this with sensitive data please!)

Follow a few quick video tutorials to get you started, but don’t become too bogged down in the videos.  It’s much better to begin playing and start making mistakes!   Mistakes are sometimes key to unveiling features you didn’t realise existed (been there).

replicate

Don’t worry.

You won’t need to be an artist to replicate your Excel charts in Tableau.

2. Replicate your simpler Excel charts

The key to building a good Tableau visualisation is to initially understand the differences between dimensions (think of categories) and measures (think of values, e.g. $).  Organise your data into these and then start with a simple bar chart, as these are probably the easiest to build.

Use the Marks card to add some colours and labels.  Yes, it’s fun, but don’t become too distracted 🙂

Once you’ve replicated your first chart from Excel to Tableau, use the ‘Show Me’ feature to reveal other ways to visualise your data.  Show Me opens up ideas for visualising your data in ways you may not have considered.  It also automates the process of creating some of the more challenging visualisations.

 

3. Build a simple dashboard

Combine two worksheets in a dashboard and turn on cross filtering!  This is the little filter icon at the top right of your worksheet.  It filters whatever you select on your chart across to the others.

Oh, and turn on animations.  Format – Animations.

image showing someone impressing their peers

4. Impress your peers, influence your stakeholders

Share your love for Tableau and your creations.  If you need to convince someone else that Tableau is the way, demonstrate your work.  The beautiful thing about Tableau is that it sells itself.  Once you’ve experienced and demonstrated the capabilities, it’s hard to justify previous methods.

The key selling points however are not only in the pleasing visual aspect of the analytics.

They are:

– Informed insights lead to better decision making and cost savings.  Tableau is an investment not an expense.

– Tableau decreases the time to refresh and analyse data leading to increased workforce efficiency.

reach out

Reach out when you need support

5. Reach out

You are not alone in this journey.  There’s a large community sharing ideas, their challenges and their analytics journey:

Tableau User Groups – most cities have one.  I frequent the Melbourne Tableau User Group and it’s a fantastic forum for sharing ideas and networking.

Tableau Public – the visualisations here are inspiring and you can generate some good ideas.

OR, use a consultant and invest in your data!  I’m not here to spoil your fun, just to save you time, money and make you look good 🙂

 

Grant Henderson is the Principal Analyst at Perceptive Data.  Connect with Grant on LinkedIn

 

Perceptive Data supports organisations at any stage of their Tableau analytics journey.

Request a Data Consultation

Are you ready for the move to Tableau?  Do you need support with your existing Tableau environment?

Contact Perceptive Data for a 30 minute consultation to talk about your data.