Excel Macros and Macro Recording Practice Questions with Solutions

Introduction

Excel Macros help automate repetitive tasks such as formatting reports, cleaning data, applying calculations, sorting information, and creating dashboards. In this chapter, you will learn how to use the Record Macro feature and understand the VBA code generated by Excel. The questions start with simple recorded actions and gradually move toward practical automation using buttons, reusable macros, and edited VBA code. Excel Macros and Macro Recording Practice questions with solutions to help you understand the concepts.

Important: Save macro-enabled workbooks as .xlsm files. A normal .xlsx file does not preserve VBA macros.

Question 1: Record a Macro to Format a Sales Report

Problem Statement

You receive a sales report every day and need to apply the same formatting repeatedly. Record a macro that formats the report header.

Excel Data

Excel Solution

First create the dataset.

Start Record Macro(view section in excel right side) and name it:

FormatSalesHeader

Perform these formatting actions on the header row:

• Bold
• Center alignment
• Apply a background color
• Change font size
• Add borders

Stop recording the macro.

Excel will generate VBA code similar to:

Sub FormatSalesHeader()

    Range("A1:D1").Select
    Selection.Font.Bold = True
    Selection.HorizontalAlignment = xlCenter
    Selection.Borders.LineStyle = xlContinuous

End Sub

Expected Result

The header row should be clearly formatted.

The macro should apply the same formatting whenever it is run.

Explanation

The Macro Recorder records your Excel actions and converts them into VBA instructions.

You do not need to write the VBA code manually when you are first learning macros.

Concepts Covered

• Record Macro
• Stop Recording
• Macro Name
• VBA code generated by Macro Recorder
• Formatting automation

Question 2: Record a Macro to Apply Number Formatting

Problem Statement

Create a macro that formats Sales as currency and Orders as whole numbers.

Excel Data

Excel Solution

Record a macro named:

FormatSalesNumbers

Apply currency formatting to the Sales column and number formatting to Orders.

A simplified VBA version can look like:

Sub FormatSalesNumbers()

    Range("B2:B6").NumberFormat = "₹#,##0"
    Range("C2:C6").NumberFormat = "0"

End Sub

Expected Result

The Sales values should appear as:

₹85,000
₹92,000
₹68,000
₹105,000
₹76,000

Orders should remain whole numbers.

Explanation

Instead of manually formatting every new report, the macro can repeat the formatting automatically.

Concepts Covered

• Macro Recording
• Number Format
• Currency Formatting
• VBA Range
• Automation

Question 3: Record a Macro to Add a Total Row

Problem Statement

Create a macro that adds a Total Sales and Total Orders calculation below a sales report.

Excel Data

Excel Solution

Record a macro named:

AddSalesTotals

The recorded/edited VBA can be:

Sub AddSalesTotals()

    Range("A7").Value = "Total"
    Range("B7").Formula = "=SUM(B2:B6)"
    Range("C7").Formula = "=SUM(C2:C6)"

End Sub

Expected Result

Explanation

The macro inserts the same formulas automatically whenever the report structure remains the same.

Concepts Covered

• Macro Recording
• VBA Formula
• SUM
• Automated Totals
• Range

Question 4: Record a Macro to Format an Employee Report

Problem Statement

Create a reusable macro that formats an employee report with a title, headers, borders, and salary formatting.

Excel Data

Excel Solution

Record the macro:

FormatEmployeeReport

A cleaned-up version can be:

Sub FormatEmployeeReport()

    Range("A1:D1").Font.Bold = True
    Range("A1:D1").HorizontalAlignment = xlCenter
    Range("A1:D6").Borders.LineStyle = xlContinuous
    Range("D2:D6").NumberFormat = "₹#,##0"
    
End Sub

Expected Result

The report should have:

• Bold headers
• Centered headers
• Borders around the report
• Salary displayed in currency format

Explanation

This type of macro is useful when an organization generates the same employee report repeatedly.

Concepts Covered

• Report Formatting
• VBA Range
• Borders
• Currency Format
• Macro Reusability

Question 5: Record a Macro to Clean a Customer Data Sheet

