Why English? | Explaining Which Hidden Rows an Excel SUBTOTAL Includes

An Excel total can be mathematically correct and still be described incorrectly. If one student hides a row by hand and another filters out a different row, the word “hidden” no longer tells the group exactly which values a SUBTOTAL includes. English matters because the explanation must identify the kind of hiding, the calculation and the records being summarised.

For students practising English comprehension, spreadsheet skills and school-project communication, the useful distinction is between a filtered-out row and a manually hidden row. Excel’s SUBTOTAL function treats those situations differently depending on its first argument. “The visible total” is therefore an incomplete description until you know which calculation produced it.

This article uses a small original paper dataset to explain SUBTOTAL 9 versus 109, then shows how to write a result that a partner can interpret accurately. It is not a complete Excel course or a claim that a software exercise guarantees better examination results. For the wider learning purpose, see Why English Matters.

Begin with the question the total is supposed to answer

Imagine a group recording quantities of paper materials for a fictional classroom display. Nobody is buying anything or reporting real student data. The group has six records, each with a quantity in column B. They want to discuss which records contribute to a calculation after different visibility actions.

Before touching a formula, ask whether the task is to total all recorded quantities, the records remaining after a filter, or only rows that are neither filtered out nor manually hidden. Those are three different sets of inputs. A number cannot explain its own set. The writer has to attach that meaning through labels and sentences.

“What is the total?” sounds like one question, but it may hide several intended questions. A partner could reasonably mean the total for the selected group while another person means the complete dataset. Clarifying the intended scope before calculating is usually easier than repairing a confident but mismatched report afterwards.

A useful first sentence

Try: “We need the sum of the quantities in rows that remain after the filter, including any of those rows hidden manually.” That sentence is not elegant dinner-table conversation, but it specifies a real analytical decision. A shorter report can follow once everyone understands the scope.

Alternatively: “We need only quantities in rows that are currently visible, excluding both kinds of hidden row.” The difference is small in wording and substantial in calculation. This is a practical reason English matters alongside numeracy: it helps people agree on which numbers belong in the arithmetic before they compare answers.

Back to contents

What Microsoft's current documentation establishes

Microsoft’s SUBTOTAL function reference, checked on 19 October 2026, distinguishes its function-number families. For summing, 9 includes manually hidden rows, while 109 excludes them. Both exclude rows removed from a filter’s results. Nested SUBTOTAL results within the referenced range are ignored to prevent double counting. The documented hiding behaviour concerns vertical ranges; hiding a column in a horizontal range is not equivalent.

Those are the product facts we need. Our cases assume ordinary numeric cells, the stated vertical range and the visibility conditions supplied in each exercise. We have not opened a live workbook or tested a particular device, school account or calculation setting. Follow current instructions for your actual Excel edition if its interface differs.

The explanation below is original analysis of those rules with newly chosen values. It should not be transferred automatically to Google Sheets, a PivotTable, a chart or an unrelated formula. A familiar-looking result can come from a different mechanism, and the correct next question is which mechanism is actually in use.

Back to contents

Our six-record paper worksheet

Write the following fictional records on paper. Row numbers are worksheet positions, while the record labels identify the entries. The quantities are whole numbers with the same unit: paper items. None is a percentage, price or attendance count. Keeping the unit stable lets us focus on selection rather than mixing unlike quantities.

Worksheet cell	Record label	Quantity	Supplied state in the first case
B2	Alder	14	Visible
B3	Birch	9	Manually hidden
B4	Cedar	22	Filtered out
B5	Dune	7	Visible
B6	Elm	18	Manually hidden
B7	Fern	10	Visible

The arithmetic total of all six supplied values is 80: 14 plus 9 plus 22 plus 7 plus 18 plus 10. The currently visible values are 14, 7 and 10, giving 31. The two manually hidden values contribute 27 together, and the filtered-out value is 22. These three groups account for the whole original total: 31 plus 27 plus 22 equals 80.

Do not confuse a record label with a cell address

“Birch is manually hidden” describes the supplied record’s state. “B3 contains 9” gives its worksheet position and value. A later sort or rearrangement could change the address associated with a record, so a real report may need both an identifier and an address. In this fixed paper example, the mapping remains exactly as shown.

The table also prevents the vague phrase “the missing 22” from doing too much work. Cedar’s value is present in the supplied dataset but excluded by the filter. It is not missing data, an empty cell or a confirmed deletion. The English description should preserve that distinction before anyone tries to repair the worksheet.

Back to contents

Calculate the two formulas without guessing from the screen

Consider =SUBTOTAL(9,B2:B7) under the first case’s supplied states. Cedar is excluded because its row is filtered out. Birch and Elm remain included despite being manually hidden. The contributing quantities are therefore 14, 9, 7, 18 and 10. Their sum is 58.

Now consider =SUBTOTAL(109,B2:B7). Cedar is still excluded, and the manually hidden Birch and Elm rows are excluded too. Only the currently visible Alder, Dune and Fern quantities contribute: 14 plus 7 plus 10 equals 31. The same range and values produce a different result because the first argument changes the treatment of manually hidden rows.

Explain the difference, not just the two answers

The difference between 58 and 31 is 27, exactly the combined Birch and Elm quantities. That is a useful independent check for this supplied case. It connects the numerical difference to a named group of records. A partner can inspect whether the explanation matches the table instead of trusting the formula label alone.

Do not generalise that the difference between 9 and 109 will always be 27. It is 27 here because these two manually hidden rows contribute 9 and 18. A different dataset or state would produce a different difference, possibly zero. The mechanism stays the same while the particular arithmetic depends on the supplied inputs.

The first argument is not a row count

The number 109 does not mean “use 109 rows.” The range B2:B7 identifies the cells under consideration. The first argument selects the operation and its hidden-row treatment. A learner who understands this can explain why changing 9 to 109 does not enlarge the range or add one hundred records.

This is a useful reading habit for technical notation. Different parts of an expression perform different jobs. Instead of treating a formula as an undivided string to memorise, ask what each argument specifies. English can then turn the notation into an accountable explanation: operation here, input range there, and stated assumptions around both.

Back to contents

Write a caption that tells the truth about 58

Suppose a student posts a screenshot captioned “Visible paper total: 58.” In our first case that caption is misleading, because 27 of the included items belong to manually hidden rows. The calculation can be correct while the caption misstates its scope. Changing the caption may be the first repair needed, rather than changing a formula that serves the intended question.

A more accurate version is: “58 paper items across the rows retained by the filter, including the two manually hidden records.” That tells the reader why they cannot reproduce the result by adding only the three visible quantities. It does not suggest that Excel has invented extra items or that the visible rows were incorrectly entered.

When 31 is the intended answer

If the task explicitly asks for quantities in currently visible rows only, 31 is the relevant result under the supplied conditions. A caption can say: “31 paper items in the three rows that are neither filtered out nor manually hidden.” The record count and quantity total are both labelled, so a reader does not mistake three records for three paper items.

Neither answer is universally better. The choice depends on the question. Students should practise defending a formula by linking it to the intended input set, not by declaring that a newer-looking or longer function number is automatically the right one. Correctness includes the relationship between the task, the calculation and the interpretation.

Use “in this view” with care

“In this view” can identify a useful context, but it does not specify the formula’s behaviour. A value displayed below filtered rows may still include manually hidden rows. Add the inclusion rule when it matters. The reader should not have to infer the calculation’s scope from its physical position on the screen.

For the broader issue of interpreting a selected group without calling it the complete dataset, read the guide to filtered spreadsheets and subsets. That article handles the reading scope of a filter. Our narrower question is which rows the documented SUBTOTAL variants actually include.

Back to contents

Change one state and predict the result

Leave Birch and Elm manually hidden, but remove the filter exclusion affecting Cedar. Cedar’s 22 becomes visible in this paper variation. The 9 formula now includes all six quantities and gives 80. The 109 formula includes the four non-hidden rows and gives 53: the earlier visible 31 plus Cedar’s 22.

