As this tweet asks: what's the validity of the various lists of the most common passwords people choose, such as this one http://www.whatsmypass.com/the-top-500-worst-passwords-of-all-time.
The answer is: it depends. If you dump the passwords at the average website, you'll see these as common passwords.
But they may not reflect passwords chosen for important sites, like corporations or banking. The less important a site, the poorer the passwords. People will choose poor passwords for something like Sony Playstation gaming than they would for their corporate account. This is especially true when your corporate account enforces rules for password complexity and reset.
Thus, just because the password "123456" is incredibly common doesn't mean that fact is useful to hackers trying to get valuable information.
Or, look at it another way. When we pen-testers break in, we usually want to get an "administrator" account rather than a "user" account, so that we can control the system. Administrators choose tougher passwords than users. Thus, just because users choose bad passwords doesn't mean we can crack administrator passwords.
There is another flaw in the statistics. Yes, "123456" might be the most common passwords on the Internet, but what percentage of passwords match that? Do 10% of users choose that password? 1%? 0.1%? Depending on the importance of a website, that number is going to be closer to 0.001% than 1%. In other words, just because you know the most common passwords doesn't mean you'll be likely to guess a persons password before the system locks you out.
What these lists do tell is the psychology behind what people choose as passwords. People choose easy patterns on the keyboard, like "123456" or "qazwsx". People choose their children's names or birthdates. People choose a swearwords. People choose sports teams. People choose words like "dragon" and "monkey". I have no idea why "monkey" is so popular, I just know that it is.
This information can be used in password crackers. Unlikely guessing a person's password on a website, which is one attempt every few seconds, cracking passwords can try billions of combinations per second. But even doing a billion tries per second, a hacker still can't guess an 8 character password in 100 years. Therefore, a hacker has to be smart. Knowing that "monkey" is popular, the hacker will try variations, like "m0nkey" or "monkey1234". Thus, if a hacker gets a database of encrypted passwords (aka. hashes), he or she will be able to guess 20% of those passwords, either by trying the most common passwords, or simple variations of those passwords.
This is also useful to people wanting to know how to choose passwords. Even if your password isn't in the list, it shows you how people choose passwords, and what not to do. For example, maybe your sports team isn't in the list, but when you see the many sports teams that are, you learn that maybe basing your password on a sports team isn't a good idea.