Ninja Excel BlogMacros in ExcelHow to make macros in Excel

How to make macros in Excel

Man working on computer

Macros in Excel: The basics step by step

What is a macro in Excel?

A MACRO It is an action or series of actions that can be executed as many times as you want, since the instructions are saved within an Excel Workbook to be used whenever you need. With macros in Excel you can create and modify functions, forms, tables, complex applications, etc. It's like any Excel function but you can program it to do what you want.

What are macros for in Excel?

Purpose: A Macro allows you to automate repetitive Excel tasks. With a single click, all the tasks you leave scheduled are executed.

Advantages: You avoid errors, optimize your time and give you greater order to work. 

How to enable macros in Excel?

Before creating the macro, you must have the “Programmer” tab visible in Excel.

If you have Windows follow these steps:

  1. Right-click anywhere on the ribbon and click “Customize the Ribbon.”Right click to install programmer tab
  2. When the options box opens, in the box on the right, click on the “Programmer” square. Add developer tab
  3. Click “Accept”.
  4. You will find the “Developer” tab next to the “View” tab.

If you have a Mac, follow the following steps:

  1. Click on “Excel” (top left)
  2. Click on “Preferences”
  3. Select the “Toolbar and Ribbon” section
  4. When the options box opens, in the box on the right, click on the “Programmer” square
  5. Click “Save”.
  6. You will find the “Developer” tab next to the “View” tab.

How to make macros in Excel?

There are two ways to create a macro in Excel, it can be through:

  1. Macro recorder: This tool is ideal for those who do not know how to program but want to use macros. Under this mode, Excel records the clicks and keys you press to later follow the same instructions. This article will explain how to apply this.
  2. Visual Basic for Applications (VBA): is the language in which macros are programmed. Excel has a VBA editor that allows the creation and modification of Macros through this language. If you know some programming or know VBA this may be preferable. To delve deeper into how to create macros with VBA you can see the article “Mastering VBA”.

Ninja Tip: A common and very useful practice is to complement both: make a recording to quickly create a first version of the macro and then make some changes through VBA, thus having more options and flexibility.

Ninja Tip:  When you want to save an Excel Workbook with macros, it must be saved as .xlsm, an Excel file that supports macros.

In the following video you can see an example of a macro and better understand what they are:

Recording macros

To start, let's create a macro that changes the cell format to percentage.

Ninja Tip: Macro names must start with a letter, spaces or symbols are not accepted in the names.

  1. Select “Record Macro” within the “Scheduler” tab.Click on record macro
  2. To save your macro, you must first name it. Choose a short name that describes well what it does and is easy to identify when you have many macros. 

Ninja Tip: Macro names must start with a letter, spaces or symbols are not accepted in the names.

  1. Choose where to save the macro. It is usually saved in the same Excel workbook.

Ninja Tip: If you save the macro in this Workbook and want to use this macro in another workbook, you only need to open this workbook to access the macro. If you save it in “Personal Macro Workbook” the macro will be available in all your Excel workbooks.

Choose book for the macro
  1. Optionally, you can create a keyboard shortcut. A shortcut is a combination of keys assigned to run the macro. 

Ninja Tip: Even though it is useful for an action that is done very often, you must be careful that it is not repeated with existing shortcuts. You can make shortcuts with Ctrl+Shift+letter to avoid this type of problem.

  1. Optionally, you can add a description. It is useful when there is more than one person who will work with these macros.Write the description of the macro we created
  2. Click on ”Accept”.
  3. Then, you must do the actions you want to repeat. In this case, click on Home and then on percentage format. Do not select another cell or select A1 again because it changes the macro configuration, the “Mastering VBA” article goes into depth about that topic. First macro: choose percentage format
  4. Select “Stop Recording.”First Macro: Stop macro recording
  5. Ready! You've already made your first macro!

You can see another example of how to create a macro in the following video:

Then, to implement the Macro:

  1. Select the cells to which you want to apply the macro and click on “Macros” in the “Developer” tab.

Ninja Tip: You can use Alt+F8 instead of clicking on “Macros”

Click on Macros to run it
  1. Select the macro you want to run and click “Run”.Select the macro to run
  2. That's all! The results are already seen.
