Excel is one of the most widely used tools for data management, and mastering its features can take your productivity to the next level. One powerful feature is Macros—a tool for automating repetitive tasks that you would otherwise have to perform manually.

In this comprehensive guide, we will walk you through the basics of Excel macros with chatGPT, show you how to create them, and provide a detailed set of practical examples. Whether you’re formatting cells, generating reports, or even sending emails, macros can handle it all. By the end, you’ll not only understand macros but will also be ready to use them in your daily workflow. All the macro examples and data we’ll work with today can be accessed here: Macros in Excel.

1. What Are Excel Macros?

Macros in Excel are sequences of actions or commands that are recorded and saved for future use. They enable you to automate tasks by recording your steps and replaying them whenever you want. For example, if you always apply the same formatting to your data or create reports in the same way, macros can automate these tasks, freeing up your time for more complex activities.

To put it in an analogy – Macros are like a recipe! Imagine you’re baking cookies. Every time you bake them, you follow the same recipe. Instead of repeating the steps each time (gather ingredients, mix, bake), you wish you could automate the process. That’s exactly what a macro does in Excel—it follows a “recipe” of steps you define, allowing you to “bake your cookies” (complete your tasks) with a single click!

Why Use Macros?

Here are some key benefits of using macros in Excel:

• Save time: Once you create a macro, you can run it with a single click, no matter how complex the task.
• Reduce human error: Macros eliminate the risk of mistakes by repeating actions exactly as recorded.
• Increase productivity: By automating routine tasks, you free up your time to focus on more strategic work.

**

2. How to Record and Run a Macro in Excel: Step-by-Step Guide

Creating a macro in Excel is simpler than it sounds. Please note that macros may not already be enabled on your Microsoft 365 Excel App. Fret not as we shall be going through every step in detail and discuss any possible issues that may arise. 

Now, follow these steps to activate macros and record your first macro:

1. Enable the Developer Tab
   By default, the Developer tab is hidden in Excel. Here’s how to enable it:
   • Go to File → Options → Excel Options  → Customize Ribbon. 
   • Check the box next to “Developer” and click OK.

2. Record Your First Macro
   Once the Developer tab is visible:
   • Click “Record Macro” in the Developer tab.
   • Name your macro something memorable, like “AutoFormat,” and choose where to store it (we recommend “This Workbook” for now).
   • Excel will now record everything you do. Go through the actions you want to automate, such as applying specific formatting or formulas.

3. Stop Recording the Macro
   After completing the task, go back to the Developer tab and click “Stop Recording.”

4. Run the Macro
   You can now run the macro by going to View → Macros → View Macros. Alternatively, you can also go to Developer Tab→ Macros. Select your macro from the list and click “Run” with any new data you may have. 

This simple process allows you to automate any task in Excel. Once you understand the basics, you can start experimenting with more advanced functionalities.

**

3. Real-Life Examples: Automate Your Workflow using Excel Macros with ChatGPT and Recording Feature

Let’s look at five detailed examples where you can use macros to automate daily tasks. Each example provides a step-by-step video walkthrough along with explanations to give you practical applications of macros in real-world situations.

3.1. Automating Cell Formatting 

Imagine you are tasked with formatting cells in a specific way every week. You might change font sizes, bold certain cells, or apply a specific color scheme to highlight important numbers. A macro can automate this process.

3.1.1 Automation by Recording Macros

Real-Life Application:
If you work in sales and need to highlight high-performing products, this macro will allow you to apply the exact formatting to sales figures, helping you quickly identify trends. 

Let’s say you are tasked with formatting a weekly sales report. Every week, you need to add a new row at the top called “Weekly Sales Report” and merge and centre it, along with filling it in Grey. Furthermore, you need make all the column headers bold and change font size to 14 and add a dollar sign for every value under “Sales” column. We’ve recorded the macro in Week1 report tab and run the macro on new data under Week2 report tab. 

Step-by-Step:

• Start recording macro.
• Insert a new row a top and merge and center the required columns by selecting Merge and Centre option under Home Tab.
• Highlight this merged cell with the color blue. Select the header columns in the next row and bolden them along with increasing the font size to 14.
• Select the rows under “Sales” column and change the number format to “$ English (United States)” under the Number Section of Home Tab. 
• Stop recording.
• After recording, this formatting can be applied to any selected range of cells by running the macro for any new set of data.

Take a look at this video example for a detailed demo on how to automate this with a single click!

3.1.2. Using ChatGPT to Generate VBA Code for Cell Formatting

