Excel macro is a sequence of commands that tell Excel to perform a series of tasks. Generally, those are repetitive tasks, which are prone to errors if repeatedly keyed manually. They are created using a macro recorder, which records the user's keystrokes while performing the repetitive task, then replays those keystrokes every time the macro button or shortcut key is used.

What are Macros used for:

VBA Programmers and Excel end-users create and use macros to simplify their workflow, reduce a series of steps down to a single statement, or to automate repeated tasks. For instance, instead of manually separating first name, last name, and phone number from one column into three, you can create a macro function, and instead of redoing the same header data on every spreadsheet you create, use a macro.

Macro fundamentals:

There are three essential tips you have to be aware of when using macros:

• Always begin your macro at the Home position by using the shortcut keys Ctrl+ Home.
• Always use directional keys when navigating through the spreadsheet (Up, Down, End, Right, Left)
• Always use relative cell addresses. The reason behind this is the keystrokes that macros record are relative to the starting cell’s location, that is why the 1st rule is important (A1 - Home position)

Macro tips:

• Use relative cell references. Using Absolute cell references restricts the macro’s ability to function if anything changes in the dataset.

Relative cell reference
Absolute cell reference

• Keep your macro functions short and focus on one task per function, you can always combine multiple macros later on. For instance, create the first macro to sort your data, second macro to extract particular information of a dataset, third macro to print the extracted value, etc. If you would combine all these tasks into a single macro, it will run slower. Additionally, if the macro breaks, it will take a longer time to find the error. In contrast, if you create multiple smaller macros and run them separately, you can review the results much faster with increased precision.
• Constant values which never change in your dataset should be entered in advance. For instance, these can include product ID's, product prices, product descriptions, customer information, etc. The main function of macros is to perform monotonous jobs such as entering formulas, sorting, formatting, filtering Pivot table's, creating charts, or building complex Tables.

Essential keyboard shortcuts:

• The End key in combination with the arrow keys (up, down, left, right) is your bread and butter which can help you navigate through a spreadsheet and making sure the macro will execute the right way.
  Let's assume you want to highlight column A, from the Home position. Press the End key, then Shift+ Down arrow (simultaneously), and the column will be highlighted.
  

• You can highlight your entire data table by pressing Ctrl+A. By pressing Ctrl+ A twice you will highlight the entire spreadsheet ( Press and hold the Ctrl key, then press the letter A twice)
  

• With the help of the New Excel Ribbon, shortcut keys are displayed on the Ribbon Menu, you just have to press Alt key once (press the key again to turn the option off) and let the shortcut keys help you navigate through functions while recording your macro.
  
  

Setup your first macro:

In order to record a macro, go to View tab or developer tab > Macros > Record Macro. In the Record Macro dialog box, you will have to provide the following information:

• Macro Name: the first character must be a letter, followed by letters, numbers, or underscores. No other characters are accepted.
• Shortcut key (optional): There's a limited option of unique shortcuts natively available. From default, two shortcuts are certain, CTRL+ J and CTRL+ M. If you take any other shortcut, your macro will overwrite that key’s primary function.
• Store Macro: Leave the field on the default "Personal Macro Workbook"
• Description (optional): Describe what the macro does.

Record your macro:

Imagine that you receive a spreadsheet with a list of customers with contact phone numbers that are differently formatted with dashes, periods, parentheses, plus signs and letter.
We have to properly format our table by recording a macro that will replace all the periods and parentheses and replaces them with dashes, also format the cells for centered text, except the customer name fields in column A.

1. Select the Developer tab OR View tab > Macro > Record Macro button.
2. In the following dialog box, enter a name for the Macro, Shortcut key (optional), and a Description of your macro (optional). Leave Store Macro on default and press OK.
3. At this stage, you are in the Record mode where every step you do, the macro records it, so you have to focus on the objective.
4. Follow the key shortcuts to start recording your macro:

Macro part 1 - Align phone numbers

• Ctrl+Home (start the recorded table position at A1 cell)
• Ctrl+A (This will highlight the selected data table)
• Alt-H-F-A (Open the Alignment tab for the selected cells). Keys separated by a dash "-" expect to be pressed separately; keys joined by "+" expect to be pressed together.
• Set the Horizontal and Vertical to "Center" field
• Press OK button
  

Macro part 2 - Align customer names:

• Ctrl+Home
• Down navigational key (selecting the first Customer field)
• Shift+End–Down (Highlight first column of the table)
• Alt-H-F-A ((Open the Alignment tab for the selected column)
• Set the Horizontal and Vertical to "Left" field
• Press OK button
  

Macro part 3 - Edit phone numbers to the desired format:

• Ctrl+Home
• Ctrl+H (opens the Replace tab from the Find&Replace dialog box)
• Type . (period) in the "Find what" field box
• Press TAB key to navigate to the next field
• Type - (dash) in the "Replace with" field box
• Navigate with TAB key to "Replace All" button and press ENTER (this will replace every period to dash on the worksheet)
  
• Use TAB key to navigate to "Find what" field box
• Type ) right parenthesis in the "Find what" field box
• Use TAB key to navigate to "Replace All" button and press ENTER
• Use TAB key to navigate to "Find what" field box
• Type** (** left parenthesis in the "Find what" field box
• Use TAB key to navigate to "Replace with" box and delete the “-” dash, leave it empty. (this will delete every left parenthesis )
• Use TAB key to navigate to "Replace All" button and press ENTER
• Navigate with TAB to "Close" button
  

Stop Recording your macro

Stop Recording your macro with your prefered method:

• Alt-W-M-R keyboard shortcut
• Alt-L-R keyboard shortcut
• navigating to the View tab or developer tab > Macro > Stop Record button.
  

Ways to run your macro

Run your macro with one of the following methods:
a) Navigating to** View tab or Developer tab > Macro > View Macro > select Macro > Run**.
b) Use the navigational keyboard Alt-W-M-V
c) Create a keyboard shortcut for your most used macro (example Ctrl+Shift+M)
d) Create a custom button at the Quick Access Toolbar
e) Create a custom button under Developer ribbon tab

Shortcuts to run your macro:

Quickly access your most used macro by creating a macro button and adding it to the Quick Access Toolbar or to a Ribbon menu:

Adding macro to Quick Access Toolbar

• Select File > More Commands > Quick Access Toolbar.
  
• When the screen appears, pick "Macros" from the Choose Commands From field panel (left box)
• Select the macro template from the list that you want to display.
• Click the "Add" button to move the selected macro to the other (right box) panel.
• Click the "Modify button" at the bottom of the right panel, pick a symbol you want to associate the macro with
  
• The new macro button will appear on the Quick Access Toolbar
  

Adding macro to Ribbon menu

• Select File > More Commands > Customize Ribbon.
• Check the "Developer" box inside the right panel, then click the plus + to expand and close the group under that tab.
• Select the Newly created group button (bottom center of the right panel), and press the "New Group" button at the bottom of the list.
• Next, press the "Rename" button, and enter the name for your new group and select a symbol from the listed icons; click OK when finished.
• Select Macros group from the left panel and all the macros you have available on your system will resemble.
  
• Pick the macro from the list you want to attach to the Ribbon and click the "Add" button to add it to your new custom group. Press OK after you finished.
• Go to the Developer tab where the Macros group is located (right end of the menu) with your chosen macro functions. This way, you can execute your task quickly with these custom made macro buttons.