Excel Advanced - Getting the Most from Your Data

0 of 8 activities completed

Microsoft Excel Advanced · Lesson 05

Getting the Most from Your Data

Make long worksheets easier to navigate by grouping detail, creating outline levels and calculating subtotals that reveal the right amount of information on demand.

60-minute lessonMixed Excel versions8 guided activitiesHands-on dataset

Learning outcomes

Create worksheet outlinesGroup related rows and columns, then collapse or expand the detail.

Build nested groupsCombine department and regional groups into useful summary levels.

Calculate subtotalsInsert regional and departmental totals using more than one calculation.

Extract visible summariesCopy only the displayed subtotal rows without hidden detail.

Practice dataset

The embedded dataset contains quarterly operating figures for four Malaysian sales regions. It is intentionally arranged as an ordinary range because Excel's Subtotal command is unavailable while the data remains an Excel Table.

24detail rows

4regions

8departments

RM 496,000actual spending

Recommended workflow: download the Excel file, open the Operating Data sheet and save a working copy. Keep an untouched copy because grouping and Subtotal change the worksheet structure.

1. Understand outline levels

ACTIVITY 1

Inspect and sort the source

1. Open Operating Data and click within A1:H25.
2. Confirm there are no blank rows, merged cells or subtotal rows.
3. Sort first by Region A–Z, then Department A–Z, then Month No. Smallest to Largest.
4. Check the first group is Central → Marketing → January to March.

Important: Subtotal detects a change in a grouping field. If matching records are not adjacent, Excel creates repeated and misleading subtotal rows.

My data is sorted by Region, Department and Month No.

ACTIVITY 2

Create a manual row group

1. Select the three Central Marketing detail rows.
2. Choose Data → Group → Rows.
3. Use the minus symbol beside the rows to collapse the group.
4. Use the plus symbol to expand it again.
5. Choose Data → Ungroup when finished.

Grouping hides rows; it does not delete or filter them. Formulas continue to reference grouped cells.

I collapsed, expanded and ungrouped the three rows.

2. Create nested groups

ACTIVITY 3

Group departments inside a region

1. Group the three Central Marketing rows.
2. Group the three Central Operations rows separately.
3. Select all six Central rows and group them again.
4. Use the outline level buttons to move between the overall, regional and detailed views.

1Overall2Region3Department4Detail

The exact number of displayed levels depends on whether Excel counts the ungrouped detail as an additional level.

My nested groups show department and regional views.

ACTIVITY 4

Group worksheet columns

1. Select columns G:H containing Budget and Actual.
2. Choose Data → Group → Columns.
3. Collapse the group to present only identifiers and Variance.
4. Expand and ungroup the columns.

Column groups are useful when a report contains supporting calculations that readers do not need to see all the time.

I grouped and restored the Budget and Actual columns.

3. Insert regional subtotals

ACTIVITY 5

Subtotal Actual by Region

1. Remove all manual groups and confirm the data is sorted by Region.
2. Click inside the range and choose Data → Subtotal.
3. At each change in: Region.
4. Use function: Sum.
5. Add subtotal to: Budget, Actual and Variance.
6. Keep Summary below data selected and click OK.

Checkpoint: Grand Total Actual is RM496,000. Central is RM134,300; Eastern RM116,500; Northern RM118,700; Southern RM126,500.

What formula does Excel insert?

=SUBTOTAL(9,G2:G7)

Function number 9 means SUM. SUBTOTAL ignores other subtotal results, preventing double counting in the grand total.

My regional totals and grand total reconcile.

ACTIVITY 6

Use summary levels

1. Click outline level 1 to show the grand total only.
2. Click level 2 to show regional subtotals.
3. Click level 3 to restore every detail row.
4. Use individual plus and minus symbols to open only one region.

This is the central benefit of outlining: one worksheet supports executive summary and operational detail without separate reports.

I switched between grand total, regional totals and detail.

4. Add multiple subtotal calculations

ACTIVITY 7

Add average spending without replacing sums

1. Open Data → Subtotal again.
2. At each change in: Region.
3. Use function: Average.
4. Add subtotal to: Actual.
5. Clear Replace current subtotals.
6. Click OK and inspect the Sum and Average rows for every region.

Average Actual checkpoints: Central RM22,383.33; Eastern RM19,416.67; Northern RM19,783.33; Southern RM21,083.33.

Common mistake: leaving “Replace current subtotals” selected removes the Sum subtotals instead of adding a second calculation.

Both Sum and Average subtotal rows are present.

5. Copy only the visible summary

ACTIVITY 8

Extract regional totals

1. Return to a view that shows regional Sum rows but hides detail.
2. Select the visible summary range.
3. Choose Home → Find & Select → Go To Special → Visible cells only, or press Alt+;.
4. Copy and paste into a new worksheet named Regional Summary.
5. Use Paste Special → Values if the summary must remain independent.

Without “Visible cells only,” Excel may copy the hidden detail rows as well.

My new sheet contains summary rows without hidden detail.

6. Remove subtotals safely

1. Return to the detailed source sheet.
2. Choose Data → Subtotal → Remove All.
3. Confirm that 24 original detail rows remain and all inserted subtotal rows are gone.
4. Clear any remaining manual groups through Data → Ungroup → Clear Outline.

Do not delete subtotal rows manually. Remove All restores the original structure consistently and removes the outline created by the Subtotal command.

Excel Table compatibility

If the Subtotal command is unavailable, the data may be stored as an Excel Table. Click within it, choose Table Design → Convert to Range, confirm Yes, then apply Data → Subtotal.

Microsoft 365 alternative for a dynamic summary

A PivotTable or formulas such as SUMIFS, UNIQUE and FILTER can create summaries that update more flexibly. The Subtotal command remains useful when you need subtotal rows embedded directly inside a sorted detail list.

Troubleshooting

Subtotal is disabled
Convert the Excel Table to a normal range before using the command.

Repeated region totals
Sort by the grouping field so identical values are adjacent.

Grand total is too high
Use the inserted SUBTOTAL formulas; do not replace them with SUM formulas that include subtotal rows.

Hidden rows were copied
Select Visible cells only with Go To Special or Alt+; before copying.

Knowledge check

1. Does grouping delete hidden rows?

No. Grouping changes only the display. The rows and their values remain in the worksheet.

2. Why must the data be sorted before applying Subtotal?

Excel inserts a subtotal whenever the chosen field changes. Sorting keeps each group together.

3. How do you retain Sum while adding Average?

Run Subtotal again with Average and clear “Replace current subtotals.”

4. What is the safest way to restore the source?

Use Data → Subtotal → Remove All, then clear any separate manual outline if necessary.

Completion checklist

• Source records: 24
• Grand Total Budget: RM506,000
• Grand Total Actual: RM496,000
• Grand Total Variance: RM10,000 favourable
• Regional Actual totals: RM134,300 / RM116,500 / RM118,700 / RM126,500
• Original rows after Remove All: 24

Next lesson: test assumptions and find target values with Data Tables, Goal Seek, Scenario Manager and Solver.

Lesson 05 · Getting the Most from Your Data · Excel Advanced