Tap water's bad press is mostly about three issues: the way it looks, the way it tastes and what's in it. The first two are matters of preference; the third is about safety. Many people who filter their tap water are concerned about contamination.
Is it warranted? Not usually, unless the drinker has a severely compromised immune system. In the United States, municipal tap water systems have to meet safety standards, and they're regularly inspected by the government. The water is heavily treated to remove particles, chemicals, bacteria and other contaminants. Typically, chlorine and fluoride are added for disinfection and dental-health benefits, respectively.
Of course, no system is perfect, and on rare occasions contaminants such as pesticides (like atrazine), pathogens (like E. coli, cryptosporidium and giardia), and fuel byproducts (like MTBE and perchlorate) have been found in municipal water supplies. Well water is more likely than city water to be contaminated, because it doesn't go through the same regulated treatment and testing. And any tap-water supply runs the risk of tasting, smelling or looking bad, simply because of added chlorine or region-specific, innocuous particles that are not specifically targeted in the treatment process.
In the rare case of contamination, the less-rare case of baseless fear of contamination, or the somewhat common case of aesthetic shortcomings, there are two typical solutions: bottled water or filtered water.
Bottled water is a controversial option. It has come out in the last decade that some bottled water brands are selling $2 liters that are no more pure or "mountain spring"-like than tap water (see How Bottled Water Works). And for most people with the slightest green-leanings, bottled water's eternal landfill presence and high-carbon-footprint container makes it a guilty pleasure, at best; at worst, a vicious, absurdly overpriced and possibly fraudulent assault on the planet.
Which leaves filtered water. With some smart implementation, it can be a pretty reasonable approach to cleaning up the tap.