Examples of famous 'workhorse' theorems I use the term 'workhorse' to describe a theorem which is technically challenging to prove, perhaps very deep, but the statement is either uninteresting at first glance or too imposing to be understood by non-experts. I will give some examples of what I feel are 'workhorse' theorems:
1) Bombieri-Vinogradov theorem. This theorem, which is believed (according to Jean-marie De Koninck and Florian Luca's book) to be the reason for Bombieri's Fields Medal in 1974, asserts basically that the Generalized Riemann Hypothesis is true 'on average' over an impressive range of primes. The exact statement, however, is likely quite obtuse to non-number theorists. That said, Bombieri-Vinogradov has an impressive list of consequences, including the most recent results on bounded gaps between primes (due to Maynard).
2) Heath-Brown's 'Theorem 14'. Proved by Heath-Brown in his 2002 paper "The density of rational points on curves and surfaces", this theorem generalized the Bombieri-Pila determinant method to the $p$-adic setting. Its statement is long and difficult to understand at first glance, but it has enormous consequences including (ultimately shown by Salberger) the so-called dimension growth conjecture. It also provided uniform estimates for curves (the best result on this is a preprint due to Miguel Walsh Edit: I just found out that Walsh's paper has now appeared in print and can be found here: http://imrn.oxfordjournals.org/content/early/2014/06/29/imrn.rnu103.refs) and surfaces. It has consequences for concrete diophantine problems, including power-free values of polynomials and power-free values of $f(p)$ where $p$ ranges over the primes only (previous error bounds only provided $\log$ power savings, which are insufficient for this case).
So roughly speaking a workhorse theorem is one where the statement of the theorem is not intuitive nor easy to understand, its proof is difficult and perhaps not very enlightening, but nonetheless it has extraordinary consequences and can be used to prove results which are much easier to understand or seemingly unrelated.
 A: I think it is reasonable to view the h-cobordism theorem (and its relative, the s-cobordism theorem) as a workhorse of differential topology.  The statement isn't actually that hard - it simply gives a natural condition under which a cobordism between two high dimensional manifolds is homotopically trivial - but the proof is quite difficult (it implies the high dimensional Poincare conjecture and contributed greatly to Smale's Fields medal).  And the theorem is at the very heart of the surgery exact sequence, one of the most important and powerful tools in high dimensional topology.
