Imagine how easy your life would be if your computer could do repetitive tasks for you automatically. This is exactly why we need and use Macros in Excel. In simple words, Macro is the sequence of instructions you record (or write) in Excel. The instructions can be as simple as populate the cells with specific values or as complex as generate and save the report in the required style and format.
For this article, we are using Microsoft Excel 2013, but the steps are similar for the most versions of Excel.
In this article, you will find the description of the four very simple steps to start with your very first Excel Macro and a video at the end to show how easy it is.
Step 1 - prepare your Excel
First, launch Excel and make sure that you see a “Developer” tab in the Ribbon.
If you do not see a “Developer” tab (which is the default state of Excel), for Excel 2013 right click on the ribbon area and select “Customize the Ribbon…” menu from the drop-down menu.
A new window will appear. Now only think you need to do is to tick the box next to “Developer” line in the “Customize the Ribbon” section.
Once you press “Ok”, you will see the “Developer” tab in the ribbon.
In “older” versions of Excel, such as Excel 2007, in order to display “Developer” tab go to “Options” (top left sign) and in the displayed drop down menu click “Excel options”. On the “Popular” tab, you will find “Developer” tab check box. Simple tick the box and “Developer” tab will appear.
Step 1 is completed. Our Excel is ready for our first macro.
Step 2 - decide on what your macro should do
Good understanding of what your macro has to do is important, as well as it is important to understand what steps we will perform to achieve the required outcome. Think on the steps that you need to perform in Excel, but do not spend too much time figuring out the most efficient instructions for your Macro. You will be able later to make necessary changes. Future articles will cover this topic.
In our example, let us replicate the values of the first cell A1 into the next 10 cells. At the end, we need to see cells A1 – A10 with exactly the same values.
In order to achieve this outcome, we need to copy the value in cell A1 into the cells A2, A3...A10.
Now we know what we would like to achieve and how can we do this. Let us start recording.
Step 3 - record your very first Macro
There are two places where you can start recording of the Macro.
On the "Developer" tab, you will find "Record Macro" button. At the bottom of the page, you will see very similar “Record Macro” icon.
Before we start recording, enter value in cell A1, for example "This is my very first Macro!".
Press "Record Macro" button. You will see the new window that will ask you for additional parameters.
Let us change the “Macro name” for something meaningful. This will help us understand what this Macro is about. In our example, the “Macro name” would be “OurFirstMacro”.
Another field we are interested in is “Shortcut keys”. I will explain why we need this “Shortcut key” in the next step. For now, enter “r” (small letter r).
Make sure that “This Workbook” is selected in the “Store macro in” section.
The window should look like this.
Press "OK" to start Macro recording.
The icon “Record Macro” and the sign will change and will show “Stop Recording”. This indicates that Excel started to record your actions. It is important to understand that Excel records only the actions you perform in Excel, not the time you spend in Excel. That is why, do not rush and take as much time as you need to record your very first Macro.
Let us start the actions.
- Select cell A1 (if already selected, select another cell, for instance B1, and then again cell A1).
- Copy the cell A1 either by pressing Ctrl+C or by right clicking on the cell and choosing “Copy”.
- Select range A2-A10.
- Paste the value to the range A2-A10 either by pressing Ctrl+V or by right clicking on the selected range and choosing “Paste”.
- Select cell A1.
- Press “Esc” to deselect cell A1.
- Press "Stop Recording" button.
Great! We have recorded your very first Macro. Now the question is what we do with it. Follow to the next step to learn.
Step 4 - Running your very first Macro
Before we run our very first Macro, let us clean up the values in cells A1 – A10 and enter a new value in cell A1. For example, “Running my very first Macro!”.
There are two ways how you can run your Macro. First, by running the Macro from the list of Macros in the "Macros" menu under "Developer" tab or by pressing the shortcut thatwe created right before the Macro recording started. Remember how we entered “r” into the “Shortcut key” area. This is exactly the shortcut we should use to run our Macro.
However, first to go "Developer" tab and press "Macros" button.
You will see a list of all available Macros in the new window. Select the Macro you would like to run, in our case "OurFirstMacro" and press "Run".
In a moment, you will see that the value from cell A1 was copied to cells A2-A10. This took almost no time. Is not it great?
Now, let's change the value of cell A1 to "Running my first Macro through shortcut!".
Press Ctrl+r and see what happens. The value of cell A1 was populated into cells A2-A10.
This is that simple.
Here is the full video of the whole process
Congratulations! You are now able to record and run your own Macro in Excel.
Macros have been very (and I mean very) helpful for me and I hope will be very helpful for you as well!
Good luck with the exciting learning of VBA for Excel.