Welcome to Chandoo.org – A short introduction to our site

Welcome to Chandoo.org – A short introduction to our site:

Welcome to Chandoo.org. Thank you so much for taking time to visit us.
Over the last few weeks, we have quite a few new members to the site. Its good time I said hello and introduced this site to you.
PS: If you have been following chandoo.org for a while, you can still find useful information in this post. So read on.

What is Chandoo.org?

At Chandoo.org, our goal is simple. We want you to become awesome in Excel. We emphasize the YOU part, because that is what this is all about. You & making you awesome.

How does Chandoo.org make you awesome?

Simple. We do this using 4 methods.

1. Give awesome tips, tutorials, examples & downloads

3 or 4 times every week, we write about various creative & productive ways in which you can use Excel to become awesome at what you do.
You can get these articles right in to your inbox by joining our free e-mail newsletter. Or you can subscribe to our RSS feed & read the articles in your favorite news reader.
When you join our newsletter, you also get a free e-book with 95 excel tips.
But joining my newsletter or subscribing to RSS feeds can only give you future posts. There is a ton of useful information, tutorials & tips buried in the archives of this blog. You see, we have been writing about excel for almost 5 years now.  Please check out,

Pages for Beginners	for Advanced users	Special Excel uses
» Excel for Beginners – Tutorials » Learn Excel by Topic » Excel Formula Examples » Excel Formula Examples » Excel Charts » Excel Tips » Pivot Tables	» Advanced Excel Skills » Advanced Formulas » Array Formulas » Dynamic & Interactive Charts » Excel & Productivity » Excel Dashboards » Excel VBA	» Project Mgmt. using Excel » Excel Dashboards » Financial Modeling » Statistics & Probability » Simulation » Optimizing Excel » Risk Management

And yes, grab a helmet. Because this stuff is mind-blowing.

2. Conduct awesome training programs

We conduct 5 different Excel training programs, all aimed to improve your skills & make you a hero in your office. To date, we have trained more than 3,000 professionals from all parts of the world and made them awesome in Excel.
All our programs are completely online & you can enroll at any time. You can access the training videos 24×7 and learn at a pace that works for you.
Our training programs at a glance:

Course	What you get?	Know more
Excel School	Step by step training to make you awesome in Excel (and Dashboards). 32 hours of video classes. Clear & easy to understand explanations on all aspects of basic & advanced Excel.	Click here
VBA Classes	Create your VBA code & macros by going thru this well designed VBA course. Learn all day-to-day aspects of VBA with lots of examples, theory. 24 hours of video classes.	Click here
Financial Modeling School	Learn how to make an integrated valuation model using MS Excel. Model cash-flows, profit-loss & balance sheets in spreadsheets. Analyze valuations using scenarios. 20 hours of training.	Click here
Excel for Project Managers	Master the art of project management. Learn how to create gantt charts, project budgets, trackers & status reporting dashboards all using Excel. 6 modules.	Click here
Excel Formula Course	Write better formulas & analyze data. 6 modules on all sorts of everyday formulas. Master advanced formulas like SUMIFS, SUMPRODUCT, INDEX+MATCH, Date formulas, Text formulas.	Click here

3. Sell Excel Tools to make you Awesome

We sell Excel templates for awesome project management & an e-book for learning formulas. These products are crafted with so much passion. More than 2,000 customers have bought these from us and have enhanced their productivity and became heros in front of their bosses & colleagues.

4. Run an Awesome Excel Forum

Almost 3 years ago, we started an Excel forum. It has been growing steadily and now hosts more than 5,000 discussions with 2,000+ active users. Dedicated users like Hui, Luke, SirJB7, Narayank, bobhc, Faseeh contribute regularly and answer questions with passion & kindness. It has become hidden treasure of knowledge, new ideas & learning for many. You too can join our forums & share your knowledge (or ask your questions).
Register on Chandoo.org forums

Ask a question today

Who is behind Chandoo.org?

Although started as a personal website back in 2004, after 8 years, Chandoo.org runs on a small employee force (4) and massive volunteer community.

About Chandoo

My name is Purna Duggirala. Chandoo is my nickname. I have used the same for registering this website in 2004.
After working for a few years as a business analyst with India’s leading IT company, I quit in April 2010 to make this website my full time work. You can read the back story here. Also, you are welcome to read my adventures in entrepreneurship at Startup Desi.
I am happily married to Jo, my college sweetheart and love of life. In September 2009, we became parents to twins – a boy and a girl. Nishanth (boy) & Nakshatra are as naughty, hilarious & lovable as they come. And our life is even more beautiful ever since.
We live in Vizag, a small coastal town in south east part of India. [more...]

People who help me running this site

