Word Cloud in Excel Using VBA (Free Download)

A word cloud in Excel using VBA turns a simple two-column list into an eye-catching visual where every word is sized by its value. Word clouds, also called tag clouds or text clouds, are common in Power BI and other visualization tools, but Excel has no built-in word cloud chart. This macro builds one inside a single cell: it joins all the words, then changes the font size, colour and font of each word according to its number. You can download the free practice file and follow every line of the code below.

⬇️  Download the Word Cloud Practice File
Word Cloud.xlsm inside a .zip (32 KB) · macro-enabled workbook · enable macros after opening

Watch the Video Tutorial

In the video PK creates word clouds for month-wise sales and country-wise population, shows how each setting changes the result and explains the macro step by step.

What You Will Build

• A Setting sheet where you paste words in column A and values in column B.
• Five settings: minimum font size, maximum font size, separator, multiple font colour (Yes/No) and multiple font name (Yes/No).
• A Word Cloud sheet where all words appear in one wrapped, centred cell with a double border.
• A macro that sizes every word in proportion to its value and can apply random colours and fonts.
• A Create Word Cloud button to rebuild the cloud whenever the data changes.

Part 1 – Set Up the Workbook

Step 1: Create the Setting sheet

The practice file has two visible sheets, Setting and Word Cloud. On Setting, row 1 says Paste your data here, row 2 holds the headers (Country and Population in the file) and the data starts in row 3: words in column A, values in column B. Keep the list free of blank rows, because the macro uses the count of column A to find the last row.

Step 2: Add the settings and the font list

The settings sit in G6:H10:

• H6 Minimum Font Size (10 in the file)
• H7 Maximum Font Size (45 in the file; the screenshot above uses 60)
• H8 Separator: a comma followed by two spaces
• H9 Multiple Font Color: Yes or No
• H10 Multiple Font Name: Yes or No

In column P, under the header Font Name in P1, list the fonts the macro may pick from. The file has ten, starting in P2, including Arial, Bodoni MT Black, Castellar, Impact and Times New Roman. You can add more; the macro counts the list each time.

Step 3: See how the settings change the cloud

In the video PK first uses month-wise sales with a 10 to 60 font range. November, the highest month, is the biggest word and October, the lowest, is the smallest. Set Multiple Font Name to No and every month uses the same font; set Multiple Font Color to No and all text turns black. Lower the maximum size to 30 and the biggest word shrinks to 30 points. Then he pastes the country list, sets the range to 10 to 45 and China and India come out largest.

Part 2 – The Font Size Logic

Step 4: Scale each value between the minimum and maximum size

Before writing code, PK proves the logic with worksheet formulas. With a minimum of 10 and a maximum of 45, the difference is 35 (in H2), and the largest population, China’s 1,420,062,022, sits in D3. The font size for Afghanistan in C3 is:

=$H$6+B3*$H$2/$D$3

That returns 10.92: the minimum size plus Afghanistan’s share of the largest value, multiplied by the 35-point range. Fill it down and China gets exactly 45. A value of zero would get the minimum size, and the largest value always gets the maximum. The macro uses the same formula in VBA.

Part 3 – Write the Word Cloud Macro

Step 5: Declare the sheets, sizes and largest value

Press Alt+F11, insert a module and start the macro:

Option Explicit

Sub Create_Word_Cloud()

Dim Setting_Sh As Worksheet
Set Setting_Sh = ThisWorkbook.Sheets("Setting")

Dim WCSh As Worksheet
Set WCSh = ThisWorkbook.Sheets("Word Cloud")

Dim Min_Font_Size As Integer
Dim Max_Font_Size As Integer

Min_Font_Size = Setting_Sh.Range("H6").Value
Max_Font_Size = Setting_Sh.Range("H7").Value

Dim Max_Value As Long
Max_Value = Application.WorksheetFunction.Max(Setting_Sh.Range("B:B"))

Dim i As Integer

Dim start_ As Integer
Dim length_ As Integer

The two font sizes come from H6 and H7, and Max_Value is the largest number in column B. PK points out that the minimum value is not needed; only the maximum is. (The download file still declares an unused Min_Value line.)

Step 6: Join all the words into cell B2

WCSh.Cells.Delete
WCSh.Range("B2").Locked = False

For i = 3 To Application.CountA(Setting_Sh.Range("A:A"))

