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?

[Update May 4, 2023] These are all the lyrics from the Born to Run album, not the song!]

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

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?

[Update May 4, 2023] These are all the lyrics from the Born to Run album, not the song!]

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 with a list of omitted words. You can add or remove words in that list, if you’d like to.

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.

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.

A full written transcript follows the video.

Video Transcript

In this video, you’ll see how to combine text and numbers and then format those numbers if necessary, so they show us proper dates or currency or other formats. This is Debra Dalgleish from Contextures.com

Simple Formula

On this worksheet, we have a column with text and a column with numbers.

In each text cell, I’ve ended with a space character. So when I combine the text and the number, there’ll be a space character between them.

I’m going to create a very simple formula.

-Start with an equal sign, then click on the first cell.

-Type an ampersand (shift 7).

-Then click on the number, press Enter.

And it nicely combined the text and the number.

Now if I point to the fill handle, and double click to copy that down, all the others are combined, but there’s no formatting.

Add Formatting

We’re going to start with an equal sign and click on the text cell, then our ampersand.

And I want it to show the number, but formatted.

To format the number, I’m going to use the TEXT function.

It needs a value, which is our number here, then a comma.

Then how do we want this formatted? We’re going to put something inside double quotes. We’re going to give it a text string to use, as the guide for formatting.

I would like to see the full weekday name, and to do that, I’m going to type dddd, another double quote, and close the bracket.

Press Enter, and it says, Today is Wednesday.

Another Date Format

Now in this cell, instead of seeing the weekday name, maybe I’d like to see this day-month format. So I’ll copy this cell down to give us a starting point.

And then in our formula, instead of dddd, I would like d-mmm, press Enter. And there’s the date formatted just the way we have it here

Add the Year

Now, I could copy this down to the next cell, or maybe I’d like to include the year in that one — d-mmm-yyyy

I don’t need it in the next cell. I could have it on another sheet. And I could use that in my text function instead of typing the format each time.

Click on that cell and press Enter.

More Formats

Now in this cell, I’d like currency. So in this cell I’ll type the currency format.

So the number signs mean there may or may not be a thousands or hundreds or tens, but I always want a first number and I want a comma separator.

If I copy this down, it should pick up that format.

And for the final one here, we’d like fractions. Use # ?/? and copy that down. And there’s our hours with fractions.

Get the Workbook

Thanks for watching this video. You can go to my website, contextures.com to get this workbook, and please subscribe to my Contextures YouTube channel, so you can see the latest videos as I post them.

_______________________