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.