Recording Macros in Excel

Publication Date :

Blog Author :

Edited by :

Download FREE Recording Macros Excel Template and Follow Along!
Recording Macros Excel Template.xlsx

Table Of Contents

arrow

How to Record a Macro in Excel?

To record a macro in excel, either the View tab or the Developer tab 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.

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 references.” 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 (FAQs)

1

What does recording macros in Excel mean?

Arrow down filled
2

Give some tips to record macros in Excel.

Arrow down filled
3

What are absolute and relative references while recording macros?

Arrow down filled