Hence, Lisp code is made of nested lists. When you think further about it, it means that Lisp metaprogramming facilities may do things just with plain list manipulation functions! So it's not about metaprogramming itself, it's that it's incredibly easy. This is what people refer as "code as data" and "homoiconicity" and so on.
Yes, all programming languages are programmable. But Lisp tends to be unlimited in its programmability, in almost the same way that Unix is: if you have root, you can change anything. I'm pretty sure that in most Common Lisp systems, you can redefine large parts of the compiler at runtime. And so on.
Syntactic macros are just one part. They can be extremely handy. Lots of Lisp systems use them well. Random examples: the DEFSYSTEM macro of ASDF; the DEFINE-EASY-HANDLER macro of Hunchentoot (a Common Lisp web server); the (controversial) LOOP and ITERATE macros; etc.
Another somewhat random example: Movitz was (is?) a project to write an x86 kernel in Common Lisp. It actually included its own compiler. It defines a lot of macros for instruction definition [0] and uses them in code that I don't understand anything of [1] but is probably very clear to someone versed in assembly.
Recently I've been trying to add some nice logging to a JavaScript program, and it's a typical scenario where the normal syntax is just annoying enough to make the code ugly and hard to scan—if I had macros, I could invent some other syntax.
How to use macros is a tradeoff that I guess comes with Lisp experience, but they can be a powerful escape when the standard syntax is annoying, and a way to define your own DSLs without restriction.
[0]: https://common-lisp.net/viewvc/movitz/ia-x86/def-instr.lisp?...
[1]: https://common-lisp.net/viewvc/movitz/ia-x86/instr-add.lisp?...
How would you write a program to write a program? How can you use this generated program in your other code? It's really hard to do with a lot of programming languages, even those that have eval like JavaScript, so it never occurs to people that something like a template system or a code translator/DSL should be trivially easy to write.
Lisp uses prefix notation. With mexpr[1] it uses infix notation.
And so on.