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 20On 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 Integercntr = 1
i = 1For 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 🙂
43 Responses to “Create Cool Tag Clouds in Excel using VBA”
Hey this really is cool . Smart play Chandoo ! I wish I could use this on my blog 😀
@Kashif - welcome to PHD. Stay tuned for more cool stuff 😉
Hi Chandoo.
Its realy helping me in my work..
Thanks!
I have added color also in the same code and its working fine.
Now I have challenge in gettin getting the ouput like:
Color has to be fixed based on the percentages.
For example:
A 30%
B 60%
C 20%
I require in word cloud with
A as font 20 with Red
B as font 15 with Blue
C as font 10 with Green
Please can you help me on this.
vara.stat@gmail.com
This is absolutely brilliant, but I get an error:
Compile Error: Syntax Error.
The comment lines underneath Sub createCLOUD () are red, as is
taglist = taglist & cell.Value & “, ”
and after that error message, Sub createCLOUD () is highlighted.
Running Excel 2003, SP2
@Kevan... thanks for your comments. I think when you pasted the code you should change the quotes from " to normal double quotes. Just remove any of the double quotes from the code and replace them by keying in double quotes from your keyboard. Alternatively download the attached excel and use the code in it instead. Let me know if it still fails.
Welcome to PHD 🙂
Yes, you are right i have got the same error and corrected as per your suggestions.. its working now, Thanks 🙂
Cute code, looks like something you could use instead of just sorting or listing values. But I wonder if most people understand blog tag clouds.
Thx,
JP
Hey,
I couldn't get it to work. I got the following error message:
ou may encounter this error for the following reasons:
Macro security is set to:
Very High and the application encounters a signed macro, but the macro was automatically disabled. Use the following procedure to enable the macro:
Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to High by clicking the High radio button.
Close the file and any other instances of the application currently running on the computer (close all applications that also use the application you are currently running).
Open the file again and examine the certificate of trust details and set the Always trust macros from this publisher box if you trust the certificate issued by the publisher.
Click the Enable button to allow the macro to run.
High and the application encounters a signed macro, but you selected Disable when prompted by the macro warning dialog when opening the file. Use the following procedure to enable the macro:
Close the file and any other instances of the application currently running on the computer (close all applications that also use the application you are currently running).
Open the file again and examine the certificate of trust details and set the Always trust macros from this publisher box if you trust the certificate issued by the publisher.
Click the Enable button to allow the macro to run.
High or Very High and the macro was not signed and was automatically disabled by the application. Use the following procedure to enable the macro, if you have verified you can trust the source of the unsigned macro:
Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to Medium by clicking the Medium radio button.
Close the file and any other instances of the application currently running on the computer.
Open the file again and click the Enable button when prompted to allow for the unsigned macro to run.
When you have accepted the macro, return the macro security level to its previous setting.
Another instance of your application has a lock on the security settings and is disallowing any changes to security settings.
It is recommended to close all Office related applications or other instances of the same application when you attempt to make changes to your security settings.
Visual Basic for Applications (VBA) is not installed on your computer.
If VBA is not installed, it is not possible to accept certificates of trust attached to VBA macros. Therefore, any VBA macro attempting to run, even with an attached certificate of trust, cannot be run because the necessary macro interpreter is not installed on the system. Two scenarios can cause this error:
Your administrator chose not to install VBA
The version of Office you are running does not install VBA by default
If you are running an instance of Office that does not provide VBA as an installable feature, you will need to upgrade your version of Office to run VBA macros.
The certificate of trust is invalid - (when this occurs, you cannot select the Enable button).
The certificate is no longer trusted, was revoked by the issuing authority, or is damaged. There are several other possibilities which you may need to explore as to why the certificate is no longer recognized as valid by your system. It is recommended that you not trust or run a macro that has an invalid certificate.
A troubleshooter help topic is available online regarding this issue.
More information about this error message online.
Never mind.
It worked.
@Abdul... welcome to PHD... and moreover, welcome to working with Macros in Excel, you have to adjust the security settings to "mild" or something to let this code run in your computer.. Looks like you have already figured that out. Let me know if you have any further difficulties 🙂
Neat! Took me a bit to figure out what was going on (cell E10 formatting to wrap text helped... as did a change of values in the cells...) but once I saw it, I was quite impressed!! Nice job!
It worked but only on percentages not on values. Also if the percentages are all low it doesn't do the size relatively across the set; you just get one size text.
apart from that very good work.
@Steve: Thanks 🙂
@Adey: thanks. You are right, it doesnt scale well when the range is small.
I guess the culprit could be
Dim minImp As IntegerDim maxImp As Integer
can you try redefining them as numbers or floats? Let me know if it still doesnt work.
Hi Chandoo.
Its realy helping me in my work..
Thanks!
I have added color also in the same code and its working fine.
Now I have challenge in gettin getting the ouput like:
Color has to be fixed based on the percentages.
For example:
A 30%
B 60%
C 20%
I require in word cloud with
A as font 20 with Red
B as font 15 with Blue
C as font 10 with Green
Please can you help me on this.
vara.stat@gmail.com
[...] is a tag cloud (excel tag cloud tutorial & templates). The fonts are sized based on their relative market share [...]
Great job! Thanks!
It works perfectly (tried it with values too)! This is awesome thanks! I am new visitor to your blog and it totally rocks.
Social comments and analytics for this post...
This post was mentioned on Twitter by Srividya: Psyched about a piece of VBA code to create word/tag clouds in Excel. Thanks to @r1c1 http://bit.ly/5U4QA...
Hey,
I like the idea of the code, but I'm having a problem with your variables. The following variables are not defined, an easy work around that I've now defined 🙂
cntr = 1
i = 1
But for the following code:
For Each cell In Excel.Selection
It says 'cell' is a variable not defined (i have selected the range first). I've looked in the object explorer and can't find 'cell', should I dim 'cell' as a range?
Thanks
Tasha
P.S. I love your open-source attitude - may it long prevail! 🙂 Thanks
@Tasha... Welcome to PHD. thanks for asking a question.
you could just add dim cell to your code.
I have not defined variables as it is not mandatory to define them in VBA. But may be your workbook settings mandate that variables be defined.
PS: Thank you. We love to keep things simple and open at Chandoo.org
Hi,
I got round the problem and thought I'd share. I had Option Explicit turned on and it would not accept the variable 'cell'. I turned it off and hey presto it worked 🙂
I also added the following - to allow for multiple use:
Dim inputcell As Variant
' paste values in cell ...
inputcell = InputBox("Which Cell do you want to paste the data into?")
Range(inputcell).Select
ActiveCell.Value = taglist
ActiveCell.Font.size = 8
I am getting the programme to work, but alas it's not changing the font size for the highest frequency word 🙁 Maybe someone has some feedback on that to share?
e.g. One result gives me this:
Facebook,facebook and myspace,Have seen, don't use,,,,,,,Myspace,,,
or this:
,,Have seen, don't use,,,,,,Delicious,Delicious,,,
Cheers
[...] for or talking about — anywhere that they are entering free-form text. I found an article on chandoo.org about how you could create a tag cloud in Microsoft Excel, and adapted it for use with Next [...]
Is there anything you can't do with Excel? I'm saving this one... I will make a use for it!
This is very cool and *almost* what I need. What I *actually* need is what's displayed here: http://www.youtube.com/watch?v=BYkVoyakkms - but http://www.ExcelVisualBasics.com seems to have disappeared. Any ideas whether there's another version of that around anywhere?
How would I reverse the two columns? - that is, I want to have the count in the first column and the tags in the second column.
FYI: there are a number of widely used SEO analysis sheets that use that arrangement, and this would be an awesome addition to them with that column reversal.
Thanks in advance for all you do!
Thanks a lot!
I love this. I do have a quick question. When using this document, I need to be able to input numbers in the hundreds of thousands. When putting numbers, in column C, that are high, it doesn't seem to be working. How would I modify the code to build a cloud using high number values?
I appreciate your help.
Hi,
How can i change the selection to a pref-defined 2 column data rather than by user selection.
So i can automate it.
Thanks
Out of curiosity why is minImp always 0? I've ran it with tons of different values percentages, integers, etc. and every time minImp comes back as "0". Was curious as to whether this was by design?
I need the code vba in excel to display the tag values for the selected date.
ex:Private Sub CommandButton1_Click()
Sheet1.Range("b1") = "sinusoid"
Sheet1.Range("b2") = "10/1/2012"
Sheet1.Range("b3") = "10/31/2012"
Sheet1.Range("b4") = "10h"
Sheet1.Range("E8:F8") = "=PISampDat(""sinusoid"",""9/1/2012"",""9/30/2012"",""45m"",1,""PI"")"
Sheet1.Range("E8:F8").Select
Call dlresize
End Sub
.................
this code is not working can u help regarding this code
thanks !
Hi Chandoo,
I am using Excel 2010 and I cannot seem to make it work.
Is there something I have to tweak to make it work?
Thanks,
Leo
@Leo
Without any guidance it could be anything!
Can you specify what isn't working?
I'd suggest going through the code and retype all the " marks as often WordPress screws them up.
Thanks for this, i urged me to make my own. Although there are a few Excel based solutions, for me they were too boring. I'm almost there but my solution is to use WordArt.
Unlike some other languages, you cannot determine if objects/shapes (wordart) are overlapping with each other, so to get around this i have made the columns on a worksheet 0.08 and the rows 0.75 and used the TopLeftCell and BottomRightCell properties to see if they overlap, and then move them if they do by randomly generating co-ordinates.
It works quite nice and you can also randomly select wordart styles/colours for each word.Another existing function that i couldn't find was to include phrases in the word-count
Swell! Many thanks Chandoo. Works very well.
It's an old post, but found it really interesting. I was keen to see if I could add colour scale to the code also. (I see there were a few request for it in the comments also)
Hope the demo file is of value / interest.
https://onedrive.live.com/redir?resid=AED191A2C782478B!11841&authkey=!ALRcTpxK-z-S7Hc&ithint=file%2cxlsm
The code :
"Sub createColourCloud()
' 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
Dim tCol As Double
If Selection.Columns.Count 3 Then
MsgBox "Please select a 3 column range" & vbNewLine & _
vbNewLine & "Column 1 = Tags" & _
vbNewLine & "Column 2 = Size" & _
vbNewLine & "Column 3 = Colour intensity ", vbInformation + vbOKOnly, "Hello"
Exit Sub
End If
size = Selection.Count / 3
'arrays
Dim tags() As String
Dim importance()
Dim colourRating()
'resize arrays
ReDim tags(1 To size) As String
ReDim importance(1 To size)
ReDim colourRating(1 To size, 1 To 2)
'min / max Importance
Dim minImp As Integer
Dim maxImp As Integer
'min / max Colour
Dim minColour As Double
Dim maxColour As Double
cntr = 1
i = 1
For Each cell In Excel.Selection
'add value to tags()
If cell.Cells.Column = Selection.Column Then
taglist = taglist & cell.Value & ", "
tags(i) = cell.Value
'add size to Importance()
ElseIf cell.Column = Selection.Column + 1 Then
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
'add "emphasis" to the colour
Else 'column = 3
If Val(cell.Value) maxColour Then
maxColour = colourRating(i, 1)
End If
If colourRating(i, 1) < minColour Then
minColour = colourRating(i, 1)
End If
i = i + 1
End If
cntr = cntr + 1
Next cell
' paste values in cell e26
Range("e26").Select
ActiveCell.Value = taglist
ActiveCell.Font.size = 8
strt = 1
For i = 1 To size
'Format the Characters
With ActiveCell.Characters(Start:=strt, Length:=Len(tags(i))).Font
'Add size
.size = 6 + Math.Round((importance(i) - minImp) / (maxImp - minImp) * 14, 0)
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
'add colour
If colourRating(i, 2) = 1 Then
'colour blue . set the colour gradient
ColourStrength = Abs(((colourRating(i, 1)) / (maxColour))) ' convert to percent
.Color = RGB(150 - ColourStrength * 150, 150 - ColourStrength * 150, 255 - ColourStrength * 50)
Else
'colour Red . set the colour gradient
ColourStrength = Abs(((colourRating(i, 1)) / (minColour))) ' convert to percent
'set Red, green, blue values
.Color = RGB(255 - ColourStrength * 50, 150 - ColourStrength * 150, 150 - ColourStrength * 150)
End If
End With
strt = strt + Len(tags(i)) + 2
Next i
Exit Sub
tackle_this:
' errors handled here
MsgBox "An Error Occurred", vbCritical + vbOKOnly, "Wow, looks like there is an error!"
End Sub"
Can you please tell me what values should be in the 3rd column
I have my words and counts but don't know what to do with column 3
Expensive 176
Confusing 69
Trustworthy 180
Modern 140
Friendly 157
Ordinary 107
Boring 47
Innovative 91
This is exactly what I need to do so thank you very much in advance
Hi Belinda ,
The third column can have anything else that measures the importance or value of the words in your cloud.
The colour will give another dimension to the word cloud.
Looking at your words I could suggest a couple of options .
1. Manually assign a 'positivity' score to the words. For example "trustworthy" and "innovative" are positive words , so give them a value > zero . Eg 3,4,5
And words like "boring" and "expensive" are negative words , so give them a negative value . Eg -3,-4,-5
2. Another option is to just copy column 2 to column 3 . This will give your a double effect of big words with a strong blue colour and small , light coloured words .
I think option 1 might be better for Your words .
Hey Chandoo,
You have some awesome Macros here. You should submit them on MSOexchange.com, it's a site where developers can post their Microsoft Office Tools.
Hi Chandoo,
This code is awesome. It works perfectly for a corporate dashboard I'm building but I'd like to enhance this code to add color scheme to the taglist. With Tableau, I'm able to have the words range with different shades of the same color; with the smallest word having a light red and then the shade of red gets darker accordingly. Can I replicate something like that? If not, can I get it as different colors for each word? Many thanks.
This is quite impressive. big WOW. brilliant approach. thanks.
Thank You !!