How to Create a Macro in Excel with Visual Basic

Manually formatting reports every Monday morning or cleaning up the same data export every single day can really drag you down. If you perform the same set of clicks and keystrokes in Excel over and over, you can automate that work with a macro. This article will show you how to create your first macro, first by recording your actions and then by dipping your toes into the Visual Basic code that powers it all.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is an Excel Macro?

An Excel macro is simply a recording of a sequence of actions - clicks, keystrokes, formatting changes - that you can save and run whenever you need to perform that exact sequence again. It's the ultimate tool for automating repetitive tasks, from simple formatting to complex data cleansing operations.

The magic behind these macros is a programming language called Visual Basic for Applications (VBA). While a full-blown programming language might sound intimidating, Excel gives you an easy entry point: the Macro Recorder. It watches what you do and writes the VBA code for you, giving you a perfect starting point for learning and customization.

First, Set Up Your Excel for Macros

Before you can record anything, you need to enable the "Developer" tab in Excel's ribbon. This tab contains all the tools you'll need for working with macros and VBA. It's hidden by default, but turning it on is a one-time setup.

Here’s how to enable the Developer tab:

1. Right-click anywhere on the Ribbon at the top of Excel (like on the "Home" or "Insert" tab) and choose "Customize the Ribbon...".
2. In the dialog box that appears, look at the list of tabs on the right-hand side. Find "Developer" and check the box next to it.
3. Click OK.

You should now see a brand new "Developer" tab in your Excel ribbon. This is your mission control for all things automation.

A Quick Note on Macro Security

On the Developer tab, you'll see a button for "Macro Security." Macros are powerful, and because they can run code, they can be a security risk if they come from an untrusted source. By default, Excel usually disables macros from outside sources. For the work you do yourself, you're fine, but always exercise caution when opening Excel files with macros from others.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Your First Macro: Using the Macro Recorder

The absolute best way to learn VBA is to let Excel write the code for you first. The Macro Recorder is perfect for this. It watches your every move and translates it into VBA code.

Let's create a macro that formats a header row. We'll make the text bold, center it, and add a light gray background color.

Step 1: Get Your Data Ready

On a new worksheet, type some simple headers in cells A1, B1, and C1. For example: "Date", "Campaign Name", "Revenue".

Step 2: Start Recording

1. Go to the Developer tab.
2. Click on Record Macro. The "Record Macro" dialog box will pop up.
3. Macro name: Give your macro a descriptive name. Names cannot have spaces, so use something like FormatHeaderRow.
4. Shortcut key: This is optional, but you can assign a keyboard shortcut to run the macro quickly. Be careful not to overwrite a common shortcut like Ctrl+C. Let’s use Ctrl+Shift+H.
5. Store macro in: Choose "This Workbook." This ensures the macro is saved with your current file.
6. Once you're set, click OK.

Excel is now recording everything you do!

Step 3: Perform the Formatting Actions

With the recorder running, perform the actions you want to automate. It's a good practice to be deliberate here.

1. Select the range you want to format, such as cells A1 through C1.
2. Go to the Home tab.
3. Click the Bold icon (B).
4. Click the Center alignment icon.
5. Click the Fill Color dropdown and select a light gray color.

Step 4: Stop Recording

Once you are finished, go back to the Developer tab and click Stop Recording. That's it! Your macro has been created.

Step 5: Test Your Macro

Let's see it in action. Go to a new, blank sheet. Type some new headers in row 1. Now, press your shortcut key: Ctrl+Shift+H. Instantly, your headers will be formatted exactly as you specified. You can also run the macro by going to Developer > Macros, selecting "FormatHeaderRow," and clicking "Run."

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Peeking Under the Hood: The Visual Basic Editor (VBE)

You've recorded a macro, but where did the code go? It's time to look at the VBA code that Excel just wrote for you. To do this, we'll open the Visual Basic Editor (VBE).

On the Developer tab, click the Visual Basic button on the far left (or use the shortcut Alt + F11).

The VBE might look a little overwhelming at first, but let's break it down:

• Project Explorer (Top Left): This is a tree view of all open workbooks and their components, like sheets and modules. The code for our macro is stored in a "Module." Find the folder named "Modules" and double-click on "Module1" in it.
• Code Window (Right): This is the main area where you'll see and write your VBA code. When you double-clicked "Module1," your recorded macro's code appeared here.

