Open In App

How to Create a Macro in Excel: Build, Run, and Use Macros Easily

Last Updated: 06 Jan, 2025

N

Neeraj
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

How Do I Create a Macro in Excel- Quick Steps

  • Open MS Excel > Start Recording a Macro
  • Perform the Actions You Want to Automate > Stop Recording
  • Run the Macro

Are repetitive tasks consuming your time in Excel? With this Excel macros tutorial, you can learn how to create and use macros in Excel to automate tasks and improve efficiency. This guide will walk you through the essentials, from understanding what macros are to creating a macro button for quick access. Whether you’re a beginner looking for a step-by-step guide to Excel macros or someone eager to explore advanced features, this article provides everything you need to make and run a macro with ease.

How-to-Create-a-Macro-in-Excel

How to Create a Macro in Excel

Table of Content

What is Macro in Excel

An Excel Macros is a set of instructions that you can create to automate repetitive tasks. Think of it like a recorded script that performs actions such as formatting cells, entering data, or generating reports, all with just a single click. This helps save time and reduces the chance of errors when doing the same task over and over. Creating a Macro in Excel takes some basic steps to complete that will be covered in the given article.

Uses of Macros in Excel

Macros in Excel help automate repetitive tasks, making work faster and more efficient. Here are some common uses:

  • Automating Repetitive Tasks: Macros can quickly perform repetitive actions, such as data entry, formatting cells, or applying formulas, saving time and reducing errors.
  • Data Analysis and Manipulation: Automate data sorting, filtering, and calculations to make data analysis faster and more consistent.
  • Generating Reports: Create complex reports automatically by recording the steps required, such as gathering data, applying filters, and formatting.
  • Data Validation: Automate data checks, validations, and corrections to ensure data accuracy.
  • Customizing Excel Functions: Macros can be used to create custom functions tailored to specific needs, enhancing Excel’s built-in functionality.
  • Importing and Exporting Data: Automate data imports from external sources or exports to other formats with just a single click.
  • Simplifying Complex Workflows: Automate complex sequences of tasks, making it easier to complete detailed processes consistently.

Macros help streamline Excel tasks, making it a powerful tool for productivity and data management.

How to Create a Macro in Excel

Creating a macro in Excel allows you to automate repetitive tasks, saving time and increasing productivity. Macros are written in Visual Basic for Applications (VBA), but you can create them either by recording your actions or by writing VBA code manually. Here’s how to create a macro in Excel:

Step 1: Enable the Developer Tab

To create or record macros, you need to enable the Developer Tab in the Excel ribbon.

  • Open Excel and go to File > Options.
  • Click Customize Ribbon from the left-hand menu.
  • Check the box for Developer under the “Main Tabs” section.
  • Click OK. The Developer tab will now appear in the ribbon.
developer

Enable the Developer Tab

Step 2: Record a Macro in Excel

Recording a macro in Excel is simple and allows you to automate repetitive tasks. Follow these steps to record your first macro:

  • Go to the Developer Tab and click Record Macro in the “Code” group.
  • Name the Macro:
    • Enter a descriptive name (e.g., FormatData).
    • Optionally, assign a shortcut key (e.g., Ctrl + Shift + F).
    • Choose where to store the macro:
      • This Workbook: Macro is saved in the current workbook.
      • Personal Macro Workbook: Macro is available across all workbooks.
  • Click OK to start recording.
  • Perform the actions you want to automate (e.g., formatting cells, applying filters).
  • Go back to the Developer tab and click Stop Recording once you’re done.
Record-a-Macro-

Developer tab>>Select “Record Macro”>>Enter “Macro Name”>>Enter “Shortcut”>>Press ok

How to Run a Macro in Excel

Running a macro in Excel is quick and easy. Follow these steps to execute a macro:

Step 1: Open Microsoft Excel and Select Your Workbook

  • Open the Excel workbook containing the macro you want to run.
  • Ensure macros are enabled for the workbook:
    • Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and enable macros.

Step 2: Go to the Developer Tab and Select the Macros Option

  • Click on the Developer Tab in the ribbon.
    • If the Developer Tab is not visible, enable it via File > Options > Customize Ribbon.
  • In the “Code” group, click on Macros.
Create a Macro in Excel

Click on Macros

Step 3: Run the Macro

  • In the Macro dialog box that appears:
    • Select the macro you want to run from the list.
    • Click Run.
  • Alternatively, use the shortcut key (if you assigned one while creating the macro) to execute the macro directly.

How to Create a Macro button in Excel

Creating a macro button in Excel allows you to run macros with a single click. Here’s how to link a macro to a button:

Step 1: Insert a Shape

  • Go to the Insert Tab in the ribbon.
  • In the Illustrations group, select Shapes.
  • Choose the desired shape (e.g., rectangle, circle) and draw it in your worksheet.
Insert-a-SHape

Insert a Shape

Step 2: Add Text to the Shape

  • Right-click on the shape and select Edit Text.
  • Enter a descriptive label for the button, such as “Run Macro” or the name of the macro.
image

Add Text to the Shape

Step 3: Assign a Macro to the Shape

  • Right-click on the shape again and select Assign Macro from the drop-down menu.

Step 4: Select the Corresponding Macro

  • In the Assign Macro dialog box:
    • Select the macro you want to assign from the list.
    • Click OK.

Step 5: Test the Macro Button

  1. Click on the shape (your button) to run the assigned macro.
  2. Ensure the macro performs the desired action.
Create a Macro in Excel

Select Assign Macro

Easy Tips for Creating a Macro

Here are some easy tips for creating a macro in Excel to improve your productivity:

1. Plan Your Steps

Before recording a macro, outline the steps you want to automate. This ensures nothing is missed during the recording process, helping you create efficient Excel macros.

2. Use the Macro Recorder

  1. Go to the View tab, select “Macros”, and click “Record Macro”.
  2. Perform the tasks you want to automate.
  3. Click “Stop Recording” when you’re done.

3. Give Your Macro a Clear Name

Use descriptive names for your macros so you can easily identify their purpose (e.g., “FormatReport”). This helps in managing multiple Excel VBA macros.

4. Create a Shortcut Key

Assign a shortcut key for easy access, but ensure it doesn’t conflict with common Excel shortcuts for smoother use.

5. Test Your Macro

Run your macro on a test sheet to ensure it works as expected, reducing potential errors.

6. Use Relative References (if needed)

Click “Use Relative References” when recording if you want your macro to apply to different cells based on your selection.

7. Edit the Macro (Optional)

Go to “Macros” > “View Macros“, select your macro, and click “Edit” to refine the VBA code if you have some coding experience.

These macro creation tips make automating tasks in Excel easy, helping you save time and work more efficiently!

Also Read

Conclusion

Creating a Excel macro is a straightforward process that can significantly enhance your productivity and efficiency. By creating a macros in Excel, you can save valuable time and ensure consistency in your work. To create a macro, simply enable the Developer tab, record your actions, and then save the macro for future use. Whether you’re formatting data, generating reports, or performing complex calculations, macros can streamline your workflow and reduce errors. In the above article we have explore how to create a macro in Excel by some simple and easy steps.

How to Create a Macro in Excel – FAQs

How do you create a macro in Excel?

Follow the steps given below to create a macro in Excel:

  • Open MS Excel
  • Go to the Developer Tab
  • Select Customize Ribbons
  • Record your Macro
  • Stop Recording

How to Make a Macro on Mac?

To create a macro on Mac in Excel:

  1. Enable the Developer Tab: Go to Excel > Preferences > Ribbon & Toolbar and check Developer.
  2. Record Macro: In the Developer tab, click “Record Macro”, name it, and add an optional shortcut.
  3. Perform Actions: Carry out the steps you want to automate.
  4. Stop Recording: Click “Stop Recording” in the Developer tab.
  5. Run Macro: Go to Developer > Macros, select your macro, and click Run.

How to create a macro in excel without recording?

To record a macro in Excel without recording follow the step given below:

  • Open Excel
  • Enable the Developer Tab
  • Open the VBA Editor
  • Insert a New Module
  • Write Your VBA Code
  • Save Your Macro

