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.