As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Create a Macro in Excel: Step-by-Step Guide

If you've ever found yourself in a situation where you had to perform the same task in Microsoft Excel over and over again, you know how time-consuming and tedious it can be. That's where macros come in. A macro is a recording of a series of actions that you can play back as many times as you want. In Excel, macros are written in Visual Basic for Applications (VBA), a programming language that is built into Microsoft Office. By using the macro recorder, you can automatically create a macro without having to learn VBA.

Before You Begin

Before you can create a macro, you need to decide which actions you want to record. For example, if you want to record a macro that inserts your company's logo and address into a document, you would first open a document and insert the logo and address. Once you've performed the actions you want to record, you're ready to start recording the macro.

Step 1: Turn on the Developer tab

If the Developer tab isn't visible, you need to turn it on. The Developer tab is where you go to develop macros and to write and test code. Here's how to turn on the Developer tab:

  1. Click File > Options. The Excel Options dialog box appears.
  2. Click Customize Ribbon in the left pane.
  3. In the right pane, under Main Tabs, select the Developer check box, and then click OK.

Step 2: Start recording the macro

Once you've turned on the Developer tab, you're ready to start recording the macro. Here's how:

  1. On the Developer tab, in the Code group, click Record Macro.
  2. In the Record Macro dialog box, do the following:
  3. In the Macro name box, enter a name for the macro. The name can be up to 64 characters and can include letters, numbers, and underscores.
  4. To assign a shortcut key to the macro, click Shortcut key, and then enter the key combination.
  5. In the Store macro in list, click Personal Macro Workbook if you want the macro to be available every time you start Excel.
  6. Click OK.

Step 3: Perform the actions you want to record

After you click OK, Excel enters macro-recording mode and displays Record Macro in the status bar. When you're in macro-recording mode, Excel records all the steps you take, as long as you don't click any of the command buttons on the Stop Recording toolbar. If you make a mistake or want to stop recording before you finish, you can click Stop Recording on the Stop Recording toolbar. If you do, you'll be prompted to save the macro; if you click No, the macro will be deleted.

Step 4: Stop recording the macro

When you've finished recording the macro, click Stop Recording on the Stop Recording toolbar.

Step 5: Test the macro

Now that you've recorded the macro, it's a good idea to test it to make sure it works the way you want. Here's how:

  1. Open the workbook in which you want to test the macro.
  2. On the Developer tab, in the Code group, click Macros.
  3. In the Macros dialog box, under Macros in, click Personal Macro Workbook.
  4. Click the macro you want to run, and then click Run.

Step 6: Save the workbook as a macro-enabled workbook

If you want to be able to use the macro in other workbooks, you need to save the workbook as a macro-enabled workbook. A macro-enabled workbook has a .xlsm file extension and can contain macros. Here's how to save a workbook as a macro-enabled workbook:

  1. Click File, and then click Save As.
  2. In the Save As dialog box, in the Save as type list, click Excel Macro-Enabled Workbook (*.xlsm), and then click Save.