Excel Template or Macro? When to use each!

A lot of Excel users are confused about when to use macros and when to create templates.  A macro is a recording of formatting changes and other steps that can be replayed quickly. A template is a pre-formatted spreadsheet with headers and formulas – awaiting your data. I find that whichever one you know how to create, is the one you tend to use!  But just like you might be able to use a shoe for a hammer, it’s better to have the right tool for the job!

The goal of both is the same: To restructure the worksheet to fit your specific needs.  For example, Ben receives monthly invoices from three different vendors. He then reformats them into one worksheet and imports that into an Access database.

Some of the formatting changes include deleting blank rows, inserting a date column, naming a range, changing cell styles, removing duplicates and separating text into columns.  This is tedious work and Ben wants a faster way to do it.

The question is should he use a macro or a template?

Here is a good rule of thumb! Create a template when you create the spreadsheet and control the data entry.  If you already have the spreadsheet with all the formatting and structure, but it’s full of data, simply delete the data and under File, Save As, save the file as a template.  When it’s time to create the new worksheet, go to File, New and find the template under Personal.

Use a macro when you don’t have any control over how the data is originally formatted like Ben’s invoices from different vendors who are NOT going to change their invoice structure for one customer.  You can check out more in depth instruction on macros here. When you create a macro with lots of steps, you might consider creating several little macros and one final macro to make all the previous macros run in order.

You can create a macro that will automate just about any steps that you have.  And they are as easy to create as recording a voice mail message!  Go to the View tab, Macros, Record Macro. Give it a name, and the Stop Recording button appears on the status bar. Perform the steps that you want to record, then click the Stop Recording button. It’s that easy!  When it’s time to play the macro, go back to View tab, Macros, View Macros. Select the one you want and click Run.  All the steps run in order!

Bottom line is if you create and update the worksheets, use a template to create the structure.  If you receive the worksheets regularly and they are not set up the same, use a macro to restructure them.  That way you can have the right tool for either job!