"Turing complete" is defined as "able to do everything a Turing machine can do, if you give it enough resources". All modern programming languages are either Turing complete or "effectively" (*) Turing complete, simply because it's hard to be a useful programming language without that.
In particular, if you have…
- Some basic means of doing arithmetic, like an "increment" instruction
- Some basic means of storing variables
- Some way of checking the value of a variable, and branching based on it
- Some way to make a loop (i.e. branch/jump backward)
…then your language is most likely Turing-complete. It's been shown that the language SUBLEQ, in which the only instruction is "subtract $a$ from $b$, then branch to $c$ if $d \leq 0$", is Turing-complete. Same with the language Brainfuck, which is about as simple as you can possibly go.
For some other examples, it's been shown that Magic: the Gathering is Turing-complete. Same with Pokémon Yellow, the Sendmail configuration file, Internet routers, and PowerPoint presentations.
And Turing proved that for anything Turing-complete, there are certain problems that can never be solved. The Halting Problem is one of these. That means that, for example, it's possible to get into a situation in a Magic: the Gathering game where whether or not the game ever ends is undecidable, or to create a Sendmail configuration that can't be proven to ever finish starting up.
In other words, it's really hard to make something that's useful for computation but not Turing complete.
So, what's so special about Turing completeness? It sounds like a pretty weak property, if things like Sendmail config files have it.
The key is…nobody's ever found something stronger than Turing completeness. That is, nobody's ever been able to make an algorithm that X fancy new machine can run, but a Turing machine can't. In theory, anything your computer can do, a game of Magic: the Gathering can do. Or a PowerPoint presentation, or Conway's Game of Life. Some people claim that human consciousness can do things a Turing machine can't, but even that is questionable: look into the Church-Turing Thesis.
TL;DR: Turing completeness is useful because weaker models aren't very interesting (**), and stronger models have never been proven to exist.
(*) C, for example, isn't technically Turing complete, because of some lawyerly details in the specification; what it comes down to is, adding more addressable memory makes it a different "implementation", which doesn't count as "the same" language. If you don't care about the fine print, and are happy to call "C with 32-bit pointers" and "C with 64-bit pointers" the same language, then it's Turing-complete.
(**) Well, this isn't quite true. Finite state automata, pushdown automata, and the like are all very interesting and useful for certain tasks. But they're also very limited, since it's been proven that they can never possibly do certain things that we want to do. For example, a finite state or pushdown automaton can never answer the question "are there the same number of As, Bs, and Cs in this string?"