Excel Macro Tutorial for Beginners

Key Takeaways

• An Excel macro is a recorded sequence of actions that automates repetitive tasks, saving you significant time on formatting, data entry and reportin
• You can create your first macro in minutes using Excel's built-in Record Macro feature, with no programming experience required.
• Understanding how to enable, record, run, save and edit macros gives you a foundation for more advanced automation with VBA.
• Download our free practice file to follow along with every step in this Excel macro tutorial.

If you spend time in Excel doing the same formatting, data entry or reporting tasks over and over, an Excel macro can do that work for you in seconds. A macro records a sequence of actions you perform in a spreadsheet and replays them on command, letting you automate repetitive tasks without writing a single line of code.

This Excel macro tutorial for beginners walks you through everything you need to get started: enabling macros, recording your first one, running it multiple ways, saving it properly and even peeking at the code behind it. No programming experience is required.

To follow along using our example, download.

What Is a Macro in Excel?

Think of a macro as a small program that performs several tasks in sequence. You can "write" this program by simply recording the steps for each task as you are doing them. When you play back the recording, the program runs and the steps are repeated.

The programming language that executes macro programs is called Visual Basic for Applications, or VBA. When you use the macro record feature, Excel is helping you write a VBA program behind the scenes. Advanced VBA programmers can write very complex macros that perform tasks beyond what a user can simply record by hand.

For beginners, it helps to understand that there are two broad categories of macros:

Most people start by recording macros and then gradually learn to read and edit the VBA code Excel generates. That progression is exactly what this tutorial covers.

Common tasks people automate with macros include:

• Formatting reports with consistent fonts, colors and borders
• Cleaning and reorganizing imported data
• Inserting standard headers or footers across multiple sheets
• Generating summary calculations on a recurring basis
• Bulk-renaming worksheets or copying data between workbooks

When to Use Excel Macros

A repetitive task that involves several steps is the perfect candidate for a macro. Do you have to re-format a report every week from a vendor into your department's style and formats? Are you updating an old document to include new data line by line? Do you type the same long company name over and over?

Ask yourself: Have I performed this same set of steps three times, and do I anticipate I will do it at least five more times? If the answer is yes to both, then it is probably worth your time to record a macro.

Here are a few signs a task is a good macro candidate:

• You perform the same sequence of clicks or keystrokes on a regular schedule
• The task involves multiple steps that must happen in the same order every time
• Manual repetition increases the risk of errors or inconsistencies
• The task is tedious enough that you dread doing it
• Other team members need to replicate the same process on their own files

Real-world scenarios where macros shine include standardizing column widths and header formatting on weekly vendor reports, stripping extra spaces and fixing capitalization in imported data, generating a summary sheet that pulls totals from multiple tabs and populating a template with updated figures each month.

How to Enable Macros in Excel

Before you can record or run a macro, you need to make sure Excel is set up to allow them. This involves two things: making the Developer tab visible on the ribbon and confirming your macro security settings.

How to Show the Developer Tab

The Developer tab contains all the macro tools you will use in this tutorial. It is hidden by default in most Excel installations. To enable it on Windows:

1. Click on the File tab.
2. Click Options.
3. Click Customize Ribbon.
4. Put a checkmark by Developer in the Main Tabs pane.
5. Click OK.

On Mac, go to Excel > Preferences > Ribbon & Toolbar, then check the box next to Developer.

Once enabled, the Developer tab appears on the ribbon with buttons for Record Macro, Macros, Visual Basic and more.

Macro Security Settings

Excel disables macros by default to protect you from potentially harmful code. You can adjust this behavior in the Trust Center:

1. Click the File tab.
2. Click Options.
3. Click Trust Center, then click Trust Center Settings.
4. Click Macro Settings.

You will see four options:

• Disable all macros without notification - blocks all macros silently
• Disable all macros with notification - blocks macros but shows a prompt so you can choose to enable them (recommended for most users)
• Disable all macros except digitally signed macros - only allows macros from trusted publishers
• Enable all macros - runs all macros without prompting (not recommended due to security risks)

For most beginners, "Disable all macros with notification" strikes the right balance. You stay protected while still being able to run macros in workbooks you trust.

How to Record a Macro in Excel

Now that your Developer tab is visible and your security settings are in place, you are ready to create your first macro. Recording a macro is as simple as clicking a button, performing your tasks and clicking stop.

1) Click on the Developer tab and look for the Record Macro button in the Code group.

2) Fill out the Record Macro dialog box.

3) Click OK. Excel is now recording every action you take.

4) Perform the steps that you wish to record. Work carefully and in order, because every click and keystroke is captured.

5) When you are finished, click the Stop Recording button on the Developer tab or the status bar at the bottom of the Excel window.

That is all it takes to create a macro in Excel. The macro is now stored and ready to run whenever you need it.

Tips for the Record Macro Dialog Box

The dialog box you see in step 2 has four fields that are worth understanding:

• Macro name must begin with a letter and cannot contain spaces, symbols or punctuation. Use descriptive names like FormatWeeklyReport or ClearDataEntry.
• Shortcut key lets you assign a keyboard shortcut to trigger the macro. Warning: if you choose a key combination that Excel already uses, the macro will override it. Do not choose Ctrl+C, for example, or you will lose the copy shortcut.
• Store macro in determines where the macro lives. Choose "This Workbook" to keep it in the current file. Choose "Personal Macro Workbook" if you want the macro available in every workbook you open. Choose "New Workbook" to store it in a fresh file.
• Description is optional but highly recommended. Write a brief note about what the macro does so you (or a colleague) can identify it later.

The Personal Macro Workbook (PERSONAL.XLSB) is a hidden workbook that opens automatically every time you launch Excel. It is the best storage location for macros you use across many different files, like inserting a standard company header or applying your department's formatting.

How to Run a Macro in Excel

Once you have recorded a macro, there are several ways to run it. Choose the method that fits your workflow:

• From the Developer tab Macros dialog box
• With a keyboard shortcut
• By assigning the macro to a button or shape in your worksheet

Running a Macro from the Developer Tab

1. Click the Developer tab.
2. Click the Macros button.
3. Select the macro you want in the dialog box.
4. Click Run.

This is the most straightforward method and works for any macro stored in the current workbook or your Personal Macro Workbook.

Running a Macro with a Keyboard Shortcut

If you assigned a keyboard shortcut when you recorded the macro, simply press that key combination to run it instantly. For example, if you assigned Ctrl+Shift+F, pressing those keys will execute the macro.

To assign or change a shortcut after recording:

1. Click the Developer tab and click Macros.
2. Select the macro and click Options.
3. Enter or update the shortcut key and click OK.

Assigning a Macro to a Button or Shape

For macros you run frequently, adding a clickable button directly in your worksheet is convenient:

1. Click the Developer tab.
2. In the Controls group, click Insert, then select Button (Form Control).
3. Draw the button on your worksheet by clicking and dragging.
4. In the Assign Macro dialog box that appears, select your macro and click OK.
5. Right-click the button to edit its label text (for example, "Format Report").

You can also assign a macro to any shape. Insert a shape from the Insert tab, right-click it, select Assign Macro and choose the macro you want. This lets you create visually appealing run buttons that match your workbook's design.

How to Save a Macro-Enabled Workbook

Because macros are programs running within your Excel document, you will need to save the workbook as a macro-enabled workbook file type. This tells Excel to load the programs when it opens the workbook again. If you accidentally save as a standard .xlsx file, Excel will strip out all your macros without warning.

To save a macro-enabled Excel file:

1. Click the File tab.
2. Click Save As.
3. Browse to the folder where you wish the workbook to be saved.
4. Choose Excel Macro-Enabled Workbook (.xlsm) in the Save as type dropdown menu.
5. Type a name for the file and click Save.



The key distinction is the file extension: .xlsm files preserve macros, while standard .xlsx files do not. Always double-check the file type before clicking Save.

If you stored a macro in the Personal Macro Workbook (PERSONAL.XLSB), that file saves automatically when you close Excel. You do not need to save it manually. Any macros stored there will be available the next time you open Excel, regardless of which workbook you are working in. 

Practical Excel Macro Examples

The best way to learn macros is to try them. Below are two beginner-friendly examples you can record yourself or paste into the VBA Editor. To see a working example in action, download MacrosForBeginners.xlsm and follow the steps above to run FormatEmployeeSheet.

Example 1: Auto-Format a Report

This macro selects a data range, applies bold headers, adds borders and auto-fits column widths:

Sub FormatReport() 

Range("A1:E1").Font.Bold = True 

Range("A1:E20").Borders.LineStyle = xlContinuous 

Columns("A:E").AutoFit 

End Sub

Here is what each line does:

• Range("A1:E1").Font.Bold = True - makes the first row bold so headers stand out
• Range("A1:E20").Borders.LineStyle = xlContinuous - adds solid borders around every cell in the range
• Columns("A:E").AutoFit - resizes columns A through E to fit their contents

Example 2: Clear a Data Entry Form

This macro resets specific cells so a form is ready for the next entry:

Sub ClearForm() 

Range("B2:B10").ClearContents 

Range("B2").Select 

End Sub

• Range("B2:B10").ClearContents - erases the values in cells B2 through B10 without removing formatting
• Range("B2").Select - moves the cursor back to the first input cell so the form is ready to use

Try recording your own version of these examples first, then open the VBA Editor to compare your recorded code with the examples above. You will start to see patterns in how Excel translates your actions into code.

How to Edit and Manage Macros

Once you have recorded a few macros, you will eventually want to tweak them, review what they do or remove ones you no longer need. Excel makes all of this accessible through the Macros dialog box and the VBA Editor.

Viewing and Editing Macro Code

To open the VBA Editor and see the code behind any recorded macro:

1. Click the Developer tab.
2. Click the Visual Basic button (or press Alt+F11).
3. In the Project Explorer pane on the left, expand your workbook and open the Modules folder.
4. Double-click the module (usually Module1) to view the code.

You will see your recorded macro wrapped between Sub MacroName() and End Sub. Everything in between is the sequence of actions Excel captured. A few key VBA elements to recognize:

• Sub and End Sub mark the beginning and end of a macro
• Range("A1") refers to a specific cell or range
• Selection refers to whatever is currently selected
• .Font, .Borders and .Interior control formatting properties

Editing recorded code is one of the fastest ways to learn VBA. Try changing a cell reference or a formatting value, then run the macro to see the result.

Deleting a Macro

To remove a macro you no longer need:

1. Click the Developer tab.
2. Click the Macros button.
3. Select the macro you want to delete.
4. Click Delete and confirm.

It is good practice to remove macros you are no longer using, especially before sharing workbooks with colleagues. This reduces file size and eliminates potential security concerns.

Next Steps for Building Your Excel Skills

Recording your first macro is a milestone, and it opens the door to much more powerful automation. Once you are comfortable recording and editing macros, the natural next step is learning to write VBA code from scratch. VBA lets you add decision-making logic, loop through thousands of rows, create custom dialog boxes and build interactive dashboards that go far beyond what the recorder can capture.

VBA remains one of the most widely used automation languages in business. Finance professionals, government agencies, healthcare organizations and logistics teams rely on it daily, and the skill continues to be in high demand.

If you are ready to take your Excel skills further, Pryor Learning offers hands-on training courses that cover everything from intermediate Excel techniques to VBA programming. Whether you prefer live seminars or self-paced online learning, structured training can accelerate your progress and help you build real confidence.