If i = 3 Then
WCSh.Range("B2").Value = Setting_Sh.Range("A" & i).Value
Else
WCSh.Range("B2").Value = WCSh.Range("B2").Value & Setting_Sh.Range("H8").Value & Setting_Sh.Range("A" & i).Value
End If

Next i

• WCSh.Cells.Delete clears the Word Cloud sheet completely, so every run starts fresh.
• The loop runs from row 3 to Application.CountA(Setting_Sh.Range("A:A")). Because rows 1 and 2 hold the two headers, that count equals the last data row.
• The first word goes into B2 as it is. Every next word is added to the end with the separator from H8 in between, so B2 ends up as Afghanistan,  Albania,  Algeria and so on.

Step 7: Format each word with Characters

For i = 3 To Application.CountA(Setting_Sh.Range("A:A"))

length_ = Len(Setting_Sh.Range("A" & i).Value)
start_ = Application.WorksheetFunction.Search(Setting_Sh.Range("A" & i).Value, WCSh.Range("B2").Value)

With WCSh.Range("B2").Characters(start_, length_).Font

.ColorIndex = IIf(Setting_Sh.Range("H9").Value = "Yes", Application.WorksheetFunction.RandBetween(3, 56), vbBlack)

.Bold = True

.Size = Min_Font_Size + Setting_Sh.Range("B" & i).Value * ((Max_Font_Size - Min_Font_Size) / Max_Value)

If Setting_Sh.Range("H10").Value = "Yes" Then
.Name = Setting_Sh.Range("P" & Application.WorksheetFunction.RandBetween(2, Application.CountA(Setting_Sh.Range("P:P"))))
End If
End With
Next i

How the code works

• length_ is the length of the word and start_ is its position inside B2, found with the worksheet function Search.
• Characters(start_, length_).Font lets you format just part of a cell’s text. That is the trick behind the whole word cloud.
• ColorIndex uses IIf: if H9 is Yes, it picks a random colour index from 3 to 56; otherwise the word is black. Index 1 is black and 2 is white, which is why the random range starts at 3. The colour index table is in PK’s VBA cell formatting lesson.
• .Size is the Step 4 formula in VBA: minimum size plus value times (maximum minus minimum) divided by the largest value.
• If H10 is Yes, .Name picks a random font from P2 down to the last font in column P.

Step 8: Format the cell and assign the button

With WCSh.Range("B2")
.EntireRow.AutoFit
.WrapText = True
.EntireColumn.ColumnWidth = 130
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlDouble
End With

WCSh.Activate
WCSh.Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

The row height is auto-fitted, the text wraps inside a 130-wide column, it is centred, and the cell gets a double border. Finally the Word Cloud sheet is activated and gridlines are hidden. Back on the Setting sheet, add a shape, type Create Word Cloud on it and use Assign Macro to link it to Create_Word_Cloud. Save the file as .xlsm.

The Complete VBA Code

Option Explicit

Sub Create_Word_Cloud()

Dim Setting_Sh As Worksheet
Set Setting_Sh = ThisWorkbook.Sheets("Setting")

Dim WCSh As Worksheet
Set WCSh = ThisWorkbook.Sheets("Word Cloud")

Dim Min_Font_Size As Integer
Dim Max_Font_Size As Integer

Min_Font_Size = Setting_Sh.Range("H6").Value
Max_Font_Size = Setting_Sh.Range("H7").Value

Dim Max_Value As Long
Max_Value = Application.WorksheetFunction.Max(Setting_Sh.Range("B:B"))

Dim i As Integer

Dim start_ As Integer
Dim length_ As Integer

WCSh.Cells.Delete
WCSh.Range("B2").Locked = False

For i = 3 To Application.CountA(Setting_Sh.Range("A:A"))

If i = 3 Then
WCSh.Range("B2").Value = Setting_Sh.Range("A" & i).Value
Else
WCSh.Range("B2").Value = WCSh.Range("B2").Value & Setting_Sh.Range("H8").Value & Setting_Sh.Range("A" & i).Value
End If

Next i

For i = 3 To Application.CountA(Setting_Sh.Range("A:A"))

length_ = Len(Setting_Sh.Range("A" & i).Value)
start_ = Application.WorksheetFunction.Search(Setting_Sh.Range("A" & i).Value, WCSh.Range("B2").Value)

With WCSh.Range("B2").Characters(start_, length_).Font