Notice what did not change. The original six quantities are identical. Nobody added a new record or changed Cedar from 22 to another value. The calculation changed because its contributing set changed. A useful report should name that cause instead of saying merely that the spreadsheet total “went up.”

A different variation: unhide the manual rows only

Return to the original filter state, so Cedar is excluded. Now unhide Birch and Elm manually. Both formulas give 58. Their agreement does not mean the formulas have become identical in every circumstance; the state that made them differ has been removed. The comparison needs the current conditions attached to it.

If a student sees the same answer from two methods, ask why they agree in this case. Agreement can be reassuring, but it is not a proof that the methods always select the same inputs. A carefully chosen counterexample, such as our first state, reveals the difference that one convenient test might miss.

A final variation: show every row

With no filtered-out rows and no manually hidden rows, both formulas give 80 for this dataset. This is a useful baseline. You can compare later selected totals against the all-record arithmetic without pretending that the baseline is the answer to every narrower question.

Paper variation	SUBTOTAL 9	SUBTOTAL 109	Explanation of the difference
Original state	58	31	Manual rows contribute 27 only to 9
Cedar’s filter exclusion removed	80	53	Manual rows still contribute 27 only to 9
Manual rows shown, Cedar still filtered out	58	58	No manually hidden contributing rows remain
Every row shown	80	80	All six original quantities contribute

The table is a prediction for our stated paper conditions, not a record of a live Excel test. A real workbook should be checked independently, especially if the visible result does not match the expected contributing records. Unexpected output calls for inspection, not an invented story about what the software must have done.

Back to contents

Did You Know? A subtotal inside the range is not necessarily counted again

For a separate original extension, suppose B8 contains =SUBTOTAL(9,B2:B7) and displays 58 under the original state. Put =SUBTOTAL(9,B2:B8) in B9. The documented nested-subtotal rule means B8’s subtotal is not added again. The outer result remains 58 under our assumptions, rather than 116.

This example concerns a cell containing a SUBTOTAL calculation. It is not permission to assume that any number labelled “subtotal” will be ignored. If someone types the number 58 into a plain numeric cell inside a range, the label beside it does not turn that cell into a nested SUBTOTAL formula. Mechanism and wording must be distinguished.

The English lesson is especially useful: a name does not create a function’s behaviour. A row called “summary” may hold a formula, a pasted value or ordinary data. A reviewer needs to inspect what the cell actually contains before explaining why it was included or excluded. Labels help readers, but they do not replace technical evidence.

Keep the example’s scope modest. We have not analysed every nested formula, error value, table total row or calculation mode. The purpose is to show why a plausible-looking double count should be checked against the documented function behaviour rather than assumed from adding every displayed number in a column.

Back to contents

A review conversation that catches the actual misunderstanding

Imagine Lina says, “My total is 58, but I can only see 31. Excel is wrong.” Arun asks, “Which rows did the formula include?” That question is more useful than immediately replacing the result with a hand-calculated number. It separates a possible calculation problem from a possible misunderstanding of scope.

Lina checks the expression and identifies 9 as the first argument. She then notices that Birch and Elm were hidden manually. Her revised explanation is: “The result includes 27 from those manually hidden rows. It answers the filter-retained question, not the visible-only question.” The numerical discrepancy now has an evidence-based explanation.

Ask a focused question

Helpful questions include “Were these rows filtered out or hidden manually?”, “Which cells does the reference include?” and “Does the task want all retained records or only currently visible rows?” Each question targets a specific unknown. “Why is this weird?” may express frustration honestly, but it gives a helper less information about where to investigate.

The tone can remain friendly. A partner who hid rows to make a worksheet easier to read has not necessarily made a mistake. The problem may be an incomplete handover about what was hidden and why. Clear English helps the group repair that missing context without blaming the person who organised the display.

Report the completed check accurately

