How to Use Excel Macros to Save Time and Automate Your Work

If you work with Excel spreadsheets regularly, you probably find yourself repeating the same steps over and over. Wouldn’t it be nice to click a button and have those tasks happen automatically?

That’s where Excel macros come in. You can use macros to automate repetitive tasks, which can save you a lot of time and effort.

What is excel macro?

An Excel macro is a recorded sequence of Excel commands and actions that you can play back as many times as you want. Macros can be used to automate almost any sequence of tasks in Excel, from entering your company’s name and address into a spreadsheet to something as complex as creating a custom report. If you can do it in Excel, you can probably automate it with a macro.

To use a macro, you first need Record This. You perform the sequence of steps you want to automate, and Excel tracks them all and saves them in a macro. After you record a macro, you can play it again at any time. You can also assign a keyboard shortcut to a macro, so you can run it with just a few keystrokes.

Excel macros are based on Microsoft’s Visual Basic for Applications (VBA) programming language. When you record a macro, Excel translates your actions into VBA code under the hood. So in addition to creating macros by recording them, you can also write them manually in VBA code. In this article, I’ll focus primarily on creating macros by recording them – the easiest and fastest way. After that, I’ll discuss how you can edit or write macros from scratch using VBA and provide some resources for self-learning.

how to record a macro

To help illustrate the process, I’ll use a small sample data set. Let’s say we’re responsible for taking customer names and balances and doing two things: first splitting the customer names into separate first and last names, and then highlighting all those who have a balance greater than zero. . In this example, we are given seven clients to work with.

Excel Macros 01 Sample Data Simeon Brathwaite/IDG

Our starting data set. Notice the File menu at the left end of the Excel ribbon. (Click on the image to enlarge it.)

To create the macro, we’ll use the Developer tab in the ribbon toolbar at the top of the Excel window. It is not present by default, so we have to add it. click on file Click on the tab on the far left of the ribbon (highlighted in the screenshot above) and then on the screen that appears alternative at the bottom of the left column.

The Excel Options screen appears. choose customize Ribbon from the left navigation bar. Then, in the “Customize the Ribbon” area on the right, look for the “Main Tab” list and check developer checkbox. Click Ok,

Excel Macros 02 Excel Options Developer Tab Simeon Brathwaite/IDG

To turn on the Developer tab in the ribbon, check the Developer checkbox. (Click on the image to enlarge it.)

