How to Write a Simple Macro in Microsoft Excel

PDF download Download Article
PDF download Download Article

While Excel is full of time-saving features like keyboard shortcuts and templates, you can save even more time by creating macros to complete repetitive tasks. This wikiHow teaches how to create simple macros for Excel spreadsheets.

Part 1
Part 1 of 3:

Enabling The Developer Tab

PDF download Download Article
  1. 1
    Open Excel. The process for showing the Developer tab is the same for many versions of Excel for Windows. There is a slight difference for Excel for Mac, which will be detailed below.
  2. It's in the editing ribbon above your document space.
    • In Excel for Mac, click the "Excel" menu at the top of your screen.
    Advertisement
  3. You might find this option at the bottom of the menu.
    • In Excel for Mac, click the "Preferences" menu option.
  4. It's in the panel on the left side of the window.
    • In Excel for Mac, click "Ribbon & Toolbar" in the "Authoring" section.
  5. You'll see this on the right side of the window under the header, "Main Tabs."
    • In Excel for Mac, you'll see "Developer" in the "Tab or Group Title" list.
  6. You'll see the Developer tab appear in your tab list.
    7. Advertisement
Part 2
Part 2 of 3:

Recording a Macro

PDF download Download Article
  1. If you don't see it, you need to enable it again.
  2. You'll find this in the Code section of the Developer tab. You can also press Alt + T + M + R to start a new macro (Windows only).
  3. Make sure that you'll be able to easily identify it, especially if you're going to be creating multiple macros.
    • You can also add a description to explain what the macro will accomplish.
  4. You can assign a keyboard shortcut to the macro to easily run it. This is optional.
  5. This will create a Ctrl + ⇧ Shift +letter keyboard combination to start the macro. If you don't press Shift , then you run the risk of overwriting any keyboard shortcuts that already exist. For instance, if you entered z in the box without pressing Shift , you'd overwrite the "Undo" shortcut (which is Ctrl + Z )
    • On Mac, this will be a ⌥ Opt + ⌘ Command +letter combination.
  6. More options will drop down for you.
  7. If you're only using the macro for your current spreadsheet, just leave it on "This Workbook." If you want the macro available for any spreadsheet you work on, select "Personal Macro Workbook."
  8. Your macro will begin recording.
  9. Pretty much anything you do will now be recorded and added to the macro. For example, if you run a sum formula of A2 and B2 in cell C7, running the macro in the future will always sum A2 and B2 and display the results in C7.
    • Macros can get very complex, and you can even use them to open other Office programs. When the macro is recording, virtually everything you do in Excel is added to the macro. [1]
  10. This will end the macro recording and save it.
  11. In order to preserve your macros, you'll need to save your workbook as a special macro-enabled Excel format:
    • Click the File menu and select Save.
    • Click the File Type menu underneath the file name field.
    • Click Excel Macro-Enabled Workbook.
    12. Advertisement
Part 3
Part 3 of 3:

Using a Macro

PDF download Download Article
  1. 1
    Open your macro-enabled workbook file. If you have closed your file before running your macro, you'll be prompted to enable the content.
  2. This appears at the top of the Excel spreadsheet in a Security Warning bar whenever a macro-enabled workbook is opened. Since it's your own file, you can trust it, but be very careful opening macro-enabled files from any other source.
  3. 3
    Press your macro shortcut (if you want to use a shortcut key). When you want to use your macro, you can quickly run it by pressing the shortcut you created for it.
  4. This will display all of the macros that are available in your current spreadsheet.
  6. The macro will be run in your current cell or selection.
    • If you don't have custom buttons enabled, you can go to Customize Ribbon and add it there. [2]
  7. If you want to learn more about how macro coding works, you can open the code of any macro you've created and tinker with it:
    • Click the Macros button in the Developer tab.
    • Click the macro you want to view.
    • Click the Edit button.
    • View your macro code in the Visual Basic code editing window.
    8. Advertisement

Community Q&A

Search
Add New Question
  • Question
    If I have two lines of dates in one cell, how do I delete the first line?
    Community Answer
    Double click on the cell, or click the cell and then F2. Then, delete the part that you don't need. If you want to do this on a large number of cells on the same column, then select the column, click "DATA" on the top menu, click on "Text to Columns," and try the different options to divide the data there. (There are too many to list here.) You can divide by commas, spaces, dashes, periods, etc., or specify the length. Make sure the columns on the right are empty; the divided data will split into those columns.
    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 2 Helpful 8
  • Question
    While recording a macro, will it record something I want done outside of Excel, such as opening up a website and clicking on things in that website?
    Community Answer
    No. The macro program in Excel is limited to Excel. Once you use an outside program, Excel will be unable to record what you are doing.
    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 15 Helpful 29
  • Question
    How do I secure files in Excel?
    Community Answer
    Click the Microsoft Office button, point to Prepare, and then click Encrypt Document. In the Password box, type a password, and then click OK. In the Reenter Password box, type the password again, and then click OK. To save the password, save the file.
    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 9 Helpful 7
See more answers
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      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!

      You Might Also Like

      How to
      Use Macros in Excel
      How to
      Create a User Defined Function in Excel
      How to
      Create Pivot Tables in Microsoft Excel to Analyze Data
      How to
      Apply Conditional Formatting in Excel
      Easy AutoFilter Basics for Beginners
      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
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b
      2. ↑ https://support.microsoft.com/en-us/office/assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c

      About This Article

      Article Summary X

      1. Enable macros.
      2. Click the Developer tab.
      3. Click Record Macro .
      4. Name the macro.
      5. Specify a shortcut key.
      6. Select This Workbook under “Store macro in.”
      7. Click OK .
      8. Perform the commands you want to record.
      9. Click Stop Recording when finished.
      10. Save the file as a Macro-Enabled Workbook (*.xlsm) .

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

      Reader Success Stories

      • Nicholas Johnson

        Jul 18, 2017

        "I'm just a Mechanical Engineering major trying to learn about Excel further, and this has been helpful to get ..." more
        Rated this article:
      More reader stories Hide reader stories
      Share your story

      Is this article up to date?

      Advertisement