fbpx
Search
Close this search box.

Create Cool Tag Clouds in Excel using VBA

Share

Facebook
Twitter
LinkedIn

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:
tag clouds in excel how to?


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 🙂

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

43 Responses to “Create Cool Tag Clouds in Excel using VBA”

  1. kashif says:

    Hey this really is cool . Smart play Chandoo ! I wish I could use this on my blog 😀

  2. Chandoo says:

    @Kashif - welcome to PHD. Stay tuned for more cool stuff 😉

    • Vara says:

      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

  3. Kevan says:

    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

  4. Chandoo says:

    @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 🙂

    • sarithagowda says:

      Yes, you are right i have got the same error and corrected as per your suggestions.. its working now, Thanks 🙂

  5. JP says:

    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

  6. Abdul says:

    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.

  7. Abdul says:

    Never mind.

    It worked.

  8. Chandoo says:

    @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 🙂

  9. Steve says:

    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!

  10. Adey says:

    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.

  11. Chandoo says:

    @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 Integer
    Dim maxImp As Integer

    can you try redefining them as numbers or floats? Let me know if it still doesnt work.

    • Vara Prasad says:

      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

  12. [...] is a tag cloud (excel tag cloud tutorial & templates). The fonts are sized based on their relative market share [...]

  13. retrolaser says:

    Great job! Thanks!

  14. Sri says:

    It works perfectly (tried it with values too)! This is awesome thanks! I am new visitor to your blog and it totally rocks.

  15. 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...

  16. Tasha says:

    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

  17. Tasha says:

    P.S. I love your open-source attitude - may it long prevail! 🙂 Thanks

  18. Chandoo says:

    @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

  19. Tasha says:

    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

  20. [...] 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 [...]

  21. Casey says:

    Is there anything you can't do with Excel? I'm saving this one... I will make a use for it!

  22. 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?

  23. DaveG says:

    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!

  24. Werner says:

    Thanks a lot!

  25. John Knox says:

    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.
     

  26. okl says:

    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

       

  27. Mike says:

    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? 

  28. prasad says:

    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 ! 

  29. Leo Gante says:

    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       

    • Hui says:

      @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.
       

  30. David says:

    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

  31. Ekki says:

    Swell! Many thanks Chandoo. Works very well.

  32. Barnaby says:

    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

    • Barnaby says:

      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"

      • Belinda says:

        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

        • Barnaby says:

          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 .

  33. Derek says:

    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.

  34. Vivek says:

    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.

  35. tom says:

    This is quite impressive. big WOW. brilliant approach. thanks.

  36. Harsha Vardhan says:

    Thank You !!

Leave a Reply