Training By Himanshu

Tag: personal macro workbook

  • How to Record and Run Macros in Excel: A Complete Step-by-Step Guide for Beginners and Professionals

    Automating repetitive tasks in Excel is one of the most effective ways to increase productivity, reduce errors, and improve workflow efficiency. Whether you frequently apply formatting, generate reports, clean datasets, or perform repetitive calculations, Excel Macros can save you significant time. Knowing how to record and run macros is considered one of the top skills in data entry, MIS reporting, financial modeling, operations management, and analytics roles.

    In this detailed article, you will learn the complete process of recording and running macros in Microsoft Excel, including essential settings, real examples, best practices, shortcut keys, macro storage options, and commonly used facts. By the end of this guide, you will have a clear understanding of how macro automation works and how you can apply it confidently in your day-to-day Excel tasks.

    What Is a Macro in Excel

    A Macro is an automated script that performs a sequence of actions in Excel. It is powered by VBA (Visual Basic for Applications), which is the built-in programming language inside MS Office. When you perform a task manually and record it, Excel translates your actions into VBA code. Later, you can run the macro to repeat the same actions automatically.

    Macros are widely used in reporting, formatting, data extraction, reconciliation, dashboard preparation, and financial statements. Many companies consider Excel automation a mandatory skill for MIS Executives, Account Assistants, Financial Analysts, and Data Administrators.

    Why Learn to Record Macros

    Here are some key reasons why macro recording is a valuable skill:

    • It saves time by automating repetitive tasks.
    • It reduces human errors by executing consistent actions.
    • It requires no coding knowledge for basic macros.
    • It improves efficiency and productivity.
    • It helps produce standardized reports.
    • It supports advanced reporting when combined with formulas.
    • It enhances job opportunities in MIS, accounts, and analytics fields.

    Based on industry surveys, companies report that automating tasks with Excel Macros can reduce manual work by up to 60 percent for routine reporting activities.

    Preparing Excel for Macro Recording

    Before recording macros, ensure that Excel is set up properly.

    1. Enable the Developer Tab

    Macros are accessible from the Developer tab. If you don’t see it on your ribbon, follow these steps.

    StepAction
    1Go to File > Options
    2Click Customize Ribbon
    3Enable Developer checkbox
    4Click OK to display the Developer tab

    Once enabled, you can access the Record Macro and Macro Tools easily.

    2. Enable Macro Settings

    If macros are disabled, Excel may not allow recording or running them.

    SettingPurpose
    Disable all macrosHighest security, macros do not run
    Disable with notificationShows alert before running macros
    Enable all macrosLowest security, used in trusted environments

    For learning purposes, choose “Disable with notification”.

    How to Record a Macro in Excel

    Excel’s Record Macro feature allows you to capture multiple actions, including formatting, formula entry, sorting, filtering, and more. Below is the step-by-step process.

    Step-by-Step Procedure to Record a Macro

    Step 1: Click Record Macro

    Go to Developer tab and choose “Record Macro”. A dialog box will appear.

    Step 2: Enter Macro Name

    Provide a meaningful name. Macro names must follow these rules:

    • No spaces allowed
    • Must start with a letter
    • Can contain underscores

    Examples of valid names:
    FormatReport, HighlightValues, Sales_Calculation

    Step 3: Choose Shortcut Key (Optional)

    You can assign a shortcut like Ctrl + Shift + F.
    Avoid overriding default Excel shortcuts such as Ctrl + C or Ctrl + S.

    Step 4: Select Macro Storage Location

    Excel provides three storage options:

    Storage LocationMeaning
    This WorkbookMacro works only in the current file
    New WorkbookMacro will be saved in a new Excel file
    Personal Macro WorkbookMacro becomes available in all Excel files

    The Personal Macro Workbook option is commonly used for daily automation tasks.

    Step 5: Add Description (Optional)

    You can describe what the macro does, for reference.

    Step 6: Perform Your Actions

    Once recording starts, Excel captures all actions, such as:

    • Selecting cells
    • Formatting data
    • Applying formulas
    • Inserting sheets
    • Sorting or filtering
    • Creating tables

    Example recorded action:
    Selecting the range A1:F1 and applying bold formatting.

    Step 7: Stop Recording

    Go back to Developer > Stop Recording.

    Your macro is now saved and ready to run.

    How to Run a Macro in Excel

    After recording, the next step is running the macro. There are three ways to run a macro.

    Method 1: Run from Developer Tab

    1. Go to Developer
    2. Click Macros
    3. Select your macro
    4. Click Run

    This method is useful when multiple macros exist in a workbook.

    Method 2: Run Using a Keyboard Shortcut

    If you assigned a shortcut key during recording, simply press:

    Ctrl + Shift + (your assigned key)

    For example, Ctrl + Shift + R for formatting a report.

    Method 3: Run from a Button on the Sheet

    You can assign a macro to:

    • A shape
    • A button
    • An icon
    • A form control

    This provides a user-friendly method, especially in dashboards and reports used by non-technical users.

    Example: Recording a Macro to Format a Sales Report

    Here is a simple example to understand the real-time use of macros.

    Objective:

    Format a sales report with bold headers, borders, and proper alignment.

    Steps Recorded:

    1. Select the header row.
    2. Apply bold formatting.
    3. Change background color (optional).
    4. Apply borders to the data.
    5. Auto-fit columns.

    After recording, the macro can be run anytime to automate the same formatting on new datasets.
    This kind of macro is frequently used in MIS departments for monthly sales, daily stock reports, and financial summaries.

    Understanding the VBA Code Behind Recorded Macros

    Even if you don’t write code manually, recording macros helps you learn VBA automatically. When you record a macro, Excel generates VBA code similar to:

    Sub FormatSalesReport()
    Range("A1:F1").Font.Bold = True
    Range("A1:F20").Borders.LineStyle = xlContinuous
    Columns("A:F").AutoFit
