Subtotals in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUBTOTAL function do in Excel?

The SUBTOTAL function calculates a summary value like sum, average, or count for a range of cells. It can ignore hidden rows, making it useful for filtered lists.

beginner

How do you use SUBTOTAL to sum only visible cells in a filtered list?

Use SUBTOTAL with function number 109: <br>=SUBTOTAL(109, range). This sums only visible cells, ignoring hidden rows from filters.

intermediate

What is the difference between function numbers 9 and 109 in SUBTOTAL?

Function number 9 sums all cells including hidden ones. Function 109 sums only visible cells, ignoring rows hidden by filters.

intermediate

Can SUBTOTAL ignore rows hidden manually (not by filter)?

No, SUBTOTAL ignores only rows hidden by filters when using function numbers 101-111. It includes manually hidden rows in calculations.

advanced

How can SUBTOTAL help when you have multiple subtotal calculations in a list?

SUBTOTAL ignores other SUBTOTAL formulas in its range, preventing double counting when you have nested subtotals.

Which SUBTOTAL function number sums all cells including hidden rows?

A 109

B 9

C 1

D 101

Function number 9 sums all cells including hidden rows. Function 109 sums only visible cells.

What does SUBTOTAL(109, A1:A10) do?

A Sums only visible cells in A1:A10

B Counts all cells in A1:A10

C Averages all cells in A1:A10

D Ignores all hidden and visible cells

109 tells SUBTOTAL to sum only visible cells, ignoring rows hidden by filters.

Does SUBTOTAL ignore rows hidden manually (not by filter) when using function number 109?

A Yes, it ignores all hidden rows

B Only if the rows are hidden by a macro

C Only if the rows are hidden in a grouped outline

D No, it includes manually hidden rows

SUBTOTAL ignores only rows hidden by filters, not manually hidden rows.

Why is SUBTOTAL useful when you have multiple subtotal formulas in a list?

A It ignores other SUBTOTAL formulas to avoid double counting

B It counts all cells twice

C It hides all subtotal rows automatically

D It converts subtotals to averages

SUBTOTAL ignores other SUBTOTAL formulas in its range, preventing double counting.

Which function number would you use with SUBTOTAL to calculate the average ignoring hidden rows?

A 1

B 109

C 101

D 9

Function number 101 calculates average ignoring rows hidden by filters.

Explain how the SUBTOTAL function works and why it is useful with filtered data.

Think about how filtering hides rows and how SUBTOTAL can skip those.

Describe the difference between function numbers 9 and 109 in the SUBTOTAL function.

One counts everything, the other skips filtered-out rows.