# How to create a Venn diagram in Excel

Excel has a Venn diagram option in its SmartArt but three pretty identical circles doesn’t do it for me. When the sizes of the circles are supposed to mean something, I want them to look the part as much as I can.

So, instead of using SmartArt, I do it manually. I manually create three circles and then make them the right size. What this technique does NOT do is size the crossed parts properly but for me it’s still better than three identical circles. And, it also doesn’t account for perceptions of the size depending on the height of the circle vs the area of the circle.

Step #1: I’ve put my raw data into Excel though it is completely unnecessary. For this technique, the data table can be written solely in your brain. Using the ‘insert shape’ option, choose a circle. If you hold the ‘shift’ key down while you draw the circle, it will turn into a perfect circle.

Step #2: Copy the circle 3 times for each of the 3 circles of the Venn diagram. Now, select one of the circles and then right click. A menu will appear and you can choose the size and properties option. This option let’s you choose the exact size of the circle. First, click on the ‘Lock aspect ratio’ button. Second, in the height box, type in the number that represents the size of the circle. Don’t worry if the circle is way too big or small. Just get the number right. Repeat for each of the three circles.

Step #3: Now you should have 3 circles that represent the 3 sizes in the right proportions. You can now reposition the circles so that they overlap properly. If the circles are way too big or small, then use the + or – options in the bottom right of the screen. The circles will then fit into the screen.

Step #4: Now you can re-colour the circles or make the circle lines transparent. Just right click on the circle you want to revise and choose line and fill until you’ve got it the way you like. You can also make the rows and columns have white lines by clicking on the cross-hatched box and re-colouring them. Now you can treat the object as you might any other in Excel. Myself, I make the screen as large as possible, screen cap it, paste it into the Paint program on my machine, and then save it as a brand new jpg image. You’re done!

# How to create a word cloud with word counts, Excel, and Wordle

When you’ve got the right purpose in mind, word clouds can be very useful. But when you only have a list of words and their counts, particularly when the word counts are large, how do you turn a short list of words into word cloud?

Well, let’s take an easy example and work with this list of words. Because this is an easy list, we could just re-write it into this: Cake, cake, cake, cake, cake, brownies, brownies, brownies, cookies, cookies, pie, pie, pie, tarts, tarts, tarts, tarts, tarts, tarts, tarts, tarts, tarts, squares, squares, squares, squares. That took me about 1 minute to write out.

Cake | 5 |

Brownies | 3 |

Cookies | 2 |

Pie | 3 |

Tarts | 9 |

Squares | 4 |

But what happens when your list looks like this? Are you really supposed to write out each word thousands of times just so they can be copied into Wordle? And what happens if there are several hundred words in your list and they all have hundreds or thousands of mentions? It could take an hour to do to accurately and, as you’ll soon find out, is a complete waste of time.

Cake | 4522 |

Brownies | 3492 |

Cookies | 2431 |

Pie | 3142 |

Tarts | 878 |

Squares | 3627 |

Have no fear! A quick little Excel trick is in order. Have a peek at the picture here and notice the equation. This handy little equation tells Excel to choose the word in column A and then repeat it by the number in column B. The concatenate portion inserts a space between each word which is important for Wordle to distinguish between each word.

Now all you need to do is copy the contents of column D into Wordle.

And then click on Go! Now you can try it with a really long list of words and it will just take a couple minutes. Enjoy!

###### Related articles

- The Bakery Review: Phipps Bakery Cafe (lovestats.wordpress.com)
- The Bakery Review: My Market Bakery (lovestats.wordpress.com)
- How to create teeny tiny barcharts in Excel #MRX (lovestats.wordpress.com)
- How to fake a two directional bar chart in Excel (lovestats.wordpress.com)

# 3 ways to calculate the mean in Excel

This is one of my favourite interview questions: Please calculate the mean of the ten numbers on this excel sheet.

This simple question first lets me find out if they know what I mean by the word mean. Some people think they’re being smart and ask me whether I want the mean, median, or mode. Smart, but not smart enough. Other people need me to tell them that mean refers to average. But that’s another story.

This first option is the one that most people are familiar with. If you look to the top left hand portion of the screen, there is a little box that is either blank or says “SEARCH.” If you click on the black triangle beside this box, a list of options will pop open and you can flip through them one by one. In the list, you’ll eventually find the one that says average. Then all you need to do is follow the steps that Excel lays out for you. This is a great option to use if you’re trying to find a brand new feature when you don’t even know if it exists. It’s also a great way to learn what options are available so that you can use them later on.

The second option is known by even fewer people. Instead of going to the blank box and searching for the feature you want, simply press the = key and type the name of the function you want. Even if you’re one of the folks who uses the first option to calculate the mean, you probably already know that the function is called average. So just type it. You’ll probably also know the names of functions like “sum” or “stdev” so why search through a list of a hundred options when you already know the one you want to use.

And last but not least is the feature that the fewest people know about. In the bottom right hand section of your screen in the status bar are some fancy little features. By simply highlighting the numbers you’re interested in, you’ll see various attributes of those numbers appear in the menu bar. Yup, that’s the mean you see there. If you need to know very quickly what the mean of your numbers is, just highlight and look and you’re done. Very cool!

###### Related articles

- How to fake a two directional bar chart in Excel (lovestats.wordpress.com)
- How to create an Excel bar and line chart in one (lovestats.wordpress.com)