Tag Archives: Microsoft Excel

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.

venn diagram excel

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.venn diagram excel

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.  venn diagram excel

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!venn diagram excel

 

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.

excelwords

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

wordletext

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!

wordle

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.calculate mean in excelcalculate mean in excelcalculate mean in excel

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!

How to create teeny tiny barcharts in Excel #MRX

Prepare to be astounded. This great little chart is perfect for those situations where you normally share an excel sheet instead of creating a ppt or word file.

First, go and download a font called Bargraph. Be sure to install it before you open up Excel.

Now follow these few steps.

  1. Lay out your series of numbers in separate columns as you normally would. Monthly sales data, weekly counts, whatever it may be.
  2. Copy the concatenate equation that you see in the image below to turn the 12 separate numbers into 1 single number in column N.
  3. Change the font of column N to bargraph, as you can see in the font choice menu.
  4. Done!

bar graph font excel

You may find that the numbers you want to use aren’t single digits as is required to create this chart. All you need to do is transform your numbers into single digits. It may be as simple as dividing each number by 600 as I have done below. You could also try using log or other traditional functions. Be sure to round to zero decimal places or truncate the number so that there are no decimal places. Remember, you can only use the numbers between 0 and 9.  And…. you’re done!bar graph font excel

How to fake a two directional bar chart in Excel

Excel has lots of charting options. Line charts, bar charts, scatter plots, pie charts and oh, so much more. But it doesn’t have this chart.

This is a nice chart for showing two values for many different variables. It’s easy to read with the labels listed vertically and it fills a page nicely without being overwhelming.

Here are the tricks of the trade. You can see everything that I’ve done in the image here so it’s easy to follow along.
Two sided bar chart in excel

First, put together the three columns of data – the labels, the first score, the second score. Second, copy out the equation that you see in the Excel menu bar

=REPT(“g”,D5*2)

REPT: Excel will repeat a value a certain number of times

“g”: The value Excel is going to repeat. Notice that the font I’m using in that particular cell is webdings. You might have to use a different font to generate the same result but you’re looking for any character in any font that is a really wide fat box.

D5*2: This is how many times Excel is going to repeat the value. In the case of the highlighted cell, Excel is going to duplicate the g value 4.4 times 2. If you find your bar is too long, instead of multiplying by 2, trying dividing by 2.

Now, align right the first column and align left the second column. To remind your reader of what’s going on, colour in green the column that reflects the good thing and colour in red the column that reflects the bad thing. Well, now that I look at it, perhaps the yum column ought to be in green!

At this point, you can either copy paste the cells directly into powerpoint or word, or create a screencapped image and paste it anywhere. Enjoy!

How to create an Excel bar and line chart in one

I have a habit of creating charts in Excel that include both a bar chart and line chart together. They mystify people as there is no option in Excel do do such a thing. Oh, but indeed there is. Follow along my dear friends and I’ll show you.

First of all, create a bar chart as you normally would. A column for labels, a column for the first set of numbers, and a column for the second set of numbers.Bar Chart

Now the tricky part.

Click inside the chart on one set of data. You’ll see here that I clicked on the blue bars. All the blue bars have the little circles on the corners to indicate that I have indeed selected all of them.

How to create a bar and line chartNow, in the top excel menu, click the option that let’s you choose the design of your charts. In this case, it’s simply called design.

At the very left of my menu, there’s now an option that says change chart type. Click!

This opens up all the chart types and I’m going to click on the line chart. Now click OK.

And you’re done! You should now have a chart with both lines and bars in it.

Bar and Line ChartThis same process will work for any number of bars or lines. You could create a chart with one set of bars and 14 lines, or 3 sets of bars and 1 line. In the end, all that matters is that the chart is readable and doesn’t misrepresent the results.

Enjoy!

 

Other posts

%d bloggers like this: