- Macros automate repetitive actions in Excel, reducing errors and saving time.
- You can create them with the recorder and enhance them with VBA for added flexibility.
- Use relative references to accommodate different ranges and avoid redundant steps.
- Save as .xlsm and manage security to run macros with confidence.
If you find yourself performing the same tasks in Excel repeatedly, macros are your best friend for saving time. With them, you can automate entire sequences of actions (learn more about VBA programming ), from formatting to importing data or performing calculations, and trigger them whenever you want with a click or a keyboard shortcut.
When you create a macro, Excel records your clicks and keystrokes and faithfully re-executes them. You can then tweak the code to fine-tune the execution and consult resources for Excel . It's common to start with the macro recorder and then refine it with VBA, achieving greater control, flexibility, and zero errors due to carelessness.
What exactly is a macro in Excel?
A macro is a list of instructions that Excel executes in a defined order . It can be as simple as applying a percentage style or as complex as preparing a report with pivot tables , formatting it, creating worksheets, and sending an email in Outlook. Macros are saved in your workbook and can be run whenever needed.
You don't need to know how to program to get started: the recorder translates your actions into VBA code. Even so, some VBA knowledge will allow you to create custom functions, forms, and even mini-applications within Excel.
Why use macros? Real advantages
The main purpose of a macro is to avoid repetitive tasks and minimize errors . If you prepare the same report every month, a macro will save you from the mechanical steps and allow you to focus on what's important.
Its benefits include time savings, consistent execution (without missed steps), and the ability to create shortcuts and buttons to speed up processes . If you only use Excel occasionally, it might not be worth it; but if you use it daily, you'll notice the difference from day one.
Enable the Developer tab (Windows and Mac)
The Developer tab is hidden by default. In Windows, right-click the ribbon and select "Customize the Ribbon." Check "Developer" and click OK. After that, you'll see the Developer tab next to View.
On a Mac, go to Excel > Preferences > Toolbars & Ribbon. Under "Main Tabs," select "Developer" and save. This will give you access to the recorder, VBA, and controls . If you're new to Excel, review the basic Excel guide to familiarize yourself with the ribbon and its options.
How to create macros with the Recorder
The recorder is perfect for getting started. In Developer > Record Macro (or Alt+T+M+R on Windows), assign a descriptive name. Remember: the name must begin with a letter, without spaces or symbols.
Choose where to save: "This workbook" will work in most cases. If you want to keep it always available, select "Personal Macro Workbook" and Excel will create/save it in Personal.xlsb , which opens hidden with Excel.
Optionally, add a shortcut. To avoid conflicts (for example, losing Ctrl+Z), it's recommended to use shortcuts with Ctrl+Shift. You can also write a short, clear description (very useful when you have many macros).
Click OK and perform the actions to be recorded. For example: select A1 and apply the "Percentage" format. Avoid unnecessary clicks or selection changes that are not part of the intended process, because the recorder records almost everything.
When finished, stop recording. To run it, go to Developer > Macros (or Alt+F8), select your macro, and click Run. You'll see Excel repeat the steps in the exact same order.
Run, edit, and manage your macros
From Developer > Macros (Alt+F8) you can run, edit, or delete macros. If you choose Edit, the Visual Basic Editor will open with the recorded code, which you can clean up and optimize (it often includes redundant steps).
Assigning a macro to a button or shape is very convenient: insert a shape, right-click > Assign Macro, choose the macro, and you're done. When you click the shape, it will run instantly.
To keep it readily available on the Quick Access Toolbar, open the toolbar menu (down arrow) > More Commands > under "Commands available in", choose "Macros", add yours, and change the icon if you wish. Now you'll have a permanent button even when you're not in Developer mode.
If you need to reuse the macro in another workbook, you can copy the module from the Visual Basic Editor. Open VBE (Alt+F11), drag the module to the other open workbook, or use Export/Import .bas file.
Programming macros in Excel with the Visual Basic (VBA) Editor
To go beyond the recorder, open the Editor with Alt+F11. Insert a Module (Insert > Module) and create your first subroutine. The basic pattern is Sub Name() … End Sub.
For example, to display a message and write a value in cell A1, you could start with something simple. This way you understand the structure and how to call the macro from Excel (Alt+F8) or assign it to a button, with very few lines of code :
Sub Primera_Macro()
Range("A1").Value = "Esto de las macros mola."
MsgBox "Hola, querido amigo."
End Sub
Another quick example of applying the percentage style to the current selection. This macro replicates what you did with the recorder , but typed directly:
Sub FormatoPorcentaje()
Selection.Style = "Percent"
End Sub
The ideal combination is: record to speed things up, then open the VBE to clean up unnecessary steps, add variables, conditions, or loops. This way, you turn a "literal" recording into a robust and reusable macro.
Absolute vs. relative references
By default, the recorder creates macros in absolute mode: it always acts on the same cells (for example, B3) regardless of where you are when running it. If the data changes position in your process, this may not be what you need.
With "Use relative references", the macro works relative to the active cell when execution begins. For example, if you started recording in cell B3 and typed "First Name", "Last Name", etc., moving to the right, when running from cell C4, it will type the same information relative to cells C4, D4, E4, etc.
A typical example: adding titles to a table that sometimes starts in cell B3 and sometimes in cell C5. With absolute mode, it would always write in cell B3. With relative mode, it will adapt to the selected starting cell.
Common mistakes when recording processes
A common pitfall is recording a formula "drag" for, say, three rows, and thinking it will work for longer tables. The recorder will register something equivalent to " fill three cells down ," not "to the end of the table."
The solution is to change your approach: use dynamic ranges (for example, detecting the last row), loops, or the CurrentRegion method, or adjust the macro in VBA after recording it so that it scales to any size.
Good practices when recording
Before recording, run through the process manually a couple of times to refine it. Make short, specific recordings instead of one huge one; this makes them easier to maintain and combine.
Avoid unnecessary clicks, scrolling through the ribbon, and selections that don't contribute anything. Keep the macro name and description clear so you or any colleague can instantly identify its purpose.
Run macros in many ways
In addition to the Macros dialog box (Alt+F8), you can launch macros using a shape, a button on the Quick Access Toolbar, or by opening the workbook. You can even have Excel run the macro from other Office apps, allowing you to automate cross-application tasks (for example, updating a table and sending an email via Outlook).
Think carefully about keyboard shortcuts: if you assign Ctrl+Z to a macro, you'll lose the ability to Undo while the workbook is open. Therefore, it's recommended to use Ctrl+Shift combinations to avoid conflicts.
Saving workbooks with macros
When a file contains VBA code, save it as an .xlsm file so the code is preserved and can be run. Saving as an .xlsx file will destroy the VBA project . If you want global macros, use the Personal Macro Workbook (Personal.xlsb).
Security and enabling macros
Macros may be blocked depending on your security settings. Check the Trust Center to enable them selectively, and never enable macros from unknown sources. Understanding your security level helps you balance protection and productivity.
Assign macros to buttons, shapes, and controls
To enable users to run macros automatically, assign a macro to a button, shape, or control. Right-click the object > Assign Macro > select. You can also place an icon on the ribbon or Quick Access Toolbar and link it to the macro.
If you use forms or ActiveX controls, you can assign macros or event procedures (for example, on click) to them. This allows for more user-friendly interfaces and guided processes.
Useful macro examples (VBA)
These examples illustrate common tasks. You can paste them into a Module (Alt+F11 > Insert > Module) and adapt them. Remember to sign the workbooks or adjust the security settings if your organization requires it so you can run them without warnings.
Copy the selected content to another location on the same sheet: Selection.Copy Destination
Sub CopiarSeleccion()
If TypeName(Selection) = "Range" Then
Selection.Copy Destination:=Selection.Offset(0, 2)
Else
MsgBox "Selecciona un rango de celdas primero."
End If
End Sub
Print the active sheet: ActiveSheet.PrintOut
Sub ImprimirHojaActual()
ActiveSheet.PrintOut
End Sub
Save the file as a macro-enabled workbook (.xlsm): ActiveWorkbook.SaveAs
Sub GuardarComoXlsm()
Dim ruta As String
ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
Apply formatting to a range (adjust the range to your needs): With Range(…)
Sub FormatoRango()
With Range("A1:D10")
.Font.Bold = True
.Interior.Color = RGB(230, 230, 250)
.Borders.LineStyle = xlContinuous
End With
End Sub
Find a value in a range and display the first match: Range(…).Find
Sub BuscarValor()
Dim celda As Range
Dim valor As String
valor = InputBox("¿Qué valor quieres buscar?")
If valor = "" Then Exit Sub
Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
If Not celda Is Nothing Then
MsgBox "Encontrado en: " & celda.Address
celda.Select
Else
MsgBox "No se encontró el valor especificado."
End If
End Sub
Practical case: formatting as a percentage
Recording: Start recording, name it, choose where to save it, and optionally assign a shortcut and description. Select the cells and apply "Percent" from the Home tab. Stop recording and then run it back with Alt+F8. If you want to play it back with minimalist code, use Selection.Style = "Percent" in VBA.
Tip: During recording, avoid switching cells unnecessarily. These back-and-forth movements are recorded and can slow down and corrupt the macro.
Automate long tasks: small macros are better
If you have a process with many steps, consider dividing it into several smaller macros and calling them sequentially. This makes maintenance easier, allows you to reuse blocks , and simplifies debugging.
For example, one macro to prepare data, another to format, another to generate a printout, and another to export to PDF or send by email . You can call them one by one or all at once.
Edit and clean up the recorded code
The recorder captures "everything": selections, clicks, and tab changes. When the VBE is opened, it removes unnecessary selections (Select/Selection) by replacing them with direct references (for example, Range("A1").Value = ... ) and groups formats into With/End With blocks.
In addition, it adds variables and basic error handling. A couple of if statements and simple checks prevent errors when the range is empty or the selection is not as expected.
FAQ
How do I create a UserForm in Excel? Open the Visual Basic Editor (Alt+F11), go to Insert > UserForm, and add controls (text boxes, buttons, etc.). You can display it using a macro.
Sub MostrarFormulario()
UserForm1.Show
End Sub
From there, assign procedures to the events of the controls (for example, a button click) to execute the logic you need.
How do I protect/unprotect a sheet with macros? Use these routines by changing the password as desired. They allow you to activate or deactivate protection with a single click.
Sub ProtegerHoja()
ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub
Sub DesprotegerHoja()
ActiveSheet.Unprotect Password:="segura"
End Sub
How do I delete a macro? Go to Developer > Macros, select the macro, and press Delete. If you want to delete all the code from a module, open the VBE (Alt+F11), locate the module in the project, right-click, and select Remove Module (you can export it first if you want to save it).
One last practical tip: If you format a monthly report where customers with outstanding balances should be highlighted in red and bold, record the sequence once, adjust the VBA code to apply to the correct range (using last-row detection or filters), and save the macro in your personal macro workbook. Next time, a simple shortcut or button will be all it takes to apply the formatting instantly, flawlessly, in seconds.
