Recording Macros in Excel

Recording macros is a method whereby excel stores the tasks performed by the user. Every time a macro is run, these exact actions are performed automatically. Macros are created in either the View tab (under the “macros” drop-down) or the Developer tab of Excel.

How to Record a Macro in Excel?

To record a macro in excel, either the View tab or the Developer tabDeveloper TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options more can be used, as shown in the following images.

excel macros example 1
excel macros example 1-1

Let us consider an example to understand the steps to create an excel macro.

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

The following table contains the overall grades of students. The report is to be formatted on a daily basis. We need to perform tasks like applying borders, highlighting headers, setting background colors, and so on.

We want to record a macro for these repetitive tasks.

excel macros example 1-2

The steps to be performed for recording macros are listed as follows:

  1. In the Developer tab, select “record macro” under the “code” section.

    excel macros example 1-3

  2. The “record macro” dialog box opens, as shown in the following image. In “macro name,” enter “formatting.”

    Note: It should be ensured that no space is entered while naming the macro.

    excel macros example 1-4

  3. In “shortcut key,” assign the shortcut key “F” to the macro. This shortcut runs the macro.

    excel macros example 1-5

  4. In “store macro in,” select “this workbook.” With this selection, the macro is stored in the present workbook. Moreover, this macro will work only in the current workbook.

    excel macros example 1-6

  5. In “description,” type what the macro intends to do.

    excel macros example 1-7

  6. Click “Ok” and the “record macro” option changes to “stop recording” under the “code” group.

    excel macros example 1-8

  7. At this time, the actions we perform are recorded in the macro. To ensure that the automated tasks are executed every time a new range is selected, activate “use relative referencesRelative ReferencesIn Excel, relative references are a type of cell reference that changes when the same formula is copied to different cells or worksheets. Let's say we have =B1+C1 in cell A1, and we copy this formula to cell B2 and it becomes more.” This is done prior to creating the macro.

    example 1-9

  8. Once the steps of formatting like applying borders, highlighting headers, setting background colors, etc., have been recorded, click “stop recording” (shown in the sixth point).

  9. Apply recorded formatting with the help of the shortcut key “Ctrl+Shift+F.” The output is shown in the following image.

    example 1-10

Using Recorded Macros

There is another table containing the grades of students. It has to be formatted in the same way the data of example #1 was formatted.

example 1-11

Select A1 and press “Ctrl+Shift+F” for formatting the selected cells. Hence, custom formatting is applied in one go without carrying out multiple commands.

example 1-12

The limitation of the macro recorder is that it is full of unnecessary codes.

The Cautions While Recording a Macro

The following points must be remembered while recording a macro:

  • The file containing macros must be saved with the “.xlsm” extension.
  • While downloading an Excel file with the “.xlsm” extension (macro-enabled workbook), it should be ensured that the source is authentic.
  • The “auto sum” function should not be used while creating a macro because it hard codes the formula.
  • The formula should be typed with a single dollar sign like “=SUM(E$2: E10).” The macro recorder records E$2 as a fixed reference and begins the SUM directly below the “row 1” headings. It recognizes E10 as a relative reference and points directly above the current active cell E11.

Frequently Asked Questions

1. What does recording macros in Excel mean?

To avoid doing the repetitive tasks again and again, Excel provides a way of automating them. The steps like cell formatting, number formatting, saving workbooks, deleting worksheets, importing data, and so on are recorded as macros.
A macro is created using the macro recorder and can be replayed whenever required. The macro can be run, edited, copied (in another workbook), assigned (to a button or object), enabled, and disabled. The Excel macro code can be viewed and edited in Visual Basic EditorVisual Basic EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft more (VBE).

2. Give some tips to record macros in Excel.

The tips to record macros in Excel are listed as follows:
• Relative referencing must be activated to ensure that the macro works with new selections.
• Record a macro only if you are familiar with the steps. If the shortcut key “Ctrl+Z” is pressed, it is recorded. So, a smooth recording is essential for running the macro efficiently.
• A backup of the workbook must be created before running a macro. This helps prevent unwanted changes to the current file.
• Instead of a large macro, create several short ones. This is because shorter macros are easy to understand and allow error-free recording.

3. What are absolute and relative references while recording macros?

The two references are explained as follows:
– If a macro is recorded using absolute referencesAbsolute ReferencesAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute more, the VBA code will always refer to the same cell selection. This selection is used every time a macro is run, irrespective of the current active cell. For instance, cell A2 is selected, text “hello” is typed, and the “enter” key is pressed. The code will always begin by selecting cell A2.
– If a macro is recorded using relative references, the VBA code will record the movement rather than the exact cell selection. Working on the preceding example, if cell K2 is selected, Excel will move relative to cell K2. So, Excel will not work on cell A2.

Note: By default, a macro is recorded using absolute references.

Key Takeaways
  • Recording macros is a method whereby Excel stores the tasks performed by the user.
  • To run a macro implies that the exact recorded actions are executed automatically.
  • A macro can be recorded with the help of either the View tab or the Developer tab.
  • No space should be entered while naming the macro.
  • Prior to creating a macro, “use relative reference” must be activated to switch on relative referencing.
  • The file containing macros must be saved with the “.xlsm” extension.

Recommended Articles

This has been a guide to Record a Macro in Excel. Here we learn how to create a Macro in Excel along with Excel example and downloadable Excel templates. You may also look at these useful functions of Excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *