How do I create a Wordle Cloud

Word Cloud in Excel

The word cloud is also known as the tag cloud in Excel. This is a visual representation of text in Excel in a variety of colors or formats. In a word cloud, the frequency with which work is represented is defined by the font of the word Excel's intelligent art and forms stand out.

What is Word Cloud in Excel?

Word Cloud is the collection of words to be displayed in the form of a visualization in Excel. Hope you've seen some of the best numbers and creative visualizations dashboards in Excel. The dashboards range from classic fonts with attractive colors to some great images. One of the most peculiar dashboards that you come with "Word Cloud" must have seen. It looks like a sophisticated software downloaded image, but it is not downloaded from anywhere, it is created using Excel VBA encoding. Yes!!! You are right that it was made by VBA coding. In this article, we are going to show you how to create such a word cloud using Excel.

How do I create a word cloud in Excel?

You can download this Word Cloud Excel Template here - Word Cloud Excel Template

To make a word cloud in Excel we need word data and what colors we need for these words. For example, suppose you create a word cloud containing 30 Excel formulas. So create a list of 30 Excel formulas in the worksheet.

Write the above formulas on your worksheet in the first column.

When you have written the formula names above in column B, apply the RANDBETWEEN function for all 30 formulas as follows, keeping the lower value as 1 and the upper value as 250.

Once the data is ready we need to go to the Visual Basic Editor. Create a user form like the following in VBA.

Now we need to configure the user form in VBA. Here we need to write code for each button. Below is the code for each button.

In the user form above, double click the Different Colors button and add the following code.

Code:

Private Sub CommandButton1_Click () ColorCopeType = 0 Unload Me 'This is for another color End Sub

Then double click the black button and add the following code.

Code:

Private Sub CommandButton2_Click () ColorCopeType = 1 Unload Me 'This is for the black color End Sub

For all other buttons, add the following codes.

Code:

Private Sub CommandButton3_Click () ColorCopeType = 2 Unload Me 'This is for the red color End Sub Private Sub CommandButton4_Click () ColorCopeType = 3 Unload Me' This is for the green color End Sub Private Sub CommandButton5_Click () ColorCopeType = 4 Unload Me 'Dies is for blue color End Sub Private Sub CommandButton6_Click () ColorCopeType = 5 Discharge me 'This is for the yellow color End Sub Private Sub CommandButton7_Click () ColorCopeType = 6 Discharge me' This is for the white color End Sub

Once we have configured the code we need to declare the variable in the module above.

Now we need to write some code to create a long word cloud. 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 otherwise WordCount = WordCount + 1 End If Next ColumnA Select the 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"). Area ("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 = leaves ("word cloud"). Area (Sheets ("Word Cloud"). Cells (c.Row, c.Column), Sheets ("Word Cloud"). Cells (d.Row, d.Column)) For each e In plot area e.Value = Sheets ( "Formula list"). Range ("A1"). Offset (x, 0). Value e.Font.Size = 8 + sheets ("formula list"). Area ("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 end for End If Next e plotarea.Columns.AutoFit End SubFormelliste "). Area (" 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 = xlenter. VerticalAlignment = xlCenter x = x + 1 If e.Value = "" , then quit for End If Next e plotarea.Columns.AutoFit End SubFormelliste "). Area ("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 Fall 6 RedColor = 255 GreenColor = 255 BlueColor = 255 End Select e.Font.Color = RGB (RedColor, GreenColor, BlueColor) e.HorizontalAlignment = xlenter. VerticalAlignment = xlCenter x = x + 1 If e.Value = "", then exit for End If Next e plotarea.Columns.AutoFit End SubWert / 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 Fall 5 RedColor = 255 GreenColor = 255 BlueColor = 100 Fall 6 RedColor = 255 GreenColor = 255 BlueColor = 255 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 SubWert / 4 case select 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 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 SubAutoFit End SubAutoFit End SubHorizontalAlignment = xlCenter e.VerticalAlignment = xlCenter x = x + 1 If e.Value = "", then quit for End If Next e plotarea.Columns.AutoFit End SubHorizontalAlignment = xlCenter e.VerticalCenter x = xlCenter e.VerticalCenter x = x + 1 If e.Value = "", then exit for End If Next e plotarea.Columns.AutoFit End Sub

Copy and paste the code above into your module. The worksheet with a formula list should be called the “formula list”. Then paste a new worksheet into Excel and name it "Word Cloud".

In the newly inserted sheet, set the zoom to 40% and the row height to 85.

Now run the code in VBA by pressing the F5 key. You will see the color in which you select the user form.

Choose the color according to your preference. If you don't want a single color, choose the "Various Colors" option. We get the word cloud in the sheet "word cloud".

Here's how we can create Word Cloud using VBA encoding.