How To Create Macros In Excel

Microsoft Excel is our go-to when creating long spreadsheets for work or smaller ones to track our data. It has let us bid farewell to piles of paper documents. We can create accessible documents in Excel. However, some tasks can be repetitive in Excel. For example, If you have to format a report. The task of changing fonts and adjusting cells can seem arduous and may take up a chunk of your time. It is when we can create macros to save up on our time and resources.

Macro is an action or set of activities that enable you to run it on any cell to do the same number of functions that you did manually. It is one of the perks of technological progress. All you have to do is to record the functions and create a macro to automate the tasks.

The Process

Following is a step-by-step process to create macros in Excel.

Step I: Turn on the Developer Tab

This tab enables users to create macros, design forms, VBA applications, and so much more in Excel.

1. To turn it on, move to the Ribbon, right-click anywhere and select Customize the Ribbon. A dialog box appears. Over the right side of the dialog box, select Main Tabs under Customize the Ribbon.
2. Check the Developer Tab checkbox.
3. Click OK. Now you can find the Developer Tab next to the View tab in the Ribbon.

Step II:Turn On Relative Reference

Make Sure that the Relative Reference is Turned On.You can find it under the File tab. The relative reference enables a cell to change its formula when copied elsewhere. It is essential to turn this feature on when recording macros.

Step III: Navigate to the Developer Tab

In the Code group of the Developer Tab, click Record Macro. It appears on the left side of the status bar.

Step IV: Name the Macro

After naming the Macro, enter a Shortcut Key, a Description, and then Click OK to record the Macro. Refer to the Macro name box, Shortcut Key, and the Description box, respectively. To do so, refer to the following steps mentioned below:

1. Give a descriptive name to the macro. Excel names Macro1, Macro 2 as default. Macro names must begin with a letter or an underscore. For example, names like Macro Tutorial won't work. You must give the name as Macro_Tutorial.
2. Assigning a keyboard shortcut key is an optional step. However, if you do give, be careful about the exact key combination. It is usually of the form Ctrl+ Key combination. For example, Ctrl+B implies the Bold command. Hence if this function is assigned to a macro, the built-in keyboard shortcut for the Bold command won't work. That means Ctrl+B will execute the macro, but the font of the selected text won't get bold.
3. The dialog box also asks you where you want to store the macro. You can choose the workbook you are currently in or save it in the New Workbook. However, in this case, the macro only works in the file it is created. The advanced storage option "Personal Macro Workbook" is designed to store macros that Excel files can use on the same computer.
4. The next step is to give a description to the macro. It is optional, although it lets you and your staff be informed about what to expect when you enable the particular macro.
5. Now, as you have entered all the details, make sure to click the OK button to close the Record Macro dialog box.

Step V: Start Recording 

After having started recording, perform the functions you wish to automate, like filling down a column of data.

Step VI: Stop Recording

On the Developer Tab, click on Stop Recording to the left side of the Status Bar once you are done.

Step VII: Selecting Specific Macro

Click on Macros on the Code group of Developer tab, select the Specific macro, and click Edit. It enables the Visual Basic Editor. You can make changes to the code, close the Editor and rerun your macro. You will get the desired results.

Additional Information

Following are some additional information concerning using macros in Excel.

1. You can assign a macro to any button, graphic icon, or object. All you have to do is right-click on the object in the worksheet and click on Assign Macro. It will assign one of your existing macros(of your choice) to the entity.
2. To record a macro or stop recording, the shortcut key is Alt+T+M+R.
3. Shortcut key Alt+F8 opens the Macro Dialog Box.
4. Macros can be run using a shortcut key, a button, a graphic icon, or via the Quick Access Toolbar.
5. Using the Excel option, you can Disable VBA Macros with or without any notification.

Conclusion

A macro (short for macro-instruction) makes the job of the user easy by enhancing work speed. It allows us to display output for more comprehensive data without spending a lot of time typing the same functions. It reduces the length of the program and gets the job done with just simple keystrokes. Excel is an excellent solution to record statistics via these simple functions or charts or Pivot Tables.

We at OpenGrowth, are committed to keeping you updated with the best content on the latest trendy topics from any major field. Also, both your feedback and suggestions are valuable to us. So, do share them in the comment section below.