EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Record Macro in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Record Macro in Excel

By Madhuri ThakurMadhuri Thakur

Record Macro in Excel

Record Macro in Excel (Table of Contents)

  • Record Macro in Excel
  • How to Record Macro in Excel?

Record Macro in Excel

Macro, by definition, is a set of instructions, which are used to perform a particular task.

Macro is an automatic repetitive task sequence that is used to replace a repetitive task of keystrokes and mouse actions. Basically, what we repeatedly do in an excel project, we can use a macro to do that for us. So, for example, if we have few formulas that will be repeated, whether there is any data change or not, we can use a macro to do that for us instead of us doing the same task repeatedly and again.

In technical terms, Macro enables a user to trim downtime normally used in repetitive tasks. Macros can contain functions, formulas, logics, etc.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Macro is created by recording a sequence of keyboard and mouse actions using the built-in macro recorder function in Excel. When a macro is recorded, it can be used again by assigning a keyboard shortcut or from the View macro function.

There are two ways to use Excel Macros:

  • Macro Recorder: We can record our steps or certain tasks by excel record macro function, call it whenever required by any assigned hotkey, or call it from View Macros.

As in Example 1, we created a macro to fetch the department list of employees.

Record Macro

  • VBA Editor: In the VBA editor, we write a set of codes in VB language and perform certain tasks in the worksheet.

As in Example 2, we prompted the user about the new code and called in the other macro to fetch the department list.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,768 ratings)

VBA Editor

How to Record Macro in Excel?

Record Macro in Excel is very simple and easy to create. Let’s understand the working of Record Macro in Excel by some examples.

You can download this Record Macro Excel Template here – Record Macro Excel Template

Record Macro in Excel – Example #1

In a workbook, we have two sets of data in two different worksheets. For example, from sheet 2, we want some data in sheet 1. Normally we use V-lookups to find the data. But we can use the macro recorder to do the same for us.

data in sheet 1

This is data in sheet 1 and data in sheet 2 shown as below,

data in sheet 2

Suppose we want the Department Name in Sheet 1 for the respective employees. Now we can use the simple V-lookup function for this.

V-lookup function

The Output is:

Result of V-lookup function

But if the sheet is in the same format and the data gets changed, then we have a repeated task repeatedly. Macro in Excel comes in handy here. We can record the same steps using the excel macro tool and make excel do the same tasks for us. So let us learn to use the Excel Macro Recorder tool.

  • Firstly, Go to Views Tab, and in the rightmost corner, there is an option for macros.

Record Macro Example 1-4

  • Click on Record Macro.

Record Macro Example 1-5

  • A dialog box pops up.

Record Macro Example 1-6

  • In the dialog, Box there is a box to name the macro and assign a shortcut key. So we can write a small description of the macro too.

Record Macro Example 1-7

(Tip: Do not use space in Excel macro Name)

  • Click ok, and we can see that Excel has started recording our sequence.

Record Macro Example 1-8

  • Now perform the tasks required to fetch the department name from sheet 2, i.e. V-lookup from sheet 1.

VLOOKUP Formula is applied in Sheet 1.

Record Macro Example 1-9

  • It gives the desired output in Sheet 1 as in Sheet 2.

Record Macro Example 1-10

  • Select the remaining cells and Press Ctrl +D to get rest results.

Record Macro Example 1-11

  • Now our task is complete; we click on stop Recording.

Record Macro Example 1-12

  • Under the View tab in the macros section, we can check that our macro has been recorded from the view macros option.

Record Macro Example 1-13

  • If we click on the view macros option, it shows the macro name which we added. Here it is, Dept_List.

Record Macro Example 1-14

  • Delete the data under Dept Column to check whether our macro is working or not.

Record Macro Example 1-15

  • Press CTRL + D and see the result.

Press CTRL + D

We have successfully recorded and tested our first Macro.

Record Macro in Excel – Example #2

Prompt a message to run the above V-lookUp Macro.

Example 1 was a tool by excel which is used to record a macro. Now we will use VBA to create a macro. For this, we need to activate the Developer tab.

Go to Files and then to options.

Step 1

There is a checkbox in the customize ribbon toolbar on the right-hand side with the name “Developer”. Mark the checkbox as a check and click ok.

Step 2

Before getting started with VBA, we need to get some basics.

A VBA Code contains variables, declarations, logic, and functions, which sums up as a whole code.

First, we need to follow some steps to create a macro-code.

  • Select the workbook in which we want to use the macro. In the current example, our target workbook is sheet 1.
  • In the developer, Tab clicks on Visual Basic, Which opens a VB Editor for us.

Step 3

  • Click on the Object where we want to write our code: sheet 1, and insert than to Insert Module.

Step 4

  • Now we can start writing code in the Module. But, first, we open our function by SUB() function.

Tip: We use sub () procedure as its main purpose to carry out a particular task or action.

Record Macro Example 2-4

  • Now write the following code in the Sheet.

Record Macro Example 2-5

The message box function gives a prompt to a user as VBA Code, and then macro, which was recorded as Dept_List, will be called.

  • When we click on Run in the VBA Editor, it gives us the following prompt.

Record Macro Example 2-6

Record Macro Example 2-7

  • We Select VBA Project Module4. New Code as the Macro to Run and excel first gives us a prompt and then the Department List.

Record Macro Example 2-8

Record Macro Example 2-9

Then the Department List will be updated,

Record Macro Example 2-10

Tip: Excel with Macro should be saved as *.xlsm as Excel Macro-Enabled workbook to save the macros in it.

Explanation of Excel Macro:

Macros are used to reduce repetitive tasks, which are done on a daily basis. A set of tasks are recorded and can be accessed over and over. It can be accessed by a menu list or from some hotkeys defined by the user.

If we go by definition, Macro is a set of rules or patterns, which specifies how a certain input sequence should be mapped to a certain output sequence according to a defined procedure.

Things to Remember About Record a Macro in Excel

  • Macro Name should not contain space.
  • A macro should be saved as “Excel Macro-Enabled Workbook” and an extension as *.xlsm.
  • Before writing any VBA code, we need to activate the developer Tab by customizing the ribbon button in the options.
  • Excel Macro Recorder has its limitations, but we can virtually automate anything from Excel VBA.

Recommended Articles

This has been a guide to a Record Macro in Excel. Here we discuss how to create Record Macro in Excel examples and downloadable excel templates. You may also look at these useful functions in excel –

  1. PROPER Function in Excel
  2. Guide to VLOOKUP Function in Excel
  3. How to Use MIRR Excel Function?
  4. SUMIF Function in Excel – You Must Know
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
4 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Record Macro Excel Template

EDUCBA

Download Record Macro Excel Template

EDUCBA

डाउनलोड Record Macro Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more