After checking the six paper records, say “The result matches the supplied row states and values.” Do not say “the whole workbook is verified,” because no whole workbook has been inspected. A narrow successful check is valuable precisely because its boundaries are clear. Another person can then decide what additional review is needed.

This is useful in school projects where different people check different parts. One student may verify data entry, another the formula range and another the final wording. A concise handover should say which job was completed. “Checked” without an object can hide a gap between the work performed and the assurance the reader hears.

Back to contents

A parent-and-student activity without real data

Use six paper slips with the quantities from our table. Give two slips a “manually hidden” marker and one a “filtered out” marker. Ask the learner to build the contributing set for 9, then rebuild it for 109. Only after the set is clear should they calculate the sum.

This order reveals whether a mistake is arithmetic or interpretation. If the learner selects the correct slips but adds incorrectly, work on the calculation. If they add their chosen slips accurately but selected the wrong group, revisit the language and function rule. Different difficulties deserve different help rather than one general instruction to concentrate harder.

• Name the question being answered.
• Identify the range and unit.
• Separate manual hiding from filtering.
• List the contributing records before adding.
• Write one sentence linking the total to that list.

Let the child explain a counterexample

Ask whether “both formulas always give the same answer” is true. The learner can use the original state to show that it is false, then explain why they agree when all manual rows are shown. This is more informative than asking for repeated recitation of the two function numbers.

Do not make the child memorise the fictional record labels. They are tools for reasoning, not the educational target. Once the distinction is understood, invite a new small dataset with different quantities and a clear unit. Check the new arithmetic together so that an accidental numerical error does not obscure a correct explanation of scope.

Keep the practice safe and proportionate

There is no need to use classmates’ names, marks, contact details or real payment records. Invented quantities are enough. On a school-managed device, follow the school’s approved software and file-sharing rules. A paper exercise is also a valid choice when accounts, versions or permissions would distract from the language distinction.

Multilingual discussion can help. A student may first understand the difference between filtering and manual hiding in another language, then practise the English explanation needed for an English-language task. The reasoning is not exclusive to English. The practical gain is being able to communicate it accurately to the intended reader.

Back to contents

Student advice: write the interpretation beside the number

When a project asks for a total, include a short label that identifies the population, unit and relevant exclusions. “58 paper items in filter-retained records, including manually hidden rows” is more useful than an isolated 58. It lets a teacher or partner evaluate whether the calculation answers the intended question.

If the task is unclear, ask before changing someone else’s workbook. You can say, “Should this total include rows hidden manually, or only rows currently visible?” That question shows understanding rather than weakness. It gives the task owner a concrete decision to make and reduces the chance of producing a polished answer to the wrong question.

Do not turn visibility into a privacy promise

Our topic concerns calculation scope, not access control. A row being out of sight does not establish that a recipient cannot access its contents. Do not put sensitive information in a practice workbook or treat hiding as a substitute for the owner’s actual sharing policy. The safest teaching examples use invented records from the start.

Likewise, do not infer data quality from a row’s state. A manually hidden record is not automatically wrong; a visible record is not automatically checked. Visibility, inclusion and accuracy are separate properties. An honest result paragraph should not quietly convert one into another merely because the spreadsheet looks orderly.

Keep explanations independent of the current screen

A screenshot may omit the formula bar, row states or the full reference range. If your partner needs to reproduce the calculation, provide the relevant expression and assumptions in the message. The reader should not have to guess whether an unexplained gap in the row numbers came from filtering, manual hiding or a cropped image.

This habit makes a useful learning reflection possible: “I learnt to explain why the total included records I could not see.” That identifies an observable improvement. It is more credible than claiming mastery of data analysis after one exercise, and it gives a teacher a specific skill to extend in the next task.

Back to contents

Transfer to education, further study and work

School research can involve selected survey records, experiment groups or time periods. Later study and work may involve other kinds of filtered reports. The transferable habit is not memorising our six quantities. It is asking what set a number summarises and keeping the answer attached to the number as it moves into a report or presentation.

