Filterable Word Clouds in Tableau (With Pivots!)

Word clouds – randomly arranged collections of the most common words used in a text, where word size varies with how frequently it’s used – have a bit of a mixed reputation in data analytics circles. While the argument can certainly be made that there are more precise ways of showing word frequency, it’s also true that they’re an easy, user-friendly way to quickly pull out common ideas from a text. I recently used a word cloud to visualize responses to a community engagement survey, at the request of a stakeholder, and really enjoy how some of the most important recurring themes in the answers spring to the surface.

There are many excellent tutorials on how to create word clouds in Tableau, so I’m not going to dive too deeply into that process in this post. (The quick version: Words go on the “label” card, and word count goes on the “size” card). Instead, let’s take a look at a way to take your word clouds up a notch by making them fully filterable. To see what I mean by fully filterable, check out the (mock data) version of my community engagement dashboard below. When you click on a piece of demographic information on the left side of the dashboard, like selecting the “Staff” segment of the “Role” donut chart, the word cloud on the right side filters down to only analyze staff responses.

What makes this tricky is that most methods of making a word cloud in tableau have you pre-process the data, compiling a file with each word and its recurrence count. That means that the responses stop being associated with the characteristics of the responder, and you lose the ability to filter. The solution I used is to skip most of the preprocessing, and instead use Tableau’s pivot function to transform the data into a usable form.

Heads up – pivoting dramatically increases the number of rows in your data source. For this method, you’ll be multiplying each row in your original data by the number of words in the longest response, which may not be appropriate for very large response sets or very long responses. Do some testing to make sure your dashboard performance doesn’t suffer!

To get started, you’ll need a file with one line per survey response, where the responses are split into one-word-per-column chunks. (I like using google sheets for this pre-processing stage, since it makes live connections to a qualtrics or google forms survey possible – more on this in a later post!) In the image below, you can see that I used a response ID to label each row. The demographic characteristics associated with each response ID are in a separate sheet, and I’ll join them in later in Tableau.

Tip: Excel and Google Sheets both have “split” functions that allow you to split a response into multiple columns by word. I also kept the original, unsplit response in my file so that I could use it for sample responses.

Next, bring your file into Tableau as a data source. Select all the columns that contain single words, click the down arrow, and select “Pivot”. Be aware that your pivoted data will contain lots of blank rows, since your pivot size is based on the length of the longest response – this is ok! While setting up my data source, I also added the sheet that contained demographic info and joined it to this table by response ID.

Now head over to the sheet where you want to create your word cloud. Add the “Pivot Field Values” column to the text card, and “CNT(Pivot Field Values)” to the size card. I also like adding “CNT(Pivot Field Values)” to the color card, to add an extra layer of visual clarity.

You’ll also notice a couple things going on in the filters pane of the sheet. I have 4 action filters which are being used to filter down responses when a viewer clicks on a section of the charts on the dashboard – you can create your own action filters on your dashboard, or use direct filters. I also am using a calculated field, titled “Remove Words Q1”, where I put a list of words I don’t want to include in the word cloud. These are words like “the”, “a”, “and”, etc, that are very common but don’t add to the understanding of the responses. I used a calculated field because the original dashboard had four separate questions, and I wanted to be able to copy-and-paste the calculated field for each question. If you only have one question, or some time on your hands, you can also just directly filter out words by adding “Pivot Field Values” to the filters pane.

So there you have it – a fully filterable word cloud! In a future post, I’ll talk about how to make this a live flow directly from a survey using Google Forms or Qualtrics. What are your thoughts on word clouds? Do they have a place in the data viz world, or are there better ways of showing key ideas?