Tutorial – Record and Run a Macro – Real Life Example

Today, we are beginning to learn one of the most relevant and useful tools of Excel which is macros. Macros can be proven as game changers for many people and they can save hours at your workplace. So let us start learning how to record and then run a macro in excel.

Here we begin.

What is a Macro?

Macro is a set of instructions that we record once and then ask excel to repeat those instructions to save us time. A lot of time, people are assigned to do some work in their offices on a daily or weekly basis. We might need to create a report that uses the same formatting. Instead of doing the repeated stuff manually, we can just record the procedure using macros and then run the macro to perform the intended repeated task for us. Macro is capable of automating repeated tasks.

We do not require to code in order to record and run a macro in excel. Learning to use one would save hours of work.

infographics to record and run a macro in excel

Creating a Macro to Insert a Combo Chart

Let us suppose John works as an employee in a company that asks him to generate a report every week from the data as follows.

what are macros in ms excel raw data

This data has three columns and begins from cell A3. Cel A1 contains the name of the department. John needs to create a combo chart based on this data. The chart has fixed formatting for all of the charts that he would create in the future.

create a chart using macro

We would now help John to automate the following steps involved in making this combo chart.

Step 1 – Start to Record a Macro

To start recording a macro, we either need to enable the developer tab or from the status bar.

Enable Developer Tab

Follow these steps to add the developer tab on the ribbon.

  • Right-click anywhere on the ribbon and click on Customize the Ribbon option.
add developer tab to the ribbon
  • Now mark the Developer tab and then click Ok.
add developer tab to the ribbon customize the ribbon

This is going to add the developer tab on the ribbon. You will find a button Record Macro that will start recording the macro.

developer tab on the ribbon

The shortcut to start recording a macro is from the button located at the status bar at the bottom of the worksheet.

start recording a macro in excel

Click on this button and this opens the Record Macro Dialog box.

recording a macro in excel step 1

Begin Recording the Macro

Enter the name of the macro. The shortcut key will make us run the macro once we finish recording it. We are storing this macro in This Workbook. If John wants to use this macro in different workbooks then he can store the macro in the Personal Macro Workbook instead of This workbook.

Click on ok and then whatever you do would be started to record as a macro.

macro recording

Make sure to not make any selections or clicks that are not required. Even if you do, feel free to delete the macro and begin recording the macro from start again.

Step 2 – Recording the Instructions in the Macro

Recording of the macro has now started. Perform these steps correctly and carefully.

  • Go to Cell A3 and then on the insert tab on the ribbon. Click on Recommended Charts button in the Charts Group. Navigate to the All charts tab and choose the Combo Chart. Mark Secondary Axis for Company Average.
insert a combo chart using macro
  • This inserts a combo chart with default formatting. the combo chart would be selected. Combo Chart would become the active chart.
  • Now start formatting the chart using the exact steps:-
    • Click on the + icon at the top right corner of the chart and unmark Chart Title.
    • Right Click on Legend and choose Format Legend. Set the position to the top.
    • Click on the Salary Data Series and set its fill color to green.
    • Click on Computer Average Data Series and set the outline color to blue.
recording a macro in excel step 2

Step 3 – Stop Recording the Macro

Since we are now done with the formatting of the chart. Excel was recording all the steps you did. Now you can stop recording the macro from the developer tab.

stop recording a macro in excel

Step 4 – Running the Macro

After recording the macro, John can run it for the same type of data. Let us say we have the data for the sales department in another worksheet of the same workbook.

running a macro using shortcut key

Press Ctrl Shift A keys together to run the Combo Macro.

running a macro using shortcut key step 2

We have now learned to record and run a macro in excel. This macro would work for similar types of data irrespective of the number of records in the range.

Now we have come to an end.

Thank you for reading.

Leave a Comment