Results first macro executed

You can also create a button that when clicked on it executes a macro. You can watch the following video to learn how to do it:

Program macros in Excel with Visual Basic Editor

  1. Select Visual Basic on the Developer tab.
Program macros in excel with visual basic editor
  1. The Visual Basic Editor will open and we must create our first macro. First we must open our sheet in which we will work, for this, click on the sheet you want the macro.
Program macros in excel with visual basic editor
  1. Now we will begin by creating the macro, for this we will indicate the subroutine with the word “Sub” and then the name of the macro, in our case, MyFirstMacro. 
Program macros in excel with visual basic editor
  1. We click on Enter so that the parentheses at the end of the name and End Sub appear automatically, which indicates the end of the subroutine, that is, the end of the macro.
Program macros in excel with visual basic editor
  1. Now between Sub and End Sub we must write the macro code. As we are looking to insert a percentage into the cells, we must write the code Selection.Style = “Percent”Program macros in excel with visual basic editor
  2. We return to Excel by clicking on the Excel icon and that's it! We have already programmed our first macro.
Program macros in excel with visual basic editor
  1. Now, we must run our macro for student attendance, we select the attendance cells and click on Macros.
Program macros in excel with visual basic editor
  1. A dialog box of our macros will open, we select the macro that we want to execute, in our case MyFirstMacro and we click on execute.
Program macros in excel with visual basic editor
  1. Ready! Now we can see that the assists are in percentage format.
Program macros in excel with visual basic editor

Using relative references in macros

There are two types of references in macros:

  1. Absolute reference: The cells you select when recording the macro will remain fixed. That is, each time you run the macro the same cells will always be filled, regardless of which cell you have selected when you run the macro. The macro is recorded in absolute mode by default.
  2. Relative reference- The macro will be executed on the cells depending on which cell was selected when the macro was executed.

Ninja Tip: It is better to use relative references to have greater flexibility in macro execution.

For example, every week you export an Excel table to which you must periodically apply names to the columns. To do this, you record an Excel macro that allows you to fill in the names of these columns. Let's see what happens if I do an absolute macro versus a relative macro.

Record macros in absolute mode

  1. Click on “Record macro”, name it and save it in the corresponding Book. We will call it “MacroAbsoluteExample”.Absolute macro: recordAbsolute macro: name and book to save
  2. Then, click on B3 and fill in the column names accordingly and click on “Stop Recording”.Absolute macro: fill with data and stop recording
  3. Now, when you are faced with another table of the same type, go to another Book or tab and click on “Macros”.Absolute macro: run the macro
  4. Choose the macro you created and click “Run”.Absolute macro: Choose the macro and run it
  5. Notice that it is written In the same place than when we created the macro. That means that it is “absolute”: the rules are followed to the letter and the instruction in this case is that in B3 you write “First name”, in C3 you write “Surname”, etc. without considering which cell I have selected when executing the macro. In this case the macro did not meet the objective because the table started in C5 and not in B3.Absolute macro: executed and incorrect

Record macros in relative mode

  1. Click “Use relative references”.Relative Macro: choose the relative macro option
  2. You must select the cell you want to fill (B3), click on “Record macro”. Name it and save it. We will call it “MacroRelativeExample”. Relative Macro: start recording the macro
  3. Then, fill in the column names accordingly and click “Stop Recording.”Relative Macro: fill and stop recording
  4. Now, when you are faced with another table of the same type, go to another Book or tab and click on “Macros”.Relative macro: run the macro
  5. Choose the macro you created and click “Run”.Relative macro: select and run the macro
  6. Now it is seen that the instruction It was executed taking into account which cell you had selected when starting the macro. When creating the macro, it was started in B3 and written in B3, C3, D3 and E3. So, now when I selected C4 and ran the macro, it was written to C4, D4, E4 and F4. Thus the macro records that you have to write “First name” in the cell that was selected at the beginning and then write “Last name” in the cell that corresponds to the right.Relative macro: executed successfully

Common error in relative mode in macros

A common mistake when using macros in Excel is dragging formulas to fill a table and believing that relative mode will fill the entire table. For example, you created a macro that creates a new column with a formula. A common mistake is to drag to the end of the table and believe that the macro will work for a table with a different number of rows.

