Easy way to Create Macro in Excel (Time Saver)

Create macro in excel for tasks you do repeatedly is an essential skill to learn if you use excel daily. Learn how to do it in this blog…

Create Macro in Excel

We all have Excel tasks that we perform repetitively. Each task may require tens of steps… Why waste your time, redoing the same steps, while you can record a macro that automates repetitive tasks… and saves you a lot of time.

When you run a macro, as if you are putting Excel on Autopilot…

In this tutorial, I show you my “Pre-Flight” Checklist while we create a very useful macro from ground up using the classic technique and commands from the Ribbon.

Once I finish it, I close and reopen the file and recreate the project using only Shortcuts… Amazing shortcut

Fasten your seat belt and get ready for a spectacular Excel Flight.

You can Download the Exercise file and follow along by clicking here below:

A Macro recorder is like an Audio recorder, but, instead of recording Sound, it records your keyboard punches and your mouse clicks. Everything you do on your keyboard or mouse is being recorded. Behind the scene, Excel writes the code for you in VBA.

Preparing for Recording a Macro

1. Add the Developer Tab to the Ribbon
   File Tab ► Options ► Customize the Ribbon ► Check the Box for “Developer”
2. A file having a Macro must be saved as a Macro Enabled Excel File .xlsm
3. Plan for your steps upfront (before recording) to avoid errors while recording.
4. Check the Security settings to enable macros: On the left side of the Developer Tab ►Click Macro Security and select the Second option ►OK

Macro Checklist:

1. Is it a Relative or an Absolute Macro? A relative macro will repeat the recorded steps relative to a starting position. While an Absolute macro will repeat the steps on the same exact range used while recording.
2. What is your starting Cell? This is important for relative macros.
3. Your Macro name cannot have spaces. Otherwise, it will be declined.
4. Assigning a Shortcut to run the macro. That is a common way to run the macro. Your shortcut includes another modifier key (beside the CTRL) plus a character or a number.
5. Where to store the macro. By default, it is saved in “This Workbook”. This is the safest location for beginners. Other options include: New Workbook and the Personal Template.
6. What is your Ending Cell? This is specially important if you are running macros consecutively or if you are stringing macros together (Done in VBA).

In the Exercise workbook (Did you download the file?)

We have a list of records imported from another application. All records are in column A but on multiple rows and they have the same pattern: First Name, Last Name, Address, City, State, Postal Code, Phone, Email 1 and Email 2.

I cannot analyze my data unless I bring each complete record in a separate row.

If I had few records, I could retype them. However, If I have thousands of records, it’s better to automate the process of fixing them, by creating a macro, and this is what we’ll be doing.

Before recording, I have to decide whether it is a relative or an absolute macro. This one is relative macro because, while recording I fix only the first record… while every time I run the macro, I will fix a different record. So, I want to repeat the same steps, in the same sequence, relative to a starting cell (the empty cell on top of the record to fix).

To tell Excel I am recording a Relative macro, I have to click the command “Use Relative Reference” in the Code group to the left side of the developer tab.

Selecting the starting Cell: Cell A2

Start the recording by clicking on “Record Macro” in the Code group to the left side of the developer tab. You can also Start/Stop recording by clicking on the small square to the far left side of the Status Bar.

The Macro dialog Box opens.

Type a name for the macro. Better use a descriptive name e.g. “FixRecords” without spaces. Capitalizing the first letter of each word improves readability.

Assign a shortcut by clicking in the box to the left of CTRL and press SHIFT + any character. I will use Shift + P. To run the macro, we use CTRL + SHIFT+ P

Keep the default location for storing unchanged “This Workbook” ► Hit OK.

We are now in the process of recording.

Macro Steps:

Move one cell down by hitting the down arrow once.

Select the entire first record by dragging from A3 to A11

Copy the entire selected record (Home Tab ►Copy)

Select the starting cell A2 one more time

Click on the down arrow of the Paste command and select from the menu “Transpose” (the rightmost option in the second row) ► The first record is now fixed.

Delete the original record: Select the rows #3 to row #11►Right click and select Delete.

Prepare for running the macro by selecting the Ending cell ►cell A3. This is the cell in the same relative position for the next record.

Stop the recording either by clicking on Stop Recording on the Developer Tab or by clicking on the small solid square to the left side of the Status bar.

Test your macro by using the assigned shortcut: CTRL + SHIFT + P

Every time you use the shortcut, you fix another record.

NB: when you reach the last record and go beyond, you get a runtime error. This can only be fixed in VBA.

Now let’s close the File without saving, and re-open it to repeat by using shortcuts.

Using Shortcuts ONLY

Add the Developer tab (if not already there) by using the shortcut: ALT , F , T , C. (consecutive shortcut not simultaneous) ►Check the box for Developer.

Select Cell A2

Select a Relative macro by using the shortcut: ALT , L , U (consecutive shortcut not simultaneous)

Start the recording by using the shortcut: ALT , L , R (consecutive shortcut not simultaneous)

Name the macro and set the shortcut (as above) ►Hit OK

Move one cell down by hitting the down arrow once.

Select the entire first record by hitting CTRL + SHIFT +

Copy the first record by using the shortcut: CTRL + C

Shrink your selection to the active cell (the top one) by using the shortcut: SHIFT + Back Space

Move to the cell above (Cell A2) and transpose by using the shortcut: ALT , E, S, E (consecutive shortcut not simultaneous)

Shrink your selection to the active cell (the leftmost one) by using the shortcut: SHIFT + Back Space

Select the first cell in the record to delete (Cell A3) by hitting the Down Arrow once

Select the original record: Select from A3 to A11► by using the shortcut: CTRL + SHIFT +

Expand your selection to select the entire rows #3 to #11 by using the shortcut:

Shift + Spacebar

Delete The selected rows by using the shortcut: CTRL + Minus Sign (on the numeric key pad).

Shrink your selection to the active cell (A3) by using the shortcut: SHIFT + Back Space.

Stop the recording by using the shortcut: ALT , L , R (consecutive shortcut not simultaneous)

Now enjoy testing your macro using the assigned shortcut CTRL + SHIFT + P

NB: All shortcuts using the CTRL key are Simultaneous shortcuts, while All the Shortcuts using the ALT key are consecutive one.

If you want to create a macro from scratch, you read our friends blog here to learn more about it.

You can watch the Video tutorial here below: