3 Ways to Smooth the Excel Transition

Help your audience evolve from spreadsheets to data visualizations

How to convert a text table to a highlight table, leverage the ‘Viz in Tooltip’ feature to display a trend within a crosstab cell, and allow users to toggle between a crosstab and a line graph in a single view.

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to be sharing three different ways to smooth the Excel transition. What I mean by that is, you’ve likely run into stakeholders that are wanting to look at all of their analyses as just raw spreadsheets of data. And I’m going to be providing three specific tactics on how to get them to kind of move on to something more visual, something that’s going to be more effective and help them do their analysis. We’re going to be talking about highlight tables, the Viz in Tooltip feature, and I’m also going to be sharing a hack that allows your end user to toggle between a raw text table of data and something more visual.

To help get us started and explain why this is important, I’m going share the exercise that I often talk about, which is to try to answer a basic business question in this raw text table of data. We’re looking right now at Sub-Category and Month and Profit. And if I were to ask just a simple business question such as what is the highest number in this dataset, our end user would really struggle to find that answer because they’d have to scan either left to right or top to bottom to figure out what the highest number was.

They’d have to look at all 203 marks on this view. So my first tip for you, a very easy one to implement, is to simply switch this text table to a highlight table. A highlight table is kind of a gateway visualization. If I click Show Me, it’s the third option on the first row. And I’ll fit the entire view again so that we can see this better. But this takes that same text and it just applies some simple encoding of color. Color is a preattentive attribute that helps us process this much faster than just looking at a raw text table of data.

Now the cells with the higher profit values are colored a darker blue the cells with a lower profit value are colored a darker orange. So that’s my first tip for you. The reason that this is so effective at smoothing the transition and getting your end users to move on to something more visual is they still see the raw data. If they need those exact numbers to feel comfortable, they are represented on this view. But whether they like it or not, they’re getting the benefit of this preattentive attribute of color that’s helping them process this visual.

By the way, if you’re interested in this highlight table, there’s another video here at Playfair Data TV that shows you three different techniques for how to make your highlight tables more engaging. My second tip for you to make the end user kind of more receptive to visuals or kind of a way, honestly, to kind of sneak in the values or the benefits of preattentive attributes is to use the Vis in Tooltip feature. To help illustrate, I’m going to undo this sheet just to get us back to the raw text table.

And I’m going to set up a second sheet. So I’ll give this a name and call it my Text Table. And I’ll start a second sheet that I’ll call Line Graph. And we will look at continuous Month and Profit. So I just set up a simple trend here. But over back on the Text Table tab, I will add that trend line to the tooltip. Right now, our tooltips are showing us just the data points that are on the view, so the month in that cell, the Sub-category represented, and the Profit value.

I’m going to customize the tooltip by clicking on the Tooltip Marks Card and I’ll add this at the bottom. And if I click this Insert button, the first option is Sheets. You can add entire sheets to tooltips. This feature is called Viz in Tooltip. So if I click Line Graph, you’ll see the name of the sheet, the dimensions of that sheet within the tooltip, and then you can customize this. But let’s go ahead and see how the defaults look.

I’m going to click OK. And now when I hover over a cell, I get to see the four year trend for that specific combination of Month and Sub-Category. So when I hovered over Chairs here, what that line graph represents by default is the four year trend during the month of February for the Chairs Sub-Category. So again, we’re kind of forcing them to benefit by something more visual. We’re smoothing this transition. They’re still seeing the raw text table but they’ll almost kind of stumble into this interactivity.

If they’re hovering over a number, just to kind of focus on it and take a closer look, this visual will appear where they’ll get the benefit of looking at something as a visualization. We can now not only see the raw number but we can see a four year trend for that number. So we’ve just added value to this visualization. My third tip for you to smooth the Excel transition is to allow your end user to flip back and forth between a text table and something more visual. To do this– and you can do this with any chart types but I’ll just illustrate it with the two that we’ve built so far, Text Table and Line Graph.

The first thing you need to do is setup a parameter that contains your two choices. So I’ll call this my Text Table / Line Graph Toggle. The data type is going to be String because we’re eventually going to allow our end user to choose from two words or two phrases, technically, “Text Table” or “Line Graph”. That’s also a very short list. So I’ll click List for allowable values because I’ve only got two things to type here, either Text Table or Line Graph.

And again, you can do this with any charts. You can do this with more than two charts if you want to. But I’m just illustrating this little hack here. So first step, set up the parameter with the name of our two chart types. Click OK. We now need a calculated field. And that calculated field is going to be the same thing we just set up. So this is our toggle. And the entire formula for the calculation is the parameter.

So I started to type the name of the parameter. I clicked on it. If it turns purple, that color coding means that Tableau recognized that field as a parameter in my data source. But that is the entire formula. I’m going to click OK. And I need to show the parameter control because what we’re going to do is filter each one of these two sheets to either show when Text Table is selected or show when Line Graph is selected.

So I’m going to show the parameter control, which will allow me to quickly flip back and forth between the two. I need to drag my calculated field, Text Table or Line Graph Toggle, to the Filters Shelf. And notice there’s only one option, it’s the option selected in the parameter control. I’m on the Text Table sheet, so I want this sheet to show when Text Table is selected in my parameter control. So this is exactly what I want so far.

Going to click Text Table and click OK. Over on the Line Graph worksheet– and notice it’s still in the Tooltip, that’s what that’s doing there. You can leave it for now, not a big deal. I’m going to add this same calculated field to the Filters Shelf but I don’t want the line graph to show when Text Table is selected. So before I add this filter, I will show the parameter control again and flip this to Line Graph.

Now, if I go to add the filter, Line Graph is the only option because that is what is selected in the parameter control. I’ll click on that and click OK. And now notice what happens when I go between these two sheets. If I go to Text Table, because Line Graph is selected in the parameter control, nothing is showing up. When I go to Line Graph, I see the line graph because it is selected in the parameter control, which is controlling this filter on the Filters Shelf.

If I were to switch this to Text Table, the Line Graph sheet goes away but my Text Table sheet returns. This will be very important later on when we do the last step in this little hack. So we’ve got our two sheets individually. We’ve built a parameter control that controls which one is being shown. The last step is to start a new dashboard. And very critical step, we need to put both of these sheets into a vertical layout container.

So I’m going to drag Vertical onto the view and just place each of my two sheets inside of that vertical layout container. So Text Table first. Notice the parameter control came over automatically. I’ll put the Line Graph second, but very important that this has to be inside of the vertical layout container. It’s also best practice with this little hack to hide the titles. So I’m going to right-click on each one of those and hide the titles.

And now notice when I’ve got Text Table selected in the parameter control, they see the raw text table. If I choose Line Graph, we see the line graph. What’s happening is because these charts are in a vertical layout container, they are expanding. So both sheets are always on this view. But what’s happening is when I click Text Table, the line graph isn’t showing and it gets minimized to just the very bottom of this layout container.

When I click Line Graph in the parameter control, we have the same thing in the other direction, the Text Table gets scrunched up at the top. That sheet is still on the view, it’s just almost invisible. It is– you’ll see just three or four pixels of white. That is the sheet within that layout container but it’s almost not even noticeable. So when Line Graph is selected, the Text Table contracts. The Line Graph expands to fill the vertical layout container. And we’ve just provided the ability for our end user to toggle between these two.

The only real pitfall with this approach is it’s a little bit fragile if you are ever fixing the height of either of the sheets. So what I mean by that is if we, at some point while we are laying out our dashboard, accidentally fix the height– so if this was selected– this would break that layout container’s ability to expand and contract to show the most appropriate chart. So if you’re following along or you try this in the future and it’s not quite working for you, the first thing I would check is to make sure you don’t have Fixed Height selected on any of those individual worksheets.

But again, we’ve just created three different ways for our end user to still see the raw data– so the text table that they’re accustomed to seeing– but these three techniques I’ve just shared will help kind of smooth their transition into something more effective. This is going to help them do their analyses and provide something that’s more visual to help us understand what’s going on in the data.

This has been Ryan with Playfair Data TV – thanks for watching!