An Excel Dashboard to Visualize 10,007 Comments [Dashboard Tutorial]

Posted on November 24th, 2010 in Charts and Graphs - 27 comments

First some good news,

On 21st November, 2010, our little blog received its 10,000th comment!

Thank you so much for making this happen.

Those of you reading chandoo.org for a while know my penchant for comments. I have learned a lot of excel tips & ideas just by reading the comments you posted on this blog. I think comments are one of the best parts of this blog. So, naturally, I wanted to celebrate this milestone, with something big & awesome.

My intention was to download all the 10,000+ comments and play with the data to come up with something outstanding, like a dashboard. It took me 2 days to conceptualize and create this beauty.

10,007 comments visualized in an Excel Dashboard:

An Excel Dashboard to Visualize 10,007 Comments

[click here for a larger version]

Summary of Findings from the Dashboard:

  1. Out of 10,007 comments, 8766 are comments left by people and 1241 are ping-backs (a comment made automatically by other blogs when they link to chandoo.org).
  2. Roughly 20% of comments are @ replies.
  3. 31 posts had more than 50 comments each. The maximum comments were 313 on the last visible cell poll.
  4. These 10,007 comments came from 2507 unique people. Top 20 commenters made 28% of comments.
  5. Median words per comment are 33. You said a total of 485,000 words so far. Impressive.
  6. There were only 5 days with zero comments in 2010 (as against 66 in 2008 and 15 in 2009).
  7. Fridays are most popular days for commenting with 20% comments coming in.

Learn How Dashboard is Constructed in a Crash Course:

I made this dashboard with lots of love & coffee. Of course, coffee wont magically turn data into in-cell charts. We need to arm twist our data and get the insights out ourselves.

That is why I made an hour long tutorial explaining how I constructed this dashboard. In the video I explain how I came up with the design, what formulas I used to cleanse & process the data, how various charts were constructed, what techniques I have used to put this together.

As this video is kind of advanced training on dashboards, I have decided to sell it. You can get a copy of the video & unlocked excel files for $37.

What you get with the purchase:

  • A HD video explaining the dashboard construction process
  • Same video in iPod compatible format for watching on the go.
  • 2 Excel files, the original version & instructor version (unlocked)

Please note: You will not enjoy the video if you are an Excel beginner. Instead go thru Excel Dashboards page to equip yourself with necessary dashboard & charting skills before getting a training like this.

How is this Dashboard Made?

If you are curious to know which nuts & bolts are used in the dashboard, read up:

  • The chart showing monthly trend of comments and day of week distribution are 2 different charts, one arranged on top of other.
  • The word cloud showing relative frequencies of words used in comments is made using wordle. This is the only non-native Excel part of the dashboard.
  • The Top 10 tables at the bottom are incell charts with some fancy colors.
  • I have used pivot tables, SUMPRODUCT, SUMIFS, INDEX+MATCH, VLOOKUP formulas to process the data.
  • Word counts are generated by processing the comment text using this technique.

Download the Dashboard File:

Click here to download a locked copy of the dashboard [mirror here]. You can examine the dashboard, but you can not alter it as it is password protected.

If you want an unlocked copy, you can get it by purchasing the video tutorial. Click here (or here).

A Big Thank You

A big, warm, cuddly thanks to you for making 10,000 comments. Everyday, your comments teach me new tricks on Excel & make me better at what I do. I am sure you feel the same about others comments.

Special thanks to top commenters – Jon Peltier (228), Hui (186),  Jeff Weir (148), Robert (138), Jimmy (62), Rick Rothstein (60), Martin (58), Daniel Ferry (54), Dan l (53). Also, kudos to Stef@n for leaving a 900 word comment, the longest ever.

How do you like the dashboard?

Do you like the dashboard? Do you find it insightful? What modifications you would have made to it? Go ahead and share your ideas and tips with us. Please leave a comment.

PS: And get a copy of the training video if you work with dashboards a lot. I am sure you can pick up few tricks to become even more awesome.

http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