Problem Statement

A customer report contains unnecessary blank rows. Create a macro that clears a selected range before new data is entered.

Excel Data

Excel Solution

Record a macro named:

ClearCustomerEntryArea

A simple VBA version:

Sub ClearCustomerEntryArea()

    Range("A2:D100").ClearContents

End Sub

Expected Result

The customer entry area from row 2 through row 100 will be cleared while the header remains.

Customer ID | Customer Name | City | Sales
--------------------------------------------
             Empty Entry Area

Explanation

This type of macro is useful for templates where users repeatedly enter fresh data.

Concepts Covered

• ClearContents
• Data Entry Template
• Macro Automation
• Range Selection

Question 6: Record a Macro to Sort Sales Data

Problem Statement

Create a macro that sorts sales data from highest Sales to lowest Sales.

Excel Data

Excel Solution

Record a macro named:

SortSalesHighestFirst

The VBA can be written as:

Sub SortSalesHighestFirst()

    Range("A1:C6").Sort _
        Key1:=Range("C2"), _
        Order1:=xlDescending, _
        Header:=xlYes

End Sub

Expected Result

After running the macro:

Explanation

The macro performs the same sorting operation automatically instead of manually selecting Sort every time.

Concepts Covered

• Macro Recording
• Sorting
• VBA Sort
• Descending Order
• Header Parameter

Question 7: Create a Macro to Generate a Simple Sales Report

Problem Statement

Create a macro that formats a sales report and automatically calculates Total Sales.

Excel Data

Excel Solution

Create the macro:

GenerateSalesReport

A practical VBA version:

Sub GenerateSalesReport()

    Range("A1:D1").Font.Bold = True
    Range("A1:D6").Borders.LineStyle = xlContinuous
    
    Range("A7").Value = "Total"
    Range("B7").Formula = "=SUM(B2:B6)"
    Range("C7").Formula = "=SUM(C2:C6)"
    Range("D7").Formula = "=SUM(D2:D6)"
    
    Range("B2:D7").NumberFormat = "₹#,##0"

End Sub

Expected Result

The report should display:

Explanation

One macro now performs several repetitive operations: formatting, borders, formulas, and number formatting.

Concepts Covered

• Multiple Macro Actions
• SUM
• Currency Formatting
• Automated Reporting
• VBA Formulas

Question 8: Assign a Macro to a Button

Problem Statement

Create a button that runs a formatting macro when clicked.

Excel Data

Excel Solution

Create a macro:

Sub FormatProfitReport()

    Range("A1:C6").Borders.LineStyle = xlContinuous
    Range("A1:C1").Font.Bold = True
    Range("B2:C6").NumberFormat = "₹#,##0"

End Sub

Insert a Button from the Developer tab and assign:

FormatProfitReport

Expected Result

The worksheet should contain a button such as:

┌─────────────────────────┐
│   Format Profit Report  │
└─────────────────────────┘

Clicking the button should automatically format the report.

Explanation

Assigning a macro to a button makes automation easier for users who do not want to open the Macro dialog every time.

Concepts Covered

• Form Controls
• Button
• Assign Macro
• Macro Execution
• User-Friendly Automation

Question 9: Edit Recorded VBA Code to Display a Message

Problem Statement

Record a simple macro and then edit its VBA code so that Excel displays a message when the macro finishes.

Excel Solution

Create a macro named:

ReportCompleted

Edit the recorded code and add:

Sub ReportCompleted()

    Range("A1").Font.Bold = True
    
    MsgBox "Sales report formatting completed successfully."

End Sub

Expected Result

After the macro finishes, Excel should display a message box:

┌──────────────────────────────────────────┐
│ Sales report formatting completed        │
│ successfully.                            │
│                                          │
│                    [ OK ]                │
└──────────────────────────────────────────┘

Explanation

The Macro Recorder creates the initial VBA code. After learning the basics, you can edit that code and add functionality that cannot easily be created through recording alone.

Concepts Covered

• Editing Recorded VBA
• MsgBox
• VBA Statements
• Macro Completion Message

Question 10: Build a Complete Sales Report Automation Macro