Your code should look something like this:

Sub FormatHeaderRow()
'
' FormatHeaderRow Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
    Range("A1:C1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
End Sub

Understanding and Modifying Your VBA Code

The recorder often creates more code than is strictly necessary, but it's a great lesson in how VBA works. Let's clean it up and break down the important lines:

• Sub FormatHeaderRow(): This declares the beginning of a "Subroutine" (a set of commands) named "FormatHeaderRow". Every macro starts with Sub [MacroName]() and ends with End Sub.
• ' FormatHeaderRow Macro: Any line starting with a single quote (') is a comment. Excel uses it for notes, the code ignores it.
• Range("A1:C1").Select: This line tells Excel to select the range of cells from A1 to C1.
• Selection.Font.Bold = True: This line acts on whatever is currently selected (Selection) and sets its Font.Bold property to True.
• The sections starting with With Selection are just a way to perform multiple actions on the same object (the selection) without retyping its name repeatedly.
• .HorizontalAlignment = xlCenter: This centers the text in the selected cells.
• With Selection.Interior...: This section handles the background fill color of the cells.

Let's modify our code to make it more efficient and change the color. You can edit the code directly in the VBE. Replace the old code in Module1 with this cleaned-up version:

Sub FormatHeaderRow_V2()
' Keyboard Shortcut: Ctrl+Shift+H remains assigned to the original
    With Range("A1:C1")
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        
        'Change Interior Color to a Light Blue
        .Interior.Color = RGB(197, 217, 241)
    End With
End Sub

In this version, we act on the Range("A1:C1") object directly instead of selecting it first, which is much faster. We also specified an exact RGB color for the background fill, giving us more precise control. To test it, go back to Excel, place your cursor in the code, and press F5 (or the Run button in the VBE).

Writing a Simple Macro from Scratch with Visual Basic

Now that you've seen and modified code, let's write one from scratch. This is much simpler than it sounds.

Our goal: create a macro that displays a message box with the current date and time.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Open the VBE and Insert a Module

Open the Visual Basic Editor (Alt + F11). If you still have "Module1" open, great. If not, go to the menu and click Insert > Module to create a fresh space for your new code.

Step 2: Write the Subroutine

In the new module's code window, type the following:

Sub ShowDateTime()

   MsgBox "The current date and time is: " & Now()

End Sub

Let's break that one line down:

• Sub ShowDateTime()...End Sub: We are defining a new macro named "ShowDateTime."
• MsgBox: This is a built-in VBA function that creates a simple pop-up message box.
• "The current date and time is: ": This is the static text (a "string") we want to display. It must be inside double quotes.
• &: The ampersand is used to join (or concatenate) two pieces of text together.
• Now(): This is another built-in VBA function that returns the current computer date and time.

To run it, go to Developer > Macros, select "ShowDateTime," and click "Run". A message box will pop up instantly!

Saving Your Work: Macro-Enabled Workbooks

This is a critical, final step that trips up many beginners. Standard Excel workbooks (.xlsx) cannot save macro code. If you save your file in that format, all your VBA work will be deleted permanently.

To save your macros, you must save the file as a Macro-Enabled Workbook:

1. Go to File > Save As.
2. Choose your location.
3. Crucially, in the "Save as type" dropdown menu, select Excel Macro-Enabled Workbook (*.xlsm).
4. Click Save.

From now on, just save the file normally. The .xlsm extension tells Excel to preserve your VBA code.

Final Thoughts

Excel macros are your first step into true workflow automation. By starting with the recorder, you can automate repetitive tasks immediately and get a free lesson in VBA at the same time. Examining and tweaking that code in the Visual Basic Editor is the bridge to writing powerful custom solutions from scratch to handle your unique challenges.

Automating tasks one by one in a spreadsheet is powerful, but that often comes after the biggest manual task of all: just getting your data into Excel in the first place. My team and I built Graphed because we wanted to go a step further and automate the entire reporting process from start to finish. Instead of exporting CSVs and then running macros to merge and format them, our platform connects directly to your data sources like Google Analytics, Shopify, and social ad platforms, letting you use AI and simple conversations to build and update your dashboards in seconds.