There are many people who directly and indirectly contribute to our success. I am just mentioning the key people to keep this short.

• Hui: contributes voluntarily to our site as a guest author (60 posts, 1,000+ comments), forum member (3,500+ posts). Lives in Perth, Australia with Eva (wife) and kids.
• Vijay: manages our online VBA classes, contributes occasionally as guest author, forum member. Full time employee of Chandoo.org. Lives in Delhi, India with Anita (wife) and Ashwin (son).
• Sameer: answers student questions on Excel School & VBA classes. Employee of Chandoo.org.
• Ravindra: manages student admissions to our online courses. Helps me with phone and email answering. Full time employee of Chandoo.org. Lives in Ongloe, India.
• Paramdeep: runs our financial modeling courses. Occasionally writes on chandoo.org. Lives in Delhi, India with wife and son.

Learn more about us & what we use to run this site.

How to use this website?

This site is awesome because you are awesome. We learn from each other, share what we know, be respectful to others & have a sense of humor. We love to make mistakes and improve every day.
The following is a best way to use this site and become awesome,

• Join the newsletter or add this site to RSS newsreader.
• Each article has a comments section. Make sure you read the comments and respond / ask any questions related to that topic.
• If you want to explore and learn more, visit archives page and click on a random month. Start reading.
• Play with downloadable excel files. Modify formulas or break the contents to understand how it works.
• Use navigation links at the bottom of each article to see next & previous artciles.
• Have a read of chandoo.org policies
• Check out contact details if you want to get in touch with me.

Searching Chandoo.org

On all pages on this site, you can find a search bar at top-right corner. It has auto-complete. Start typing and you will see suggestions. We have both image & text search, so that you can quickly find what you want. All powered by magicians at Google.

Navigating Chandoo.org

Today, we have more than 1,000 articles, 20,000 comments, 25,000 forum posts and 50,000 active users of our site. All this means, we have massive information. So navigating & making sense becomes a bit difficult.
Worry not, we are working to make it easier for you. Follow the top menu links to quickly access any area of site. You can place pretty much any word next to http://chandoo.org/wp/tag/ and reach the relevant page (example: tag/dashboards, tag/charting, tag/conditional-formatting…). Check out archives to see monthly listing of all articles. Use search to find specific examples or articles you want. If nothing works, post your request on forums or email me (contact details here).

Connecting with Chandoo.org

While we are not as social as Paris Hilton, we do have a sizable presence on latest web fads. Click on below links to connect with us on your favorite social media platform.

• Chandoo.org on Twitter
• Our Facebook Fanpage
• Chandoo.org on Linkedin
• Our YouTube Channel, has over 60 videos
• Our RSS Feed – add this to your Google home page or Flipboard etc.

Once again Welcome to Chandoo.org

Thank you so much for visiting our site. I wish you become awesome in not just Excel, but everything else you do.

Formula Forensics 025. Count Unique Values in a Range

Formula Forensics 025. Count Unique Values in a Range:
This week at the Chandoo.org Forums, Ajinka asked a question about counting unique values in a range.
Faseeh answered with a neat Sumproduct() based formula and quoting a post that Chandoo had written at Chandoo.org answering the question in 2009.
A few people asked how it worked and Luke M gave a good response which I will be plagiarising in part here.
Faseeh’s formula was =SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8))
As always at Formula Forensics you can follow along using a Worked Example which you can download here: Excel 97-2013.

Count Unique Values

Faseeh’s formula was =SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8))
So lets look at how that works
=SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8))
The formula is a Sumproduct() based formula which tells us that the Sumproduct() function is being used to multiply and addup the component arrays. As there is only 1 array component in our formula, Sumproduct simply adds up the values. You can learn more about the Excel Sumproduct function here: Formula Forensics 007
The components of the Sumproduct() function are:
1/COUNTIF(B2:B8,B2:B8)
Lets start with the COUNTIF(B2:B8,B2:B8) part
In a blank cell F12 put =COUNTIF(B2:B8,B2:B8), press F9 instead of Enter

Excel will respond with ={3;1;2;3;3;2;1}

What is Countif() doing ?

The Syntax of Countif() is:


In our example COUNTIF(B2:B8,B2:B8) the Range and the Criteria are the same Range B2:B8

So Countif will Look at the Range (B2:B8) and see what matches the criteria in each cell in the Criteria Range (B2:B8), 1 cell at a time.
Lets look at the first few cells in the Criteria and work through them.
The first cell in the Criteria is B2 which contains “ABC”

We can see that the Range contains the first value in the criteria “ABC”, 3 times

This is the first 3 in the Array shown above ={3;1;2;3;3;2;1}



The second cell in the Criteria is B3 which contains “XYZ”

