How to automate your spreadsheets with Excel macros

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:

Macro tips

Essential keyboard shortcuts:

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:

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

  1. Ctrl+Home (start the recorded table position at A1 cell)
  2. Ctrl+A (This will highlight the selected data table)
  3. 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.
  4. Set the Horizontal and Vertical to "Center" field
  5. Press OK button



Macro part 2 - Align customer names

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



Macro part 3 - Edit phone numbers to the desired format

  1. Ctrl+Home
  2. Ctrl+H (opens the Replace tab from the Find&Replace dialog box)
  3. Type . (period) in the "Find what" field box
  4. Press TAB key to navigate to the next field
  5. Type - (dash) in the "Replace with" field box
  6. Navigate with TAB key to "Replace All" button and press ENTER (this will replace every period to dash on the worksheet)



  7. Use TAB key to navigate to "Find what" field box
  8. Type ) right parenthesis in the "Find what" field box
  9. Use TAB key to navigate to "Replace All" button and press ENTER
  10. Use TAB key to navigate to "Find what" field box
  11. Type ( left parenthesis in the "Find what" field box
  12. Use TAB key to navigate to "Replace with" box and delete the “-” dash, leave the box empty. (this will delete every left parenthesis )
  13. Use TAB key to navigate to "Replace All" button and press ENTER
  14. Navigate with TAB to "Close" button



Stop Recording your macro

Stop Recording your macro with your prefered method:

Ways to run your macro

Run your macro with one of the following methods:

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


  1. Select File > More Commands > Quick Access Toolbar.



  2. When the screen appears, pick "Macros" from the Choose Commands From field panel (left box)
  3. Select the macro template from the list that you want to display.
  4. Click the "Add" button to move the selected macro to the other (right box) panel.
  5. Click the "Modify button" at the bottom of the right panel, pick a symbol you want to associate the macro with



  6. The new macro button will appear on the Quick Access Toolbar



Adding macro to Ribbon menu

  1. Select File > More Commands > Customize Ribbon.
  2. Check the "Developer" box inside the right panel, then click the plus + to expand and close the group under that tab.
  3. Select the Newly created group button (bottom center of the right panel), and press the "New Group" button at the bottom of the list.
  4. 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.
  5. Select Macros group from the left panel and all the macros you have available on your system will resemble.



  6. 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.
  7. 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.





ABOUT

RECENT ARTICLE

SIGN UP

Stay up to date on the most popular data analysis and visualization products out there, and learn how to make data-driven decisions. All of our courses ensure quality, usability, and uniqueness of the content.

By creating an account you agree to our Terms & Privacy.