For broader learning with formulas, ranges and charts, read Microsoft Excel for Students. It provides the wider spreadsheet route. Our article adds a focused explanation of two SUBTOTAL settings, rather than replacing the broader guide or claiming to cover every way of summarising data.

In group work, preserve the analytical question

Suppose one teammate prepares a visible-only total while another writes about all retained records. Their work can be individually careful yet incompatible. Agree on the analytical question and label the output before combining the parts. The communication task is to align the scope, not simply to make the numbers match by changing a formula until they agree.

For a wider account of those shared responsibilities, see how school projects connect research, group work and presentation. A clear handover about a calculation is one small part of that larger process. It does not replace subject knowledge, valid data collection or careful interpretation of the project’s actual instructions.

Keep career claims in proportion

Enjoying spreadsheet checks may encourage a student to explore further data activities. It does not prove suitability for a particular course, guarantee admission or predict earnings. A useful next experience is another task that requires defining a quantity, checking inputs and explaining a limitation. Formal choices should use current official course and admission information.

The practical English skill can still be named confidently: “I can distinguish the records a formula includes from the rows a reader currently sees.” That is a real, explainable capability within the practised context. Demonstrating it again with a changed dataset is a better next step than turning one successful example into a grand promise about the future.

Back to contents

An editing clinic: repair the scope, not the student's confidence

Consider the sentence “We hid three rows, so both totals exclude all three.” The supplied case contains two kinds of hiding, and the 9 formula includes the two manually hidden rows. A repair should name the distinction: “Both exclude the filtered-out row; only 109 also excludes the two manually hidden rows.”

Now edit “The total increased by 22 because we added Cedar.” In our variation the record already existed; its filter exclusion was removed. A better version is: “The contributing set increased by Cedar’s 22 when that row returned to the filter results.” The correction preserves the observed increase while repairing its described cause.

An accurate short handover

Try a complete three-sentence note: “The original six records total 80 paper items. Under the supplied filter and manual hiding, SUBTOTAL 9 gives 58 and SUBTOTAL 109 gives 31. Choose between them according to whether manually hidden retained rows belong in the requested summary.” The note states the baseline, the conditional results and the decision still needed.

Ask a partner to explain the 27 difference without looking at your workings. If they identify Birch and Elm, the message has preserved the relevant relationship. If they think 27 was deleted or newly added, inspect the wording together. A reader’s paraphrase is useful evidence about what the explanation actually communicated.

Back to contents

A harder case: one row has both kinds of exclusion

Our first table assigned each hidden row one stated reason. A more demanding paper variation gives Cedar two conditions at once: its row is manually hidden and also fails the active filter. Leave all quantities and the other row states unchanged. This variation asks whether manual hiding can somehow make a filtered-out row contribute to the 9 calculation.

It cannot under the documented rule. A filtered-out row is excluded by both variants. Cedar still contributes nothing to either result while the filter excludes it. The original results remain 58 and 31. The fact that Cedar is also manually hidden does not override its filter exclusion. The two conditions should not be treated as competing labels from which the reader arbitrarily chooses one.

Remove the filter but retain all manual hiding

Now remove Cedar’s filter exclusion while keeping its manual hiding, together with the manual hiding of Birch and Elm. SUBTOTAL 9 includes all six values and gives 80. SUBTOTAL 109 still includes only Alder, Dune and Fern, giving 31. The difference is now 49: Birch’s 9, Cedar’s 22 and Elm’s 18.

Compare this carefully with our earlier no-filter variation, where Cedar was visible and 109 gave 53. The quantities are identical in both variations. The difference is Cedar’s remaining manual-hide state. A report that merely says “we removed the filter” leaves that important condition unstated, so a reader cannot predict the visible-only result reliably.

This is why a good handover may need to name what was not changed. “The filter was removed, but Cedar is still manually hidden” is more informative than “everything is back.” The second phrase makes a broad restoration claim that the supplied action does not establish. English preserves the distinction through a simple contrast clause.

State the difference as a set, not a slogan

