Fun With a Word Cloud in Excel

When people think about spreadsheets, they usually think of numbers, formulas and calculations. But spreadsheets are great at organizing words too! Here are a few of the functions you can use with text, and a fun word cloud tool to try.

Fun Word Cloud Tool

It was Word Nerd Day last Sunday (January 9th). Well, I’m a nerd, and I like words (are you playing Wordle?), so I looked for something fun, that combines words and spreadsheets.

My “word search” led me to the Clearly and Simple blog, which is written by Robert Mundigl. Back in February 2015, Robert shared his Excel workbook that lets you create word clouds in Excel, so I downloaded his sample file, and tried it out.

Seven years later, and who knows how many different Excel versions, Robert’s word cloud still works great!

Guess the Springsteen Song

There’s a generic example that you can download, but I got the file that’s filled with Bruce Springsteen lyrics. It sounded like more fun, and easier than inputting my own word list.

The sample file had a light grey background for the word cloud, with Lucinda font in green text, and I found that hard to read. There’s just not enough contrast for my world-weary eyes.

If your eyes are better than mine, can you guess which Springsteen song these words are from?

word cloud in Excel hard to read

Change the Word Cloud Settings

Fortunately, Robert made it easy to change all the word cloud settings, right on the main worksheet.

So I made a few changes to the settings, to see if the word cloud could be easier to read:

reduced the max number of words
chose Impact font, in black
increased both minimum and maximum font sizes

Background Tip:

There was no setting to change the background colour,
so I selected the word cloud shape.
Then, on the Shape Format tab, I used the Shape Fill command, and
selected the white fill colour

word cloud settings in Excel

Create the Word Cloud

After you’ve changed the word cloud settings, it’s easy to create your new word cloud in Excel.

At top of the worksheet, select a song from the drop down list.

I chose the song, Born to Run, and here’s the word cloud for that song.

The word “Born” is in the song’s title, but it’s certainly not prominent in the word cloud! Can you find it?

word cloud for born to run

Word Cloud Word Counts

On another sheet, Robert has a list of the words from each song, with a word count. In that list, I used the filter, and found “born”, way down the list, with a count of 3.

Tip: There’s another sheet a list of omitted words. You can add or remove words in that list, if you’d like to.

word count list for born to run

Check the Word Cloud Again

Based on the information in that word count list, I knew that “born” would be one of the smallest words in the word cloud, so I looked again. Finally, I found “born” at the left side of the chart.

And here’s a fun feature – if you point to a word, a tool tip appears, showing the word and its word count!

There’s more information about the word cloud on Robert’s blog, so check it out, and download one of the sample files.

word cloud with tooltip

Excel TEXTJOIN Function

Eventually, you might need to stop playing with the word cloud, and get back to work in Excel.

If your work involves words, or combining words and numbers, there are Excel functions and formulas that will make the job easier.

Are you using Excel 365 for your work? If so, try the new TEXTJOIN function. It makes it easy to combine the contents of multiple cells.

This short video shows a couple of TEXTJOIN examples, and there are written steps, and more examples, on my Contextures site.

Use TEXTJOIN With Conditions

You can use conditions with the TEXTJOIN function too, as Sarah shows in this video.

Combine Text and Numbers

If your version of Excel doesn’t have TEXTJOIN, you can use the & operator to combine data from two or more cells.

Also, you can use the TEXT function, to format the numbers, so they’re easy to read in the combined text string.

This short video shows how to do that, and there are written steps and more examples on my Contextures site.

_______________________