End Sub

    Learning to interpret this code allows you to:

    • Modify recorded macros
    • Add additional features
    • Optimize performance
    • Build more powerful automation tasks

    Even a small level of VBA knowledge can increase your automation capabilities significantly.

    Best Practices for Recording Macros

    To make your macros efficient, keep the following guidelines in mind:

    • Avoid unnecessary clicks while recording.
    • Select data precisely instead of selecting entire columns.
    • Use keyboard shortcuts for faster and cleaner macro recording.
    • Keep macro names meaningful.
    • Store frequently used macros in the Personal Macro Workbook.
    • Test macros with sample data before final use.
    • Use buttons for macros used by multiple team members.

    These practices help create compact macros that run faster and are easier to maintain.

    Macro Security Considerations

    Since macros can run scripts, Excel includes security features to prevent harmful code. Always enable macros only from trusted sources. In corporate environments, IT administrators often lock macro settings to avoid security risks.

    Key points:

    • Never open unknown macro-enabled files from email.
    • Use digital signature for macros in large organizations.
    • Store only trusted macros in your Personal Macro Workbook.

    Common Problems and Solutions

    Here are some frequent issues and their solutions:

    ProblemSolution
    Macro cannot be run due to security settingsEnable macros with notification
    Shortcut key not workingEnsure no default Excel shortcut is overridden
    Macro not saved in workbookSave file as .xlsm format
    Button assigned to macro gives errorReassign macro after renaming it

    Understanding these common issues makes learning Macros smoother and more effective.

    Conclusion

    Recording and running macros in Excel is a powerful way to automate tasks and improve productivity. Whether you are working with formatting, data cleanup, reporting, or repetitive calculations, macros can save hours of manual work. With just a few steps, anyone can activate the Developer tab, record macros, and run them using buttons or shortcuts. As your skills grow, you can explore VBA to further enhance automation and create fully customized solutions for professional tasks.

    Mastering macros not only improves workflow efficiency but also strengthens your profile for roles in MIS, analytics, accounts, finance, operations, supply chain, and administration. Companies consistently value candidates who can automate tasks, reduce errors, and deliver faster results.

    Disclaimer

    This article is for educational purposes only. The information provided is based on general Excel functionality and may vary based on the version of Microsoft Excel used. Always enable macros only from trusted sources to avoid security risks.

  • Excel Macro Recording – A Complete Guide with Real-Life Examples

    🔍 What is Macro Recording?

    Macro Recording in Excel lets you automate a series of steps (like formatting, calculations, copying data, etc.) by recording them into a script using Visual Basic for Applications (VBA).

    You don’t need to know coding—just record your actions, and Excel writes the VBA code in the background.

    🛠️ How to Start Recording a Macro:

    ✔ Step-by-Step:

    1. Go to the View tab → Click Macros → Record Macro
      • OR enable the Developer tab via:
        • File → Options → Customize Ribbon → Tick Developer
    2. Give the macro:
      • A Name (no spaces, e.g., FormatReport)
      • (Optional) A Shortcut Key like Ctrl+Shift+F
      • Choose Where to Store:
        • This Workbook (use in current file only)
        • Personal Macro Workbook (use in all Excel files)
    3. Click OK and perform your actions
    4. Click Stop Recording (from the same menu)

    Now your steps are recorded!

    ⚠️ Common Errors and How to Avoid:

    IssueTip to Avoid
    Recorded steps apply to wrong cellsAlways select cell references carefully (avoid absolute ones like A1 if you need flexibility)
    Copy-paste issuesUse Paste Special and be careful with ranges
    Macros don’t work on other filesUse relative references or store macros in Personal Workbook
    Overwrites existing dataTest on a copy of data first
    Macro security blocks codeEnable macro settings via: File → Options → Trust Center → Macro Settings

    🧪 Now, Let’s Explore 5 Practical Examples, with Full Instructions:

    ✅ 1. Format a Monthly Sales Report Automatically

    🧾 Use Case:

    You get a raw report every month: inconsistent fonts, no borders, unformatted headers.

    🔧 Steps to Record Macro:

    1. Click Record Macro, name it SalesFormat
    2. Select the header row → Bold, Center, Fill color
    3. Select entire data → Apply Calibri, font size 11
    4. Auto-fit all columns → Home → Format → AutoFit Column Width
    5. Add borders → Home → Borders → All Borders
    6. Freeze header row → View → Freeze Panes → Freeze Top Row
    7. Click Stop Recording

    ▶️ How to Use:

    • Next time you open the report, press the shortcut or run the macro from Macros list.
    • Your formatting is applied in seconds.

    ⚠️ Avoid:

    • Don’t record steps like clicking on random sheets.
    • Avoid selecting specific ranges like A1:D20—use Ctrl+A or Table Format if data size varies.

    ✅ 2. Create and Save Multiple Invoices from a Template

    🧾 Use Case:

    You want to generate 50 invoices using a common template, changing name, amount, and saving each separately.

    📝 Requirements:

    • One sheet with customer names, invoice numbers, amounts
    • One template sheet with placeholders

    🔧 Steps to Record Macro:

    1. Click Record Macro, name it GenerateInvoice
    2. Select the template
    3. Go to cell B5, type =Sheet1!A2 (customer name)
    4. Go to cell B6, type =Sheet1!B2 (amount)
    5. Save As → Choose folder → Name the file as Invoice_001.xlsx
    6. Click Stop Recording

    ▶️ How to Use:

    • Loop this macro using VBA (or copy manually for few records)
    • Edit recorded macro in VBA to add loop, file name logic, and auto-save

    ⚠️ Avoid:

    • Don’t hardcode cell values
    • Always test first 1–2 invoices manually before full automation

    ✅ 3. Merge Multiple Sheets into One Summary Sheet

    🧾 Use Case:

    You have 10 region-wise sheets and want to combine data into one master sheet.

    🔧 Steps to Record:

    1. Start macro: MergeSheets
    2. Create a new sheet Summary
    3. Go to Sheet1 → Select data (excluding headers) → Copy
    4. Go to Summary → Paste at top
    5. Repeat with Sheet2, Sheet3 by pasting data below previous one
    6. Click Stop Recording

    ▶️ How to Use:

    • Use this macro to consolidate region-wise, branch-wise, or monthly data.

    ⚠️ Avoid:

    • Don’t copy headers each time
    • Don’t use fixed ranges like A2:D20. Use CurrentRegion or UsedRange

    ✅ 4. Send Personalized Emails from Excel via Outlook

    🧾 Use Case:

    You want to send each employee their attendance record.

    📝 Setup:

    • Excel sheet with Name, Email, Attendance %

    🔧 How to Use Macro (Basic Structure):

    1. Open VBA editor (Alt + F11)
    2. Use this code: vbaCopyEditSub SendEmails() Dim OutlookApp As Object, Mail As Object Dim ws As Worksheet, i As Integer Set ws = ThisWorkbook.Sheets("Sheet1") Set OutlookApp = CreateObject("Outlook.Application") For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Set Mail = OutlookApp.CreateItem(0) Mail.To = ws.Cells(i, 2).Value Mail.Subject = "Your Monthly Attendance" Mail.Body = "Hello " & ws.Cells(i, 1).Value & ", your attendance is: " & ws.Cells(i, 3).Value & "%" Mail.Send Next i MsgBox "Emails sent!" End Sub
    3. Run the macro

    ⚠️ Avoid:

    • Outlook must be installed and open
    • Avoid sending test mails to real clients—try to test on dummy emails

    ✅ 5. Clean and Standardize Raw Data Automatically

    🧾 Use Case:

    You get data with extra spaces, inconsistent casing, blank values.

    🔧 Steps to Record:

    1. Click Record Macro → Name: CleanData
    2. Select data column
    3. Use:
      • =TRIM() to remove spaces
      • =PROPER() or =UPPER() for name formatting
      • IF(cell="", "NA", cell) to fill blanks
    4. Paste values → Clear old data
    5. Click Stop Recording

    ▶️ How to Use:

    Run this every time you import new data.

    ⚠️ Avoid:

    • Don’t record sorting steps unless needed
    • Always back up original data

    💡 Bonus Tips:

    • Save macros in Personal Macro Workbook to reuse in any file
    • Use Alt + F8 to view and run macros
    • Learn to edit macros in VBA Editor to add power like loops, conditions

    ✅ 10 Interview Questions with Sample Answers (Macro Recording & VBA)

    🔹 Q1. What is a macro in Excel, and why is it used?

    Answer:
    A macro is a set of recorded instructions that automate repetitive tasks in Excel. It’s based on VBA (Visual Basic for Applications). Macros are useful for actions like formatting reports, cleaning data, or generating templates, saving time and reducing manual errors.

    🔹 Q2. How do you record a macro in Excel?

    Answer:
    To record a macro:

    1. Go to the View tab or Developer tab → Click Record Macro
    2. Give it a name, optional shortcut key, and choose where to store it
    3. Perform the steps you want to automate
    4. Click Stop Recording
      The macro is now saved and can be run to repeat the same steps.

    🔹 Q3. Can you give an example of a task you’ve automated using macros?

    Answer:
    Yes, I automated the formatting of a monthly sales report. I recorded a macro that:

    • Bolded the header
    • Applied font styles
    • Added borders
    • Auto-fitted columns
    • Froze the top row
      Now, instead of manually formatting every report, I just run the macro in seconds.

    🔹 Q4. What are the limitations of using macro recording?

    Answer:

    • It records absolute cell references by default, which makes it less flexible for dynamic data
    • It can record unintended steps (like selecting wrong sheets)
    • Not suitable for conditional logic or loops
      For more flexibility, macros can be edited in the VBA editor.

    🔹 Q5. What is the difference between relative and absolute referencing in macros?

    Answer:

    • Absolute recording always affects specific cells (e.g., A1)
    • Relative recording affects cells based on the current selection
      For example, a macro recorded relatively will apply formatting wherever your cursor is, not just in A1.

    🔹 Q6. How can you edit a recorded macro?

    Answer:
    Open the VBA Editor using Alt + F11, then locate your macro under “Modules.” There, you can edit the generated VBA code—for example, replacing static cell references with variables or adding loops.

    🔹 Q7. What is the file format used for saving macros in Excel?

    Answer:
    Macros are saved in workbooks with the extension .xlsm. Regular .xlsx files do not support macros. If you try to save a macro-enabled workbook as .xlsx, Excel will warn you that your macros will be removed.

    🔹 Q8. How do you handle errors in macros?

    Answer:
    Basic error handling can be added using:

    vbaCopyEditOn Error Resume Next