In these cases, the difference between the two sums comes from manually hidden rows that are not also excluded by the filter. Saying “the difference is all hidden rows” is too broad: filtered-out quantities do not contribute to either sum. Saying “the difference is all manual rows” can also be too broad when some manual rows are still filtered out.

Ask a learner to identify a row that meets both descriptions and explain why it contributes to neither subtotal while the filter remains active. The exercise practises the scope of “and,” “also” and “still,” as well as the calculation. It adds a genuinely new condition rather than simply substituting different numbers into the original table.

For a short written answer, try: “Cedar is excluded by the filter in both calculations; after that exclusion is removed, its remaining manual hiding affects 109 but not 9.” The sentence describes a sequence of states, not a contradiction between two answers. A partner can follow what changed and why the later total differs.

Including more values does not always make a larger sum

Our main quantities are positive, so including the manually hidden records makes the first total larger. That is not a universal property of including more rows. Consider a separate signed-adjustment model in C2:C4: 8, minus 6 and 2, with the minus-6 row manually hidden and no filter exclusion. These are balance adjustments, not a claim that physical paper items have negative counts.

SUBTOTAL 9 includes all three adjustments and gives 4. SUBTOTAL 109 excludes the manually hidden minus 6 and gives 10. The more inclusive calculation is smaller because the additional contribution is negative. A learner should explain the contributing values rather than assume that the larger displayed result must represent the larger set.

This short counterexample also improves the wording of a report. Say “the methods include different rows,” not “109 always gives a lower total.” The latter happens in our first positive-value case but fails in the signed-adjustment case. Good English distinguishes a case-specific numerical pattern from the general inclusion mechanism.

Back to contents

Frequently asked questions

Does SUBTOTAL always mean visible rows only?

No. Under the documented distinction used here, 9 includes manually hidden rows while 109 excludes them. Both exclude filtered-out rows. The word “subtotal” alone therefore does not identify the contributing set. Read the first argument, the reference and the current row states before describing the result.

Are filtered-out rows deleted?

Not in our supplied cases. They remain part of the underlying six-record dataset but are excluded from these SUBTOTAL results while filtered out. Our variation removes Cedar’s filter exclusion without adding a new record. Do not describe a change in selection as deletion or new data entry without evidence that either occurred.

Why can the two formulas agree in one test and differ in another?

Their difference concerns manually hidden contributing rows. If none remain, both may produce the same result, as our variations show. Agreement under one state does not establish universal equivalence. Test the condition that distinguishes the methods before making a broader statement about their behaviour.

Can I apply this explanation to hidden columns?

Not by simple substitution. Microsoft’s reference distinguishes vertical data ranges from horizontal ranges and warns that hiding a column does not have the same effect in the described horizontal example. Our calculations deliberately use a vertical column of quantities. Use the actual function documentation for a differently structured task.

Does a correct total prove the report is accurate?

It proves much less than that. The total may match its stated inputs while the inputs, unit, selection rule or final interpretation are inappropriate for the task. This article checks original arithmetic and scope. It does not verify any real workbook, survey, school submission or business record.

What is a useful next practice task?

Create a small invented dataset with a new unit and different quantities. Specify one filtered-out row and one manually hidden row, calculate both variants manually, and explain the difference in a sentence. A changed case tests understanding more effectively than repeating our answers from memory.

Back to contents

Useful next reading and one complete result sentence

Use the English Learning Hub for a broader study route. SETC’s How English Works explores how grammar and wording carry relationships; eduKateSingapore’s oral communication guide is useful when explaining a calculation aloud and responding to follow-up questions.

For your next step, choose 58 or 31 from the original case and write a sentence that makes its contributing set unmistakable. Include the unit and the treatment of manually hidden rows. Then let a partner identify the records from the sentence alone. If they choose the same set you calculated, the English explanation has preserved the arithmetic’s meaning.

That is the practical value of language here. A number can travel from a cell to a caption, a presentation and a decision without silently changing what it describes. Clear English does not replace calculation. It helps the calculation remain attached to the question it actually answers.

Back to contents