Simple. It means that warning bells should be ringing in your ears.
Literally, it means the seller wants to sell his home. But that alone isn't sufficient to discern the real meaning when you see "motivated seller" in an ad. Of course he wants to sell his home - he wouldn't have it on the market otherwise. And while it's true that there are some sellers out there who really don't want to sell, or are just testing the waters, they're actually pretty rare. Sellers may be reluctant, sad or unhappy to be moving on, but it's reasonably safe to assume that if a house is on the market and a sign is in the yard, the seller has made a commitment to sell.
So what does it actually mean?
Short version - it means that the seller is being poorly represented by his agent.
Why is that? "Motivated seller", and similar phrases, strongly suggest that the seller will take less than he is asking for the home. If that phrase appears as marketing copy in an ad, presumably written by the listing agent, or is otherwise communicated, unless the seller has a full understanding of the impact of saying that and has approved its use, we have a problem. It's a violation of real estate licensing law in Colorado for an agent to indicate that a seller will accept less than asking price. (And likewise for indicating that a buyer will pay more).
And even with seller approval, it's poor marketing. It suggests that the home is over-priced or has some kind of problem, that the seller will be perhaps more flexible than he is actually willing to be, and that price is going to be a major negotiating issue - one with a vague starting point. Buyers will be expecting a bargain, and may be more reluctant to give up that expectation once it has been dangled in front of them.
Wouldn't it be better to just price the home correctly to begin with or adjust the price if necessary? Or if the price is sound, show a positive commitment to it instead of undermining it from the beginning?