OBINTUSACADEMY
HOME CONTACT SERVICES PRIVACY
COMPUTER BASICS MS-PACKAGES
Excel - How To Create Macro

MICROSOFT EXCEL

MODULE ONE

Prerequisite Introduction to Microsoft Excel Getting Started MS Excel Environment Title Bar Quick Access Toolbar Search Account Name Buttons Menu Tab Ribbons Dialog Box Launcher Name Box Insert Function Formula Bar Worksheet Area Worksheet Navigation Creating New Sheet Status Bar Worksheet View Format Zoom Slider MS Excel File Tab Entering Data Saving Your Workbook Opening Saved Workbook Copy, Delete and Hiding Sheet Closing Entire Workbook

MODULE TWO

Cells, Rows and Columns Resizing Rows and Columns Inserting Data Select, Delete and Move Data Copy, Paste and Cut Find and Replace Checking Spellings Inserting TextBox Inserting Symbols Inserting Special Characters Zooming Undoing and Redoing Changes

MODULE THREE

Mini Toolbar Setting Fonts Text Decoration Setting Text Case Text Alignments Text Orientation Increase and Decrease Indentation Wraping Text Merging Cells Cells Borders Cells Shades Copy Formatting Insert and Delete Worksheet Format Worksheet, Cells, Rows and Columns

MODULE FOUR

Insert Tables Insert Pictures Insert Shapes and Smart Art Insert Screenshots Insert Icons Insert 3D-Models Using Add-ins Insert Charts 3D Maps Creating Sparklines Insert WordArt

MODULE FIVE

Addition Subtraction Multiplication Division Using Formulas Using Functions Quick Functions

MODULE SIX

Fill Handling Sorting Filtering References (Relative and Absolute) Conditional Formatting Applying Styles

MODULE SEVEN

Page Themes Page Colors Page Fonts Page Effects Page Header and Footer Page Numbering Page Margins Page Orientation and Size Print Area Page Breaks Setting Background Print Titles Preview and Print Share Workbook Freezing Panes Range Data Validation Excel Templates MS Excel Shortcuts Conclusion

HOW TO

Excel How-To Send Email Separate Text Date and Time Format Macro Thesaurus Insert Comments Password Workbook Count Characters Count Words Add and Remove Hyperlinks Insert PDF in Excel Editing and Formatting Restrictions How to Compare Columns How to Compare Worksheets Add Signature in Worksheet Calculate Age Calculate Mean Calculate Median Calculate Standard Deviation Find the Mode Add and Remove Commas How to Remove Filter Convert Excel to CSV Excel to PDF Excel to Ms-Word Document Excel to Picture Formats Duplicate Rows and Columns Hide and Unhide Rows and Columns Rows and Columns Conversion How to Remove Gridlines Create Graphs Insert Check Box Add and Remove Watermark Logical Operators Logical Functions Text Functions Square Symbol Pi Symbol Selecting Different Columns Creating QR Code Creating Bar Code Insert Bullet Points Generate Random Numbers Combine Two Columns Wrap Text Create Dropdown List

How To Create Macro

PREVIOUS NEXT
definition
DEFINITION:

Macro is the combination instructions and commands to form a single command that is used to automatically complete an operation or task in Excel by a single click.

