• About
  • Contact
  • Privacy Policy
  • Submit For Review
  • Template Gallery
  • Excel 2016 Udemy Course
  • Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

TheAppTimes

App Reviews, Tutorials, & Gadget News Hub

  • Gadgets
  • How To
  • Mobile Apps
  • Software and Web Apps
  • Buying Guides
  • Show Search
Hide Search
Home » How To » How to Create Excel Macros Using Macro Recorder

How to Create Excel Macros Using Macro Recorder

January 31, 2015

Excel users often find themselves performing certain tasks on a repetitive basis, such as preparing reports on a weekly or monthly basis. Such tasks can involve repeated actions such as copying data from one particular source, or formatting them in a particular style, or applying conditional formatting for certain numbers and so on. Instead of performing these mundane tasks repeatedly, you can get Excel to do it for you – using macros.

What are Excel Macros?

Macros are small programs or scripts that are created in the Visual Basic Editor. The beauty of using Excel is that you don’t have to be a geek or know coding to create macros. The Excel Macro Recorder button automatically does that for you. All you need to do is replicate the actions and Excel will write out the macro for those actions in the background. What is then left for you to do is assign that macro to an object, such as a command button, and voila, its ready to put to use.

In this post, we’ll take a look at how you can create Excel macros using the Excel Macro Recorder.

How to Create Excel Macros

In this tutorial, I’ll show you how to create a macro to highlight cells having a value of less than 40 and mark them as red. For this purpose, I have an Excel sheet containing the names of employees and the hours they clocked in during the week. I want to highlight the cells of employees who clocked in less than 40 hours and mark them red. I use a simple conditional formatting rule to do this.

Now, instead of having to go to Conditional Formatting->Highlight Cell Rules->Less than-> 40 and selecting red for every new weekly sheet, I can create a macro and just hit a button to instantly get the results for the rest of my worksheets. Let’s see how.

You can download the Weekly Timesheet to try the activity yourself. The solution sheet is attached here.

A. Enable the Developer Tab

Excel’s Macro related buttons are available in the Developer tab. The Developer tab is not enabled by default, so the first thing you need to do is activate it. You will find all the instructions to do that here. Or simply follow the instructions below.

1. Launch Excel. Select the File tab and click Options.

2. In the Excel Options dialog box, select Customize Ribbon.

3. In the Customize the Ribbon list box, check the Developer check box and click OK.

B. Change the Macro Security Settings.

In the next step, you need to modify the macro security settings. Macros are usually disabled to prevent any dangerous codes from running on the program. We will need to temporarily enable them whenever we create Excel macros. To do this:

1. On the Developer tab, in the Code group, click Macro security.

2. In the Trust Center dialog box, under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run) and then click OK.

ALSO READ:  How to Use Text to Speech to Check Errors in Excel

C. Start Recording the Macro

1. In the Developer tab, in the Code group, click Record Macro. Alternatively, click the Record Macro button in the Status bar. (Right-click the Status Bar and select Macro Recording if you can’t see the button.)

2. In the Record Macro dialog box, in the Macro name box, enter a name for the macro. The macro name must always start with a letter and must contain no spaces. We are going to name this macro Highlight_Hours. Always try to give a relevant easily recognizable name for your macro.

3. You can assign a shortcut key if you work with shortcuts, but the danger here is that if you accidentally use a shortcut that is already assigned to an action, it will be overwritten by the macro. You can skip this step instead and use a button to run the macro.

4. In the Description text box, enter a description for the macro.

5. Click OK to start the recording.

6. On the Week 1 sheet, select cells C2 to C26.

7. On the Home tab, in the Styles group, click Conditional Formatting.

8. From the Conditional Formatting drop down list, select Highlight Cells Rules and then select Less Than.

9. In the Less Than dialog box, enter 40 and select Light Red Fill. Then click OK. Your data displaying work hours less than 40 is now highlighted in light red.

10. Go to the Status Bar and click the Record Macro button to stop the recording. Alternatively, in the Developer tab, in the Code group, click Stop Recording.

You have now recorded your first macro.

D. Assign the Macro button to the Quick Access Toolbar

Now its time to assign a button to activate the macro on other sheets in the workbook.

1. Click the Quick Access Toolbar dropdown menu and select More Commands.

2. In the Excel Options dialog box, in the Choose commands from dropdown, select Macros.

3. In the results section, you will see the command for the macro you created. Select it and click Add.

add the macro command to create excel macros

4. Click OK to close the dialog. The macro button is now added to the Quick Access Toolbar.
Now navigate to the rest of the worksheets and click the macro button to quickly ascertain the workers who have logged in less than 40 hours of work.

This is just one simple way to record a macro. You can create for macros for any of your repetitive tasks. Just remember to accurately record your actions in a macro and you’re set to go.

Related Reading

A few other macros you may be interested in:

Remove Hyperlinks in an Excel 2010 Worksheet With a Simple Macro

Extract URLs From Hyperlinked Text Using Macros in Excel 2010

How to Unprotect Multiple Worksheets in One Go Using Excel Macro

Macro to Concatenate Columns in Excel 2010

How to Record Microsoft Word Macros

Tweet10
Share20
WhatsApp
Pin4
Buffer
34 Shares
Tweet10
Share20
WhatsApp
Pin4
Buffer
34 Shares

Filed Under: How To Tagged With: excel how to, excel macros, learn excel

You May Also Like

Ways to Use Excel Mobile - TATFI
10 Creative Ways to Use Excel Mobile in your Business
How to Get Rid of the Paste Options Menu in Word, Excel and PPT
How to Get Rid of the Paste Options Menu in Word, Excel and PPT
How to Calculate Number of Days Between Two Dates in Excel
How to Calculate Number of Days Between Two Dates in Excel

About Adeline Gear

Adeline is a tech enthusiast who loves exploring the latest tools and applications in the marketplace. She also loves her Android and spends time reviewing apps and playing games when she has the time.

Reader Interactions

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Hottest New Gadgets!

Amazfit Falcon Smartwatch - TATFI

The Amazfit Falcon is a Military Grade Smartwatch with Premium Looks and Great Features

Couchmaster Cyboss Ergonomic Couch Desk - TATFI

Couchmaster Cyboss is an Ergonomic Desk to Keep Off Neck Pain

Philips Hue Festavia String Lights - TATFI

Add a Bit of Sparkle to Your Christmas Tree with Philips Hue Festavia String Lights

Samsung Galaxy Tab A7 Lite Kids Edition - TATFI

This Kid Friendly Tablet from AT&T is a Thoughtful Gift for Your Little One This Christmas

Is An iPhone Really Worth It - TATFI

Is an iPhone is Really Worth It?

Devices You Might Want to Try - TATFI

4 Fantastic Devices You Might Want to Try

Amazon Kindle 2022 - TATFI

Meet the Amazon Kindle 2022 – Packed with New Features

Popular Tags

android android apps android games android how to apple casino games chrome extensions excel excel how to facebook gadgets gaming gmail google instagram ios apps ios games ios how to ios tips ipad iphone kids apps learn excel management microsoft music apps new releases outlook how to photo apps powerpoint powerpoint how to productivity Samsung security smartphone smartphones software tablet tablets twitter web browsers windows 10 word how to xbox youtube

Disclosure

TheAppTimes is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.
Approaches to Digital Marketing - TATFI

Blending Technical and Content-Centric Approaches to Digital Marketing

Tech Tips For Startups - TATFI

Useful Tech Tips For Startups

Boox Tab X Android eReader - TATFI

Boox Tab X is an Android eReader That Helps You Do More Than Just Read

Best e Readers to Buy in 2023 - TATFI

5 Best eReaders to Buy in 2023

Best Translation Apps for Android - TATFI

5 Great Translation Apps for Android

Dynamic Island Feature on Your Android - TATFI

How to Get the Dynamic Island Feature on Android

Connected TV Advertising In 2023 - TATFI

5 Things You Need To Know About Connected TV Advertising In 2023

Smart Cost Cutting Strategies for SMEs - TATFI

5 Smart Cost Cutting Strategies for SMEs

Frequently Asked Questions About PMP - TATFI

Frequently Asked Questions About PMP

Technologies behind the online casino world - TATFI

The Latest Technologies Behind the Online Casino World

B2B Marketing Strategies To Grow Your Business - TATFI

Effective B2B Marketing Strategies To Grow Your Business

Exciting Games To Play This Christmas - TATFI

Exciting Games To Play With Family And Friends This Christmas

TheAppTimes © 2023 // Runs on StudioPress