.ColorIndex = IIf(Setting_Sh.Range("H9").Value = "Yes", Application.WorksheetFunction.RandBetween(3, 56), vbBlack)

.Bold = True

.Size = Min_Font_Size + Setting_Sh.Range("B" & i).Value * ((Max_Font_Size - Min_Font_Size) / Max_Value)

If Setting_Sh.Range("H10").Value = "Yes" Then
.Name = Setting_Sh.Range("P" & Application.WorksheetFunction.RandBetween(2, Application.CountA(Setting_Sh.Range("P:P"))))
End If
End With
Next i

With WCSh.Range("B2")
.EntireRow.AutoFit
.WrapText = True
.EntireColumn.ColumnWidth = 130
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlDouble
End With

WCSh.Activate
WCSh.Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Fix Words Inside Other Words (Addition)

This section is an addition to the video. Look closely at the country cloud above: in Equatorial Guinea and South Sudan the two parts have different styles. Search returns the first match, so when the loop reaches Guinea it finds the Guinea inside Equatorial Guinea, which comes earlier in the text, and formats that instead. Because the words are joined in a known order, you can track the position yourself instead of searching:

Dim sep As String
sep = Setting_Sh.Range("H8").Value

start_ = 1
For i = 3 To Application.CountA(Setting_Sh.Range("A:A"))

    length_ = Len(Setting_Sh.Range("A" & i).Value)

    With WCSh.Range("B2").Characters(start_, length_).Font
        ' same .ColorIndex, .Bold, .Size and .Name lines as before
    End With

    start_ = start_ + length_ + Len(sep)
Next i

Each word starts right after the previous word plus the separator, so start_ is always exact, even for repeated or overlapping words.

Common Errors and Fixes

Part of a long name has the wrong size or colour

A shorter word was found inside a longer one that comes earlier in the text, such as Guinea inside Equatorial Guinea. Use the position-tracking loop from the section above.

Some words are missing

The loop stops at the count of column A. A blank row in the middle of the list, or extra text below it, shifts that count. Keep the data in one continuous block from row 3.

Run-time error 9: Subscript out of range

The sheet names in your workbook do not match "Setting" and "Word Cloud". Rename the sheets or change the names in the code.

Tips

• Use Multiple Font Name = No for the most readable cloud, as PK suggests at the end of the video.
• Click the button again to get a new random colour mix if a few words come out too light.
• Copy the Word Cloud cell as a picture to use it in PowerPoint presentations.
• Microsoft’s reference for Range.Characters explains how to format part of a cell’s text.

Want It Ready-Made?

The free practice file above has everything you need to learn. If you would rather buy the polished version, the Word Cloud in Excel Using VBA template is on NextGenTemplates.com, and you can browse more Excel VBA tools there.

Related Tutorials

• VBA Cell Formatting: Number Format, Colors, Borders and Fonts
• Text Join Formula for All Excel Versions
• Comparison Info-graphics in Excel
• Info-graphics: Flag Chart in Excel

Frequently Asked Questions

What is a word cloud in Excel?

A word cloud, also called a tag cloud or text cloud, shows a list of words where each word is sized by a value such as sales, population or frequency. Excel has no built-in word cloud chart, so this macro builds one inside a single cell by changing the font size of each word.

How does the macro decide the font size of each word?

It scales each value between the minimum and maximum font size you enter on the Setting sheet. The size is the minimum font size plus the value multiplied by the difference between maximum and minimum size, divided by the largest value. The largest value always gets the maximum font size. Step 4 shows the exact formula.

Can I use my own data?

Yes. Paste your words in column A and their values in column B of the Setting sheet, starting in row 3, with no blank rows. The words can be months, products, countries or keywords. Then click Create Word Cloud.

Why are some words hard to read?

With Multiple Font Color set to Yes, each word gets a random colour index between 3 and 56, and a few of those colours are very light on a white background. Click the button again for new colours, or set Multiple Font Color to No for black text.

Is the word cloud file free?

Yes, the practice file with the Setting sheet, sample data and macro is a free download on this page. A ready-made version is also available on NextGenTemplates.com.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel. New to macros? Start with his free step-by-step VBA course.

Conclusion

A word cloud in Excel using VBA needs only one cell and one idea: join all the words, then format each word with Characters so its font size follows its value. Add random colours and fonts, a double border and a button, and any list of months, products or countries becomes a visual in one click. Download the practice file, paste your own data and build your first word cloud today.