How to Use Macros in Excel

PDF download Download Article
PDF download Download Article

This wikiHow teaches you how to enable, create, run, and save macros in Microsoft Excel. Macros are miniature programs which allow you to perform complex tasks, such as calculating formulas or creating charts, within Excel. Macros can save significant amounts of time when applied to repetitive tasks, and thanks to Excel's "Record Macro" feature, you don't have to know anything about programming in order to create a macro.

Things You Should Know

  • Macros make it easy to automatic tasks in Microsoft Excel.
  • To create macros yourself, you'll need to enable macros in the Developer menu of Excel.
  • Saving a macro-enabled spreadsheet is a little different than saving a spreadsheet without macros.
Part 1
Part 1 of 4:

Enabling Macros

PDF download Download Article
  1. Double-click the Excel app icon, which resembles a white "X" on a green box, then click Blank workbook .
    • If you have a specific file which you want to open in Excel, double-click that file to open it instead.
  2. It's in the upper-left side of the Excel window.
    • On a Mac, click Excel in the upper-left corner of the screen to prompt a drop-down menu.
    Advertisement
  3. You'll find this on the left side of the Excel window.
    • On a Mac, you'll click Preferences... in the drop-down menu.
  4. It's on the left side of the Excel Options window. [1]
    • On a Mac, click instead Ribbon & Toolbar in the Preferences window.
  5. This box is near the bottom of the "Main Tabs" list of options.
  6. It's at the bottom of the window. You can now use macros in Excel.
    • On a Mac, you'll click Save here instead.
    7. Advertisement
Part 2
Part 2 of 4:

Recording a Macro

PDF download Download Article
  1. If you opened a blank workbook, enter any data which you want to use before proceeding.
    • You can also close Excel and open a specific Excel file by double-clicking it.
  2. It's at the top of the Excel window. Doing so opens a toolbar here.
  3. It's in the toolbar. A pop-up window will appear.
  4. In the "Macro name" text box, type in the name for your macro. This will help you identify the macro later.
  5. Press the ⇧ Shift key along with another letter key (e.g., the E key) to create the keyboard shortcut. You can use this keyboard shortcut to run the macro later.
    • On a Mac, the shortcut key combination will end up being ⌥ Option + ⌘ Command and your key (e.g., ⌥ Option + ⌘ Command + T ).
  6. It's in the middle of the window. Doing so prompts a drop-down menu.
  7. This option is in the drop-down menu. Your macro will be stored inside your spreadsheet, making it possible for anyone who has the spreadsheet to access the macro.
  8. It's at the bottom of the window. Doing this saves your macro settings and begins recording.
  9. Any step you perform between clicking OK and clicking Stop Recording while be added to the macro. For example, if you wanted to create a macro which turns two columns' worth of data into a chart, you would do the following:
    • Click and drag your mouse across the data to select it.
    • Click Insert
    • Select a chart shape.
    • Click the chart that you want to use.
  10. It's in the Developer toolbar. This will save your macro.
    11. Advertisement
Part 3
Part 3 of 4:

Saving a Macro-Enabled Spreadsheet

PDF download Download Article
  1. 1
    Understand why you have to save the spreadsheet with macros enabled. If you don't save your spreadsheet as a macro-enabled spreadsheet (XLSM format), the macro won't be saved as part of the spreadsheet, meaning that other people on different computers won't be able to use your macro if you send the workbook to them.
  2. It's in the upper-left corner of the Excel window (Windows) or the screen (Mac). Doing so will prompt a drop-down menu.
  3. This option is on the left side of the window (Windows) or in the drop-down menu (Mac).
  4. It's in the column of save locations near the left side of the window. A "Save As" window will open.
    • Skip this step on a Mac.
  5. In the "Name" text box, type in the name for your Excel spreadsheet.
  6. Click the "Save as type" drop-down box, then click Excel Macro-Enabled Workbook in the resulting drop-down menu. [2]
    • On a Mac, you'll replace the "xlsx" at the end of the file's name with xlsm .
  7. Click a folder in which you want to save the Excel file (e.g., Desktop ).
    • On a Mac, you must first click the "Where" drop-down box.
  8. It's at the bottom of the window. Doing so will save your Excel spreadsheet to your selected location, and your macro will be saved along with it.
    9. Advertisement
Part 4
Part 4 of 4:

Running a Macro

PDF download Download Article
  1. Double-click the spreadsheet that has the macro in it to open the spreadsheet in Excel.
  2. It's in a yellow bar at the top of the Excel window. This will unlock the spreadsheet and allow you to use the macro.
    • If you don't see this option, skip this step.
  3. This option is at the top of the Excel window.
    • You can also just press the key combination you set for the macro. If you do so, the macro will run, and you can skip the rest of this method.
  4. You'll find it in the Developer tab's toolbar. A pop-up window will open.
  5. Click the name of the macro which you want to run.
  6. It's on the right side of the window. Your macro will begin running.
  7. 7
    Wait for the macro to finish running. Depending on how large your macro is, this can take several seconds.
    8. Advertisement

Community Q&A

Search
Add New Question
  • Question
    Can I use a macro that I create in other spreadsheets and future spreadsheets on the same pc?
    Community Answer
    Yes, you can use a macro that you crate in other spreadsheets and future spreadsheets on the same pc.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 8 Helpful 23
  • Question
    How can I write macros that will change a spreadsheet as soon as I create it?
    Community Answer
    You will first need a basic understanding of VBA. There are many tutorials on this.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 31 Helpful 22
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      • Macros are generally useful for automating tasks which you must perform often, such as calculating payroll at the end of the week.
        Thanks
        Helpful 1 Not Helpful 0
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Warnings

      • Although most macros are benign, some macros can maliciously change or delete information on your computer. Never open a macro from a source which you don't trust.
        Thanks
        Helpful 0 Not Helpful 0
      Advertisement

      You Might Also Like

      How to
      Create a Custom Macro Button in Excel
      How to
      Write a Simple Macro in Microsoft Excel
      How to
      Compare Data in Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      Advertisement

      References

      1. ↑ https://support.office.com/en-us/article/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6
      2. ↑ https://support.office.com/en-us/article/file-formats-supported-in-excel-for-mac-f9970726-bb98-489c-afe2-6d876873945a

      About This Article

      Article Summary X

      1. Enable Developer options in Excel.
      2. Click the Developer tab.
      3. Click Record Macro .
      4. Enter the macro name and details.
      5. Click OK .
      6. Perform the macro's steps.
      7. Click Stop Recording .

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 765,363 times.

      Is this article up to date?

      Advertisement