How do I create a personal macro in Excel?

The steps of creating a personal are also same as the steps for creating macro in Excel.

  • Go to the Developer Tab
  • Select Customize Ribbons
  • Record the Macro in your Personal Workbook
  • Stop Recording

What is the use of macros in excel with examples?

Here are some key uses of macros in Excel:

  • Data Entry: Automate the entry of repetitive data to save time and reduce errors.
  • Formatting: Apply consistent formatting to cells, rows, or columns across multiple worksheets or workbooks.
  • Sorting and Filtering: Quickly sort and filter large datasets based on specific criteria.
  • Data Cleaning: Remove duplicates, correct data errors, and standardize data formats.
  • Statistical Analysis: Perform statistical calculations, such as averages, standard deviations, and correlations.

How to enable macros in Excel?

Follow the steps given below to enable the macros in Excel:

  • Open Excel
  • Access Excel Options
  • Navigate to Trust Center Settings
  • Adjust Macro Settings
  • Enable Macros for a Specific Workbook


Article Tags :

N

Neeraj
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

A

Asha Senthilkumar

How to Enable & Run a Macro in Excel? 6 Ways

article_img
We can record a macro in Excel or write from scratch in VB Editor. Excel provides different options to run a macro. In this article, we explore four different approaches to running a macro in an Excel application. Even while executing an Excel macro is a straightforward process for seasoned users, it might not be clear to newbies. You will discover numerous ways to execute macros in this article, some of which may entirely alter the way you interact with Excel files. The below macro will write the text “Geeks for Geeks” in cell B5 Sub updateText() ActiveSheet.Range("B5").Value = "Geeks for Geeks" End Sub How to Run a Macro from Excel RibbonFollow the below steps to run a macro in the Macro dialogue box: Step 1. Go to View >> Macros >> View Macros – to pop-up Macro dialogue box [keyboard shortcut - Alt+F8]. Step 2. Select a Macro from the list (e.g. updateText) and Press “Run” Output: How to Run Macro from VBA EditorFollow the below steps to run a macro in VBE: Step 1. Open Excel Step 2. Go to Developer>> Visual Basic – to open VBA Editor [keyboard shortcut - Alt+F11] Step 3. Select anywhere in the macro code and click the “Run button” (Green Arrow) or Press F5 How
Read More

J

Jimish Ravat

How to Run a Macro Automatically

article_img
To run a macro automatically when the workbook opens one must enable the developer's tools in Microsoft Excel. What is a Macro in Excel Macros allows automating repetitive tasks in Microsoft Excel. Some tasks need to be performed multiple times and repetition of all the actions would consume time. In such type of Scenarios, Macros helps in automating these tasks. How to Enable the Developer's ToolStep 1: Go to File > Options > Customize Ribbon Step 2: Then checkmark the Developer's tool option in the customize ribbon option. Now you can record Macro, run Macro and do everything that a developer can do. How to Run Macro Automatically when the workbook opensStep 1: Go to the developer's menu and then go to the visual basic Step 2: Go to ThisWorkbook Tab Step 3: Write down Private Sub Workbook_open() and hit enter. You will then see the below screen. You can write your code or basically whatever you want between this and it will automatically run every time the workbook is opened. Let us make a Welcome Message Box: Step 4: Save the workbook as Excel Macro-Enabled Workbook. Note: If you don't save it as an Excel macro-enabled workbook then it will not save the macros and it will
Read More

N

Nikitha Sri

Editing Excel Macros in Excel

article_img
Excel Macro is a set of actions that can be recorded, saved, used multiple times. This feature saves us a lot of time when dealing with repetitive tasks and huge data sets. We can always make changes to an existing Macro in Excel. There are two ways to record and run macros: Excel Commands Excel Visual Basic for Applications(VBA).1. Copying a Macro Code We can reuse the existing macro code while creating new Macros. For example, if you have existing macros created using the record function, we can reuse the code to write new macros. To open the code follow the below steps: Select Tools --> Macros --> Visual Basic Editor. Copy code from the existing macros(use Command + C) and paste(use Command + V) in the new macro2. Renaming a Macro Follow the below steps to rename a Macro in Excel: Select Tools --> Macros --> Visual Basic Editor. Select name on the left plane.Edit the name.3. Deleting a Macro Follow the below steps to delete a macro in Excel: Select Tools --> Macros --> Macros... Select the macro to be deleted. Click on the minus(-) symbol at the bottom.Select yes to delete the macro.
Read More