In this example, we use relative references to populate record the macro and populate the table.

Common mistake: record the macro

The formula was then put into D3 and dragged to fill the table.

Common Mistake: Drag to Fill

However, let's see what happens when we run our macro when faced with a different table:Common error: new table to apply macro

We see that when applying the macro, not all the cells in the table are filled, but only from E4 to E7. This happens because the recording understood the instruction “fill in the three cells below.” To solve this problem you can see the article “Mastering VBA”.

Common error: only four cells are filled with the macro, not the entire table

Add a macro to the toolbar

Adding a macro to the top toolbar is very useful when there are actions that you must perform very often.

  1. Create a macro, in this case, we will create one that writes the current date in cell A1. To do this, click on “Record macro”.Pin to bar: record the macro
  2. Name and choose which workbook you are going to save the macro in.

Ninja Tip: Regardless of where you choose to save the macro, the icon can be installed and will appear in all Excel Workbooks you open. If you save it in Book1 and want to run it in Book2, you will have to open Book1. If you want to avoid opening Book1 to use this macro, you can save to “Personal Macro Book.”

  1. In cell A1 write “=TODAY()” and click “Stop recording”.Pin bar: write macro functionPin bar: Stop macro recording
  2. Once the creation of the macro is complete, click on the down arrow in the upper green bar and a menu opens. In that menu select “More Commands”.Pin bar: top arrow and choose "more commands"
  3. In the “Commands available in:” section, select “Macros”Pin Bar: Choose Macros
  4. Select the macro you want in the toolbar. In this case MacroHoy. Click on “Add”.Pin bar: choose the macro to add
  5. You can press “Modify” to choose the icon for that macro. And click on “Accept”. Pin bar: choose macro icon
  6. Then, click “OK” again.Pin bar: click accept to pin macro to the bar
  7. Now you can see the macro icon on the top toolbar. By pressing the icon, the macro is executed.Pin Bar: Macro icon installed on the bar

Examples of macros in Excel

Macro to copy selected content

To copy cells in the same sheet in Excel write the following code:

Macro in Excel to copy selected content

Macro to print a sheet

To print the current Excel sheet, type the following code:

Excel macro to print a sheet

Macro to save file

To save a file, write the following code

Excel macro to save file

Macro to format a range of cells

To change the format of a range of cells, apply the following code and adjust it your way. 

macro in excel to apply a format to a range of cells

The range of cells must first be selected with the Range().Select code and then you can edit it in your own way.

Macro to search data in excel vba

To search a value in a database, write the following code. The macro will tell us the cell that first matches the searched value.

Macro in excel to search for a valueFirst we must indicate the range of cells in which we want to search and then determine the cell in which the searched value is found.


An Excel macro is an action or a set of actions that can be executed as many times as desired. It is a very useful tool as it allows you to automate repetitive tasks. It also reduces errors and work time.

Frequent questions

How to create a form in Excel with macros?

To create a form in Excel follow the following steps:

  1. On the Developer tab, select Visual Basic.
  2. In the Visual Basic Editor select the Insert tab and then click UserForm.
create a form in macros in Excel
  1. Ready! Now you can see the blank form in which you can add the elements you want.

How to protect a sheet with macros?

To protect an Excel sheet using macros, write the following code in the Visual Basic Editor. Then you must run the macro to be able to protect or unprotect it.

macros in excel how to protect a sheet

How to delete macros in Excel?

Within the Developer tab, click on Macros. In the dialog box, select the macro you want to delete and click the Delete button and that's it!

delete macro in excel

Frequent questions

How do you make a macro?

First you must have the “programmer” tab enabled, which by default is hidden in Excel, then you can record your macros

How to use the macro?

A Macro is a set of saved actions that allows us to execute them when we need them, in this way actions that are usually done manually repeatedly can be executed without having to do them manually

What are macros in Excel?

Macros are automated actions that can be repeated as many times as necessary, clicks and keystrokes are recorded and then repeated.

white ninja excel logo

The leading Excel training platform for companies.


Copyright © 2024 Ninja Excel.