' or
On Error GoTo ErrorHandler

    In recorded macros, it’s important to avoid invalid cell selections and test the macro on sample data first. Always use backup files.

    🔹 Q9. What is the Personal Macro Workbook?

    Answer:
    The Personal Macro Workbook is a hidden workbook that opens every time Excel launches. Macros stored here are available across all workbooks, making it useful for reusable automations like formatting or data cleaning.

    🔹 Q10. How do you run a macro using a shortcut or button?

    Answer:

    • You can assign a macro to a keyboard shortcut while recording or by editing macro settings
    • Or, insert a Form Control Button from the Developer tab, right-click it → Assign Macro → choose your macro

    📘 How to Prepare for Interview Questions on Excel Macros

    🧠 1. Master Macro Recording (Hands-On Practice)

    Practice these:

    • Recording a macro to format a table
    • Automating “Save As” based on cell value
    • Copying data from multiple sheets into one
    • Cleaning data (TRIM, PROPER, etc.)

    Use the VBA editor to review what your macro code looks like.

    🧾 2. Understand VBA Basics

    Even if you don’t write VBA manually, learn:

    • How to edit a macro
    • The meaning of basic VBA commands like .Select, .Copy, .PasteSpecial, Range(), and Cells()
    • How to use loops:
    vbaCopyEditFor i = 2 to 100
    Cells(i,1).Value = Trim(Cells(i,1).Value)
