There are plenty of designers, templates, and online services that let you create Word Clouds (aka Tag Cloud), but do you know how to create one yourself in a short notice, without paying anyone anything? If you have Excel and some creativity, then you can create a Word Cloud just as good as any! It’s only a matter of how much time you spend to perfect the final image. An important thing to keep in mind: Word Clouds aren’t supposed to be just a cluster of pretty-looking words…their sizes depend on some quantitative values. For example, frequency of some words/expressions will determine which words can get what sizes. The amount of some product sales could determine the size of the product names presented, etc.
After searching online, I wasn’t quite satisfied by the methods used by other Excel experts. Some are under-engineered, with no tie to actual logic or data, and some are very over-engineered basically relying on a ton of VBA code reinventing the features already in Excel. So, here I’ll show you some two quick ways to create them that are accurate, meaningful, and yet does not rely on coding alone. Basic Excel understanding and creativity are all you’ll need.
EXAMPLE 1: One that takes user-feedback from a free-form, finds specific keywords in the strings, ranks the words based on frequency of occurrence, then determines the font-sizes for each. Finally, it applies font-faces, sizes, and colors with a custom-made macro (that you can extend for your needs).
Output 1:
Another variation of the same, using the same underlying data and process but with a different visual representation.
EXAMPLE 2: The second example, uses the data of 10 tallest peaks (mountains) in USA and using their elevations, ranks them, an applies the appropriate sizes of fonts. In this example, we’ll also utilize the same steps as above but will not be using the macro due to it being a very small dataset.
Output 2:
Explanation:
Let’s look deeper at each example.
In Example 1, the keywords I want to look for are in column B. Column C contains a psedo string automatically generated by my formula to generate strings containing at least one of the keywords from B. In real-world, it’d be what your users/customers have entered as feedback in social media for example.
Using that info, I created a matrix and determine which of the keyword occur in each line of the string in column C above returning TRUE (if it occurs) or FALSE (if it didn’t occur) per line. Then I convert the trues and falses into numbers (1 and 0 respectively) so that I can count the frequency.
Once I count them, I can rank all the keywords. This ensures that the ranking will be always accurate whether you have 3 keywords, and 100 sentences or 1000s of each! However, I rank them in reverse because by default it’d rank the most occurring count to rank 1, whereas I want the rank to be the highest value based on the number of keywords (thus also making it fully scalable to any number). Finally, I simply determine font size based on the ranks. Higher the rank value, larger the font.
My matrix looks like this.
Next I want to create a table based on this that’ll actually convert the keywords look and feel according to the values above + use a variety colors and fonts. So I lay it out in a table as below (this is just an example of the fonts I applied for testing purposes). The important thing to pay attention to is that each word’s font size and name are in its row in adjacent columns as below. But the font faces and colors are not applied yet, this shows after I’ve applied it once as an experiment while testing a macro to do this for me.
Speaking of the macro to generate the above fonts with different colors, here’s the code:
You could clean this up further using VB Editor (e.g. have one larger With…End With wrapper instead of each block) if you wish.
Because I don’t like to save the code in a macro-enabled Excel file (because of the security hoops for distribution), I wrote the code in a text file, imported it and ran it on my workbook, then exported any changes back to a .bas file for the code that I can reuse whenever and wherever I like. And saved the Excel file as a normal XLSX file (because I don’t need code anymore once it’s done its work).
In Example 2, I take a different approach. This time because the dataset is quite small (only 10 records), I don’t need to use any VBA code, I can accomplish everything with formulas and some clicks.
I took a data snapshot of tallest peaks in USA for the top 10.
Below is the data for elevations…and ranked using RANK.EQ() function but in reverse (meaning rank value of 10 is the highest, not 1), then determined font size based on rank value with whatever makes sense proportionately…in my case, I just use fontsize (pts) =rank*10
I added a stock background image:
Then added transparency to it before pasting the keywords (in this case, the names of the mountains/peaks).
Another thing that’s common for both examples is converting the colored texts (keywords) into rotatable elements so that we can place them however we want. Since text in Excel belongs to a cell you have very limited ability to rotate them at different angles. To that end, the trick is to copy the formatted cell, and paste each as a linked image (or image if you don’t want to link it to the text cell’s formatting at all). Now each word becomes an image element that you can freely manipulate as for its size and placement.
Again, the more time you spend on them the more you can perfect them. I only spent about 5 minutes on each for this demonstration, and all photo editing was done within Excel’s photo processing features. For fancier effects, you could use additional software.
I hope this gives you some new ideas. This is certainly just ONE way of doing this, which I personally feel is the most accurate, quick and yet not too difficult or shabby. You can experiment with it to find your own method. Enjoy.