Instead of manually recording a macro in Excel, users can leverage AI tools like ChatGPT to generate the exact VBA code they need for repetitive tasks. This is particularly useful for those who prefer working directly with code or want to save time by skipping the recording process. Let’s walk through how you can prompt ChatGPT to generate the code for formatting cells in Excel, similar to the example of automating a weekly sales report.

Step-by-Step:

1. Open ChatGPT: Start by accessing ChatGPT, either through an online platform or the application.
2. Enter Your Prompt: Provide a detailed prompt that describes the formatting task you want to automate. Please take note that in some cases, getting the desired result using ChatGPT may be an iterative process. You may be required to tweak your prompt a couple of times to get the most effective piece of code but fret not, once you’ve got the prompt right, it shall save you tons of time!
   Here’s an example prompt you can use for automating cell formatting:
   Example Prompt:
   “Write a VBA code for Excel that performs the following tasks:
   • Inserts a new row at the top of a sheet and merges and centers it across columns A to C.
   • Fills the merged cell with sky blue color and adds the text ‘Weekly Sales Report’.
   • Makes the header row (Row 2) bold and changes the font size to 14.
   • Applies a dollar sign format to all cells in the ‘Sales’ column (Column C). Ensure the code works for any dataset, preserving the current data.”
3. Review the Generated Code: ChatGPT will provide you with a ready-to-use VBA script.
4. Copy and Paste the Code: Once you’ve reviewed the code, copy it from ChatGPT and paste it into the VBA editor in Excel:

• Press Alt + F11 or navigate to Visual Basic under Developer tab to to open the VBA editor.
• Go to Insert > Module and paste the generated VBA code.
• Press F5 to run the macro, or assign it to a button for future use.

Here’s a quick step-by-step video walkthrough to help you understand how to use ChatGPT with Excel to automate cell formatting.

Interestingly, if you compare the outputs from recording macros and using chatGPT to automate the above tasks, we can see that the results are not very different in the two approaches. This gives us a lot of comfort to use chatGPT for writing macro codes in Excel.

3.2. Generating Charts and Reports

Monthly reports can be repetitive. You filter data, sum totals, create charts, and save it to a PDF etc. With macros, you can do all of this automatically.

Real-Life Application:
Financial analysts often prepare monthly expense reports. Instead of manually generating the same reports every month, a macro can automate the entire process—from applying filters and sorting data to creating charts for effective business communication. With a single click, your visual charts and reports can be ready, saving you hours of time each month. 

For example, the example shown in the video contains data about the monthly sales of different commodities. We would like to create a stacked bar chart for this data for easy interpretation.

Step-by-Step:

• Start recording the macro from the developers tab as seen earlier.
• Navigate to the starting point of your data. Let’s assume it starts from cell A1. Now, press Shift+Ctrl+down and then Shift+Ctrl+Right to select your data. 
• Next, navigate to Insert Tab and select any chart of your choice under Charts section.
• Keep the cursor on the chart and change the format of the chart as per your choice. 
• Click somewhere outside the chart and stop recording.
• Now, when you use this macro on a new worksheet with new set of data, you may notice something amiss. Perhaps, the chart data has not changed according to the new data. This may be because of some absolute referencing in your VBA code. 
• To resolve this, open the Visual Basic editor from Developer Tab and click on the latest module that you see. Here, you will see the associated Visual Basic Application code for your macro. 
• Remove the last line of code where you see the additional absolute referencing your data and hit save as shown in the video.
• Now rerun the saved macro for new data and you will see the updated results!

Take a look at this video for a guided demo!

3.3. Applying Conditional Formatting

Conditional formatting helps in visualizing data by highlighting important values based on specific conditions. Setting up conditional formatting manually each time can be tedious. Macros can apply the same formatting rules to any dataset automatically.

Real-Life Application:
Conditional formatting is commonly used in performance reports. Sales managers can use a macro to quickly highlight products that exceed a sales target or customers whose orders have fallen below a specific threshold, making it easier to identify areas needing attention.

Step-by-Step:

• Start recording Macro.
• Select the values in “Heart Rate” Column and click on Home > Conditional Formatting > New Rules > Format only Cells that contain. Under Edit the Rule Description, select Cell Value and Less Than options and enter 60. 
• Click on Format and select the desired color under Fill tab.
• Repeat this process for any other conditional formatting rule if you wish to and stop recording.
• Run the macro whenever you need to apply the same rules to a different dataset.

Here’s a detailed video following the above steps!

3.4. QuickSearch With Macros and Vlookup

Using Excel’s VLOOKUP function is a great way to search for specific data within large datasets. However, when you need to perform repetitive lookups across multiple sheets or datasets, it can become time-consuming. This is where macros come in. By automating the VLOOKUP process, you can reduce errors and save time.

Real-Life Application:

This is especially useful for businesses or schools where data updates frequently. Whether you’re managing student grades, employee records, or product prices, automating VLOOKUP with macros ensures faster and more accurate results. For example, we will now try to look at some customer data for an e-commerce company. Based on an orderID, we would like to fetch some customer details. 

Step-by-Step:

• Start by recording a macro
• Select the cell where you want the value to be retrieved and type the VLOOKUP formula. Make sure you use absolute referencing when you pass your source data/ lookup table. To data, you must simply enter a $ sign before the column alphabet and row number (i.e., $A$22). In our example, we are try to place the retrieved value (which would be a customer name) in cell M9, the value to be looked up is in cell M8 (OrderID) and the lookup table ranges from A3:J22. Here is the completed formula used in cell M9 where B1 is just a cell that holds the column value (2 in this case). 

=VLOOKUP($M$8,$A$3:$J$22,B1,0)

• Hit enter and verify VLOOKUP is working as expected. Once done, stop recording.

Now, let’s do something fun! Let us try to activate a macro through an actual click on a button. 

• Go to Insert > Illustrations> Shapes > Rectangle and select some shape of choice and add text to it – maybe a “FIND” tag. Now right click on this object and select Assign Macro. When prompted with a list, select the macro you had just created. In this example, we have named it “vlookupMacro”. 
• Now, click on cell M10 (which is supposed to retrieve another column value for the same lookup value) and hit the FIND button you just created. Voila! You can see now see that you’re vlookup function works for new data as well. 
• Additionally, you can also add a dropdown to your lookup value cell and see the associated column values being retrieved dynamically for different OrderIDs.

Watch this video to see the above process in full detail and follow along!

https://drive.google.com/file/d/1IRU-eHxNXsHtT8-6kl559VjZuwWFlbZe/view?usp=sharing

3.5. Removing Duplicates 

Large datasets often contain duplicate entries, especially when compiling lists from multiple sources. Excel has a built-in feature for removing duplicates, and macros can automate this process, so you don’t have to manually check and delete duplicate entries.

3.5.1. Using Recorded Macros

Real-Life Application:

Data analysts or sales teams who manage large customer databases often face duplicate entries when new data is added from various sources. A macro can automate the removal of duplicates, saving time and reducing errors.

Step-by-Step:

• Start recording macro.
• Select the data by placing the cursor on the first cell of data and then pressing Shift+Ctrl+Down and Shift+Ctrl+Right.
• Select Excel’s “Remove Duplicates” feature under Data tab to clean up your data. This feature is present under Data Tools section.
• Stop recording and save the macro.
• Whenever you need to clean a new dataset, you can simply run the macro. However, this may work only if you have the same number of rows in every new set of data. In case it changes, the macro may not work as expected.
• To resolve the above issue, you would have to modify the VBA code to dynamically select the filled rows and run the removeDuplicates function on them. 

This is an easy piece of code for the same:

Sub removeDuplicates()

Dim ws As Worksheet

Dim dataRange As Range

Dim lastRow As Long

Dim lastCol As Long

‘ Set the worksheet to the active one

Set ws = ActiveSheet

‘ Find the last used row and column dynamically

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

‘ Define the dynamic range assuming the data starts from cell A1

Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

‘ Remove duplicates

dataRange.removeDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes

End Sub

Save the VBA code in the editor and navigate back to the excel sheet and rerun the macro. Now all the duplicates in varied number of rows will get deleted effectively. 

Follow this video for guidance on the same.

3.5.2 Using ChatGPT to Generate VBA Code for Removing Duplicates

To achieve the same result as in the above example without any coding requirement, we can use ChatGPT to our advantage yet again! 

Step-by-Step:

1. Access ChatGPT: Open the ChatGPT interface where you can enter prompts.
2. Enter Your Prompt: It may take several iterations to find the correct prompt for your desired result. It is best to clearly describe the desired result for the most efficient response from ChatGPT. To generate VBA code for removing duplicates, you can use the following prompt:
   • “Write me a VBA macro that removes duplicates across all columns in the current region starting from Cell A2.
   • It should dynamically adjust to the number of columns and rows to identify and remove the duplicate records.
   • Generate a message indicating how many duplicates got removed. “
3. Review the Code: ChatGPT will provide you with a code snippet tailored to your request. Ensure the generated code meets your requirements.
4. Copy the Code: Once you have the code, copy it to your clipboard.
5. Open the VBA Editor in Excel: Press Alt+F11 to open the VBA editor.
6. Insert a New Module: Right-click on any of the items in the Project Explorer, go to Insert > Module.
7. Paste the Code: In the new module window, paste the code you copied from ChatGPT.
8. Save Your Work: Save the module and close the VBA editor.
9. Run the Macro: Back in Excel, you can run your newly created macro whenever you need to clean up duplicates from your dataset.

And there you have it! Quick work without having to record a macro manually the first time around! 

**

4. Manual Work vs. Macros: Why Macros Win

It’s important to understand the time-saving potential of macros. Let’s break down the differences between manual workflows and macro-based automation:

Manual Workflow:

Without macros, most Excel users perform tasks manually. For example, if you’re tasked with creating a weekly sales report, you’d go through several steps – filtering data, applying conditional formatting, creating charts, and generating a summary. Each week, you repeat this process from scratch. While Excel offers built-in tools to speed up some of these tasks, performing them manually still takes considerable time and effort.

Macro Workflow:

With macros, you can record the entire process once and run the macro whenever needed. The automation reduces the risk of human error, ensures consistency, and saves time. Tasks that normally take 30 minutes can be reduced to mere seconds with a macro, allowing you to focus on more strategic work.

Key Advantages of Using Macros:

• Efficiency: Tasks that take hours manually can be completed in seconds.
• Consistency: Macros ensure that the process is executed in the same way every time, eliminating inconsistencies.
• Reduced Errors: By automating repetitive tasks, you minimize the risk of making mistakes.
• Scalability: Macros can handle large datasets and complex tasks, making them highly scalable for big projects.

**

5. Troubleshooting Common Macro Issues

While macros are powerful, beginners might encounter some common issues when using them. Here’s how to troubleshoot the most common problems:

5.1. Forgetting to Stop Recording

Sometimes, users forget to stop recording after completing their task. This causes the macro to include unnecessary actions, which can lead to errors when running it later.

Solution:

Always ensure that you stop recording as soon as your intended task is completed. If you’ve forgotten to stop recording, you can edit the macro in the VBA editor to remove any extra steps.

5.2. Using Absolute References

When recording a macro, Excel defaults to using absolute cell references. This means that the macro will always refer to the same cells you used while recording, which can be problematic if you want to apply the macro to different data ranges.

Solution:

For simpler use cases such as conditional formatting, use the “Relative References” option when recording a macro. This ensures that the macro applies to the active cells or ranges, making it adaptable to different datasets. 

For more complex use cases like generating charts and PDF reports, or running advanced functions, you would have to tweak the code for your macro in the VBA editor to remove any unnecessary absolute referencing.

5.3. Incorrect Macro Storage

Sometimes users mistakenly store a macro in a single workbook (e.g., “This Workbook”) instead of making it available for all workbooks (by saving it in “Personal Macro Workbook”).

Solution:

If you want to use your macro across multiple workbooks, save it in the Personal Macro Workbook. This way, it will be available whenever you open Excel, no matter which workbook you are using.

5.4. Incorrect ChatGPT Prompt

A potential issue users may face when using ChatGPT for VBA code is receiving code that may not fully account for specific workbook configurations or data structures, leading to errors or unexpected behavior.

Solution: Generating good solutions based on a given prompt is an iterative process when working with ChatGPT and other AI tools. Users should always review and test the generated code in a safe environment before applying it to critical datasets. Additionally, providing detailed prompts with context can help refine the code output. If issues arise, users can modify the code based on their unique requirements or seek further clarification from ChatGPT. If you are unable to debug an unexpected error, simply paste the error code and message in the ChatGPT UI and ask for a reason and appropriate solutions. 

**

6. FAQs About Excel Macros

Q1: Do I need to know programming to use macros in Excel?

No, you don’t need programming skills to record and use macros. Excel’s macro recorder allows you to automate tasks without any necessary coding. However, learning VBA (Visual Basic for Applications) can help you customize macros further and debug in case of any unexpected errors.

Q2: Can macros work across different Excel files?

Yes, you can store macros in the Personal Macro Workbook, which allows them to be used across any Excel file.

Q3: Can I undo a macro after it runs?

Unfortunately, you cannot undo a macro once it runs. It’s a good idea to save your work before running a macro, especially if it modifies a large dataset.

**

Call to Action: Ready to Master Excel Automation?

Now that you’ve learned the basics of Excel macros, it’s time to start practicing! Begin by automating simple tasks like formatting or copying data. As you get comfortable, explore more advanced techniques, such as using the VBA editor to customize your macros or adding user input prompts.

Want to dive deeper into Excel automation? Download our practice files or check out our advanced Excel automation course to become an expert in no time!