# How to create a word cloud with word counts, Excel, and Wordle

When you’ve got the right purpose in mind, word clouds can be very useful. But when you only have a list of words and their counts, particularly when the word counts are large,  how do you turn a short list of words into word cloud?

Well, let’s take an easy example and work with this list of words. Because this is an easy list, we could just re-write it into this: Cake, cake, cake, cake, cake, brownies, brownies, brownies, cookies, cookies, pie, pie, pie, tarts, tarts, tarts, tarts, tarts, tarts, tarts, tarts, tarts, squares, squares, squares, squares. That took me about 1 minute to write out.

 Cake 5 Brownies 3 Cookies 2 Pie 3 Tarts 9 Squares 4

But what happens when your list looks like this? Are you really supposed to write out each word thousands of times just so they can be copied into Wordle? And what happens if there are several hundred words in your list and they all have hundreds or thousands of mentions? It could take an hour to do to accurately and, as you’ll soon find out, is a complete waste of time.

 Cake 4522 Brownies 3492 Cookies 2431 Pie 3142 Tarts 878 Squares 3627

Have no fear! A quick little Excel trick is in order. Have a peek at the picture here and notice the equation. This handy little equation tells Excel to choose the word in column A and then repeat it by the number in column B. The concatenate portion inserts a space between each word which is important for Wordle to distinguish between each word.

Now all you need to do is copy the contents of column D into Wordle.

And then click on Go! Now you can try it with a really long list of words and it will just take a couple minutes. Enjoy!

# Tagxedo for colorful shapeable saveable word clouds

[tweetmeme source=”lovestats” only_single=false]The internet has been wanting this for a while and it’s finally here! Try Tagxedo for yourself! It’s text analysis for the graphically inclined.

The first one is my resume and the second is this blog.

• MRA Boston Day 1 – Tweeple save the day! Turning down business is good for business
• 5 random things about me
• A Do Not Call list for the internet. Primed for failure? #MRX
• U wanit cheep or would you like it done correctly?
• Shy Friendly Shopping

On several occasions now, I’ve come across a comment like, “Everyone on Twitter is smarter/funnier/more dedicated/better than I am.” I even saw a tweet from someone who said something like, “I go to Store ABC because the people on Twitter make me feel dumb.”

Well, if you stop and think about it, the only people you CHOOSE to follow (ignoring courtesy follows) on Twitter are the people you either:

2. want to learn from,
3. want to laugh from,
4. have similar interests, or
5. have some commonality with you that prevents you from turning off the follow.

Also think about this, do you follow people who bore you, have a stupid sense of humour, say stupid things? I doubt it. You tune those people out as fast as you can. This means you end up with a finely tuned group of people who make you happy, people who choose the best of their witty remarks, the best of their smart remarks, and the best of all the random junk that’s passing through their brain. It’s a very personalized self-determined segmentation. In my case, it means I follow:

1. researchers,
2. gardeners,
3. birders,
4. bakers,
5. online icons, and
6. a bit of random silliness.

Those are my segments. In the end, these leaves you with a very skewed representation of who is on Twitter. You’re only seeing what you want to see, and it’s dang hard to see what you can’t see. Again, in my case, it seems like everyone on Twitter loves research and works in a professional setting. So, forget that nonsense about how much better or worse people on Twitter are and enjoy what it offers you.

And if you’re interested, here’s what my Twitter interest profile looks like, thanks to Wordle. (Hi Tom!)

Related Posts

• 40% of Twitter is Pointless Babble: I Beg To Differ
• I resolve to finish eating this apple cookie
• 3 tips to force consumers to be more loyal to your brand
• Shouters, readers and blathering buzzwords
• Sean Conry: Mobile Research #netgain5 #mrx #li
• # Word Cloud of my Resume – Wicked Cool!

Have a look at this! It’s a cloud version of my resume. I just copied the text into wordle and it spit this out. I don’t know why I was surprised to see that it really represented who I am in the work world.

Try out your own word cloud at http://www.wordle.net

Related Articles