We can see that the Range contains the second value in the criteria “XYZ”, 1 times

This is the second element in the Array shown above ={3;1;2;3;3;2;1}



The third cell in the Criteria is B4 which contains “HML”

We can see that the Range contains the third value in the criteria “HML”, 2 times

This is the third element in the Array shown above ={3;1;2;3;3;2;1}



Stepping through the range and comparing each value in the criteria results in: ={3;1;2;3;3;2;1}

Reciprocal

The next part of the formula is the

1/COUNTIF(B2:B8,B2:B8)
This takes the reciprocal of our Array {3;1;2;3;3;2;1}
In a Blank cell F14 enter =1/COUNTIF(B2:B8,B2:B8) press F9 not Enter

Excel returns: ={0.333;1;0.5;0.333;0.333;0.5;1}  (I have truncated the 0.33333333333 values to save space)

Which is the same as {1/3; 1/1; 1/2; 1/3; 1/3; 1/2; 1/1}
The Sumproduct() function now steps in and adds up the values of the array returning the answer 4.

Summary

So generically if a value occurs T times in the range, it will occur T times in the criteria.
This will return the value T, T times. The smart bit here is taking the reciprocal of the Count.

So this means it will return the value T, 1/T times.
So ultimately T x (1/T) = 1.
You can see from the above it doesn’t matter how many times a value occurs, every unique value will be seen as 1 and then added up by Sumproduct

Download

You can download a copy of the above file and follow along, Download Here Excel 97-2013.

Formula Forensics “The Series”

This is the 25th or Silver Anniversary Post in the Formula Forensics series and was the first Formula Forensics completely developed in the new Office 2013.

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.

If you have a neat formula that you would like to share as Jong has done above, try putting pen to paper and draft up a Post like above or;

If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

Show only few rows & columns in Excel [Quick tip]

Show only few rows & columns in Excel [Quick tip]:
Each new sheet in MS Excel comes up with a 1,048,576 rows and 16,384 columns. While it has a certain binary romantic ring to it (2^20 rows & 2^14 columns), I am yet to meet anyone using even half the number of rows & columns Excel has to offer.

So why leave all those empty rows & columns hanging in your reports?
Would it not look cool if your reports showed only few rows & columns as needed, like this:

Today, lets learn how to do this.

Showing only few rows & columns in Excel

Step 1: Select the column from which you want to hide.
Step 2: Press CTRL+Shift+Right Arrow to select all the columns till XFD.
Step 3: Right click and hide
Step 4: Select the row from which you want to hide.
Step 5: Press CTRL+Shift+Down Arrow to select all rows until 2^20
Step 6: Hide the rows too. And you are done!
See this demo:
Bonus tips: Learn how to make better Excel sheets

Analyzing 20,000 Comments

Analyzing 20,000 Comments:
On 14th July, evening 4:51 PM (GMT), Chandoo.org received its 20,000th comment. 20,000!

The lucky commenter was Ishav Arora, who chimed, “Like super computers…Excel is a super calculator!!!!” in our recent poll.
It took us 8 years & 15 days since the very first comment to get here. And it took just 1 year 7 months & 23 days to add the last 10,000 comments (we had our 10,000th comment on 21st November, 2010).

Out of curiosity, I wanted to understand more about these 20,000 comments. So I downloaded our comment database, dumped it in Excel and start analyzing.

Understanding the comment growth

Although Chandoo.org has been around since 2004 July, we grew particularly chatty since 2009, when the site started becoming popular. If you look the time from first comment to now & plot total comments by date, this is how it looks. Each 1000 is highlighted (and 5,000s are marked in green).

While it took us more than 5 years to get to 5,000 comment mark, the next 5k came in less than an year. Now a days, we are adding 114 comments every week.
Here is another chart, showing how many days it took us to get each successive thousand comments.

Which months & days of week are popular?

Lets look at monthly trends of comments since 2008.

As you can see, All the months have seen growth since 2008 (and yoy for most months).
And when it comes to weekdays, Thursdays & Fridays are most popular with Chandoo.org commenters.

Who comments on Chandoo.org?

Between Hui & me, we have left 2,650 odd comments on Chandoo.org. The top 10 commenters have left a whopping total of 3,695 comments to date.
Lets look at how many comments are left by first time commenters vs. existing commenters.
Existing commenter is someone who has left a comment earlier with same email id.

As you can see, During first 10,000 comments, existing commenters used to rule. Now a days, about 40% comments are from new commenters.
Do newsletter subscribers comment?
We have more than 36,00 odd people tuned in to our newsletter. I wanted to know how many of them leave comments.

