A Turing machine takes a single integer as input, and calculates the result of an arithmetic pseudo-function on that input. Which doesn't sound particularly exciting, but that roughly defines "problems we can solve with a computer, given sufficient storage space and running time". So anything that *isn't* Turing complete will be unable to solve some of these problems, while a theoretical machine that was *stronger* than Turing complete could solve problems that no computer today can, which would have some amazing implications in the field of Logic.
One of the biggest aspects of computer science and Turing machines is the Halting Problem - every Turing machine takes an input, and then either halts running to provide an output, or never stops running. Is there a computable algorithm with which, for any given Turing machine, we could determine beforehand whether a particular input will cause it to halt or run forever? The answer is no, because we can encode such an algorithm in a Turing machine itself, and then we can feed that machine a *copy* of itself, but rigged to provide a paradoxical inability to determine its own halting status (vast oversimplification, read up on it elsewhere if you want to learn the gory details).
So what? Well, first it generates a class of uncomputable numbers - numbers whose derivation is such that they can never be computed. Secondly, if we assume the Church-Turing Thesis, we can use a similar argument to prove Godel's First Incompleteness Theorem, which states that in a mathematical system sophisticated enough to contain all of arithmetic, there are statements such that either (a) they are true but you can never prove them to be so in that system, or (b) they are true, but so is their negative, and hence the system is broken and you can't actually do anything useful with it.
The thing is, if we had a "Super-Turing-Complete" machine, particularly one capable of performing an infinite number of steps in finite time, we could essentially prove a whole bunch of things that we suspect are true but are currently unable to prove.
Also, it's cool to prove that something's Turing Complete because it means you can use it to do arithmetic (which includes things like proving something involving induction), usually against all expectations.