G

Gautam goel

How to Easily Calculate the Dot Product in Excel?

article_img
The dot product is the sum of the product of two vectors. For example, two vectors are v1 = [2, 3, 1, 7] and v2 = [3, 6, 1, 5]. The sum of the product of two vectors is 2 × 3 + 3 × 6 + 1 × 1 = 60. We can use the = SUMPRODUCT(Array1, Array2) function to calculate dot product in excel. Dot Product The dot product or scalar product is the sum of the product of the two equal length vectors. The general formula for the dot product is, Let us consider two vectors p = [p1, p2, p3, ..., pn] and [q1, q2, q3, ..., qn] The dot product is, p.q = \sum_{i = 1}^{n}p_iq_i p.q = p1q1 + p2q2 + p3q3 +... pnqn. Example: Given two vectors a = [4, 3, 6, 5] and b = [3, 1, 1, 2]. Find the dot product of the two vectors? Answer: We are given two vectors, a = [4, 3, 6, 5] b = [3, 1, 1, 2] We know that the dot product is, a.b = a1b1 + a2b2 + a3b3 + a4b4. a.b = 4*3 + 3*1 + 6*1 + 5*2 a.b = 12 + 3 + 6 + 10 a.b = 31 Function for Dot Product in Excel The function used for calculating dot product in excel is = SUMPRODUCT(Array1, Array2). This function calculates the sum of the product of the two selected range cells. Note: SUMPRODUCT function can calculate the sum of the product of n arrays also. We are no
Read More

R

riya aggarwal

Excel Personal Macro Workbook

article_img
When we create the macros for automation, they can be used for that particular workbook only. We cannot access them outside that workbook in others. Excel Personal Macro Workbook is a hidden workbook in your local system that pops up whenever you open the Excel application. It is a place where all the macros from any workbook can be stored and one can use the macros from here which are often required. How to create a personal macro workbook? Creating a personal macro workbook is similar to creating a macro in Excel. Let's discuss it in some simple steps : Click on the developer tab on the ribbon and then click on the Record Macro tool in the Code group.A new dialog box would open.In the option, "Store Macro In", choose Personal Macro Workbook. In this way, our macro would be stored in the personal macro workbook.After filling the dialog box, click OK.In the last, click on stop recording in the developer tab. This is how a personal macro workbook can be created. How to view, create, copy and edit macros in Personal Macro Workbook? All the options like view, create, copy and edit macros can be done from VBA(Visual Basic for Applications) Editor. Let's discuss it in some simple ste
Read More

A

Asha Senthilkumar

How to Password Protect Your Excel Macro?

article_img
It is a best practice, to protect the Macro (VBA Code) from other users to edit or view. In this article, we explain to create a simple macro and protect the same "VBA Project" using a password in Excel from other users. Implementation: Follow the below steps to protect your excel macro with a password: Step 1: Open an excel file, to create a simple “hello world” macro. Step 2: Press Alt + F11 – to open Visual Basic Editor. Step 3: Click Insert >> Module, which will add a new module as in Img1. Img 1 Step 4: Type the below code in “code window”. Sub firstMacro() MsgBox "Hello World" End Sub Step 5: Right-click “VBAProject (Book1)” and then click “VBAProject Properties”. Step 6: GoTo Protection Tab >> Check “Lock project for viewing” and type your password in both Password and Confirm password input text boxes. Press “OK” Step 7: Save your Excel workbook as Excel Macro-Enabled Workbook (*.xlsm) and close Step 8: Now reopen your excel file. Press Alt+F8 – to open the Macro dialog box. Select the macro “firstMacro” and click “RUN”. Step 9: Our macro will pop up below the message box. Close or press OK to hide the message box Step 10: Press Alt + F11 – to open Visual Bas
Read More

