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.

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.

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.

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

• Inserting a Default combo chart and taking a secondary axis for line data series
• Format the chart as per the fixed standards of the company that includes:-
  • Changing the color of bars
  • Changing the color of line data series.
  • Changing the location of the legend to the top.

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.

• Now mark the Developer tab and then click Ok.

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

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

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

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.

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.

• 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.

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.

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.

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

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.