Macros are useful when you have multiple similar tasks to perform everyday. It is used to record editing and formatting so that it can be done once by a single click. In this tutorial, I am going to be teaching and showing you step by step how to create a macro in Excel. This tutorial might be a bit long, so I recommend that you take it step by step without skipping any detail.

  • STEPS TO CREATE AND USE MACRO
  • Step 1 - First we have to include the developer tab in the menu tabs. Open Excel, in the home page click on More options and then from the sub-list displayed click on the Options
    • macro
  • Step 2 - Inside the Excel Options page, in the first column at the left, click on Customize Ribbon. Then go to the second column in the customize ribbon section and mark the Developer option checkbox. Click on the OK button afterwards.
    • macro
  • Step 3 - Now go back or open a new worksheet, and will you will notice that the Developer tab is now added to the menu tabs.
    • macro
  • Step 4 - Click on the Developer tab and you will see the various group of commands in the developer tab ribbon.
    • macro
  • Step 5 - We are going to be creating a macro that separates the column (full name) into two columns (first name and last name). So, create a simple data as seen in the image below for this tutorial.
    • macro
  • Step 6 - Once you are done with step 5, you can now start with creating the macro for separating full name into the first and last name columns. Click on the Developer tab, go the Code group and click on the Record Macro button.
    • macro
  • Step 7 - The Record Macro dialog box will be opened. In this dialog box, enter the name of your macro, enter keyboard shortcut (optional), select the option to store macro, and enter the description of your macro. Then click the OK button.
    • macro
  • Step 8 - Now in the developer tab, inside the code group, the Record Macro button will now be changed to Stop Recording. This means the recording has started, clicking this before recording the actions for the macro button will stop the recording.
    So, don't stop the recoding yet!
    • macro
    note
    NOTE:

    From the next step (Step 9) we have fully started recording the actions for separating full name into first name and last name.

  • Step 9 - Insert a new column in between the full name column and the scores column as seen in the image below.
    • macro
  • Step 10 - Change the first column header to First Name and the type in Last Name in the newly inserted column (the second column) as seen in the image below.
    • macro
  • Step 11 - Select the data of the first column excluding the header (First Name). Click on the Data tab, go to the Data Tools group and click on the Text to Columns option.
    • macro
  • Step 12 - The Convert to Text Wizard dialog box is now opened. In this first page, the Delimited option will already be selected, so click on the Next button to proceed to the next page.
    • macro
  • Step 13 - In this page, select the delimiter you wish to use and then click on the Next button.
    • macro
    definition
    DEFINITION:

    Delimiter is a character, symbol or space that separates a string or text

  • Step 14 - In this page you don't have to do anything here, but you can change the column data format if you wish. Leave the destination input as it is and just click on the Finish button.
    • macro
  • Step 15 - Full name column has now been separated to First and Last name columns.
    • macro
  • Step 16 - Now go click on the Developer tab, go to the Code group and click on the Stop Recording button.
    • macro
  • Step 17 - Congratulations, you have now created a macro which is used for separating full names in one column into first and last names in separate columns. What next?
    • macro
  • Step 18 - Now let's try viewing the macro created and also try customizing it and applying it.
    Click on the View tab. At the far-right side in the view tab ribbon, click on the Macros button. From the dropdown list displayed click on the View Macros option.
    • macro
  • Step 19 - The macro dialog box will be opened. In it, you can see the macro name, where the macro is store, its description and various action buttons at the right side.
    • macro
  • Step 20 - You can choose to see only macros in a specific work book or in all the workbooks. Click the selection button beside the heading Macros in and select the option you want.
    • macro
  • Step 21 - Clicking on the Options button will open the Macros Options dialog box where you can see the name of the macros, edit the keyboard shortcut and description.
    • macro
  • Step 22 - Now, I have edited my keyboard shortcut to Ctrl + F this means anytime I press the keyboard shortcut the macro will be initiated. I also edited the description by removing a few texts which from it.
    After editing you can click on the OK button to save.
    • macro
  • Step 23 - Now, if you click on either the Edit or Step Into buttons, the Microsoft Visual Basic for Applications window will be opened, here you can not only edit the description, but also the column headers, destination and lots more.
    • macro
    tips
    TIP:

    Microsoft Visual Basic for Applications is a programming language and an environment designed by Microsoft which can be used to create macros, perform data processing functions, generate custom forms, graphs, and reports.

  • Step 24 - Here I also edited the description and the second column header from Lastname to Surname. After you are done, you can close the window.
    • macro
  • Step 25 - Now let's use the macro. Open a new worksheet (Sheet2) and create a data with two columns full name and scores as seen in the image below.
    macro
    Now you can use the keyboard shortcut you use to save your macro. Remember in our case, we edited the keyboard shortcut to Ctrl + F, so press the shortcut on your keyboard. After using the keyboard shortcut jump to Step 28.
    macro
  • Step 26 - So, if you don't want to use the keyboard shortcut. Click on the Developer tab, and go to the Code group and click on the Macros option.
    • macro
  • Step 27 - The Macro dialog box will be opened. Here, click on the name of the macro want to use and click on the Run button at the right side of this dialog box.
    • macro
  • Step 28 - Macro applied and the data have been formatted successfully.
    • macro macro
