How custom Excel macros can streamline your workflow

Content
  1. Use macros to speed up your routine data tasks
  2. When to use a macro?
  3. Macros vs Templates
  4. Excel macro recording
  5. Add Developer Tab in Excel
  6. Create a basic macro in Excel
  7. Run the created macro in Excel
  8. Create a macro button in Excel

Use macros to speed up your routine data tasks

Excel macro is a set of programming instructions stored as a procedure. Macros avoid repeating steps for frequently performed tasks and can be invoked by a keyboard shortcut, a toolbar icon, or a button added to the worksheet itself.

The information in this article applies to Excel 2019, 2016, 2013, 2010, and Excel for Mac.

When to use a macro?

Macros can make working with spreadsheets easier by automating repetitive tasks. These tasks include everything from complex calculations that require formulas to simple formatting tasks. An example of a recurring task is adding number formatting to new data. Another example is applying cell and sheet formats such as borders and shading.

Other repetitive tasks for which macros are useful include:

  • Add or remove rows and columns

  • Protect or unprotect worksheets

  • Cell range selection

  • Add the current date to a sheet

Macros vs Templates

Using macros can help save time when performing repetitive tasks. If you regularly add certain formatting features or content (such as headings or a company logo) to new worksheets, create and save a template file that contains these elements instead of recreating them each time you start a new worksheet.

Excel macro recording

Excel has a built-in macro recorder that records a series of steps using the keyboard and mouse. Excel then converts this series of steps to VBA code. Macro Recorder and Macro Manager can be found under the View tab. To add buttons to a worksheet, a Developer tab must be added.

Add Developer Tab in Excel

The steps to open the developer tab in Excel are simple and can be followed if you have a Mac or Windows PC.

  1. Windows . Select File .

    mac . Select menu excel .

  2. Windows . Select parameters: .

    mac . Select Settings .

  3. Windows . Select Customize ribbon .

    mac . Select Ribbon and Toolbar .

  4. On the right side of the window, in the section Customize ribbon find the parameter Developer and check the box next to it. ,

  5. Select Save to close the window and display the Developer tab in Excel.

Create a basic macro in Excel

Let’s create a simple Excel macro that fills a cell with red, centers the text, and adds bold formatting to the text. While the Record Macro option is available in the Developer tab, we will use the View tab to make the process easier.

  1. Select Vision .

  2. Select Macros > Record macro .

  3. Under macro name, enter the name of the macro. Feel free to follow our lead for this example and mention it underline .

  4. In the “Save macro in” section, select This workbook to add the macro to the current file.

  5. To create a hotkey, enter a letter or number that, when pressed in conjunction with other highlighted keys, will execute a macro. For this example we will use the letter E .

  6. Enter Description macro. For this example, we’ll leave this field blank.

    The description reminds you what the macro is doing while it is running. If you plan to create multiple macros, fill in the description so you can quickly select the right one for the job.

  7. Select Okay .

  8. The macro starts recording. All changes made to your spreadsheet are logged.

  9. Select a cell. This is the cell that will be changed in the worksheet and where the macro will be written.

  10. On the Home tab, select greasy to highlight cell text, select Centre to align the contents of the cell and select Fill color to change the cell. background for red.

  11. Select Vision and choose Stop Record . Your macro has been created and saved in the workbook.

Run the created macro in Excel

After a macro is assigned a shortcut key, apply the macro by selecting the cell and pressing the shortcut key. If you have not selected a shortcut, select Vision and choose View macros . Any macro can be run by selecting and selecting it walk .

Create a macro button in Excel

Macros can be run with a hotkey. Or you can create a custom button for your spreadsheet that, when clicked, will run the specified macro.

Follow this example to create a basic macro button in Excel for a previously created macro To emphasize .

The Developer tab must be enabled. Use the steps mentioned earlier in this article to add a Developer tab to Excel.

  1. Select Developer .

  2. In the Controls group, select Insert > Knob .

  3. Select the cell on the worksheet where you want to place the button. The button can be moved and resized after it is added to the sheet.

  4. Select the macro from the list that you want to run when the button is clicked. In our example, select the macro To emphasize .

  5. Select Okay .

  6. A button appears on the worksheet that you can move or resize. To change the text displayed on a button, double-click the button and enter the desired text.

  7. . Select a cell and then click the button to run the macro.

Leave a Reply

Your email address will not be published.