About 45% of comments are from Newsletter commenters. About 5% of our newsletter subscribers (2,055 people) actually comment. The rest are happy to read the newsletter and learn.
That means, on average, each newsletter subscriber adds 5 comments (where as non-subscribers add only 2 comments)
How much % of comments are from Top 10 commenters?
In the early days (for first few thousand comments), Top 10 commenters used to contribute 50% of comments. Now a days, their contribution is at 20%. This is because of the huge number of commenters we are adding every month. As our community grew, we have lots of people who are helping each other.

Top 10 commenters – then & now
Here is how top 10 commenters fared since first 5000 comments. You can see how Hui raised to Top 2 from nowhere & how we lost some of the frequent commenters over time.

Where do the comments go?

In early days, comments are always on the latest articles. So if a post is one month old, it is quiet. But now a days, we are adding more comments on older posts than on new ones. Thanks to Google, people are discovering older content more and asking questions (or thanking us) there.

Which posts attract most comments

Next, lets see which posts are most chatty. But looking at # of comments alone is not enough. So I added % of page views (out of total page views on Chandoo.org between a sample period of APR-JUN 2012) and yearly break-up of comments received since 2008. As you can see, some posts are like blips, they get lots of comments and then become quiet. These are often polls, one time messages (like congratulations, happy new year etc.). The other posts consistently attract a lot of comments because they are visited by hundreds of people every week.
PS: You can click on link to see the actual post.

What do the commenters say?

I have an in house metric to see what the commenters say. It is called as Awesomeness Quotient. It is very simple to measure. I check the comment text to see if any of these words are in it.

Love, awesome, wow, !!, great, incredible, super, fantastic, blowing, perfect, excellent

If so, I give the comment 1 point. Else 0 points.
Then, I add up all these points to see how many points we have over the total number of comments.

As you can see, we have been hovering around 45% awesomeness quotient since inception.
PS: If I had a $ every time, someone said cool, I would have 335 cool ones.

Most frequent words in the comments

The most frequent word in our comments is Excel, used 4,650 times. The next frequent word is thank used 4,554 times. I guess that sums up what commenters say nicely.
Here is a list of
64
59 most frequent words (arranged by frequency and alphabetical order).
Note: Each sparkline has its own axis maximum. You cannot compare frequency of one word with another by looking at their heights.
Note 2: If you want this info with same axis maximum for all, click here.

Comments vs. Posts

Here are 2 tag clouds, one for the content in posts & the other for comments. Can you guess which is which?
[click here for larger version]

The left one is for comments.

Interesting Trivia

• 51% of comments are made with in one week after an article is published.
• We add 4% more in 2nd week, 4% more in next 2 weeks. That is, only 59% of comments are made with in one month of writing an article.
• We get 70% comments between 8AM-8PM (GMT). The busiest hours for commenting are 1PM & 5PM GMT
• Since 1st Jan 2010,
  
  • We had 7 quiet days (days with 0 comments).
  • And on 4 days, we received 100 or more comments
  • We got 17 comments on Christmas & New year days
• The longest comment was 11,274 characters long by Ronald on 2nd June, 2010.
• There are 5 comments with more than 5,000 characters long.
• For every legitimate comment, we get 20 spam comments. So since Jan 2008, our spam filters have blocked 417,104 spam comments.

How these charts are made?

At least 5 cups of coffee, 2 hours of thinking, several hours of SQL, VBA, Pivot & SUMIFS, an hour of formatting & conditional formatting and may be 10 minutes on Wordle.net.
I am unable to share the actual Excel file with you as there is lots of sensitive data (email addresses, IPs etc.) and the file is too heavy – 30 MB at last count.

Do you comment on Chandoo.org?

If you have never left a comment, now is your time. Go ahead and lose your comment virginity. It feels awesome to share your thoughts with rest of us.
And if you are a commenter, well, you have my love & good thoughts. Go ahead and say something more. You know I am all ears to hear what you say.

Go ahead and leave a comment. Next stop, 30k.

Thank you

Thank you so much for taking time to learn from Chandoo.org. Special thanks to 7,278 of you who left a comment on Chandoo.org ever.

How do you explain Excel to a small kid? [poll]

How do you explain Excel to a small kid? [poll]:
When I was in Perth, I visited Hui’s house one day. Lovely, Hui’s daughter (who is about 14) asked Hui how he knew me. Hui told that we both share a passion for Excel and thats how we got to know each other. Then she asked, What is Excel?
At this point, we both tried to explain what Excel is to her in a few ways with no success. Later Hui came up with a brilliant explanation.
He said, Excel has lots of small calculators all interconnected, so that you can do any sort of calculation.
So here is a challenge for you. How would you explain Excel to a small kid (or someone who never heard about it).
Share your answers using comments.
Go ahead and be funny, outrageous, creative or elaborate. Say something.
* click here to comment.