Problem Statement

Create a practical macro that prepares a complete sales report automatically.

The macro should:

1. Format the headers.
2. Apply currency formatting.
3. Calculate Total Sales.
4. Calculate Total Profit.
5. Add borders.
6. Display a completion message.

Excel Data

Excel Solution

Create a Profit column:

=C2-D2

Then create the macro:

Sub CreateSalesReport()

    'Format headers
    Range("A1:E1").Font.Bold = True
    Range("A1:E1").HorizontalAlignment = xlCenter

    'Add borders
    Range("A1:E7").Borders.LineStyle = xlContinuous

    'Currency formatting
    Range("C2:E7").NumberFormat = "₹#,##0"

    'Add totals
    Range("A8").Value = "Total"
    Range("C8").Formula = "=SUM(C2:C7)"
    Range("D8").Formula = "=SUM(D2:D7)"
    Range("E8").Formula = "=SUM(E2:E7)"

    'Format total row
    Range("A8:E8").Font.Bold = True

    'Completion message
    MsgBox "Sales report created successfully."

End Sub

Expected Result

The final report should look like:

After execution, Excel should also display:

Sales report created successfully.

Explanation

This is the complete workflow you should understand after practicing the chapter:

Record Macro
      ↓
Perform Excel Actions
      ↓
Stop Recording
      ↓
Open VBA Code
      ↓
Understand Recorded Code
      ↓
Edit Code When Required
      ↓
Run Macro
      ↓
Automate Repetitive Work

The important point is that you do not have to become an advanced VBA programmer before using macros. The Macro Recorder can create the starting VBA code, and you can gradually learn to edit and improve that code.

Concepts Covered

• Macro Recorder
• VBA
• Formatting Automation
• Formula Automation
• SUM
• Number Formatting
• Borders
• MsgBox
• Editing Recorded Code
• Complete Report Automation

Key Takeaways

• A Macro records repetitive Excel actions and allows you to run them again.
• The Developer tab provides access to Excel’s macro features.
• The Macro Recorder automatically generates VBA code.
• Recorded VBA can be viewed and edited through the Visual Basic Editor.
• Macro-enabled workbooks should be saved as .xlsm.
• Macros can automate formatting, calculations, sorting, and report preparation.
• A macro can be assigned to a button for easier execution.
• Range() is commonly used to work with cells and ranges in VBA.
• Formula allows VBA to insert Excel formulas.
• NumberFormat can automate currency and other number formats.
• MsgBox can display messages after a macro completes.
• Recording a macro is a good starting point for learning VBA automation.
• After recording a macro, learning to read and modify the generated code is the next important skill.

FAQs

1. What is an Excel Macro?

An Excel Macro is a recorded or programmed set of actions that can automate repetitive Excel tasks.

2. What is Macro Recorder in Excel?

Macro Recorder is an Excel feature that records your actions and converts them into VBA code.

3. Do I need to know VBA to record a macro?

No. You can record a macro without knowing VBA. However, understanding basic VBA becomes useful when you want to modify or improve the recorded macro.

4. What is the Developer tab used for?

The Developer tab provides tools for recording macros, running macros, opening the Visual Basic Editor, inserting form controls, and working with VBA.

5. What is the difference between a recorded macro and manually written VBA?

A recorded macro generates VBA based on your Excel actions. Manually written VBA gives you more control and allows you to create logic that may not be possible through recording alone.

6. Why should I save an Excel macro file as XLSM?

.xlsm is a macro-enabled Excel file format that can preserve VBA code. Saving a macro workbook as .xlsx can remove the macro code.

7. Can I assign a macro to a button?

Yes. A macro can be assigned to a Form Control button so users can execute it with one click.

8. Can a macro calculate formulas?

Yes. VBA can insert formulas into cells using the Formula property.

9. Can macros format an entire report?

Yes. Macros can apply fonts, borders, number formats, alignment, colors, widths, and other formatting automatically.

10. Is Macro Recorder enough to become good at VBA?

Macro Recorder is an excellent starting point, but learning to read and edit VBA code will allow you to create more powerful automation.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar