One good chip to overclock is the Intel Pentium D 805. Its relatively cheap, and you can push it up to 4Ghz (from 2.6Ghz) without requiring expensive hardware to make it stable, like water coolers or anything
Generally, you can overclock by either increasing the Front Side Bus frequency, the Multiplier, or both. The product of the FSB and the Multiplier will give you the clock cycle of the CPU, which is nowadays in GHz. If the FSB of a processor is rated as 266MHz, and the multiplier is 10x, then your total clock speed would be 2.66Ghz. If you change the FSB to 300Mhz, multipy it by 10x, you just overclocked it to 3Ghz

You can change these settings in the BIOS as mentioned in this thread before, as well as using software than you can run in the OS. Of course, overclocking is an art.. If you go too far with overclocking, you will make the CPU unstable and overheat, causing it to crash even before it finishes booting up. Thus you need to find a good balance and a lot of trial and error.
The enthusiastic overclockers usually invest ALOT of money in cooling equipment, as well as high performance RAM modules, highly rated Power Supply Units and special motherboards which can supply high voltages to the CPU, so you can get an overall performance gains. Overclockers use benchmarking software to see whether the performance gain is worth the extra energy needed. There also a sense of satisfaction that you have a very powerful computer that did not cost you a lot
Xero highlighted that its usually meant for gamers, cause they want to get that extra frame rate to get better performance in games. Plus, you can also overclock your graphics cards too! Nvidia has some good cards for overclocking

But, since there are advanced CPU's these days, most mainstream users don't need to overclock their computers. They are powerful enough to run the usual applications pretty quick!
One more thing to add, sometimes a manufactuer fabricates two chips of the same architecture and performance, but disables some functions of one chip so it be slightly less powerful. They do this so they can sell two different chips for the budget market as well as the high end market using just one type of chip. One method of overclocking the less powerful chip is to find the pins/data lines that the manufacturer had disabled, and fix it using soldering or similar methods. Its possible to get near to the same performance as the high end powerful chip. Of course, this comes at a high risk of damaging the CPU or even the whole computer permanently if you don't know what you are doing. Plus, any overclocking work done on the computer can invalidate your warranty!
Jerry, theres a program called CPUZ (
http://www.cpuid.com/cpuz.php) that can identify the type of PIII processor you have. When you find the model name, you can compare it to the original processor datasheet from the Intel website to see if your CPU has been overclocked

Of course, you can always make the BIOS default to see if the FSB or multiplier has changed.