In my opinion, there's a long answer to this question, and there's a short one. The short one goes something like this:
Most of the exposures (of usernames and passwords) we see are not targeted attacks against an individual, but they can result in an individual's credentials being exposed. A password manager helps to limit the impact on a single user by allowing them to use different passwords across all websites they access, while minimising the risk that they will forget them.
From my experience, people who oppose the use of a password manager are afraid of a targeted attack against themselves, rather than the opportunistic attacks I described - but they are correct: if someone gets access to their password repository, it's game over.
Now, there's a "right" and a "wrong" way to use password managers. Here are some tips:
- Make sure you do your research before choosing which password manager you want to use: you want to ensure that you know where your data is being stored and how it is being encrypted. You also want to make sure you're using a reputable product.
- Make sure your master password is sufficently complex. Whether you choose to use a secure passphrase or a complex password, you want to make sure it is strong enough to protect your database. If somebody guesses your password (or manages to crack it), all of your passwords are exposed.
- Never access your password database from a public computer. And, if you have to, change your master password (from a private computer) afterwards. Public computers may have keyloggers, and all kinds of other fun things which may expose your master password. Even worse, if your database is an offline database (e.g. KeePass) you're not actually deleting it from the disk when you send it to the recycle bin.
- Make sure that your private computer has adequate anti-virus/malware protection. Same logic as number three: you don't want to give people access to your master password under any circumstances.
- Use multi-step authentication whenever possible. Most popular email providers offer this now, and it's a great way to minimise the likelihood of your account being accessed, even if someone does discover your 128 character password.
Lastly, my personal preference is to avoid any "public" online password managers (e.g. LastPass). I don't mind password databases being online (e.g. putting your KeePass database in Dropbox - not that I do this), because this would take a targeted attack for somebody to find your passwords. But, I'm sure attackers would love to get their hands on the database of one of these service providers - for no other reason than to say that they did. From there, is just a matter of an opportunistic individual getting lucky, and all your passwords become theirs.