A: The Blakers-Massey excision theorem in algebraic topology. In its classical formulation it says that a certain map of pairs induces an isomorphism in relative homotopy groups in a certain range of dimensions. But it underlies a great many of the most important results in the subject, because it allows you to apply target-type techniques to domains and vice versa. 
A: The theorem of Hörmander on $L^2$ estimates for the solution of the $\bar \partial$ operator and its variants.
At first glance the role of the plurisubharmonic weights involved in the estimates is not clear and the proof given Hörmander's book using Hilbert space techniques is not very enlightening. However the Theorem (and its variants) has a wide range of applications, from complex analysis and geometry (for instance the embedding theorem for Stein manifolds and Skoda's results on the local algebra of holomorphic functions), to number theory (Bombieri's Theorem on algebraic values of meromorphic maps).
A very nice short historical overview of the Theorem can be found here.
A: I myself would place Kodaira's vanishing theorem high on such a list, at least to algebraic geometers.
A: Resolution of Singularities.  Nowadays, 'simple' proofs are available.  The theorem is a huge work horse.
A: In homotopy theory, a typical workhorse theorem is of the form 'there is a model structure on XY with such an such properties'. While there are some standard techniques producing them, most examples need some extra effort. Moreover, even the very definition of a model structure will appear obscure to non-experts and the existence of one does not appear to very interesting in itself. 
To be a bit more concrete: One of the most often used examples of a model structure is the Kan-Quillen model structure on simplicial sets, for which there is still no entirely easy proof. It is the basis of any modern treatment of the homotopy theory of simplicial sets. This model structure is also the basis of countless other model structures (like Cauchy-Schwarz produces countless other estimates). Or the various model structures on categories of spectra (S-modules, symmetric spectra, orthogonal spectra), which form the basis of modern stable homotopy theory (unless you want to use $\infty$-categories, where one often uses other work-horse theorems like straightening-unstraightening!). 
A: The Mayer-Vietoris long exact sequence in cohomology for a pair of spaces, and the Leray-Serre spectral sequence for cohomology of a fiber bundle (there are some other spectral sequences one could mention here). They are the key tools to compute cohomology of various spaces, have had huge amount of applications in concrete situations. Constructions of spectral sequences are particularly technical.
A: This was already mentioned, but the Yoneda lemma (and its dual, as well as extensions to enriched and higher categories) is perhaps one of the most important theorems of category theory. There are proofs of serious theorems which can be boiled down to repeated different uses of Yoneda [1]. It could probably be seen as the categorical analogue of Cauchy-Schwartz.
Lawvere refers to the lemma as the Cayley-Dedekind-Grothendieck-Yoneda Lemma, giving an idea of the scope of its uses and users. The question What is Yoneda's Lemma a generalization of? and its answers give an idea of results that follow from Yoneda.
[1] Urs Schreiber could no doubt recall some, I remember him emphasising this fact once.
A: Concentration of measure results might often count. I am thinking of Talagrand's Inequality, the Lovasz Local Lemma, ....
I currently think of, say, Chernoff bounds as interesting in their own right, intuitive, and relatively straightforward to prove, but as a beginning student Chernoff bounds seemed to me just as described in the post (extremely useful workhorses without much interest or clarity of their own). Now more "advanced" measure concentration results appear as workhorses to me....
A: As others have noted, this sort of thing is commonplace in analysis.  The best results often flow directly from the strongest available estimates, and the strongest estimates are often complicated and inaccessible to the non-expert.
In more algebraic areas, you may want to look for famous results that people call "lemmas" rather than "theorems."  People tend to call a result a "lemma" if it doesn't look like something you'd be interested in for its own sake, but is nevertheless useful for proving other things of interest.  Now, if you are only interested in results that are deep or difficult, then not all lemmas will qualify, since some are very simple (Schur's lemma, Zorn's lemma, Yoneda's lemma) and others are non-trivial but not too difficult (Nakayama's lemma, Hensel's lemma, Sperner's lemma).  However, there do exist "high-powered" examples such as the fundamental lemma or the Szemerédi regularity lemma.
A: Two workhorses of geometric measure theory are the covering theorems by Vitali and Besicovitch. When I first encoutered them they both seemed quite involved to state and their use was not obvious. Also they have quite lengthy proofs. But once you seem them in action ( e.g. proving Lebesgue's differentiation theorem) you learn to appreciate them.
A: All integral inequalities: Cauchy-Schwarz, Hölder, Poincaré, Sobolev, Minkowski...
At first sight, most of these seem quite pointless and unnatural. It's not easy for a beginner to see why controlling a function by its derivatives or a small gain in integrability can be important. But much of PDE theory relies on these little inequalities.
A: I think that two good examples are the Cartan-Kähler Existence Theorem and the Cartan-Kuranishi Prolongation Theorem.  
The first theorem gives sufficient (but quite subtle) conditions for a system of (real-analytic) PDE (possibly overdetermined or with degenerate symbol) to be locally solvable and describes the 'generality' of the generic real-analytic solution.  Its simplest case (which is still not trivial to prove) is the Cauchy-Kowalewskaya Theorem.
The second theorem says that, under certain technical conditions that are not easy to state without a lot of preparation and definitions (but that are almost always satisfied in practice), a specific algorithm for replacing a given system of real-analytic PDE by an equivalent system of PDE (i.e., one that has the same real-analytic solutions) will, after a finite number of applications, yield either a system that satisfies the sufficient conditions of the Cartan-Kähler Theorem or a system that is formally incompatible (and hence has no real-analytic solutions).  
Cartan used the first theorem many times in his work to derive some rather surprising results, and it continues to yield surprising and interesting results today in differential geometry and PDE, many very far from anything suggested by the statement of the theorem.  Cartan assumed the second theorem was true (it was not until after Cartan's death that Kuranishi actually proved it) and didn't exactly use it so much as rely on its truth to motivate many of his calculations.  (The second theorem has, since then, been used directly in various classification theorems, etc.)
A: All the many theorem variants establishing lower bounds for linear forms in logarithms of algebraic numbers, à la Baker.
The statements look simple only when phrased as "Then there is an effectively computable constant such that…": fully explicit forms are needed when you really want to sit down, tools in hand, and solve individual diophantine equations.
The proofs involve an awful lot of technical bookkeeping, although they ultimately boil down to the miraculous and elementary fact that there is no rational integer strictly between $0$ and $1$.
Catalan's conjecture - now Mihăilescu's theorem - is one easily stated application. (To be sure, here a Baker-type estimate did just one key part of the work. Many more ingredients were needed for the complete solution.)
A: I don't know if technically challenging can be interpreted as finding the right trick (which can be quite difficult) to prove it. If so, then the Hahn-Banach theorem should be included. While the real version has a natural proof, the complex version involves a trick that is not obvious. According to what I remember (probably apocryphal), it took Banach two years to obtain the complex version after he had proved the real version. 
And of course, H-B is used all the time in functional analysis.
A: I thought of the Jacobson density theorem in ring theory. At least for someone who does not do ring theory day for day, the way it is usually stated provokes a feeling of "M-mh. So what?" to which the ring theorist might reply that it implies the Artin-Wedderburn theorem and a (maybe unexpected) description of primitive rings. Whether its proofs are "technically challenging" is a matter of perspective; although the proofs I know are kind of easy to follow, I would not think that I could have come up with one (or with the theorem's statement, for that matter).
