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

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:

[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.

• Click here to purchase
  
• (alternative payment link).

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.