Word cloud is also known as Tag cloud in excel which is a visual representation of text in excel in different colors or formats, in a word cloud the frequency of the representation of work is defined by the font of the word, this is made by using the smart art and shapes feature of excel.
What is Word Cloud in Excel?
Word Cloud is the collection of words to show in the form of visualization in excel. I hope you have seen some of the best dashboards with numbers and creative visualizations in excel. Dashboards are ranging from classy fonts with attractive colors with some super stuff images with it. One of the peculiar dashboards you must have seen with “Word Cloud”. It looks like a sophisticated software downloaded image but it isn’t downloaded from anywhere rather created with Excel VBA coding. Yes!!! You are correct it has been created by VBA coding and in this article, we will show you how to create such a word cloud with excel.
How to Create a Word Cloud in Excel?
To create a word cloud in excel we need to have data of words and what are the colors we needed for those words. Assume you are creating a word cloud of 30 excel formulas. So make a list of 30 excel formulas in the worksheet.
Write the above formulas to your worksheet in the first column.
Once you write the above formula names in column B apply RANDBETWEEN function for all the 30 formulas as follows and keep the bottom value as 1 and the top value as 250.
Once the data is ready we need to head to the Visual Basic Editor. Create a User Form like the below in VBA.
Now we need to configure the userform in VBA, here for each button we need to write code. Below is the code for each button.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Double click on the “Different Colors” button from the above user form and add the below code.
Code:
Private Sub CommandButton1_Click() ColorCopeType = 0 Unload Me 'This is for a different color End Sub
Then Double click on the Black colored button and add the below code.
Code:
Private Sub CommandButton2_Click() ColorCopeType = 1 Unload Me 'This is for black color End Sub
Similarly, for all the other buttons add below codes.
Code:
Private Sub CommandButton3_Click() ColorCopeType = 2 Unload Me 'This is for red color End Sub Private Sub CommandButton4_Click() ColorCopeType = 3 Unload Me 'This is for green color End Sub Private Sub CommandButton5_Click() ColorCopeType = 4 Unload Me 'This is for blue color End Sub Private Sub CommandButton6_Click() ColorCopeType = 5 Unload Me 'This is for yellow color End Sub Private Sub CommandButton7_Click() ColorCopeType = 6 Unload Me 'This is for white color End Sub
Once we configure the code we need to declare the variable in the module on top.
Now we need to write a code to create a word cloud which is lengthy. Below is the code.
Code:
Sub word_cloud() Dim WordCloud As Range Dim x As Integer, y As Integer Dim ColumnA As Range, ColumnB As Range Dim WordCount As Integer Dim ColumCount As Integer, RowCount As Integer Dim WordColumn As Integer, WordRow As Integer Dim plotarea As Range, c As Range, d As Range, e As Range, f As Range, g As Range Dim z As Integer, w As Integer Dim plotareah1 As Range, plotareah2 As Range, dummy As Range Dim q As Integer, v As Integer Dim RedColor As Integer, GreenColor As Integer, BlueColor As Integer UserForm1.Show WordCount = -1 Set WordCloud = Sheets("Word Cloud").Range("B2:H7") ColumnCount = WordCloud.Columns.Count RowCount = WordCloud.Rows.Count For Each ColumnA In Sheets("Formula List").Range("A:A") If ColumnA.Value = "" Then Exit For Else WordCount = WordCount + 1 End If Next ColumnA Select Case WordCount Case WordCount = 0 To 20 WordColumn = WordCount / 5 Case WordCount = 21 To 40 WordColumn = WordCount / 6 Case WordCount = 41 To 40 WordColumn = WordCount / 8 Case WordCount = 80 To 9999 WordColumn = WordCount / 10 End Select WordRow = WordCount / WordColumn x = 1 Set c = Sheets("Word Cloud").Range("A1").Offset((RowCount / 2 - WordRow / 2), (ColumnCount / 2 - WordColumn / 2)) Set d = Sheets("Word Cloud").Range("A1").Offset((RowCount / 2 + WordRow / 2), (ColumnCount / 2 + WordColumn / 2)) Set plotarea = Sheets("Word Cloud").Range(Sheets("Word Cloud").Cells(c.Row, c.Column), Sheets("Word Cloud").Cells(d.Row, d.Column)) For Each e In plotarea e.Value = Sheets("Formula List").Range("A1").Offset(x, 0).Value e.Font.Size = 8 + Sheets("Formula List").Range("A1").Offset(x, 0).Offset(0, 1).Value / 4 Select Case ColorCopeType Case 0 RedColor = (255 * Rnd) + 1 GreenColor = (255 * Rnd) + 1 BlueColor = (255 * Rnd) + 1 Case 1 RedColor = 0 GreenColor = 0 BlueColor = 0 Case 2 RedColor = 255 GreenColor = 0 BlueColor = 0 Case 3 RedColor = 0 GreenColor = 255 BlueColor = 0 Case 4 RedColor = 0 GreenColor = 0 BlueColor = 255 Case 5 RedColor = 255 GreenColor = 255 BlueColor = 100 Case 6 RedColor = 255 GreenColor = 255 BlueColor = 255 End Select e.Font.Color = RGB(RedColor, GreenColor, BlueColor) e.HorizontalAlignment = xlCenter e.VerticalAlignment = xlCenter x = x + 1 If e.Value = "" Then Exit For End If Next e plotarea.Columns.AutoFit End Sub
Copy and paste the above code to your module. The worksheet which has a formula list should be named as “Formula List”. Then insert a new worksheet in excel and name it as “Word Cloud”.
In the newly inserted sheet adjust the Zoom to 40% and adjust the row height to 85.
So, now execute the code in VBA by pressing the F5 button, you will see the color choosing user form.
Choose the color as per your wish, if you don’t want a single color then choose the option “Different Colors”. We will get the word cloud in the “Word Cloud” sheet.
Like this using VBA coding, we can create Word Cloud.
Recommended Articles
This has been a guide to Word Cloud in Excel. Here we discuss how to create a word cloud in excel using VBA coding with example and downloadable excel sheet. You can learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