Next i

    💻 3. Know Real Use Cases

    Be ready to explain:

    • What you automated
    • Why it saved time
    • How it improved accuracy
    • Any errors you faced, and how you fixed them

    ⚠️ 4. Be Prepared for Error Handling Questions

    Know:

    • How to test a macro
    • How to avoid hard-coded ranges
    • How to avoid overwriting real data
    • How to disable screen updating during macro (improves speed):
    vbaCopyEditApplication.ScreenUpdating = False

    📂 5. Carry Demo Files (if allowed)

    If you’re in a live interview:

    • Show a file with a working macro
    • Show before-and-after results
    • Show VBA editor and explain code line-by-line

    🎓 Summary for Quick Revision

    TopicMust Know
    Recording MacrosSteps to start/stop, shortcut assignment
    Use CasesFormatting, reports, emailing, data cleanup
    Editing MacrosOpen VBA editor, basic syntax
    Common ErrorsHard-coded cells, test on copy, save as .xlsm
    Interview PrepPractice macros, understand VBA basics, real use cases

    🎓 Want to Learn Macros, Access, and SQL the Right Way?


    If you’re serious about mastering Excel automation—especially using Macros, Access, and real-world MIS reporting—this Complete MIS Training course is a great place to start. It’s packed with hands-on projects, practical simulations, and real corporate use cases. With over 16.5 hours of video lessons and 26 downloadable resources, you’ll learn how to work smarter—not harder.
    👉 Currently available at just ₹499 (down from ₹5,499) with a Certificate of Completion included.


    Enroll now

    and start building the automation skills employers value.

    Featured products