How to Create a Custom Macro Button in Excel

Macros in Excel can save a great deal of time with repetitive tasks. By assigning macros to custom buttons, you can save even more time by bringing your macro only one-click from execution.

Method 1
Method 1 of 4:

Excel 2003

  1. Image titled Create a Custom Macro Button in Excel Step 1
    1
    Click Tools → Customize.
  2. Image titled Create a Custom Macro Button in Excel Step 2
    2
    Click the Toolbars tab.
    Advertisement
  3. Image titled Create a Custom Macro Button in Excel Step 3
    3
    Click the New button.
  4. Image titled Create a Custom Macro Button in Excel Step 4
    4
    Type a name for your new toolbar.
  5. Image titled Create a Custom Macro Button in Excel Step 5
    5
    Click OK.
  6. Image titled Create a Custom Macro Button in Excel Step 6
    6
    Click the Commands tab.
  7. Image titled Create a Custom Macro Button in Excel Step 7
    7
    Select Macros from the list on the left.
  8. Image titled Create a Custom Macro Button in Excel Step 8
    8
    Click and drag the Custom Button icon from the list on the right to your new toolbar. The new button is represented by a smiley face.
  9. Image titled Create a Custom Macro Button in Excel Step 9
    9
    Right-click on the newly added button.
  10. Image titled Create a Custom Macro Button in Excel Step 10
    10
    Rename the button to your liking or leave the default name in the Name: field.
  11. Image titled Create a Custom Macro Button in Excel Step 11
    11
    Click Edit Button Image... and change the image for your button or leave it the same. The Button Editor has controls similar to the Windows Paint program.
  12. Image titled Create a Custom Macro Button in Excel Step 12
    12
    Click "Assign Macro."
  13. Image titled Create a Custom Macro Button in Excel Step 13
    13
    Select a macro you created from the list.
  14. Image titled Create a Custom Macro Button in Excel Step 14
    14
    Click OK.
  15. Image titled Create a Custom Macro Button in Excel Step 15
    15
    Click Close on the Customize dialog box.
    16. Advertisement
Method 2
Method 2 of 4:

Excel 2007

  1. Image titled Create a Custom Macro Button in Excel Step 16
    1
    Click the little arrow pointing down on the Quick Access Toolbar.
  2. Image titled Create a Custom Macro Button in Excel Step 17
    2
    Click More Commands.
  3. Image titled Create a Custom Macro Button in Excel Step 18
    3
    Select Macros from the Choose commands from list box.
  4. Image titled Create a Custom Macro Button in Excel Step 19
    4
    Select your macro from the left hand column and click the Add button.
  5. Image titled Create a Custom Macro Button in Excel Step 20
    5
    Select the macro that you just added from the right hand column and click the Modify button.
  6. Image titled Create a Custom Macro Button in Excel Step 21
    6
    Click the button image you want to represent your macro, type the display name you want in the Display name text box and click the 'OK' button.
    7. Advertisement
Method 3
Method 3 of 4:

Excel 2010

  1. Image titled 561154 22
    1
    Make sure the Developer tab is displayed. The Developer tab is a tab in the Ribbon at the top of Excel. If it is not displayed, follow these directions in order to display it:
    • Click File → Options → Customize Ribbons
    • Find the Developer check box in the Main Tabs section and click it. Press "OK" when you are done.
  2. Image titled 561154 23
    2
    Add "New Group" under the Developer Tab to create a custom group for the command/button that will be created.
  3. Image titled 561154 24
    3
    Still in the Customize Ribbon, click on the dropdown menu to choose command. Select Macro. Thereafter, all recorded macros will appear in the left box.
  4. Image titled 561154 25
    4
    Select the desired macro for button creation (make sure the newly added group is highlighted, you will know if the macro was added when it appear in the right box under your new group).
  5. Image titled 561154 26
    5
    You may now customize your button. Right-click and select Rename.
  6. Image titled 561154 27
    6
    If everything is set, Click "OK"
    7. Advertisement
Method 4
Method 4 of 4:

Excel 2013

  1. Image titled 561154 28
    1
    Make sure the Developer tab is displayed. The Developer tab is a tab in the Ribbon at the top of Excel. If it is not displayed, follow these directions[1] in order to display it:
    • Go to Excel → Preferences → Ribbon (Under Sharing and Privacy)
    • Under Customize, check the box next to the Developer tab, and press "OK"
  2. Image titled 561154 29
    2
    Click on the Developer tab and click Button. The Button icon is under the Form Controls group in the Developer tab and looks like a rectangular button.
  3. Image titled 561154 30
    3
    Place your button. Place your cursor where you'd like the button to go and drag to select the size of the button. You can make the button as small or as large as you'd like, given your needs. If you wish, you can move the button around after placement.
  4. Image titled 561154 31
    4
    Assign a macro when prompted. Excel should automatically prompt you to assign a macro to your button after you finish placing it. Once you've selected your macro, click "OK."
  5. Image titled 561154 32
    5
    Format the button. Right-click on the newly created button and select "Format Control." Select Properties → Don't move or size with cells → OK. This helps you maintain the size and placement of your button. If you don't have this property chosen, your button size and placement will change if you have it add, delete, or move cells.
  6. Image titled 561154 33
    6
    Rename the button. Change the text in the button to say whatever you'd like it to.
    7. Advertisement

Warnings

  • The user interface on versions earlier than 2003 may be different so the 2003 method may not be exactly the same for those versions.
    ⧼thumbs_response⧽
  • If you want a different button image than what version 2007 offers, you will need to download additional software that specializes in modifying user interfaces for Microsoft Office.
    ⧼thumbs_response⧽
Advertisement

You Might Also Like

Create Macros
How to
Create Macros
Write a Simple Macro in Microsoft ExcelHow to Write a Simple Macro in Microsoft Excel
Create a User Defined Function in Microsoft Excel
How to
Create a User Defined Function in Microsoft Excel
Apply Conditional Formatting in Excel
How to
Apply Conditional Formatting in Excel
Make a Spreadsheet in Excel
How to
Make a Spreadsheet in Excel
Create a Mortgage Calculator With Microsoft Excel
How to
Create a Mortgage Calculator With Microsoft Excel
Unprotect an Excel Sheet
How to
Unprotect an Excel Sheet
Create a Timeline in ExcelHow to Create a Timeline in Excel: SmartArt, Templates, and More
Create a Graph in Excel
How to
Create a Graph in Excel
Change from Lowercase to Uppercase in Excel
How to
Change from Lowercase to Uppercase in Excel
Merge Cells in ExcelHow to Merge Cells in Microsoft Excel: A Quick Guide
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Unhide Rows in Excel
Convert Excel to Word3 Easy Ways to Convert Microsoft Excel Data to Word
Advertisement

References

  1. ↑ http://office.microsoft.com/en-us/excel-help/add-a-button-and-assign-a-macro-to-it-in-a-worksheet-HP010342137.aspx

About This Article

wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 13 people, some anonymous, worked to edit and improve it over time. This article has been viewed 479,110 times.
How helpful is this?
Co-authors: 13
Updated: July 28, 2022
Views: 479,110
Categories: Featured Articles | Microsoft Excel
Advertisement