(In macOS, click Excel Menu at the top of the screen and select Preferences > Ribbon & Toolbars, In the “Customize the Ribbon” area on the right, look for the “Main Tabs” list and check developer checkbox. Click save,

Once you have the Developer tab, click on it and you will see options similar to the ones shown on the screen below.

Excel Macros 03 Macro Command Ribbon Simeon Brathwaite/IDG

Highlighted commands on the Developer tab help you record and manage macros. (Click on the image to enlarge it.)

To start recording your first macro, click record macro button, and you will be presented with the options below. First, come up with a name for your macro, keeping in mind that you can’t use spaces. For readability, you may want to separate the words with something like , either ,, Add shortcut keys or descriptions if you’d like, but these aren’t necessary.

Excel Macros 04 Record Macros Simeon Brathwaite/IDG

To get started, give your macro a name. (Click on the image to enlarge it.)

once you hit Ok, the icon should change to indicate that the macro is recording your actions. It is important that you do only what you want the macro to do and nothing else from this point until you click stop recording,

Now that the macro is recording, let’s get down to business. First, highlight the Balance Due column, then right-click and choose insert column, This will add a new column between the Customer Name and Remaining Outstanding columns.

excel macros 05 column inserted Simeon Brathwaite/IDG

Insert a new column to the left of the remaining column. (Click on the image to enlarge it.)

Next, we’ll rename the column by replacing “Customer Name” with “First Name” and adding the title “Last Name” to the column we just created. Select the customer name in the first column (cells A:2 to A:8), select Information tab in the ribbon, select More text for columns command.

A wizard appears displaying the following options. choose demarcated and hit next,

Excel Macros 06 Text to Column Wizard Simeon Brathwaite/IDG

On the first screen of the Convert Text to Columns wizard, select demarcated, (Click on the image to enlarge it.)

Next, choose space Checkbox to specify that words separated by spaces should go into separate columns. Click next,

Excel Macros 07 Text to Column Wizard2 Simeon Brathwaite/IDG

On Screen 2 of the wizard, select space as your delimiter. (Click on the image to enlarge it.)

For the last option, keep everything as it is and hit finish,

Excel Macros 08 Text to Column Wizard3 Simeon Brathwaite/IDG

Do not make any changes to the last screen of the wizard. (Click on the image to enlarge it.)

You should have the following result with the first and last names in separate columns.

excel macros 09 first last name separated Simeon Brathwaite/IDG

First and last names are now in separate columns. (Click on the image to enlarge it.)

Finally, we need to highlight each customer with a balance greater than zero. Highlight all the data in the third column (cells C:2 to C:8) and then click Home > Conditional Formatting,

Excel Macros 10 Ribbon Conditional Formatting Simeon Brathwaite/IDG

The last step is to apply conditional formatting rules to the data. (Click on the image to enlarge it.)

choose highlight cell rule and then more than, enter 0 and click Ok To highlight each customer with a balance greater than zero.

excel macros 11 format cells dialog box Simeon Brathwaite/IDG

Formatting cells that are greater than zero.

This should be the end result:

Excel Macros 12 Formatted Data in Spreadsheet Simeon Brathwaite/IDG

All cells with a balance greater than zero are now highlighted in light red. (Click on the image to enlarge it.)

Now that you have completed the task sequence, go back developer tab and click stop recording, Your first Excel macro is complete.

Stop Recording Excel Macros 13 Simeon Brathwaite/IDG

When you’ve recorded your macro, hit stop recording, (Click on the image to enlarge it.)

Important notes about working with macros

If you want to run your macro again, just click macro button and it will be available for you to play. Or, if you’ve assigned a shortcut to the macro, just press the key combination to run it.

excel macros 14 macros list Simeon Brathwaite/IDG

Click the Macros button at any time to view a list of macros available for the workbook. (Click on the image to enlarge it.)

Note that you cannot save a spreadsheet with macros as a traditional .xlsx workbook. To avoid losing your macros, you must save it as an Excel macro-enabled workbook (.xlsm).

excel macros 15 save as xlsm Simeon Brathwaite/IDG

To preserve macros, save your workbook in .xlsm format. (Click on the image to enlarge it.)

Once you make that change, whenever you want to work with a new data set, you can reopen the workbook and import the data you want to work with. . information tab and select get data, You’ll be able to import data from files, databases, and other online services.

excel macros 16 import data Simeon Brathwaite/IDG

To preserve macros, save your workbook in .xlsm format. (Click on the image to enlarge it.)

Be aware that in many cases macros are disabled by default. This is because, as Microsoft notes, “VBA macros are a common way for malicious actors to gain access to deploy malware and ransomware.” To protect organizations from such threats, Microsoft now blocks macros In files from the Internet – and sometimes in files stored on company shared drives. Your organization may have additional restrictions on macros.

So whenever you open an Excel workbook with macros (including your own), it is likely that you will see a warning message like the one below. If your workbook is one that you created or from a trusted source, go ahead and enable it. If the macro is from an untrusted source, however, do not enable it, as it may be malware.

Excel Macros Enables 17 Macros Simeon Brathwaite/IDG

You may see a warning when you open a workbook that contains macros.

Another important feature to be aware of when recording macros is the use of relative references. This feature makes it so that no matter where the data starts on the spreadsheet, the macro will be able to find it and begin processing there.

For example, the macro we created would always start processing on column A because relative references were not turned on. However, if we performed the same operations but clicked use relative references First, then the macro will be able to find where the information starts (e.g. column C) and start processing it from that point. This feature is useful if the data you’re working with won’t always start at the same point.

Excel Macros 18 Relative Reference Simeon Brathwaite/IDG

choose use relative references If not your data will always start at the same point in the worksheet.

How to edit or create a macro with code

If you want to see the VBA code behind the macro, go here developer tab click macroSelect the macro and then click edit,

You will be taken to a pane where you can view the source code for the macro you created. In the screenshot below, the underlined items show the actions we want to take, such as renaming headings and selecting rows. You can change these for different use cases. For example, if the data you work with runs from the range A1:A20, you may want to expand the range to include all possible cells.

excel macros 19 vba code Simeon Brathwaite/IDG

Peeking at the code contained within the macro. (Click on the image to enlarge it.)

If you want to try your hand at writing macros from scratch, there are many resources online to learn how to write VBA scripts, including codevars, UdemyAnd codecademy, Back in Excel, click Developer > Macros > Create, You will be taken to a blank pane where you can write VBA code.

Copyright © 2022 IDG Communications, Inc.




Leave a Reply

Your email address will not be published. Required fields are marked *