Row and column grouping in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Grouping rows to summarize monthly sales

You have daily sales data in rows 2 to 32 for January. You want to group these rows so you can collapse and expand the daily details easily. Which action will correctly group these rows in Google Sheets?

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

B Select rows 2 to 32, then go to Data > Group rows.

C Select rows 1 to 31, then right-click and choose Hide rows.

D Select rows 2 to 32, then press Ctrl + Z.

💡 Hint

Grouping rows is done by selecting the rows and using the Data menu.

To group rows in Google Sheets, select the rows you want to group, then use Data > Group rows. This creates a collapsible group for those rows.

📊 Formula Result

intermediate

Effect of grouping on formula results

You have grouped rows 5 to 10 in your sheet. You use the formula =SUM(A1:A20). What will be the result of this formula compared to before grouping?

A The formula will return an error because of grouping.

B The sum will exclude values in grouped rows 5 to 10.

C The sum will only include values in the grouped rows 5 to 10.

D The sum will include all values from A1 to A20, grouping does not affect formulas.

💡 Hint

Grouping only changes how rows look, not the data itself.

Grouping rows only changes the display by allowing you to collapse or expand rows. Formulas still calculate all data regardless of grouping.

❓ Function Choice

advanced

Choosing the right function to sum visible rows after grouping

After grouping rows and collapsing some groups, you want to sum only the visible (expanded) rows in column B. Which function will correctly sum only visible rows?

A SUBTOTAL(109, B2:B20)

B SUM(B2:B20)

C SUMIF(B2:B20, ">0")

D COUNT(B2:B20)

💡 Hint

SUBTOTAL can ignore hidden rows when using certain function codes.

SUBTOTAL with function code 109 sums only visible cells, ignoring hidden rows from grouping or manual hiding.

❓ data_analysis

advanced

Analyzing grouped columns impact on data visibility

You grouped columns C to F and collapsed the group. Which columns remain visible and accessible for editing?

A Columns A, B, G and beyond remain visible; C to F are hidden.

B All columns remain visible but are locked from editing.

C Only columns C to F remain visible; others are hidden.

D All columns become hidden.

💡 Hint

Collapsing a grouped column hides those columns from view.

When you collapse a grouped set of columns, those columns are hidden. Other columns outside the group remain visible and editable.

🧠 Conceptual

expert

Understanding nested grouping behavior

You have grouped rows 2 to 10, then inside that group, you grouped rows 4 to 6. If you collapse the outer group (2 to 10), what happens to the inner group (4 to 6)?

A Inner group expands automatically when outer group collapses.

B Only the inner group collapses; outer group remains expanded.

C Both outer and inner groups collapse; rows 2 to 10 are hidden.

D Collapsing outer group causes an error.

💡 Hint

Collapsing a bigger group hides all rows inside it, including nested groups.

When you collapse an outer group, all rows inside it, including nested groups, are hidden. The inner group state is not visible until the outer group is expanded again.