Macros in Excel: Easier Than You Think!

Man hitting his computer because he's annoyed with Excel. We've all been there.Macros make life easier.

As Edgar & Edgar’s resident VBA enthusiast, I thought I’d blog today about using macros in Excel to make your life a little easier.

For those of you out there who use Excel regularly, I’m sure at some point you’ve found yourself performing some repetitive function, over and over, and wondered “Is there an easier way?”  The answer is yes!  Macros are one of Excel’s most powerful features, and they allow you to automate repetitive tasks, as well as reduce the chances of data entry errors.

Now, some of you may have read “macro” and screamed in horror – yes, coding in VBA can be tricky, and even intimidating to those unfamiliar with it, but you can start recording basic macros with no programming knowledge required whatsoever.

What kinds of things can you do with macro recording?  Let’s try a sample project, one that will automate changing the number format of one column, and the date format of another.

Here’s our spreadsheet at the start.  Notice the numbers are just in normal number format, with four decimal places, and the dates are in MM/DD/YY format.

Unformatted spreadsheet











Now, did you ever notice the little icon at the bottom left next to “READY?”  It looks like a spreadsheet with a circle in the upper left corner?  That’s the macro “Record” button!

Excel Macro Button










Clicking that icon brings up the “Record Macro” dialog box – name your macro, assign it a shortcut key, store the macro in “Personal Macro Workbook,” and enter a brief description, then click “OK.”

Record Excel Macro














Now you’re recording!  Highlight the “Amount” column and format it for Currency.  Next, highlight the “Date” column and format it for MM/DD/YYYY format.  Click the square “Stop” icon in the bottom left (it has replaced the “Record” icon).

Viola!  You’ve now recorded your actions, and can apply them to any worksheet from now on by simply using your shortcut key.

You've done it, you created an Excel Macro!! Go forth and conquer the world, at least your office.