One of the Riddles of the Sphinx: What happens when the unstoppable force collides with the immovable object?
A: It yields.
The answer relies on the use of a pronoun which could equally refer to the Unstoppable Force OR the Immovable Object but not at the same time, and the definition of yield (to stop, or to give way to) having two meanings that can be applied to both the force and the object, but not at the same time. The solution is
that The two titles cannot exist in any capacity if both are true... thus one must yield (if one gives up or "yields" it's title, then it loses its claim).
First, whoever told you that the this is the "worst trope" is missing tropes 101. All tropes are merely tools. A hammer can be used for good or evil, but the use does not make the tool good or bad... merely a the tool user.
The "Unstoppable Force" is a metaphoric trope that can take on different aspects. It can be a physical juggernaut that will continue on its course no matter what attempt to divert it stands in it's way. It could be a moral crusader, who refuses to give up for his cause, no matter the cost. It could be a determined achiever, who won't let his circumstances stop him from his dream. Or it can a persistent predator, who will hunt his query until one of them dies.
To wit, there are several beloved characters that were portrayed as "unstoppable" to varying success. But one popular character uses this as his title to great effect.
Enter the Unstoppable Juggernaut, of X-Men fame, for whom this is his primary power. As explained, once Juggernaut starts moving, his powers allow him to create a force field that will allow him to keep moving regardless of any barrier to his momentum. Unless he decides to stop moving, nothing can stop him. The obvious solution is to force the Juggernaut to stop moving long enough to contain him so he cannot move at all. In his debut appearance, he was strong enough to walk through each of the original five X-men when they stood alone against him. The only way he was beaten was when the team realized that his brother and their mentor, Professor Xavier, was the target of the Juggernaut. Because the Juggernaut can only be stopped by choosing to stop, a powerful telepath, like Xavier, could force him to choose to stop and thus stop the "unstoppable" Juggernaut's helmet could block Xavier from doing this... so the X-Men didn't have to stop the unstoppable... they just had to steal his silly hat. Then Xavier could make the Juggernaut stop himself (incidentally, another long time foe of the X-men, the Blob, has "being an immoveable object" as his power... if he decides not to move from a spot, nothing short of moving the spot he's standing on will move him... and even then, that's only because his feet will be firmly planted on the spot. He's been tossed upside down with a huge chunk of earth stuck to his feet, because someone strong enough pulled up the hunk of earth he was rooted to and flipped it.).
Often times, the "Unstoppable Force" is merely unstoppable by conventional means. One of the scariest things about the terminators from the franchise of the same name is given to us when Kyle Reese describes them in the first film:
It can't be bargained with, it can't be reasoned with, it doesn't feel pity or remorse or fear, and it absolutely will not stop… EVER, until you are dead!
But the terminator is quite stoppable... it's just rediculously hard and it's ruthlessly efficient at stopping anyone who wants to stop it. But if you can survive and adapt to it, you can beat it... but that's easier said than done. Rather the monolog explains what makes the terminators so terrifying. Nothing short of its own death will keep it from trying to kill you, as we see in the various films. You can't scare them by holding a gun to to them. They don't care. You can't by their loyalty with material desire. They have no better nature to appeal to, no political allegiance to question, no philosophical or ideological motive to call into question. It will kill the target it's told to kill because the target is still alive. It's black and white. However, it is subject to physical limitations... they are just fewer than human physical limitations, and most humans are not prepared to fight them with advantageous weapons. What's truly scary is that they hunt humans, the way our ancestors hunted food. Humans aren't the fastest animals... but they have the highest stamina in the world and prefer to hunt at range when most prey animals anticipate a predator to get into melee. It's often said you do not have to be faster than the bear chasing you. You just have to be faster than the friend who is running away from the bear with you. Humans do not care about speed, because as fast as any animal can run, a human at a brisk walking pace will cover the distance and have energy to do it again before you can rest from your flight and heal from your injuries.
Thus the Unstoppable Force appears frequently as villains because it we find it absolutely scare to be hunted like we hunt other animals.
Edit:
The idea of "the unstoppable force" as a hero is also valid and often a staple of action movies. The idea was first popularized by the wonderful Christmas flick "Die Hard" where Bruce Willias' character, an off-duty cop, manages to outmaneuver and outwit a highly trained terrorist organization who has taken his wife's office Christmas party hostage. One interesting aspect of the film is that, at the time of release, Bruce Willas was not the typical action hero actor we think of today. His casting was actually because the story wasn't about an overly masculine 80s action hero with ripped muscles who fearlessly shoots hordes of bad guys while delivering cheesy quips in oddly accented English. Bruce Willas was by all counts was "your dad" and not "ARNALD". He had moments where he was allowed to be physically and emotionally vulnerable and he's concerned that his wife is going to leave him and voices concern that she's concealing the fact that she's married in her male dominated workplace. After witnessing Hans Gruber kill a man, he freaks out, and needs a pep talk from Al after his feet are badly injured in an encounter. He was not the overly muscled 80s action hero.
But what he lacked in manliness, he made up for in determination. The man will not give up and grows from a minor inconvenience to Hans Gruber's worst nightmare as the film continues (On observing the body language of the terrorists, his wife notes he's still alive, because of how clearly frustrated they are becoming over the situation.). Almost every single stunt Bruce's character performs is insane and he is aware of it and absolutely terrified, but the alternative is not much better.
Another example of this type of heroic Unstoppable can be seen in Taken, where human traffickers take the daughter of Liam Nielson's character, a retired Green Beret and CIA Officer. The film boils down to Liam Nielson promises the impossible to international human traffickers:
I don't know who you are. I don't know what you want. If you're looking for ransom, I can tell you I don't have money, but what I do have are a very particular set of skills. Skills I have acquired over a very long career. Skills that make me a nightmare for people like you. If you let my daughter go now, that will be the end of it - I will not look for you, I will not pursue you... but if you don't, I will look for you, I will find you... and I will kill you.
Followed by 2 hours of him looking for, finding, and killing human traffickers. Here, not only does he become the unstoppable force, he clearly tells them that there's one way to stop him and avoid the destruction that he will bring.
The Terminatora good example of this? Because that is an amazing story.