Macro security settings in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office administrator responsible for managing Excel files with macros.

📋 Request: Your manager wants you to ensure that all Excel files with macros are safe to use and that users are protected from potentially harmful macros.

📊 Data: You have a list of Excel files with information about whether they contain macros and their source trust level.

🎯 Deliverable: Create a simple guide and checklist in Excel to help users set their macro security settings correctly and identify safe files.

Sample Data

File Name	Contains Macro	Source
Report_Jan.xlsm	Yes	Trusted
Data_Export.xlsm	Yes	Unknown
Summary_Feb.xlsx	No	Trusted
Invoice_March.xlsm	Yes	Trusted
Budget_April.xlsx	No	Unknown
Analysis_May.xlsm	Yes	Unknown

1

Step 1: Create a new Excel sheet named 'Macro Security Guide'.

No formula needed.

Expected Result

A blank sheet named 'Macro Security Guide' is ready for instructions.

2

Step 2: In 'Macro Security Guide', write a short note explaining macro security levels: Disable all macros, Disable with notification, Enable all macros, and Trusted locations.

No formula needed.

Expected Result

Users can read about macro security options in the guide.

3

Step 3: Create a checklist table with columns: 'Step', 'Action', 'Status'. Fill 'Step' and 'Action' with steps to check macro settings and file trustworthiness.

No formula needed.

Expected Result

Checklist table is ready for users to mark 'Status' as Done or Pending.

4

Step 4: On the data sheet, add a new column 'Safe to Enable Macros' with formula to check if 'Contains Macro' is 'Yes' and 'Source' is 'Trusted'.

=IF(AND(B2="Yes", C2="Trusted"), "Yes", "No")

Expected Result

Column shows 'Yes' for files with macros from trusted sources, 'No' otherwise.

5

Step 5: Apply filter on the data table to show only files where 'Safe to Enable Macros' is 'Yes'.

Use Excel filter feature on the 'Safe to Enable Macros' column.

Expected Result

Filtered list shows only files safe to enable macros.

6

Step 6: Add conditional formatting to highlight files with 'Safe to Enable Macros' = 'No' in red.

Use conditional formatting rule: Formula =($D2="No") with red fill.

Expected Result

Unsafe files are visually highlighted in red.

7

Step 7: Save the workbook with macro security guide and checklist for users to follow.

No formula needed.

Expected Result

Workbook is saved and ready to distribute.

Final Result

Macro Security Guide
---------------------
Step | Action                          | Status
1    | Check macro security settings   | 
2    | Verify file source trust level  | 
3    | Enable macros only if safe      | 

Files Safe to Enable Macros:
File Name          | Contains Macro | Source  | Safe to Enable Macros
-------------------|----------------|---------|---------------------
Report_Jan.xlsm    | Yes            | Trusted | Yes
Invoice_March.xlsm | Yes            | Trusted | Yes

Files Not Safe:
Data_Export.xlsm   | Yes            | Unknown | No
Analysis_May.xlsm  | Yes            | Unknown | No

✓Only files with macros from trusted sources should have macros enabled.

✓Files from unknown sources with macros are highlighted as unsafe.

✓Users should set macro security to 'Disable with notification' to review before enabling.

✓A checklist helps users follow safe macro practices.

Bonus Challenge

Create a macro that automatically scans all files in a folder and updates the 'Safe to Enable Macros' column based on file properties.

Show Hint

Use VBA to loop through files, check if they contain macros, and check source trust level if possible.

Practice

1. What is the main purpose of Macro Security Settings in Excel?

easy

A. To change the font size of cells automatically

B. To sort data alphabetically

C. To create charts from data

D. To control if and how macros run to protect your computer

Solution

1. Step 1: Understand what macros are

   Macros are small programs that automate tasks in Excel.

2. Step 2: Identify the role of security settings

   Macro security settings control whether these programs can run, protecting your computer from unsafe code.

3. Final Answer:

   To control if and how macros run to protect your computer -> Option D

4. Quick Check:

   Macro security controls macro execution [OK]

Hint: Macros can be risky; security settings keep you safe [OK]

Common Mistakes:

• Confusing macro security with formatting options
• Thinking macro security changes data sorting
• Assuming macro security creates charts

2. Which menu path do you follow to change Macro Security Settings in Excel?

easy

A. File > Options > Trust Center > Trust Center Settings > Macro Settings

B. Home > Clipboard > Paste Special

C. Insert > Table > Macro Settings

D. View > Zoom > Macro Security

Solution

1. Step 1: Locate Excel Options

   Click File, then Options to open Excel settings.

2. Step 2: Find Trust Center and Macro Settings

   Inside Options, select Trust Center, then Trust Center Settings, and finally Macro Settings.

3. Final Answer:

   File > Options > Trust Center > Trust Center Settings > Macro Settings -> Option A

4. Quick Check:

   Macro settings are under Trust Center [OK]

Hint: Macro settings live in Trust Center under Options [OK]

Common Mistakes:

• Looking for macro settings under Insert or View menus
• Confusing macro settings with clipboard or zoom options
• Skipping Trust Center step

3. If Macro Security is set to 'Disable all macros with notification', what happens when you open a workbook with macros?

medium

A. Macros are disabled but you get a security warning to enable them

B. Macros are disabled and no notification appears

C. Macros run automatically without warning

D. Excel crashes immediately

Solution

1. Step 1: Understand the setting meaning

   'Disable all macros with notification' means macros do not run automatically but you get a warning.

2. Step 2: Identify user action

   You can choose to enable macros after seeing the notification.

3. Final Answer:

   Macros are disabled but you get a security warning to enable them -> Option A

4. Quick Check:

   Disable with notification means warning shown [OK]

Hint: Disable with notification means macros off until you approve [OK]

Common Mistakes:

• Thinking macros run automatically
• Assuming no notification appears
• Believing Excel crashes on macro security

4. You set Macro Security to 'Enable all macros' but macros still do not run. What is a likely cause?

medium

A. You need to restart Excel twice

B. The workbook is opened from an untrusted location and Trust Center blocks macros

C. Excel does not support macros

D. You forgot to save the workbook

Solution

1. Step 1: Check macro security setting

   Setting 'Enable all macros' allows macros to run without prompts.

2. Step 2: Consider Trust Center trusted locations

   If the file is from an untrusted location, Trust Center may block macros despite settings.

3. Final Answer:

   The workbook is opened from an untrusted location and Trust Center blocks macros -> Option B

4. Quick Check:

   Untrusted location blocks macros even if enabled [OK]

Hint: Check if file location is trusted when macros don't run [OK]

Common Mistakes:

• Assuming Excel does not support macros
• Thinking saving or restarting fixes macro blocking
• Ignoring Trust Center trusted locations

5. You want to allow macros only from trusted publishers but block all others. Which Macro Security setting should you choose?

hard

A. Disable all macros without notification

B. Disable all macros with notification

C. Disable all macros except digitally signed macros

D. Enable all macros

Solution

1. Step 1: Understand the requirement

   You want to allow macros only if they come from trusted, digitally signed publishers.

2. Step 2: Match setting to requirement

   'Disable all macros except digitally signed macros' allows only signed macros to run, blocking others.

3. Final Answer:

   Disable all macros except digitally signed macros -> Option C

4. Quick Check:

   Signed macros allowed only = Disable all macros except digitally signed macros [OK]

Hint: Choose signed macros only to trust known publishers [OK]

Common Mistakes:

• Choosing 'Enable all macros' which allows all macros
• Picking 'Disable all macros with notification' which warns but blocks all
• Confusing signed macros with notifications