Typical computer users, even today, seem to think that some "clever" substitution of letters (e.g., replacing the letter "o" with the digit "0") or a keyboard pattern (e.g., "qwerty") are sufficient to make their passwords hard to guess. In fact, there are extremely few, if any, tricks of this nature that haven't been thought of by those who seek to break passwords. A safer bet is to assume that any clever scheme that you can imagine has already been thought of by hundreds, if not thousands, of people.
Passwords are vulnerable for two basic reasons: 1) they are often passed over networks in clear text, and 2) given adequate resources (knowledge, tools, and compute power), they can be guessed or broken.
Even if your password is a "good password" according to the most stringent security guidelines, it may still be vulnerable. Any Unix user with the root password to the box on his desk can sniff passwords off the network and many users still exclusively use tools like telnet that transmit passwords in clear text. One of the oldest and simplest hacking tricks in the book is to use a sniffer to grab telnet packets off the network and extract the username and password. You don't even need any special tools, which typically are built into the operating system.
The worst passwords are those that are easy to guess. If you make your password the same as your home address (e.g., 7610Park), anyone who knows your address and knows your habits might guess it. If you're a sports fan or love classical music -- in fact, if you have any obvious interests -- you should be especially careful to avoid using names and words related to these interests as passwords.
Further, what can't be guessed by a knowledgeable human might be guessed by a piece of software. The most mundane form of password breaking is to guess every possible password. This might seem impossibly difficult, but if a single clue exists (for example, knowledge that passwords are whole words and numbers), the field of possibilities may go down tenfold or even a hundredfold. A piece of software capable of making half a million guesses a second might become surprising good.
Dictionary attacks and attacks that try all of the clever tricks that many of us seem to think no one else has thought of, have an even better chance of success. The classic tool for checking passwords, Crack, runs through numerous filters and permutations in trying to break passwords.