Wordle Tag Clouds in Microsoft Excel

Create dynamic Tag Clouds in Microsoft Excel using advanced Wordle

One week back, my friend and Excel MVP Chandoo published a post to celebrate the very impressive milestone of the 10,000^th comment on his blog: 10k Comments Excel Dashboard. Who dared to think he would do this without providing a dashboard? Of course he did.

Chandoo provided a very nice dashboard showing all kinds of analysis and visualization of the comments received on his blog so far. By the way: Kudos on the marvelous success of your blog, Chandoo!

Great layout and very interesting insights on an at a glance dashboard. Chandoo also included a tag cloud showing “what do they say”. He obviously created the cloud using Wordle, but he inserted it as a static image.

This made me think. Is it possible to create tag clouds in Excel dynamically?  Yes it is.

Today’s post shows a way how to include Wordle tag clouds into your Excel workbook, dynamically based on texts in any cell ranges. As always, the article includes the Excel example workbook for free download.

The Challenge

The challenge is obvious: replace the static image produced on the Wordle webpage by a dynamic feature in Microsoft Excel. Don’t go to a web browser and paste the text to Wordle anymore. Simply change an interactive control in Excel and see the results from Wordle within your Excel workbook.

Advanced Wordle

Wordle rovides an advanced functionality to send your text to be visualized in a tag cloud. Microsoft Excel provides the WebBowser form to be included in a workbook.

And this is the simple idea. Create an HTML in Excel based on your text, send it to Wordle and receive the tag cloud visualization in a WebBrowser form within your Excel workbook.

The Implementation

Here are the main steps how to do this:

1. Bring your ducks in a row

   Compile the texts you want to visualize with a Wordle tag cloud into an Excel workbook. The whole text in one cell, split up over several cells or even one word per cell. Doesn’t matter. Just get your data into an Excel workbook.

2. Insert a WebBrowser form into your workbook.
3. Create a UDF to concatenate texts in cells

   If you have your text spread over several cells, you probably want to concatenate the text with ease. Excel’s built-in function CONCATENATE is lame. You cannot specify a range. You have to insert each single cell to be concatenated. That’s why we are using a VBA User Defined Function (UDF). This function could look like this:

   Public Function Concatenate_Text(Textrange As Range) As String
   Dim str_text As String
   Dim var_cell As Variant
       For Each var_cell In Textrange
           str_text = str_text & " " & var_cell
       Next
       Concatenate_Text = str_text
   End Function

   Pretty simple. We pass through a range where our texts are and the function concatenates and returns all the strings in the cells of the specified range. Nothing new under the sun. You may have seen this one a hundred times before.

4. Create the HTML to be sent to Wordle

   To create a Wordle tag cloud from raw text, you'll need to create an HTML-Text. Here is the example posted on Wordle's website:
   
   <form action="http://www.wordle.net/advanced" method="POST">
   <textarea name="text" style="display:none">

   [… here goes the text to be wordled …]

   </textarea> <input type="submit"> </form>

   That’s easy. The text above and below our text can be static constants in 2 cells. We concatenate the first part of the HTML, the cell with our text created using the UDF and the second part of the HTML. We can do so either using Excel’s function CONCATENATE or – even better – using simple ampersands.

   Finally we give the range with our concatenated HTML-code a name, e.g. “myHTML”.

5. Write a procedure to update the WebBrowser
   Finally we write a one-line VBA sub to update the WebBrowser:

   Sub UpdateTagCloud()
       Worksheets(1).WebBrowser1.Document.Body.innerHTML = _
                                                       Range("myHTML").Value
   End Sub
   

6. Optional: additional formulas to refer to different texts

   In our example workbook, there are different texts than can be chosen from. To give the user the opportunity to switch between different input texts, we need some COUNTA formulas and a formula using OFFET and INDEX. Furthermore we are including an input element (like a combo box in our example) and assign the SUB UpdateTagCloud to this element to trigger an update of the WebBrowser as soon as the user switches to another data set. No big deal.

Done.

The Example

Data. I need some data to showcase how this works. Political speeches during elections? Outdated for the time being. The Wikileaks documents? Interesting, but this would take too much time.

So I decided to go for something completely different. Something timeless. I compiled the lyrics of the most important Bruce Springsteen records (excluding the live albums, the best-ofs and the tracks) into one Excel workbook and tag clouded them using Wordle. From Greetings from Asbury Park NJ (1973) to Working on a Dream (2009). This way we can easily see how Bruce’s lyrics changed over time.

I compiled the lyrics from brucespringsteen.net. At this place a big thank you for this fabulous website.

Here are some of the results:

Greetings from Asbury Park, NJ (1973):

Born in the USA (1984):

Devils & Dust (2005):

Please draw your own conclusions. This is a post on the technique, not on the results.

The Drawbacks

Wordle advanced is limited. Well, maybe my HTML knowledge is too limited to exploit everything that might be possible. Anyway. Even if you don’t know much about Wordle and HTML, you can create tag clouds as shown above directly in your Excel workbook.

However, there are a couple of disadvantages coming with my simple approach:

• Extra click
  After switching to another data set (in our example another record), you need to click on the submit button in the WebBrowser to make Wordle create the new tag cloud. One more click necessary for the user, but it is quite obvious what is required and from my point of view not too inconvenient.
• Randomized view
  If you transfer your text to Wordle using the HTML, Wordle creates a randomized view. I haven’t found a way to set the font type, the layout or the colors with the HTML. Maybe possible, but I don’t know how. Thus, you have to edit the Wordle tag cloud the way you want to have it after submitting the text in the Excel workbook.
• No embedded view
  Again, maybe it is just my lack of knowledge, but I don’t know a way to let Wordle create an embedded view, i.e, without the Wordle menu at the top and the buttons beneath the tag cloud.
• Scaling
  Unlike with a static picture, you are limited in sizing the WebBrowser. You can easily reduce the size of the WebBrowser, however, this will lead to a visualization where you have to scroll up or down the webpage provided by Wordle.
• Text size limitation
  Strings in Excel workbook cells are limited to 32,767 characters. Sounds like a lot of data. However, the lyrics of “The River” (double album) have already 25,681. You see: a limitation.

Although there are considerable drawbacks, I think this approach is a pretty convenient way to create Wordle tag clouds directly in your Excel workbook.

The Download Link

Here is the example workbook for free download:

Download Bruce Springsteen Lyrics Tag Clouds (Excel 97-2003, 400K)

Acknowledgement

A big thank you very much goes to Jonathan Feinberg for providing the fabulous tool Wordle for free and making all this possible. Thanks, Jonathan!

What’s next?

Next post will be another article on Microsoft Excel. This time showing a way how to improve chart tooltips with Microsoft Excel.

Stay tuned.

Update March 2015

This article was published back in November 2010. As mentioned above, the described approach comes with a few disadvantage. In the meantime an additional drawback showed up, a showstopper even: the technique doesn’t work anymore with Excel 2013, because Microsoft disabled scriptable ActiveX controls on worksheets in Office 2013.

In February and March 2015, I published two follow-up articles, providing a stand-alone Excel workbook to create dynamic Word Clouds which overcomes some of the disadvantages mentioned above and works with Excel versions 2007, 2010 and 2013:

Word Clouds with Microsoft Excel

The Implementation of Word Clouds with Excel