Recording macros in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant who wants to save time on repetitive Excel tasks.

📋 Request: Your manager asks you to create a macro that formats sales data quickly with consistent styles and calculations.

📊 Data: You have a sales data table with columns: Date, Product, Units Sold, Unit Price, and Total Sales (empty).

🎯 Deliverable: Create a macro that formats the table with bold headers, applies currency format to prices, calculates Total Sales, and autofits columns.

Sample Data

Date	Product	Units Sold	Unit Price	Total Sales
2024-06-01	Notebook	10	2.5
2024-06-02	Pen	20	1.2
2024-06-03	Marker	15	3.0
2024-06-04	Folder	5	4.5
2024-06-05	Stapler	7	6.0

1

Step 1: Select the entire data table including headers.

Click and drag from cell A1 to E6.

Expected Result

The data table is selected.

2

Step 2: Start recording a new macro named 'FormatSalesData'.

Go to View tab > Macros > Record Macro > Name: FormatSalesData > Store macro in: This Workbook > Click OK.

Expected Result

Macro recording starts.

3

Step 3: Make the header row bold.

Select cells A1:E1 > Home tab > Bold button.

Expected Result

Headers in row 1 are bold.

4

Step 4: Apply currency format to Unit Price and Total Sales columns.

Select cells D2:D6 and E2:E6 > Home tab > Number Format dropdown > Currency.

Expected Result

Unit Price and Total Sales cells show currency format.

5

Step 5: Enter formula to calculate Total Sales for first data row.

In cell E2, enter formula: =C2*D2

Expected Result

Cell E2 shows 25.00 (10*2.5).

6

Step 6: Copy the Total Sales formula down to rows 3 to 6.

Select cell E2 > drag fill handle down to E6.

Expected Result

Cells E3:E6 calculate total sales for each row.

7

Step 7: Autofit all columns to fit content.

Select columns A to E > double-click any column border in header.

Expected Result

All columns adjust width to fit data.

8

Step 8: Stop recording the macro.

Go to View tab > Macros > Stop Recording.

Expected Result

Macro recording stops and is saved.

9

Step 9: Test the macro by clearing Total Sales and running the macro again.

Clear cells E2:E6 > View tab > Macros > View Macros > Select 'FormatSalesData' > Run.

Expected Result

Formatting reapplies, Total Sales formulas fill, and columns autofit.

Final Result

Date       Product  Units Sold  Unit Price  Total Sales
-------------------------------------------------------
2024-06-01 Notebook 10         $2.50       $25.00
2024-06-02 Pen      20         $1.20       $24.00
2024-06-03 Marker   15         $3.00       $45.00
2024-06-04 Folder   5          $4.50       $22.50
2024-06-05 Stapler  7          $6.00       $42.00

✓The macro saves time by applying consistent formatting and calculations.

✓Total Sales are automatically calculated by multiplying Units Sold by Unit Price.

✓Columns are neatly adjusted for easy reading.

Bonus Challenge

Modify the macro to also highlight Total Sales greater than $30 with a green fill color.

Show Hint

Use conditional formatting steps while recording the macro: Select Total Sales cells > Home tab > Conditional Formatting > Highlight Cell Rules > Greater Than > Enter 30 > Choose green fill.

Practice

1. What is the main purpose of recording a macro in Excel?

easy

A. To automate repetitive tasks by recording your actions

B. To create complex formulas automatically

C. To protect your worksheet from editing

D. To format cells with a single click

Solution

1. Step 1: Understand what macros do

   Macros record your actions to repeat them later without manual work.

2. Step 2: Identify the main use

   Recording macros helps automate repetitive tasks, saving time and effort.

3. Final Answer:

   To automate repetitive tasks by recording your actions -> Option A

4. Quick Check:

   Macro recording = automate tasks [OK]

Hint: Macros save time by repeating your exact steps [OK]

Common Mistakes:

• Thinking macros create formulas automatically
• Confusing macros with worksheet protection
• Assuming macros only format cells

2. Which of the following is the correct way to start recording a macro in Excel?

easy

A. Go to Insert tab > Shapes > Record Macro

B. Go to View tab > Macros > Record Macro

C. Right-click a cell and select Record Macro

D. Press Ctrl + R to start recording

Solution

1. Step 1: Locate macro recording option

   In Excel, macros are recorded from the View tab under Macros.

2. Step 2: Confirm correct menu path

   The correct path is View tab > Macros > Record Macro, not Insert or right-click menus.

3. Final Answer:

   Go to View tab > Macros > Record Macro -> Option B

4. Quick Check:

   Record Macro location = View > Macros [OK]

Hint: Find Record Macro under View tab > Macros [OK]

Common Mistakes:

• Looking under Insert tab instead of View
• Trying to record macro from right-click menu
• Using wrong keyboard shortcuts

3. You recorded a macro that formats selected cells with bold text and yellow fill. What happens when you run this macro on a new selection?

medium

A. The new selection becomes bold with yellow fill

B. The macro asks you to select cells again

C. The macro deletes the contents of the new selection

D. Nothing happens because macros only work on original cells

Solution

1. Step 1: Understand macro behavior

   Macros repeat recorded actions on the current selection when run.

2. Step 2: Apply macro to new selection

   Running the macro applies bold and yellow fill to whatever cells are selected now.

3. Final Answer:

   The new selection becomes bold with yellow fill -> Option A

4. Quick Check:

   Macro applies recorded formatting to current selection [OK]

Hint: Macros repeat actions on current selection, not original cells [OK]

Common Mistakes:

• Thinking macro asks for input each time
• Believing macro deletes data accidentally
• Assuming macros only work on original cells

4. You recorded a macro but when you run it, it does not perform any action. What is the most likely reason?

medium

A. You did not select any cells before running

B. You saved the macro with a wrong name

C. Macros only work on Excel tables

D. You forgot to stop recording the macro

Solution

1. Step 1: Check macro recording status

   If you don't stop recording, Excel does not save the actions properly.

2. Step 2: Understand effect of not stopping

   Without stopping, the macro is empty or incomplete, so running it does nothing.

3. Final Answer:

   You forgot to stop recording the macro -> Option D

4. Quick Check:

   Stop recording to save macro actions [OK]

Hint: Always click Stop Recording to save macro [OK]

Common Mistakes:

• Assuming macro name affects execution
• Thinking selection is required before running
• Believing macros only work on tables

5. You want to create a macro that formats a report: sets font size to 12, applies bold to headers, and autofits columns. Which sequence of actions should you record to make the macro work correctly on any report?

hard

A. Select headers, bold them; autofit columns; select all cells, set font size 12

B. Autofit columns; select headers, bold them; set font size 12 on all cells

C. Select all cells, set font size 12; select headers, bold them; autofit columns

D. Set font size 12; autofit columns; select headers, bold them

Solution

1. Step 1: Plan macro steps logically

   First set font size for all cells, then bold headers, then autofit columns for best results.

2. Step 2: Record actions in correct order

   Recording font size on all cells first ensures uniform text; bolding headers after targets only headers; autofit last adjusts column widths.

3. Final Answer:

   Select all cells, set font size 12; select headers, bold them; autofit columns -> Option C

4. Quick Check:

   Order matters: font size, bold headers, autofit [OK]

Hint: Set font size first, then bold headers, autofit last [OK]

Common Mistakes:

• Bold headers before setting font size on all cells
• Autofitting columns before formatting text
• Not selecting correct cells for each step