For example, you could describe how to render your own domain specific markup language as HTML. Which is then happily viewed by the browser as a regular webpage, that can of course also be styled with CSS as usual.
Or how to render Markdown as HTML without a Markdown generator (might require rarely implemented XSLT 2.0 with XPath 2.0). See: https://stackoverflow.com/questions/3549827/converting-simpl...
Or render HTML as some other markup language.
This is only scratching the surface, really. But it does powerful transformations from one markup language to another.
This can of course be very useful as you get the browser able to present any markup without actually supporting said markup; without scripting or server side components.
Client side XSLT transforms are very rarely useful. It was intended for a vision of the web as a multiude of different XML formats served to end users. Since this didnt happen (for good reasons), XSLT becase less useful. In reality it is only ever used to tranform into XHTML.
It was also touted as a more powerful style sheet since it could rearrange elements and transform semantic elements into tables for presentation. But CSS supports that now in a much better way, and most significantly CSS can react to dynamic updates.
Serving XML to end users only makes sense if someone actully understands the XML format. But only very few XML formats beside XHTML and SVG have any broad support. RSS is one of the few examples, and seem to be the main use case for XSLT.