WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Word Cloud in Excel

Word Cloud in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

word-cloud-in-excel

How to Create a Word Cloud in Excel?

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

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.

wordcloud example 1.1

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

Once you write the above formula names in column B, apply the RANDBETWEEN function for all the 30 formulas as follows and keep the bottom value as 1 and the top value as 250.

wordcloud example 1.10

Once the data is ready, we need to head to the Visual Basic Editor. Create a User Form like the below in VBA.

wordcloud example 1.2

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Double click on the “Different Colors” button from the above user form and add the below code.

wordcloud example 1.5

Code:

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

word cloud example 1.3

Then Double click on the Black colored button and add the below code.

word cloud example 1.6

Code:

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

word cloud example 1.4

Similarly, for all the other buttons, add the 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.

word cloud example 1.7

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.

word cloud example 1.8

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.

word cloud example 1.9

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 –

  • Autofit Excel Row Height
  • Excel KPI Dashboard
  • Excel Calculator
  • Use AverageIF in Excel
9 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Word Cloud Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More