Over at PTS Blog, my dear friend & charting guru, Jon Peltier has an interesting post on using dot plots to visualize Murders & Suicides data by US States. Here is a sample of the charts he recommends for such data.
If you are curious how he made these charts, well, he used dot plot chart utility to create them.
Not that murders & suicides fascinate me, but I wanted to play with this data myself and see how we can visualize it. So I emailed Jon and asked him to share the raw data. Being a lovely chap Jon is, he immediately sent me the data. So here we are, playing with gory data on a Friday.
Suicides & Murders by US States – An Interactive Excel Chart
Here is a demo of the chart I came up with.
Thinking behind this chart
We see this type of data all the time. 2 or more metrics by a dimension like State, Product group, Country, Web Page or Customer. In this case, as you can guess, there is very little co-relation between murders and suicides by state. While we want to visualize them separately, we also want to keep the overall context together.
Since Jon already provided a lot of interesting options, I went with the interactive charts so that you can sort and view the data in any way you want.
How is this chart constructed?
Lets only look at the most important bits of the construction here. It is your homework to breakdown this file and understand how it works.
1. Arranging the data
The data is arranged by states in rows and murders & suicides in columns. I have added few extra columns to calculate murders per suicide and suicides per murder.
2. Sorting the data on-demand
This is the tricky part. I have used COUNTIF formula to sort the list. Learn how to sort a list of values using formulas [More on sorting values].
3. Visualizing the sorted data
I have used in-cell charts for this. Read more about in-cell dot plots and how to develop them in Excel.
4. Other nice stuff that you see in the chart
I used conditional formatting icons to highlight the column & show sort direction. Also, I used conditional formatting top 10 rule to highlight top 5 items in each column.
Download this chart & play with it
Click here to download the Suicides & Murders by US States – Interactive Excel Workbook and play with it. Examine the formulas in “suicides vs. murders” sheet to understand how the workbook is put-together.
How would you have visualized this information?
I am more interested in knowing how you would have visualized this data. Go ahead and share your views & ideas with us using comments. If you feel adventurous, go ahead and make a chart and email me at chandoo.d @ gmail.com. I will be glad to learn from you.
Go ahead and share.
More Excel Charting Tutorials
- Visualizing Survey Results with In-cell Panel Charts
- 50 Best Cities for Finding a Job – Excel Chart
- Understanding Flu Trends with an Interactive Excel Chart
- The Effect of Grammy on Sales of Music Albums – Interactive Excel Chart
- More on Conditional Formatting, In-cell Charting
Note: All the data in this charts is gathered from the sources mentioned here. Thanks to Jon for sharing the data with me.
11 Responses to “Fix Incorrect Percentages with this Paste-Special Trick”
I've just taught yesterday to a colleague of mine how to convert amounts in local currency into another by pasting special the ROE.
great thing to know !!!
Chandoo - this is such a great trick and helps save time. If you don't use this shortcut, you have to take can create a formula where =(ref cell /100), copy that all the way down, covert it to a percentage and then copy/paste values to the original column. This does it all much faster. Nice job!
I was just asking peers yesterday if anyone know if an easy way to do this, I've been editing each cell and adding a % manually vs setting the cell to Percentage for months and just finally reached my wits end. What perfect timing! Thanks, great tip!
If it's just appearance you care about, another alternative is to use this custom number format:
0"%"
By adding the percent sign in quotes, it gets treated as text and won't do what you warned about here: "You can not just format the cells to % format either, excel shows 23 as 2300% then."
Dear Jon S. You are the reason I love the internet. 3 year old comments making my life easier.
Thank you.
Here is a quicker protocol.
Enter 10000% into the extra cell, copy this cell, select the range you need to convert to percentages, and use paste special > divide. Since the Paste > All option is selected, it not only divides by 10000% (i.e. 100), it also applies the % format to the cells being pasted on.
@Martin: That is another very good use of Divide / Multiply operations.
@Tony, @Jody: Thank you 🙂
@Jon S: Good one...
@Jon... now why didnt I think of that.. Excellent
Thank You so much. it is really helped me.
Big help...Thanks
Thanks. That really saved me a lot of time!
Is Show Formulas is turned on in the Formula Ribbon, it will stay in decimal form until that is turned off. Drove me batty for an hour until I just figured it out.