Grouping and outlining in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What happens when you group rows in Excel?

You select rows 5 to 10 and use the Group feature in Excel. What is the immediate effect on the worksheet?

A Rows 5 to 10 are locked so you cannot edit them.

B Rows 5 to 10 are deleted from the worksheet.

C Rows 5 to 10 are copied to a new worksheet.

D Rows 5 to 10 are hidden and a small button appears to expand or collapse them.

💡 Hint

Think about what grouping does to help you manage large data sets.

Grouping rows hides them temporarily and adds a button to expand or collapse the group, making it easier to view or hide details.

📊 Formula Result

intermediate

Outline level effect on subtotal formula

You have a list of sales data grouped by region. You use the SUBTOTAL function with function_num 9 (SUM) on the entire data including groups. What does SUBTOTAL do when some rows are hidden by grouping?

Excel

=SUBTOTAL(9, B2:B20)

A It sums only the visible rows, ignoring hidden rows from grouping.

B It sums all rows, including hidden ones.

C It returns zero if any rows are hidden.

D It returns an error if rows are grouped.

💡 Hint

SUBTOTAL can ignore hidden rows depending on the function number used.

SUBTOTAL with function_num 9 sums only visible rows, so it excludes rows hidden by grouping.

🎯 Scenario

advanced

Using multiple outline levels

You have a worksheet with sales data grouped by Region (rows 2-20) and inside each region, grouped by Salesperson (rows 3-6, 7-10, etc.). What happens when you click the number 2 in the outline bar on the left?

A Only the Region groups are expanded; Salesperson groups are collapsed.

B All groups are fully expanded.

C All groups are fully collapsed.

D Only Salesperson groups are expanded; Region groups are collapsed.

💡 Hint

Outline levels control how detailed the view is.

Clicking level 2 shows the first level groups (Region) expanded but keeps the second level groups (Salesperson) collapsed.

❓ Function Choice

advanced

Choosing the right function to ignore hidden rows

You want to calculate the average of a range but ignore rows hidden by grouping or filtering. Which function should you use?

A SUM() / COUNT()

B SUBTOTAL(1, range)

C AVERAGE()

D COUNT()

💡 Hint

SUBTOTAL has special behavior with hidden rows.

SUBTOTAL with function_num 1 calculates average ignoring hidden rows, unlike AVERAGE which includes all rows.

❓ data_analysis

expert

Analyzing outline summary rows

You have a worksheet with grouped data and summary rows at each group level. You want a formula that sums only the summary rows (the rows that show totals for each group) but not the detail rows. Which approach works best?

A Use SUM on the entire range; it ignores detail rows automatically.

B Use SUBTOTAL on the entire range; it sums only summary rows automatically.

C Use SUMIF with a helper column marking summary rows manually.

D Use SUMPRODUCT with ISNUMBER(SEARCH("Total", A:A)) to find summary rows by text.

💡 Hint

Excel does not automatically identify summary rows in formulas.

Excel formulas cannot detect summary rows automatically; marking them manually in a helper column and using SUMIF is the reliable method.

Practice

1. What is the main purpose of using Grouping in Excel spreadsheets?

easy

A. To create charts automatically

B. To change the font color of cells

C. To organize and hide or show parts of data easily

D. To sort data alphabetically

Solution

1. Step 1: Understand grouping function

   Grouping in Excel is used to organize data by allowing users to hide or show rows or columns.

2. Step 2: Compare options

   Options B, C, and D describe other Excel features unrelated to grouping.

3. Final Answer:

   To organize and hide or show parts of data easily -> Option C

4. Quick Check:

   Grouping = Organize and hide/show data [OK]

Hint: Grouping hides/shows data sections quickly [OK]

Common Mistakes:

• Confusing grouping with sorting
• Thinking grouping changes cell formatting
• Assuming grouping creates charts

2. Which menu path correctly creates a group of rows in Excel?

easy

A. Home > Insert > Group

B. Data > Group > Group

C. Review > Protect Sheet > Group

D. View > Freeze Panes > Group

Solution

1. Step 1: Recall grouping location

   Grouping is found under the Data tab in Excel, specifically Data > Group.

2. Step 2: Check options

   Only Data > Group > Group correctly shows Data > Group > Group. Other options refer to unrelated menus.

3. Final Answer:

   Data > Group > Group -> Option B

4. Quick Check:

   Grouping menu = Data > Group [OK]

Hint: Use Data tab for grouping rows or columns [OK]

Common Mistakes:

• Looking under Home tab
• Confusing grouping with freezing panes
• Trying to group under Review tab

3. Given rows 2 to 5 are grouped in Excel, what happens when you click the minus (-) button next to the row numbers?

medium

A. Rows 2 to 5 are hidden from view

B. Rows 2 to 5 are deleted

C. Rows 2 to 5 are copied to clipboard

D. Rows 2 to 5 are sorted alphabetically

Solution

1. Step 1: Understand minus (-) button function

   The minus (-) button collapses the group, hiding the grouped rows from view.

2. Step 2: Eliminate other options

   Deleting, copying, or sorting are unrelated to the group collapse action.

3. Final Answer:

   Rows 2 to 5 are hidden from view -> Option A

4. Quick Check:

   Minus button = Hide grouped rows [OK]

Hint: Minus button collapses and hides grouped rows [OK]

Common Mistakes:

• Thinking rows are deleted
• Assuming rows are copied
• Confusing with sorting action

4. You tried to group columns but the Group option is greyed out. What is the most likely reason?

medium

A. You are in cell edit mode

B. You have not selected any cells

C. You have selected non-adjacent columns

D. The worksheet is protected

Solution

1. Step 1: Identify cause of disabled Group option

   When a worksheet is protected, many editing features including grouping are disabled.

2. Step 2: Check other options

   Non-adjacent columns or no selection usually allow grouping but may give warnings; cell edit mode disables many commands but protection is the main cause.

3. Final Answer:

   The worksheet is protected -> Option D

4. Quick Check:

   Protected sheet disables grouping [OK]

Hint: Check if sheet is protected to enable grouping [OK]

Common Mistakes:

• Selecting non-adjacent columns
• Not selecting cells
• Editing a cell disables grouping temporarily

5. You have a sales report with monthly data in columns B to M. You want to create an outline to quickly collapse all months except the total in column N. Which steps correctly achieve this?

hard

A. Select columns B to M, then go to Data > Group > Group Columns

B. Select columns B to N, then go to Data > Sort > Group

C. Select columns B to M, then right-click and choose Hide

D. Select column N only, then go to Data > Group > Group Columns

Solution

1. Step 1: Select the monthly data columns

   Columns B to M contain the months to group, so select these columns only.

2. Step 2: Use Data > Group to create outline

   Go to Data tab, then Group > Group Columns to create the outline for these months.

3. Step 3: Confirm total column remains visible

   Column N (total) is not grouped, so it stays visible when months are collapsed.

4. Final Answer:

   Select columns B to M, then go to Data > Group > Group Columns -> Option A

5. Quick Check:

   Group months only, exclude total column [OK]

Hint: Group only data columns, exclude total column [OK]

Common Mistakes:

• Including total column in group
• Using Sort instead of Group
• Hiding columns instead of grouping