Why should we value greatness more than morality? Should the magnitude of someone's impact overshadow the morality of their impact?
Opinions of celebrities don't exist in some sort of extra-dimensional cloistered thought bubble; they influence other minds and affect society in very real and measurable ways. Amplifying the celebrity status of these individuals by cherry-picking their acceptable works will in turn, unavoidably, also amplify their less-acceptable opinions -- though probably not by the same magnitude. But in general the masses are terrible at sorting out "oh, this is a righteous opinion that I should listen to" vs "this is a despicable one by the same person, but I have the intellectual & emotional maturity to be able to compartmentalize it". We're too tribal a species, on the average, for that. Our brains, societies, and cultures have not evolved to effectively handle multiculturalism well, ivory-tower internet freethinkers notwithstanding.
The flipside of cancel culture is moral nihilism, in which speech has no consequence and exists purely as a form of harmless intellectual exercise. But that's just not how it works in the real world. Speech has consequence, and for those wishing to prevent those consequences, sometimes ignoring the speaker and refusing to amplify their voice is the only realistic option they have, if they have no power to censor them outright or sufficiently amplify contrary opinions of their own. A canceled author isn't really very different from a boycotted company; it's a moral vote by a like-minded mass of humans who together value their version of morality more than that author's impacts. It's really not that difficult to not be an outspoken asshole.
Saying "I don't want to read this book" or "I don't want to play this game" because you don't like the author's values is a perfectly reasonable thing to do. It's very different from, say, government ban lists. So what if someone doesn't want to read Harry Potter or watch Weinstein movies anymore?
Back to the first question: why ought greatness be the measure of a person, instead of righteousness? Our species is largely full of mediocre apes with mediocre thoughts who nonetheless manage to coexist and thrive to some degree, yet are all too often led astray and into peril and evil by the "great" Machiavellian few among them. How does that help anything at all?