I'm a bit confused by the quoted wikipedia entry, because the category of reduced rings also has coproducts (take the tensor product, and then pass to the quotient by the nilradical), and hence the category of reduced schemes, the category of varieties over a field, and so on, all admit fibre products. [Added: See Jim's Borgers series of comments below for a discussion of why, nevertheless, there may be a purely categorical description of the sense in which constructions in the category of reduced rings can be "wrong", while constructions in the category of all rings are the right ones.]

So the answer to the question of why we need nilpotents is not that it is necessary for the existence of fibre products.

Grothendieck introduced nilpotents for many reasons, a number of which are discussed in the other answers: to get correct counting in degenerate situations, it is typically necessary to allow nilpotents; they are also the bedrock of deformation theory and other applications of analytic ideas in algebraic geometry.

It might be helpful to recall another motivation, which forms a significant part of Grothendieck's overall strategy for studying algebraic geometry: Suppose that we want to prove a property about a morphism $f: X \to S$. A typical approach is to first show that it
is a local property, in some sense, so that we can reduce to the case when Spec $\mathcal O_{S,s}$
for some point $s \in S$, and hence assume that $S$ is local; and then to use a flat descent argument to pass from $\mathcal O_{S,s}$ to its completion, and thus assume that $S$ is the Spec of a complete local ring. We then write this complete local ring as the projective limit of the quotients by its maximal ideals, and so reduce to the case when $S$ is the Spec of an Artinian local ring. Since such a Spec has a single point, we can then hope to reduce to checking our property on the fibre over this one point, which reduces us to the case when $S$ is the Spec of a field.

This is a powerful method, which absolutely requires us to be able to do geometry over an Artinian ring (and hence requires us to allow nilpotents). It comes up in lots of places, e.g. in establishing basic properties of abelian schemes, by reducing to the abelian variety case. See the anwers to this question for some examples.

howthey generalize varieties outside of the very formal passage from polynomials rings to general rings and a vague statement about generic points. It's not clear to me how much detail or motivation an encyclopedia should carry, but along with "generalized points," nilpotents are one of the hallmarks of the passage from varieties to schemes, and certainly deserve mention. $\endgroup$ – Ramsey Feb 12 '11 at 23:24