27 Responses to “An Excel Dashboard to Visualize 10,007 Comments [Dashboard Tutorial]”

  1. Vipul says:

    Congrats on reaching that mark! Comments definitely mean a larger audience participation and that’s what we love about this site.

  2. JP says:

    You’re welcome, keep up the great work.

    (that was #63)

  3. Pedro Wave says:

    Chandoo, you are the top commenter with 1,399 comments so far.
    That makes 1.28 comments by day during 3 years without forget all your awesome posts.
    Well done!

  4. dan l says:

    AWWWWWWWW YEAH THATS DAN L ON THE TOP COMMENTER LIST.

    Must….keep…commenting….to overtake….Dan Ferry….

    Great dashboard though. Things I like:
    -The hot series in two of the top ten charts
    -The MTWTF columns placed on top of a really good line chart
    -The conditional formatting of the labels on the top 10 charts. I have no idea how that works, but I think I want to find out now.

    I’m curious though chandoo:

    Are you going to try to plug this thing directly into your web server so you can update it?

  5. Jorge Camoes says:

    Nice one Chandoo. Congrats on the 10K mark and the dashboard, very clean and well designed. Is it Excel? Who could ever imagine! ;)

  6. Fred says:

    Congrats!! Who has the 10000th comment? :)

    Love your blog and I have learnt so much. There are still a lot to learn but I’m really enjoying this! Keep up the good work!

  7. HAHAHA says:

    Chandoo ,

    DashBoard is not dynamic , a static dashboard is very simple thing to do , it wont take 2 days
    next time try to do something creative rather than just do the same thing again and again.

    Poor execution and the price is too much for such an ordinary DB

  8. Gregory says:

    Nice features in your dashboard. Kudos! And the best part is: you can use it next year. :)

  9. bill says:

    i was very impressed by the “Wordle” word picture. while it is fairly easy to get Excel to do a word cloud with word sizes varying by some metric, it seems to me that it would be much more difficult to program the words at different angles and sizes while, at the same time, not overlapping the words. you could possibly do this with an xy-plot. but, getting the words to fit together without overlapping would be a neat programming trick. this subject would be very worthy of a “how to” blog. -bill

  10. dan l says:

    Hahahah,

    gfy, ‘bokay?

  11. Rohit1409 says:

    Great Going Chandoo :)

    Cheers !!!

  12. Chandoo says:

    @All.. thank you :)

    @Dan l: Good idea about making this dashboard live. Let me give it a thought. WordPress database is quite flexible. With some javascript api magic this should be possible.

    @Jorge: Thank you. Excel 2007 gives a lot of freedom when you are designing charts and putting together dashboards.

    @Fred: Rahul has the 10,000th comment – here: http://chandoo.org/wp/2010/11/10/vlookup-second-value/#comment-142747

    @HAHAHA: I see no reason why this dashboard has to be dynamic. Also, I am not as skilled as you are, so I take 2 days to imagine and create something like this. And the price is not for the dashboard, it is for the lesson explaining how to make one like this.

    If you feel adventurous, go ahead and download the free version. The data is in there, make a dashboard and share it with us.

    @Bill: I have written about excel word clouds a while ago – http://chandoo.org/wp/2008/04/22/create-cool-tag-clouds-in-excel-using-vba/

    Wordle uses some really advanced algorithms to layout the tag clouds without overlapping content. While it would be challenging to try the problem ourselves, I am sure it will take me ages to write something as elegant as wordle. So I just used theirs.. :)

  13. HAHAHA says:

    Chandoo ,

    The $37 price tag is too much for the lessons.

  14. Chandoo says:

    @Hahaha: My assumptions are very simple. This lesson will help dashboard makers quite a bit. I am guessing they can easily save 4-6 hours on research and learning work by watching this video to pick up important tricks. Assuming an hourly salary of $35 (that would be low-end for someone in BI/ Dashboard roles), this training immediately pays off.

    If you find the lesson to be pricey, I can totally understand this. Why dont you drop me an email and I will give you some discount?

  15. Hui... says:

    @Chandoo

    There is a very good Semi Automatic Excel Tag Cloud by MrExcel (Bill Jelen) pod cast here: http://www.howtodothings.com/video/how-to-make-a-pivot-tag-cloud-on-excel

  16. Tom says:

    Chandoo,

    First off, congrats on the 10,000th post. Second, I really like your dashboard. Very professional looking, smartly organized, and the content is relevant. And, as you stated earlier, it didn’t need to be dynamic. Also, I’m impressed with you executing this in two days. Speaking from experience, it’s not as easy as it looks (the conceptualization process makes the execution seem easy!). Thanks again for everything.

    Tom

  17. Chandoo says:

    @Hui.. very interesting technique. I really loved it.

    @Tom: Thank you so much. I am very happy you like it. Many people think the difficult part of making dashboards / models is in writing formulas or making charts. In my humble opinion, the hard work is in coming up with a good layout and cleaning data. Once you do that, making charts and writing formulas is a breeze.

  18. Martin says:

    Wow !!! I’ve made it !!! I’m on a top 10 list !!! and the best part of it… it’s on an Excel blog, and one of the best, I must say !!!!

    Congratulations, my friend !!

    …and the hits will keep oooon comin’ !!!!! :)

  19. xsaed says:

    Dear Chandoo,
    “The Top 10 tables at the bottom are incell charts with some fancy colors. ”
    The colors in the in-cell chart is it conditionally done (automatically) or u just color it manually?.. i found out that the number label can be conditionally formatted but how about the characters in the incell chart?

  20. Michael B. says:

    Chandoo,

    Very impressive and elegant dashboard. I’m specifically looking at your use of the wordle tag cloud. Do you happen to know if there are any copyright issues with embedding a wordle into a dashboard I’m trying to create for a company? I also was curious if there is any chance there is a student priced edition of your workbook. I’d love to learn more of the details but unsure if my research will pay for this. Thanks again and congratulations on an awesome site you truly are a master of VBA. -Mike

  21. […] An Excel Dashboard to Visualize 10,007 Comments [Dashboard Tutorial] | Chandoo.org – Lear… – […]

  22. […] You can do damn near anything in Excel.  Calendar chart visualizations.  Music videos.  Beautiful art.  More music videos.  Respiration wavelengths.  Chess games.  Word clouds. […]

  23. Shamika Schumaker says:

    Great! :)

Leave a Reply