3 Tips to Overcoming the Excel Barrier to Tableau Adoption

Ryan Sleeper
on May 12, 2016

Note: This is a guest post by Ryan Sleeper, author of Tableau 201, 2013 Iron Viz champion, and Director of Data Visualization at Evolytics.

Today, nearly seven years into my career as a data visualization practitioner, I enjoy looking back at how my Tableau work has evolved and thinking about how I was introduced to the program in the first place.

I began my career at a small lifestyle marketing agency in San Francisco with a team of just three co-workers and the agency’s owner. One day, the owner walked into the office that three of us shared and said,“I’ve heard of a new tool for visualizing data called Tableau, and I want you to try recreating some of our existing Excel reports to see how it works.”

With those instructions in mind and little to no experience with other BI or data visualization software programs to lean on, we all tried to jump right in by connecting Tableau to our existing Excel reports. Of course, the two programs operate very differently, and much of the Excel way of thinking did not work immediately when trying to create the same views in Tableau.

It did not take long for the urge to run back to the comfort of our collective Excel experience to emerge. Fortunately, I stuck with it. I learned over time the value that Tableau provides in addition to Excel. And I have made a career around asking questions of data and sharing the answers in compelling and engaging ways.

I consider myself lucky, but I’ve also seen, firsthand, others have similar success. I’ve also seen some new Tableau users revert back to more familiar tools like Excel before things begin to click for them. In fact, I view Excel as the biggest barrier to Tableau adoption. What those that give up on learning Tableau fail to see is that Excel and Tableau are different tools for different jobs, and they actually work quite well together.

Let me share my top three tips for overcoming the Excel barrier to improve the adoption of Tableau, starting with the third one.

3. Explicitly Demonstrate the Value of Data Visualization

In my post "A Spreadsheet is Not a Data Visualization," I share a relatively common conversation that I have found myself having throughout my career. Despite my best efforts to introduce more visual analyses, sometimes I simply cannot get an end user to let go of their tabular or spreadsheet views.

As data visualization practitioners, it is obvious to us that visualizing data reduces the time to insight and improves the accuracy of insights when compared to data in crosstab form. That’s probably the main reason most of us became data visualization practitioners to begin with. I’ve realized over time, though, that despite the advantages of data visualization, overcoming some audiences’ decades of spreadsheet habits can be challenging.

It is my belief that when these audiences don’t immediately adopt a new way of looking at data, it is not because they believe their spreadsheets are more effective; it is because they have never been explicitly introduced to the value of data visualization.

Instead of getting frustrated that a visualization is not immediately understood and adopted in favor of the end user’s familiar Excel table, I have found success by showing them why visualization is better. To do so, I walk them through a simple exercise where I convert a crosstab view in Tableau to a highlight table. For a specific example, see the data-driven storytelling tip from TC14: "Smooth the Excel Transition."

If this still doesn’t work, show the audience that any tab can be duplicated as a crosstab view in Tableau by either: 1. right-clicking on the sheet’s tab and choosing “Duplicate as crosstab,” or 2. navigating to Worksheet > Duplicate as Crosstab in the top navigation.

2. Use a Trick for Conditionally Formatting Individual Rows or Columns

One of the most common requests I hear from Excel users is for instruction on how to conditionally format table views in Tableau like you can do in Excel. This is one of the areas where the two programs operate very differently.

In Excel, you can conditionally format each individual cell. Tableau offers essentially unlimited conditional formatting through calculated fields and Marks cards, but it can appear somewhat “all or nothing.” For example, if you place a dimension on the color Marks card in Tableau, all of the marks on the view will be colored by that dimension.

This isn’t ideal if you are wanting to conditionally format individual rows or columns in a table view. Well, there’s a hack for that (credit: I originally saw this trick documented by Dan Montgomery in his post, "My Favorite Tableau Trick for Work: MIN(1)").

Instead of using the Measure Names dimension on the Columns shelf or Rows shelf as you normally would when creating a crosstab view in Tableau, use a placeholder measure instead.

To create a placeholder measure, create a calculated field and type MIN(0) as the formula. Once you have created this calculated field, each time you place it on the Rows shelf or Columns shelf, a new row or column will be created, respectively.

The trick is that as each new placeholder is placed on the view, a new set of Marks cards will be available. These Marks cards can be edited independently, which means you can create conditional formatting for each individual row or column!

For more information, see my recent post, "Tableau Hack: How to Conditionally Format Individual Rows or Columns."

1. Shape Data for Use with Tableau

You didn’t think we’d get through a post during Data Cleaning Month without talking about the data itself, did you?

As I mentioned, I view Excel as the biggest barrier to Tableau adoption. More specifically, the barrier presents itself when new Tableau users attempt to connect directly to existing Excel reports. For that reason, shaping data for use with Tableau is my number one tip for improving the adoption of Tableau.

The frustration that new Tableau users experience when connecting to existing Excel reports happens because most existing Excel reports do not include Tableau-ready data. Consider the following example of a common report in Excel:

There is a title along the top, a column header for each quarter, and a row for each KPI (sales, profit, and orders). In addition, there is a total for each row on the right-hand side of the table.

The format of this report poses several problems for Tableau which, upon connecting, will try to interpret the data source, classify the fields, and set up your workspace:

  1. There is a title in the first row. The first two rows are critical for Tableau to interpret the data source, so we’ve immediately gotten off on the wrong foot.
  2. The column headers are quarters, which will cause Tableau to create a field for each quarter, when in fact the quarters should all be consolidated into one field for date / quarter.
  3. The KPIs are running down the first column so, by default, Tableau will not interpret these KPIs as unique fields.
  4. There is a total in the right column. As Tableau totals fields for you, not only is this unnecessary, it will likely lead to double-counting.

The ideal format for Tableau looks like this:

Each column now represents a unique field, so the layout is vertical instead of horizontal. The title and totals have also been removed. With the data in this shape, Tableau will be able to look at the first row to determine the fields and the second row to classify the data (i.e. type, discrete vs. continuous, dimension vs. measure).

Preparing the data for use with Tableau completely removes the single largest barrier involved with an Excel to Tableau transition. While long-term habits of Excel users can take some time to overcome, setting them up for success by providing Tableau-ready data, explicitly illustrating the value of data visualization, and showing that flexible conditional formatting is also available in Tableau, you will make the transition as seamless as possible.

For more tips, tricks, and vizzes by Ryan, check out his Tableau Public page and his blog posts on Evolytics.com. You can also connect with him on Twitter @OSMGuy.

Got a data-cleaning tip to share? Tweet it to us using the hashtag #SpringDataCleaning.

Add new comment