I too am not very experienced in Lisp, but one rule of thumb I have seen for macros is: "If you don't want to automatically evaluate all the arguments of a function, use a macro instead."

As you point out, macros can be overused, but there are some things that you can do with macros that simply can't be done with functions alone (like implementing short-circuiting "and"s and "or"s using just "if" statements).

Many syntactic sugar improvements to programming languages -- like .NET's "using" statement, which allocates a resource, runs some code, then frees the resource -- can be implemented directly with macros. These improvements make code shorter, easier to read, and less error-prone... and with macros you don't have to wait for the language designer to add that functionality for you.