note
NOTE:

After formatting your data using macro, it cannot be undone using Ctrl + Z.

  • USING RELATIVE REFERENCE WHILE CREATING MACRO
  • Relative reference allows you to be able to apply the recorded macros at different places in the excel sheet
  • Step 1 - Create another worksheet (Sheet3) for this part of the tutorial. Then create the data as shown in the image below. Click on the Developer tab, go to the Code group and click on Use Relative References button.
    • macro
  • Step 2 - Now follow the same method to record macros, make sure the name, keyboard shortcut and description is different from the first macro you created, so that you can easily differentiate between the two of them.
    • macro
    note
    NOTE:

    I didn't repeat the step-by-step process of recording macro, but if you have forgotten this, quickly scroll up to Step 6 in the first section.

  • Step 3 - Now open your Macros. Go to the Developer tab, in the code group click on Macros. In the macro dialog box, there should be two macros. The first one you initially creating without relative references and the second you created with relative references.
    In the image below, you will see there are two macros ready for use.
    • macro
  • Step 4 - To test and use the macro we just created, create another worksheet (sheet4). Create another data and make sure that the location of the data in the worksheet is different from the previous one we used to record the macro. In the previous one, our worksheet data starting from cell A1, but in this one as seen in the image below, it is starting from cell C1.
    • macro
  • Step 5 - Now, lets try using the first macro and see what happens. Click on the first cell in the data you want to apply macro to (Cell 1). Click on the developer tab, go to the code group and click on the macro option (this will open the macro dialog box). From the dialog box, select the first macro you created (mine is: Separating_Names). Then click on the Run button.
    • macro
  • Step 6 - There will bring out an error message. This is because when recording macro without the relative references active, you are telling the excel that this formatting can only be applied at a particular position in the worksheet.
    • macro
  • Step 7 - Now let's try using the second macro in the macro dialog box, select the second macro you created (mine is: Separating_Text_using_relative_reference). Then click on the Run button.
    • macro
  • Step 8 - Macro applied and the data have been formatted successfully.
    • macro
tips
TIP:

When using creating macro if you are not sure that you repeated data formatting will always be in the same position on your worksheet, always make sure you record your macro with relative references.

  • CREATING A BUTTON FOR MACRO
  • You can also create a button and assign it to a particular macro, so that onclick of the button automatically formats your data depending on the macro recorded.
  • Step 1 - Create another worksheet (Sheet5) for this part of the tutorial. Then create the data as shown in the image below. Click on the Developer tab, go to the Controls group and click on Insert options. From the dropdown list of options, click on the button option.
    • macro
  • Step 2 - Now draw out the button shape to your preferred width and height using your mouse or touchpad.
    macro
    After drawing out the button shape, the Assign Macro dialog box will be displayed. In this dialog box, choose which macro to assign the button to, and click on the OK button.
  • macro
  • Step 3 - The button will be successfully inserted with a default name Button 1.
    • macro
  • Step 4 - You can edit the button name to suit the macro you assigned to that button (In my case, I have edited the name to Separating Names as shown in the image below)
    • macro
  • Step 5 - Now, click on the button, the macro will be applied and the data will be formatted accordingly.
    • macro
  • SAVING WORKBOOK THAT HAS MACRO
  • Unlike other workbook we save a file that has macro differently.
  • Step 1 - Click on the File Tab (This will open the home window). Click on the Save As option from the left column options. From the save as page, click the Browse option in the second column.
    • macro
  • Step 2 - Excel will open your file explorer to browse where you want to save the document.
    Enter your file name in the file name input field. In the Save as type selection input, click on it and change it to Excel Macro Enabled Workbook (*.xlsm). Then click on the Save button to save the file.
    • macro
  • Step 3 - The workbook will be saved successfully
    • macro
PREVIOUS NEXT
ADVERTISEMENTS

LEARNING IS A CONTINOUS PROCESS - PRACTICE MAKES PERFECT