A

Aditi Pateriya

How to Record a Macro in Excel

article_img
Excel Macro is simply a record button that records a set of actions performed on Excel that can be run multiple times. For example, if you have to format some raw data on a weekly basis you can use a macro to record yourself formatting the data once and let Excel automate the task in the future. it's used to help users save time and avoid performing redundant tasks. What is a Macro in ExcelIn simple words, a Macro is a snippet of VBA code that empowers you to execute a series of actions with a single command. Whether you're generating this code using a macro recorder or crafting it manually, it all falls under the umbrella of macros. How to Get the Excel Developer Tab Show Up in the RibbonThe first step to record a macro is to get the Developer tab turned on. If you already see the Developer tab in the ribbon, then you can directly follow the steps to record a macro. How to Enable the Developer Tab in ExcelIn order to record a macro the Developer tab on the ribbon needs to be activated as it isn't on by default within Microsoft Excel. The developer tab contains all the macro tools among various other features. How to Activate: To activate it click on the file tab> go to options
Read More

R

riya aggarwal

Assigning Excel Macro to Objects

article_img
In Excel, a recorded macro can be assigned to different objects like a shape, graphic, or control note. Instead of running the macro from the required tool in ribbon, we can create these objects to run them easily. They get very handy when multiple macros are there. Individual objects can be created for different macros. In this article, let's discuss how can we assign macros to different objects : 1. Assigning Macro to a Shape It is one of the easiest ways to run a macro is to create any shape in the worksheet and use it for running the macro. The steps that need to be followed are: Click on the Insert Tab on the ribbon. Go to the Illustrations group and click on the Shapes icon. In the sheet, choose any shape we like and want it to be assigned as the macro. Click where you want to add the shape in the sheet and the shape will automatically get inserted. The shape can be resized or re-formatted accordingly to the way you want. Text can also be added in the shape. Then Right-Click on the shape and a dialog box would be opened. Then click on Assign Macro. After right-clicking on the shape, another dialog box of Macro would be opened. In the dialog box, select the macro from the lis
Read More

A

Aditi Pateriya

UserForms in Excel Macros

article_img
A UserFrom refers to a form or a dialog box on the screen that enables a user to input data into Excel's user interface. it can be created using the Visual Basic Editor and is used to control data entry and avoid errors. 1. Creating a UserForm: To create a userform head on to the Developer tab-> Visual Basic->Insert->UserForm. You can also go to the Project window on the left side and right-click the workbook you want to use and select Insert->UserForm. This opens up a blank userform (UserForm1) and a toolbox(if the toolbox doesn't pop up head on to View>toolbox). 2. Naming the UserForm: By default, excel opens up an userform with the name UserForm1 in order to change that name: Head to the Properties window(view>Properties window), This opens a pane on the left.Click on the 'Name' section and type a name of your choice. (don't add spaces)The name won't be reflected on the userform when you do so because that's a different property of the form.Head to the 'Caption' property and type in a name, and it will be reflected on your UserForm. Now let's play with some controls and learn how to add them to our UserForm. 3. Adding a Textbox to the UserForm: A Textbox is u
Read More

A

Asha Senthilkumar

Relative References in Excel Macros

article_img
We have two options to refer a cell in Excel VBA Absolute references and Relative references.  Default Excel records macro in Absolute mode. In this article, we learn about Relative references in Excel VBA.  We select a cell “A1”, turn on “Use Relative Reference” and record a macro to type some text in cells B2:B4.   Since we turn on the “Relative reference” option.  Macro considers the number of rows and columns from active cells.  In our example, we select cell A1 and start type B2, which is to move one column and one row from A1 (Active cell). What are Relative References?In Excel, the way of recording actions within a macro that is relative to the cell or range that the macro starts from is known as a relative reference. When you start recording a macro with relative reference, Excel records the actions based on the position of the active cell at the beginning of the macro. The recorded actions will adjust accordingly when the macro is run on different ranges. Using Relative ReferencesWe must make sure that the macro is recorded starting from the cell where the steps must begin in order to record a macro with relative references. ImplementationFollow the below steps to implemen
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox