Tag Archives: excel

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.

excelwords

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

wordletext

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!

wordle

Advertisements

If you DARE – the chart test of death

Former logo of American Idol from 2002 to 2008.

Image via Wikipedia

After asking someone what a t-test is and whether they watch American Idol, I have another question I like to ask interviewees to see if they know their stuff. I ask them to create a chart that they would present to a client. Ahhh, the variety of answers this reveals.

1) Some folks tell me that this version of Excel doesn’t do charts but they can do it on their version. (Honestly. This one astounds me.)
2) Others create a chart. That’s it. Nothing else. When I re-prompt that it’s to give to a client, I still get a default chart in default colors with a default no title and default lack of zero on the axis.
3) A few create a chart and try to pick the right chart, try to put a title, and try to fix the axis.

Right away, I can see that Person #1 has weak problem solving skills and great excuse skills. There are multiple ways to chart in excel and if you can’t get one of them to work, I can’t help you. Thanks for coming out.

Person #2 pays no attention to detail and barely wants to get the job done. Thanks for coming out.

Person #3 is my person. Whether they choose the right options, I know they are curious and want to do the right thing. If they don’t know what the right thing is, I can teach them and they can learn.

If I ever get to ask you this question, please make sure your chart has a non-misleading scale, a good title, non-florescent colours, and isn’t 3D. My sincere thanks.

Read these too

  • Art, History, and Culture in Chicago Two Balloons and a Great Song
  • Survey Panel Questions – Enough Already! Word Cloud of my Resume – Wicked Cool!
  • Survey Design Tip #4: Brands are people too
  • Oh oh, I Have Another Favourite Statistic!
  • TV Shows From When I Was Short
  • %d bloggers like this: