This hands-on Excel Macros tutorial explains what is a Macro, how to create and use VBA Macros with plenty of examples:
Most of us in the industry will surely have certain tasks that are to be performed repeatedly almost every day. Now imagine if those tasks are done with just a single click. Sounds exciting? Excel Macros is the answer to that.
In this tutorial, we will learn what a Macro is? How to record a Macro using absolute and relative reference along with some practical examples.
What You Will Learn:
What Are Excel Macros
A macro is a set of actions that you can run to perform the desired task.
Suppose every month you create a report that needs to mark the user accounts with the overdue amount in bold and red. Then you can create and run a macro that applies these formatting changes every time you wish to.
How To Enable Macros In Excel
The Developer tab gives us the ability to work with features like Macros, Add-ins, and also allows us to write our own VBA code that will help us to automate anything we wish. This tab is hidden by default.
Follow the below steps to unhide the Developer tab. This works on all versions of Excel for Windows (Excel 2007,2010, 2013, 2016, 2019).
Note: This is a one-time process. Once you enable the Developer Tab, it will always be shown in a custom ribbon for every Excel instance you open, unless you go ahead and disable it explicitly.
Enabling The Developer Tab
#1) Click the File tab
#2) Click Options
#3) Click on Customize Ribbon.
#4) Under Customize Ribbon enable Developer.
Once you enable the Developer tab, it will be displayed on the ribbon list.
Developer Tab’s Options
Enlisted below are the options that are present under the Developer tab.
- Visual Basic: Gives an Editor to write or edit VBA code. Can also be opened using Alt+F11.
- Macros: Gives the list of all the already recorded macros and is also used to record a new one. Alt+F8 will directly open the list of macros.
- Add-ins: Allows to Insert an Add-In and can also manage them.
- Controls: Helps us in using Form controls and ActiveX controls. Viewing and editing the control properties. Design Mode ON/OFF is controlled here.
- XML: Helps us to Import/Export an XML data file, to manage XML expansion packs, and also to open the XML Source task pane.
How To Record A Macro
Consider an example, that your company has a certain tool which generates timesheets for various departments in Excel. You as a manager have the responsibility of reviewing and sending the sheet to the finance team every week.
But before sending you are asked to make some formatting like:
- Insert the Title for every sheet that includes team name and week number, mark it bold, and background yellow.
- Draw a border
- Bold the column headings.
- Rename the sheet name as the team name.
Instead of doing this manually every week, you can just create a macro and perform all these actions in just a click.
Recording macro is fairly easy. Navigate to the Developer tab, and hit on Record Macro.
This will open up a window where you need to enter.
#1) Macro name: Name should not have spaces in between words. This has to start with an alphabet or underscore.
#2) Shortcut key: This is useful when you are running a macro. If you press the shortcut key, it will be executed. Make sure to give a key which is not already taken, else macro will override that.
For example, if you mention Ctrl+S as the shortcut, then every time you press Ctrl+S, your macro will be executed and thereby ignoring the save file option. Hence it is recommended to add Shift, like Ctrl+Shift+D
#3) Store macro in: This has 3 options as given below.
- This Workbook: All the macros created will only be available to the current workbook. If you open a new excel then the macro created earlier won’t be available and hence it can’t be used.
- Personal Macro Workbook: If you select this, then the macro created will be stored and will be shown when you open a new excel sheet.
- New Workbook: This option will open a new workbook and the actions performed in that workbook will be recorded.
#4) Description: This will describe the purpose of the macro. It is recommended to give a detailed description so that anyone using that will know what exactly it is used for.
Once you fill in the details for the fields mentioned above, you can go ahead and perform the actions required in the Excel Workbook and everything will be recorded. Once done, go back to the Developer tab and hit on Stop Recording.
Saving An Excel Workbook With Macro
Selecting Store macro in as “This Workbook”: Consider that you have selected the Store macro in as “This Workbook” while recording. Once done go ahead and save the file. While saving you need to select Excel Macro-Enabled Workbook. You don’t have to explicitly save the macro. It gets saved automatically.
Selecting Store macro in as “Personal Macro workbook”: Now consider selecting the Store macro in as “Personal Macro workbook” while recording. You need to save the macro explicitly. If you just save the Excel file and then try to close the file. Then you will receive a pop-up dialog as shown below.
Note: If you don’t save this then the macro will be deleted.
Executing A Macro
Now that we are done with recording and saving the file, let’s try to run it and achieve the desired results. We have gone ahead and recorded a macro with all the steps required to achieve in the attendance timesheet example and saved it as a This Workbook with the shortcut key as Ctrl+Shift+B.
So every week when you receive a new Excel from the software tool, you just have to open that Excel file and hit the shortcut key (Ctrl+Shift+B) and all the changes will be incorporated as expected. The resulting Excel is given below.
Attached the Excel-Macro-Workbook
- If you have forgotten the Shortcut key, you can go to Developer -> Macros, Select the macro and click on options.
- If the Macro stored as a personal store is not visible under the Macros Tab. Go to View -> Unhide and this will show the list of all the macros.
There are 2 ways to record a Macro as shown below.
- Absolute Cell Referencing
- Relative Cell Referencing
Absolute Cell Referencing: Absolute references will always point to the particular cell where it was recorded. For example: if you record a text in A10 cell then next time when you use that macro in another workbook, it will place that text in A10.
Consider our Attendance timesheet example. We always want the title to be in the first row of every sheet. We don’t want to cell reference to change when it’s copied to other sheets or workbooks. In that case, Absolute Cell Referencing comes handy.
Relative Cell Referencing: Suppose you have to repeat the steps at various places in the worksheet. Relative references is convenient whenever you need to repeat the same calculation or steps across multiple rows or columns.
Example: Say suppose you have an Excel sheet with the full names, phone numbers, and the DOBs of 1000 employees. (Format is as shown below)
|Emp ID||Emp FullName||Phone Number||DOB|
Your manager expects you to:
- Separate First Name and Last Name.
- Add Country code Example (+91) to the phone number.
- Show DOB in the form of dd-mon-yy, Example: 10 Jan 87.
Since there are 1000 records, doing it manually would take time. So you decide to create a Macro. But using absolute reference will not solve your problem as you want it to work across multiple rows and columns. In this case, Relative reference comes handy.
Record Excel Macro Using Relative Reference
To record using a relative reference, first select the cell you want to start the recording.
Go to Developer -> click on Use Relative Reference -> Record Macro. Record anything you wish and hit stop recording.
For the above example follow these steps.
- First, we need to insert a column next to Emp FullName and change the column heading as FirstName and LastName.
- Select B2 cell- > Go to Developer -> Use relative reference -> Record Macro.
- Using Text Delimiter separate firstname and last name. Once Done Stop Recording.
- Similarly, create 2 more macros for Phone number and DOB.
- Save the File.
- To Execute, Select all the Emp FullName i.e. B3 till the last emp that is B1001 and execute the 1st Macro.
- Follow similar steps for Phone number and DOB. The resulting Excel is shown below.
|Emp ID||Emp FirstName||Emp LastName||Phone Number||DOB|
Attached File for reference
Frequently Asked Questions
Q #1) What is an example of macros in Excel?
Answer: A macro is a set of actions that you can run to perform the desired task.
Suppose you create a report every month that needs to mark the user accounts with the overdue amount in bold and red. You can create and run a macro that applies these formatting changes every time you wish to with just a single click.
Q #2) Where are macros in Excel?
Answer: All the recorded Macros will be available under Developer tab -> Macros
If you can’t find a Personal Macro, then go to View -> Unhide.
Q #3) What are the types of Cell References in Excel?
- Absolute: Absolute references will always point to the particular cell where it was recorded. For example, if you record a text in D10 cell then every time the macro is used it always points to D10.
- Relative: These are convenient whenever you need to repeat the same calculation or steps across multiple rows or columns.
Q #4) How do I save a macro in Excel to all workbooks?
Answer: While recording a macro select Personal Macro workbook under store macro in, this will make your macro to be available for all the workbooks. If you still don’t see the option, go to View -> Unhide.
In this tutorial, we have learned Excel Macros that help us in automating the routine tasks in Excel.
We have seen what a macro is? How to enable the macro to be shown in Excel. We also explored how to record a macro using absolute and relative cell referencing with examples.