How to Create Word Clouds from Your Contact Reports Using Excel

​In our February Water Cooler Chat, we were asked if it were possible to do a word cloud from contact reports using Excel because it’s a tool that is familiar and used often.
Although it is possible, the process is clunky. Since Excel wasn’t built to do word clouds, the process does require the use of a macro.
Typically when the Staupell team does word clouds, our tool of choice is Tableau.
If you’re adventurous and want to dive into using Excel for creating your Word Cloud, here are the steps. Watch this video for detailed instructions.

​Summary of Steps:

• Upload your contact report into an Excel sheet.
• Highlight the column containing your free text data.
• Select Data, then Text to Columns.
  • Choose file type Delimited.
  • Choose Space as your delimiter.
• Create an empty sheet.
• Paste the contents of the individual columns from your first worksheet into Column A of your new worksheet. (Clunky process, but hang with us. Refer to video time stamp 1:09-4:51)
• Sort Column A of all your words to get all the same words grouped together.
• Remove common words that aren’t necessary to track. (i.e. remove works like “a,” “an,” “and,” “the,” etc. Refer to video time stamp 5:49)
• Take the word list you’ve just created and make them into a pivot table.
  • Use the Word and the count of the word
• Sort by largest count to smallest.
• Select the words with the largest numbers (suggest the top 20%).
• Download the macro found on Chandoo. (Refer to video time-stamp 10:00)
• Enable editing.
• Save As Macro-Enable Workbook.
• Update columns with your data.
• Select your contact report words, click Developer on your top menu, then select Macros.
• Select CreateCloud macro and Run.

Use your word cloud in presentations and reports with your fundraising team.