Grouping and outlining in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of grouping rows or columns in Excel?

Grouping helps you organize and hide or show related rows or columns easily, making large spreadsheets simpler to read and manage.

beginner

How do you create a group for rows in Excel?

Select the rows you want to group, then go to the Data tab and click on 'Group'. This will add a collapsible outline for those rows.

beginner

What do the outline symbols (like plus and minus signs) do in Excel's grouping feature?

They let you expand (show) or collapse (hide) the grouped rows or columns with one click.

intermediate

What is the difference between grouping and subtotal outlining in Excel?

Grouping is manual and lets you choose any rows or columns to group. Subtotal outlining automatically groups data based on summary calculations like sums.

beginner

How can you remove a group in Excel?

Select the grouped rows or columns, then go to Data tab and click 'Ungroup'. This removes the outline and shows all data.

What happens when you collapse a group in Excel?

A The grouped rows or columns are deleted

B The grouped rows or columns are hidden

C The grouped rows or columns are copied

D Nothing happens

Collapsing a group hides the grouped rows or columns but does not delete or copy them.

Where do you find the option to group rows or columns in Excel?

A Data tab

B Insert tab

C Home tab

D View tab

The Group option is located under the Data tab in Excel.

What symbol usually indicates a collapsed group in Excel?

A Star (*)

B Minus sign (-)

C Check mark (✓)

D Plus sign (+)

A plus sign (+) shows that the group is collapsed and can be expanded.

Which feature automatically creates groups based on summary calculations?

A Manual grouping

B Filter

C Subtotal outlining

D Freeze panes

Subtotal outlining automatically groups data based on summary calculations like sums or averages.

How do you remove grouping from a set of rows or columns?

A Use the Ungroup option under Data tab

B Use the Sort option

C Hide the rows or columns

D Delete the rows or columns

Ungroup removes the grouping and shows all rows or columns.

Explain how grouping rows or columns can help you manage a large Excel spreadsheet.

Think about how you might tidy up a messy desk by putting similar papers in folders.

Describe the steps to create and then remove a group in Excel.

Remember the Data tab is where grouping controls live.

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