Create Cool Tag Clouds in Excel using VBA

I was toying with the idea of creating a tag cloud in excel – as a form of new visualization, this could be useful when you have medium amounts of data (eg: 50-300 rows) and you want to emphasize on what is important and what is not. I would imagine using a tag-cloud,

• When you are listing features of your software
• When you are listing your sales figures across top 1000 cities of your country
• When you are analyzing visitor data to your web start up

My goal is to generate a tag cloud from a selected data table (with just 2 columns, one with text to display, the other with any number on it) as shown below:

Download and play with a sample tag cloud visualization I have created

Since anything related to changing cell formats is not possible using functions, I had to write a VBA Macro (a subroutine that you would write in your excel sheet to achieve a task). The logic is simple:

1. Read the selected table and create 2 arrays, 1 with tags and another with the numeric data
2. Select an empty cell in the work book (I choose E10)
3. For each item in tags array:
– Add text to the selected cell
– set its font size based on normalized value between 6 and 20

The code is shown below:

Sub createCloud()
‘ this subroutine creates a tag cloud based on the list format tagname, tag importance
‘ the tag importance can have any value, it will be normalized to a value between 8 and 20

On Error GoTo tackle_this

Dim size As Integer

size = Selection.Count / 2

Dim tags() As String
Dim importance()

ReDim tags(1 To size) As String
ReDim importance(1 To size)

Dim minImp As Integer
Dim maxImp As Integer

cntr = 1
i = 1

For Each cell In Excel.Selection

If cntr Mod 2 = 1 Then
taglist = taglist & cell.Value & “, ”
tags(i) = cell.Value
Else
importance(i) = Val(cell.Value)
If importance(i) > maxImp Then
maxImp = importance(i)
End If
If importance(i) < minImp Then minImp = importance(i) End If i = i + 1 End If cntr = cntr + 1 Next cell ' paste values in cell e10 Range("e10").Select ActiveCell.Value = taglist ActiveCell.Font.size = 8 strt = 1 For i = 1 To size With ActiveCell.Characters(Start:=strt, Length:=Len(tags(i))).Font .size = 6 + Math.Round((importance(i) - minImp) / (maxImp - minImp) * 14, 0) .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With strt = strt + Len(tags(i)) + 2 Next i Exit Sub tackle_this: ' errors handled here 'MsgBox "You need to select a table so that I can create a tag cloud", vbCritical + vbOKOnly, "Wow, looks like there is an error!" End Sub

This code is totally reusable. Just right click on the sheet name at bottom & select “view code”. In the VBA Editor create a new module (Menu > Insert > module) and Paste the above code there. Go back to your excel sheet and select a 2 columned data table and run the createCloud macro. The cloud will be created and pasted in cell E10. You can change this by modifying the line Range(“e10”).Select.

Download the above code and an example in an excel

Happy charting 🙂