XLSTAT: Word clouds in Excel

Background

A  word cloud is a visual representation of the most frequently used keywords or terms in a text. The font size of a keyword in a word cloud is proportional to its frequency.

 

Dataset for Example

The attached data is from the  stateoftheunion.onetwothree website regarding the latest «State of the Union speeches» by Presidents D. Trump and B. Obama whose full texts can be obtained at:

• http://stateoftheunion.onetwothree.net/texts/20160112.html for President Obama.

• http://stateoftheunion.onetwothree.net/texts/20170228.html for President Trump.

 

Setting Up

• Open XLSTAT.

• Click on the Visualizing data menu and select the Word cloud feature.

• The dialog box for the Word cloud appears.

In the General tab:

• The Term labels refer to the column A and the Term ferquencies refers to the column B.

• Enable the Column labels option, since the first row of data contains a header.

• Check the Frequency option so that the size of the words is proportional to their frequency of appearance.

In the Options tab:

• Choose to activate the Max. words option and the Rotation period option. We do so in order to limit the number of words displayed in the cloud (here 260 words maximum) as well as to perform a periodic vertical rotation of a word at a defined period (here we have chosen to orient vertically 1 word every four).

• By default, the Random Position option is disabled to display words in decreasing frequency of appearance from the center to the periphery of the cloud.

• Finally, select the Custom color Scale option and choose the color gradient from column G. This will highlight the most common words with dark red and the least common ones with light red.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

Repeat the same procedure to set up the Trump’s Word cloud with columns D and E.

 

Interpreting the Results

The generated Word clouds are displayed below (red for Obama, blue for Trump), these are standard Excel charts that you can customize afterwards (color, font, ...).

Obama's word cloud covers a wider range of topics (high number of keywords with a high frequency of occurrence). These terms are centered around the keyword world.

On the other hand, Trump's cloud is built around the keywords will, american, and country .