CoolData blog

12 April 2010

New way to look at words

Filed under: Coolness, Data visualization, Free stuff, Text — Tags: , , , , , — kevinmacdonell @ 8:13 am

Word clouds aren’t new, but there’s a new online app for creating them that is worth checking out. Tagxedo allows you to create your clouds using some versatile tools for shaping the appearance of the cloud, which you can then easily save as a .jpg or .png.

This comes to me via a post on the LoveStats blog, where Annie Pettit has posted a couple of her own creations – one based on the text of her resume, and one on all the words in her blog.

I wrote about word clouds back in December (Quick and easy visuals of large text files), and the well-known and very cool tool known as Wordle, the creation of Jonathan Feinberg. Tagxedo does the same thing but works a little differently. Powered by Microsoft’s SilverLight browser plug-in, Tagxedo offers a nifty interface for importing your text (or URL), finely controlling your word choice, and playing with the font, colour, theme and layout of your cloud, including being able to choose a shape. The choice of shapes is rather limited – hearts, stars, rectangles and ovals, mostly. Here’s a star-shaped word cloud based on the 150 most common words on this blog:

(Click for full size image.)

My interest in word clouds is related to visualization of data – in this context, conveying the gist of a mass of text by giving prominence to the most common significant words. For example, last year I used Wordles to visualize tens of thousands of words entered as free-text comments in a survey of alumni. It’s no substitute for real analysis, but it does make a cool presentation slide!

NOTE: Check in tomorrow for Jason Boley’s amazing work with NodeXL for visualizing prospect connections in your data.

29 January 2010

Friday tip: Stop Excel from messing with long text strings

Filed under: Excel — Tags: , , — kevinmacdonell @ 12:23 pm

Ever try pasting a long string of text into Excel only to have the cell truncate at 255 characters? I hate this.

The problem crops up when I paste the results of a query of our database out of MS Access and into Excel. The queries I’m working with this week are “last and next action” reports for major gift prospects, and sometimes the Notes column contains more text than Excel likes. The result is that my notes are cut off after a certain number of characters. I was familiar with this problem when working with the constraints of Excel 2003, but I was dismayed to encounter the same problem in Excel 2007. I Googled around in desperation, to no avail.

The solution I found by accident is stupidly simple. Maybe I’m the only person who didn’t already know how to deal with this? Anyway, here it is:

Once you’ve copied the columns from your source (Access or whatever), open Excel and right-click on cell A1. Choose Paste Special, then choose CSV. Click OK to paste.

This step will prevent Excel from truncating long text strings. I don’t know why this works, and I don’t care. It just works.

Note: This is for Excel 2007. I haven’t tested it for Excel 2003, and I haven’t tested with other Paste Special options.

9 December 2009

Quick and easy visuals of large text files

Filed under: Data visualization, Free stuff, Text — Tags: , , , , — kevinmacdonell @ 7:30 pm

Earlier this year we conducted an extensive survey of alumni, made up mostly of scale statements but including a few free-text comment fields as well. Respondents typed in nearly 80,000 words in comments – that’s slightly longer than the first Harry Potter book!

Somebody has to read all this stuff (not me!). But what can we do with it in the meantime?

Why not play with it in Wordle?

According to the web site (, Wordle is “a toy for generating ‘word clouds’ from text that you provide. The clouds give greater prominence to words that appear more frequently in the source text. You can tweak your clouds with different fonts, layouts, and color schemes. The images you create with Wordle are yours to use however you like.”

Here is a word cloud for the free-text comments made in response to the question, “Do you have any other comments about your academic experience?

You can also enter the URL of any blog or feed into Wordle, and it will generate a word cloud from that. Here’s what a Wordle of this blog looks like (so far).

Word cloud for CoolData blog (up to 9 Dec 2009).

Useful or just a toy? I did use some of these word clouds in a presentation of the alumni survey results, and the response told me it was worth it. It’s a cool thing, and people like cool things. I also see Wordle creations in newspapers – I think the first example I ever saw was a comparison of campaign speeches made by Barack Obama and John McCain.

What do you use word clouds for?

Create a free website or blog at