Five numbers sit in a school spreadsheet in Clementi: 5, 7, 9, 11 and 28. A student has calculated an average of 12. Then the class filters the table to show only one group of results. One cell still says 12; another says 7. Neither calculator is necessarily malfunctioning. The real question is more interesting: which observations did each formula include? A screen can show three rows while a formula still uses five, and a worksheet can display two correct averages that answer two different questions.
If you are searching for why Excel AVERAGE ignores a filter, Google Sheets average visible rows only, SUBTOTAL 1 versus 101, average hidden rows spreadsheet, or why class data changes after filtering, you need a distinction before you need a formula. Hiding a row changes what you see; filtering selects which rows are visible; neither action automatically deletes the underlying data. Different functions are deliberately designed to calculate over different sets. When a pupil confuses the displayed subset with the calculation’s input set, the spreadsheet can give a perfectly computed answer to the wrong research question.
The 50-second route to the answer
AVERAGE(range) calculates the arithmetic mean of numeric values in its referenced range, including numbers in rows hidden by ordinary hiding or filtering. It generally ignores blanks and nonnumeric text in that range but counts numeric zero. SUBTOTAL(1, range) calculates an average that excludes rows removed by a filter; in Microsoft Excel, its function-code family 1–11 includes rows hidden manually. SUBTOTAL(101, range) averages the visible subset while excluding filtered-out rows and manually hidden rows. Excel’s official documentation states those distinctions explicitly. Google Sheets has a corresponding SUBTOTAL feature, but check current documentation and your actual spreadsheet environment rather than assuming every special case is identical across applications.
If you want the whole class, calculate the whole-class average from the intended complete dataset and label it so. If you want only the rows currently visible, use an appropriate filtered-aware calculation and label the filtering rule. If you want Group A regardless of current display, prefer a criterion-based calculation such as AVERAGEIF or AVERAGEIFS where supported. A formula is correct only relative to the question and data scope it is meant to answer.
First, understand the five-row mystery
Imagine a small science activity. Five independent groups record a time in seconds: Group A produces 5, 7 and 9; Group B produces 11 and 28. The figures are deliberately invented and do not represent actual pupil performance or a tested experiment. Put the group labels in column B and times in column C, with headers in row 1. The times occupy C2:C6. Their total is 60 seconds and their arithmetic mean is 60 divided by 5, which is 12 seconds.
Now apply a filter on column B so that only Group A appears. On the screen you see 5, 7 and 9. Their mean is 7 seconds. Yet =AVERAGE(C2:C6) continues to use the original five numeric cells, resulting in 12. =SUBTOTAL(101,C2:C6) calculates the mean over the visible rows and returns 7 when the filter hides the Group B observations. These outputs differ because their selection rules differ, not because one arithmetic operation has secretly changed.
Suppose instead you clear the filter, keep every row in view, and manually hide the row containing 28. Now the visible values are 5, 7, 9 and 11, whose mean is 8. In Excel, =SUBTOTAL(1,C2:C6) still includes the manually hidden 28 and returns 12, whereas =SUBTOTAL(101,C2:C6) excludes it and returns 8. A lesson that only says ‘SUBTOTAL ignores hidden rows’ is incomplete; the function number and the way the row was hidden matter.
The invisible membership question
An average is the total of selected values divided by the count of selected numeric values. That definition appears simple until someone asks what counts as selected. Did we select all experimental trials? Just one class? Only valid readings? Only trials that passed a preset quality rule? Only the records displayed after a filter? A formula must translate one of those choices into a precise operation. The cell displaying the mean rarely tells a reader which policy was intended.
This is why a spreadsheet can be mathematically correct and scientifically misleading. A teacher might ask for the average time across all five trials. A student filters the table to the three fastest-looking trials and reports seven without saying that two results were excluded. The arithmetic is sound for the selected subset; the interpretation is not sound for the whole group. The problem is not that filtering exists. Filtering is indispensable. The problem is using the filtered answer under a label that promises something else.
The habit worth teaching is to write the intended population in an ordinary sentence before writing the formula. ‘Mean time for all five groups, including the 28-second observation.’ ‘Mean time for Group A only.’ ‘Mean of rows currently displayed under filter Group A.’ These three sentences might produce different calculations. Once they are distinguished, the correct formula becomes easier to choose and to check.
Hide, filter, delete: three operations with different meanings
Hiding usually changes a row’s visibility. Its data can still exist and be referenced by formulas. Filtering uses a criterion to show some rows and temporarily exclude others from the display; those rows normally remain in the dataset. Deleting removes records or cells and can permanently alter formulas, totals and evidence if not undone. Students sometimes describe all three as ‘removing rows’, and that loose wording is the beginning of confusion.
A teacher can demonstrate the difference using coloured index cards. Place five cards on the table, each showing one of the values. Cover the card marked 28 with an opaque sheet: that is like hiding. Choose only the cards with Group A tags for display: that is like filtering. Remove a card from the room: that resembles deletion. If we calculate ‘all the cards originally collected’, covering and filtering do not change the population. If we calculate ‘cards currently visible’, they do. Deletion changes the underlying collection and may require reconstructing the data.
This analogy makes the distinction clear without depending on a particular software interface. It also reveals why a screenshot of three visible rows cannot prove which records the formula used. Screenshots show a view, not necessarily the range membership, function logic or rows hidden below the screen.
Why two formulas disagree even while both are working correctly
The function name AVERAGE promises a particular mathematical operation on specified arguments. It does not promise awareness of the user’s temporary visual filter. SUBTOTAL provides a family of calculations explicitly designed to account for filtered lists and optional treatment of manually hidden rows. Its first numeric argument selects both the operation and, for the code families, the visibility rule. In Excel’s documented map, 1 and 101 both mean average, but differ in how they treat manual hiding. Filtered-out rows are excluded by SUBTOTAL under either family.
Read a function as a sentence. =AVERAGE(C2:C6) means ‘calculate an average over the numeric values referenced in C2 through C6’. =SUBTOTAL(101,C2:C6) means roughly ‘calculate a subtotal average for values in this vertical range, excluding rows hidden by filters or manual hiding’. The additional number is a rule, not arbitrary decoration. A student who memorises only the number 101 without recognising the selection rule will struggle with the next spreadsheet problem.
One caveat: the formula’s range matters too. C2:C6 excludes a new row added at C7 unless the formula or table reference is expanded. A correctly chosen visibility function can still omit the latest observation because of an outdated source range. That mechanism already has an eduKate owner in Why a Spreadsheet Chart Can Ignore the Newest Result. This article’s primary responsibility is different: what a filtered or hidden row does to membership in an average.
When the wrong average looks more persuasive
Suppose the class plans to report progress over two weeks. In Week One, all five times are included and the mean is 12. In Week Two, a filter accidentally remains set to Group A, and the visible mean is 7. A student concludes that the class improved by five seconds. Nothing in the displayed values proves that conclusion. The datasets have different memberships. Even if the Group A result really was lower, the conclusion about all groups is unsupported.
This is a powerful example of an apples-to-apples comparison problem. Two averages can be individually correct yet not comparable. To make a fair comparison, define consistent inclusion rules and measurement units. Decide whether a group average should give every trial equal weight or every pupil equal weight. Note whether missing trials are excluded, replaced or counted as zero. Identify whether the comparison refers to paired pupils or entirely different groups. The formula itself cannot tell you what question you should have asked.
Students often meet this logic first in Science and Mathematics, but it is also a reading skill. When a headline says ‘average score rose’, a reader should ask which learners were counted on each occasion. When a survey says ‘average satisfaction increased’, ask who responded. The spreadsheet provides a concrete route into statistical literacy.
The number 28: an outlier is not an error by definition
In the toy dataset, 28 is much larger than the other four values. A student might be tempted to hide it and call the smaller average more representative. But large and invalid are not synonyms. Perhaps the 28-second trial reflects a genuine slower performance. Perhaps a timing error occurred. The correct action depends on evidence about the collection process, not on whether removing it makes the chart prettier.
A legitimate exclusion rule might be written before data collection, such as ‘exclude trials in which the timer failed, documenting the cause’. Applying that rule consistently can be reasonable. Hiding observations merely because they increase the mean is biased. Keeping an erroneous entry without comment is also poor practice. The best response is to document the suspect value, investigate the cause, decide using an explicit rule, and, when appropriate, show both the full-data result and a justified sensitivity analysis.
For example, the full-data mean is 12. Excluding the 28-second observation yields 8. Reporting both values with an explanation is transparent. Calling 8 ‘the class mean’ without disclosing the exclusion is not. A filter can facilitate sensitivity analysis; it must not turn into a quiet editing tool for inconvenient evidence.
Mean, median and the changing shape of a dataset
The mean is not the only summary worth considering. For the five example values 5, 7, 9, 11 and 28, the middle ordered value is 9, so the median is 9. The mean is 12 because the large value pulls it upward. If the 28 is excluded, the remaining four values have a mean of 8 and a median of 8. Those results prompt a useful discussion of distribution and which summary addresses the question.
Students may say ‘the average’ when they mean the mean, median or sometimes mode. In formal explanation, name the measure. A change in the mean after filtering may come from removing extreme values, changing subgroup composition, excluding missing data, or a combination. A rise or fall is not automatically a change in the underlying phenomenon. It may reflect a changed selection.
The skill transfers directly to reading data dashboards. A small class survey is easier to inspect than national statistics, but the question is identical: what population, variable, time period and inclusion rule does the chart represent? A dashboard with crisp typography can still conceal a shifting denominator.
Excel SUBTOTAL codes: use the map rather than guessing
Microsoft’s documentation groups SUBTOTAL function codes into two series. Codes 1–11 include values from manually hidden rows while continuing to exclude filtered-out rows. Codes 101–111 exclude manually hidden rows as well as filtered-out rows. Within those sets, 1 or 101 selects average; 2 or 102 selects count of numeric values; 3 or 103 selects count of nonempty values; 9 or 109 selects sum. There are other supported operations, including minimum, maximum and some statistical calculations.
If the question is ‘How many numeric readings are currently visible?’, =SUBTOTAL(102,C2:C6) is one candidate in a vertical range. If the question is ‘How many cells are nonempty?’, a count-of-nonempty code answers a different question. A row with the text ‘missing’ might count as nonempty but not as a numeric observation. If the question is ‘What is the sum of visible values?’, the appropriate sum code is 109, not 101. Each small choice corresponds to a claim about the underlying data.
Use the function reference for the application version you are operating. SUBTOTAL is designed around vertical list ranges; hiding a column in a horizontal arrangement is not necessarily treated like hiding a row. Do not infer behaviour from visual similarity alone. A school exercise built around the official, documented examples is preferable to passing around unverified formula folklore.
Google Sheets: same analytical question, verify the implementation
Google Sheets also provides a SUBTOTAL function with codes used to choose the aggregate operation, and its help documentation describes codes corresponding to AVERAGE, COUNT, COUNTA and SUM, including code variations for skipping hidden values. Filtering is still a change in display rather than deletion of underlying records. For a typical class list, an average of the full range and a subtotal over visible rows are different questions, just as they are in Excel.
However, ‘works the same’ should never mean ‘never check’. Spreadsheet products can differ in syntax conventions by locale, filter views, imported data types, error handling, whole-column references, smart chips and advanced functions. Even within one product, a table can contain hidden rows and a user can apply multiple criteria in sequence. Test a simple known dataset first. If your sheet returns unexpected results, inspect the actual formula and visible row state before assuming the application is buggy.
An especially useful test is the five-row dataset on this page. You can compute 12, 7 and 8 by hand, then compare the application’s output when no filter, a Group A filter, and a manually hidden 28-row are used. This makes the software behaviour observable. A learner who can predict the result before clicking ‘Filter’ is no longer relying on trial and error.
What about AVERAGEIF and AVERAGEIFS?
Sometimes you do not want a calculation to change when someone hides or shows rows. You want the mean for a clearly named group, independent of the current view. In a supported spreadsheet, a criterion-based function is often more appropriate. With group labels in B2:B6 and values in C2:C6, =AVERAGEIF(B2:B6,"A",C2:C6) expresses ‘average the numeric values associated with Group A’. For the example, the result is 7. It does not depend on whether the Group B rows happen to be visible.
For more than one inclusion criterion, an application may offer AVERAGEIFS. This can express a conceptual rule such as ‘only Group A trials from Week Two that meet the measurement protocol’. But the criteria must be legitimate and the formula ranges aligned. A data analysis can still be biased if the criterion is chosen after looking for the nicest number. Criterion-based functions improve reproducibility of a declared rule; they do not make the rule scientifically justifiable.
This is a useful distinction for homework. If a question says ‘find the mean among pupils who chose Science’, a criterion belongs in the analysis. If it says ‘find the mean of what you currently see’, a filter-aware calculation may be convenient. If it says ‘find the original overall mean’, neither a selected filter nor a selective criterion should replace the complete dataset.
A guided experiment with three predictions
Copy the following fictional observations into a fresh sheet: A/5, A/7, A/9, B/11, B/28, with group labels and values in separate columns. Put a plain average, a subtotal average using 1 and a subtotal average using 101 in three clearly labelled cells. Before doing anything else, ask pupils to predict whether all three will display 12. Under ordinary conditions with every row visible and numeric, they should.
Next apply a filter to display only Group A. Predict again. The plain AVERAGE continues to calculate 12 from the full range, while the SUBTOTAL versions both omit filtered-out Group B records and return 7. Explain why they agree with each other here despite having different function codes. Finally clear the filter and manually hide only the row with 28. Predict a third time. In Excel, the plain average and SUBTOTAL with 1 return 12; SUBTOTAL with 101 returns 8. The key isn’t remembering outputs. It is predicting the membership sets.
For one more step, remove the formula results from view and ask students to explain the calculation in plain English. ‘This average includes the five stored numbers.’ ‘This average includes only the currently displayed numbers.’ If they can state that without seeing the answer, they can reason about the mechanism. Then show how the same concept appears in selecting a fair comparison group in a real investigation.
How to check the result by hand
A learner should never need to trust an unfamiliar spreadsheet completely. For a small dataset, write out the numbers included in each calculation. Under the Group A filter, the visible values are 5, 7 and 9. Their total is 21 and their count is 3, so the mean is 21/3 = 7. For the entire dataset, the total is 60 and the count is 5, so the mean is 60/5 = 12. If only 28 is hidden, the visible total is 32 and the visible count is 4, so the visible mean is 32/4 = 8.
The hand check tests the mathematical claim, not just the formula string. Students can use a table with columns ‘question’, ‘included rows’, ‘sum’, ‘number of numeric values’, and ‘mean’. It forces them to notice that a mean needs both numerator and denominator. A wrong denominator can hide behind an apparently reasonable decimal answer. An empty cell and a zero will often behave differently: blank cells are generally ignored by ordinary average calculations, while numeric zero contributes a value and increases the count. That matters in surveys and experimental logs.
For larger datasets, a full hand check may be impractical, but students can inspect a small random sample, count visible records with an appropriate function, and compare independent calculation routes. The aim is not distrust of software. It is to build a chain of evidence from the question to the numbers and then to the displayed result.
A zero is not a blank, and ‘N/A’ is not a number
Picture three readings: 4, blank, 8. A conventional AVERAGE over those cells will generally use the two numeric readings and return 6. If the blank is deliberately replaced with a numeric zero, the average of 4, 0 and 8 is 4. Those are different datasets. A blank might mean the reading was not taken. A zero might be a genuine measurement. A text label such as ‘not available’ might be an explanation, but it is not itself a numeric observation.
Never fill missing results with zero simply to make the spreadsheet tidy unless that decision is justified by the meaning of the variable and the task. For a timed run, zero seconds might be impossible or mean a faulty instrument. For a tally of books borrowed, zero could be perfectly valid. The distinction requires domain knowledge, not merely a formula tutorial. An average calculation cannot tell you what a missing reading should mean.
Another subtlety is numbers stored as text. A value that looks like 12 in a cell may be imported as a string and treated differently by aggregating functions, depending on the formula and application. If the visible list has four numeric values and one text-formatted ’28’, a reported mean can differ unexpectedly. This is a data-type problem, not a hidden-row problem. Diagnose it separately rather than changing the filter settings until the number seems plausible.
Duplicate rows, copied totals and accidental double counting
A spreadsheet can also be wrong because the source range contains repeated observations or a total row. Suppose the values occupy five rows and an additional row holds their total of 60. If someone averages the six numeric cells, the calculation becomes (60+60)/6=20, which has no sensible meaning as the mean of the original five trials. Nothing about a filter fixes a badly defined data table. Keep raw observations separate from summaries, and make sure a formula’s reference does not include its own total or other aggregates.
The SUBTOTAL function has logic for avoiding some nested subtotal double counting, as documented by Microsoft, but that does not absolve students from organising the dataset. Arbitrary pasted totals or duplicated raw rows can still corrupt a calculation. Data should have one row per defined observation and one clear variable per column. If each row represents a pupil, do not mix pupil rows with class totals. If each row represents a trial, don’t quietly switch to one row per group halfway down the table.
This is a useful moment for Mathematics teachers to talk about units of analysis. ‘Average per student’ and ‘average per trial’ can differ even when both arise from the same records. If one student performed ten trials and another performed one, a trial-weighted average gives the first student more influence. Nothing in the word AVERAGE warns you about that. The analyst must choose and state the intended weighting.
A filter is not a scientific exclusion policy
A filter is a viewing and calculation tool. An exclusion policy is a scientific decision about which observations legitimately belong in an analysis. They can be connected, but they are not identical. A pupil who hides a difficult result has not proved it is an error. A teacher who removes an instrument-failure reading can have a defensible reason if the failure is documented and the rule is applied consistently. A researcher who explores a subgroup should label the result as a subgroup analysis rather than as the original population average.
Use a simple discipline: define the reason, apply the selection, calculate the result, and describe the consequences. If the reason changed after you saw the answer, say that the analysis was exploratory. If a comparison is planned, maintain matched inclusion rules. If the outlier materially changes the conclusion, present sensitivity checks rather than hiding that fact. The goal of digital data handling is to make the scientific argument more transparent.
In a school science investigation, the page of recorded raw observations matters as much as the final bar chart. Keep a copy of the raw table before sorting, filtering or correcting entries. The visible state of an edited spreadsheet should not be the only record of what happened.
Why screenshots often conceal the denominator
Suppose a pupil submits an image showing three rows and the text ‘average = 7’. A reviewer cannot tell whether two other rows were filtered out, deleted, on another sheet or never recorded. Nor can they tell whether the displayed value came from a formula, a manually typed number or a linked external cell. The screenshot is a view of a calculation, not necessarily an audit trail.
A stronger submission includes a clear table label, the filter criterion, the number of observations, the formula or calculation method and, where appropriate, access to the data file. For instance: ‘Group A only, n=3; average time = 7 s; rows selected by Group=A filter; calculation using SUBTOTAL on visible records.’ The sentence is compact and explains the denominator. If privacy rules protect pupil data, use anonymised or aggregated records and follow school policy; transparency does not require disclosing classmates’ personal details.
The message for parents is similar. A dashboard showing a single average mark may be useful, but ask what assessments and learners are included. One changing selection can move the number without any individual changing performance. The graph becomes intelligible only when the population is clear.
Reading a spreadsheet like a sentence
A range such as C2:C6 is a noun phrase: ‘these cells’. The formula name is a verb: ‘average’. The filter is a condition: ‘show only Group A’. The function code refines the verb: ‘ignore certain hidden rows’. When these parts are combined, the result is a complete claim about data. If one part is hidden, the sentence is ambiguous.
Students who struggle with formulas can use spoken translation. Read =AVERAGE(C2:C6) as ‘take the mean of numbers in C2 through C6, even if rows are currently hidden’. Read =SUBTOTAL(101,C2:C6) as ‘take the mean of the visible data in this vertical range under the current row visibility’. This translation makes function arguments meaningful rather than cryptic tokens.
Now reverse the exercise. Give the student the sentence ‘Find the mean of Group A only, whatever other filters are active’ and ask which method expresses it reliably. A criterion-based function may be the better answer. The learner is no longer just clicking menus but designing a computation to answer a question.
How this connects to statistics rather than just Excel
The central statistical concept is the analysis population. In school work that may be the set of trials, questionnaire responses or observations that meet a stated rule. An average is a summary of that population. When a subset changes, the summary may change. Whether the change is meaningful depends on why the subset changed. If Group A differs from Group B because one used a different apparatus, a mean difference might reflect the apparatus. If the groups differed in age, prior knowledge or response rate, those differences could affect interpretation.
This is why spreadsheet education belongs beside measurement and inference, not merely computer navigation. The most transferable questions are: Who or what is counted? How were records collected? Which rule included or excluded each one? What would the result be under a reasonable alternative rule? What claim remains supported? The learner who can answer those questions can work across survey tables, science results and public dashboards.
Teachers might combine the five-row experiment with a discussion of the median, range and why a single average can obscure variation. Students could report the full-data mean and median, then show the subgroup means. There is no need to invent sophisticated statistics. Precision about membership already changes the quality of reasoning.
A parent route: ask ‘average of what?’
Parents do not need to know the SUBTOTAL function code to support this skill. Show a child five cards with numbers and group labels. Ask for the mean across all five. Then cover the two Group B cards and ask for the mean of the cards still showing. Finally ask whether the first result has become wrong. The child should say no: the questions were different. If the child says ‘the computer changed the answer’, ask what information the calculation was allowed to use.
You can also ask about familiar data. A school report may show an average across test papers. What happens if one practice quiz is hidden from view but still included? Does the number describe all papers or only the displayed ones? The point is not to dispute school marks or assume a software error. It is to practise defining the denominator and the intended claim.
For a learner who is anxious about formulas, begin with physical cards and hand arithmetic. Add the spreadsheet only when the concept is stable. The digital function then feels like a tool for a known task rather than a source of unexplained authority.
A teacher route: two versions of the same answer
Give the class one dataset and two questions. Question A: ‘What is the mean of all five recorded times?’ Question B: ‘What is the mean of the three Group A times?’ Let half the class answer using arithmetic and half using a spreadsheet. Collect 12 and 7, and ask whether the answers contradict each other. The discussion should reveal that they do not, because the inclusion rules differ.
Next present two screenshots with identical displayed rows but different formula results. Ask pupils to infer plausible formulas. This trains reverse reasoning: seeing a number and identifying what computation could have produced it. Finally, give a flawed report claiming the entire class improved after the analyst applied a subgroup filter. Ask pupils to repair the wording rather than just the formula. A good revision reads: ‘For the selected Group A observations, the mean was 7 seconds; this does not by itself establish a change for all groups.’
Assessment can be short. Students receive a five-row table and must identify the question, formula, included records and interpretation. Award credit for correct membership reasoning even if a minor application-specific syntax detail needs correction. The enduring skill is data selection logic.
A learner troubleshooting flow when the average ‘looks wrong’
Start by reading the question again. Does it ask about everyone, a named subgroup, or visible rows? Next inspect the input range; check whether a new row falls outside it. Check the formula name and function code. Look for active filters, manually hidden rows and filter views. Confirm that the values have the intended numeric type. Check whether blanks, zeros, duplicates or totals entered the range. Calculate a small sample manually. Only then explore application settings or a possible software defect.
Do not solve a wrong average by repeatedly hiding and unhiding rows until a pleasing value appears. That is result-chasing, not diagnosis. Use a methodical table of possible causes: wrong population, wrong range, wrong function, wrong data types, duplicate records, missing values, or wrong units. Test one hypothesis at a time. This is familiar scientific method in a digital setting.
One especially valuable step is to save an unchanged copy of the raw dataset. That makes reversibility possible. When the teacher later asks how the result arose, a child can reconstruct it. A filter can be cleared. A formula can be inspected. A deleted row may be much harder to recover. Good analysis practices are also good error-recovery practices.
Common mistakes—and a more useful replacement for each
‘AVERAGE is broken when a filter is on.’ Better: AVERAGE may be following its documented full-range membership; decide if that is the intended population. ‘Everything I cannot see is excluded.’ Better: visibility does not imply deletion or exclusion from every formula. ‘SUBTOTAL always excludes hidden rows.’ Better: its code family and the hiding mechanism determine inclusion. ‘A mean after filtering is the new class mean.’ Better: specify the subgroup and the filter criterion.
‘A blank reading means zero.’ Better: decide whether the data are missing or genuinely zero. ‘The largest value must be an error.’ Better: investigate and document any exclusion rule. ‘The same average value means the same dataset.’ Better: distinct distributions can have the same mean. ‘The screenshot proves the calculation.’ Better: show the range, function, filters and count. ‘The formula is complicated, so the analysis is rigorous.’ Better: evaluate whether the result answers the stated question.
‘It is fine to compare week-one all-class mean with week-two subgroup mean.’ Better: hold population definitions consistent or explain the change. ‘Hidden rows are harmless to the audit.’ Better: record what was hidden and why. ‘The tool selected the data, so its result is objective.’ Better: a tool follows choices about data scope that humans made, intentionally or accidentally.
What the filtered mean can and cannot tell you
A filtered mean can summarise exactly the observations currently selected by a filter-aware function. It can help compare groups or inspect the effect of excluding a suspected mistake. It cannot, without further justification, establish a cause of any difference. It cannot make the hidden records disappear from the original experiment. It cannot guarantee that the selected rows are representative of a larger population. It cannot prove the subgroup improved simply because its mean is lower than the whole-population mean.
Similarly, an overall mean can summarise all included values. It may obscure important subgroups. It may be influenced by a large outlier. It may be based on unequal participation. Both overall and filtered means are useful when interpreted as answers to explicit questions. Neither should be treated as a magic ‘true average’ without defining the underlying set.
One good sentence for a school report is: ‘The arithmetic mean for all five fictional observations was 12 seconds; after filtering to the three Group A observations, the visible-data mean was 7 seconds. These statistics describe different groups and cannot by themselves establish a change over time.’ That phrasing is modest, clear and scientifically meaningful.
Frequently asked questions
Why doesn’t Excel AVERAGE change when I filter rows?
A conventional AVERAGE function uses numeric values in its referenced range even when some rows are filtered out. The filter changes which rows you see, not which stored values the ordinary function references. Use a filter-aware operation such as SUBTOTAL when the intention is to summarise visible rows.
How do I average only visible rows in Excel?
For a suitable vertical numeric range, =SUBTOTAL(101,C2:C6) selects average while excluding filtered-out and manually hidden rows. Check the range and spreadsheet version. If you require a stable named subgroup independently of display, consider a criterion-based function instead.
What’s the difference between SUBTOTAL 1 and 101?
In Excel, both choose average. Code 1 includes manually hidden rows, while 101 excludes them. Both exclude rows omitted by a filter. This distinction is set out in Microsoft’s SUBTOTAL function documentation.
Does Google Sheets support average of visible rows?
Google Sheets provides SUBTOTAL with codes for averaging and for skipping hidden values. Test the behaviour in a small known sheet, especially when filter views, imported files or other features are involved. Do not assume ordinary AVERAGE will automatically follow the visible screen.
Why does deleting a row give a different result from filtering it?
Filtering keeps the record in the underlying table but hides it from the view; deleting removes the record or shifts cells and can alter formula references. These are different operations and can change the calculation differently. Preserve the raw dataset before editing.
What should I do when the average rises after excluding an outlier?
First justify the exclusion. A large value is not inherently invalid. Show the full result and, when relevant, the result without the observation, together with a reason for the comparison. Do not call a selected average the whole-population mean.
Does a blank cell count as zero in AVERAGE?
In ordinary Excel AVERAGE over a cell range, a blank does not contribute as a numeric value, while numeric zero does. This can change both the total and the count. Determine whether blank means unrecorded, inapplicable or genuinely zero in your study.
How can I count the visible numeric records?
An appropriate SUBTOTAL count-of-numbers code can help in a vertical range; Excel documents code 102 for excluding manually hidden rows as well as filtered rows. Compare the count with the expected subgroup size. Do not confuse numeric count with count of nonempty cells.
Does sorting change the average?
Reordering all the same numbers does not change the arithmetic mean. However, a wrongly constructed range, an incorrectly aligned label column or partial sorting can misassociate observations and groups. Keep rows intact and verify identifiers before calculating subgroup statistics.
Which average is the correct one to hand in?
The one that answers the actual task. For ‘all five trials’, use all five valid observations and state the count. For ‘Group A’, use only Group A records. For ‘visible rows’, use a function whose visibility rules match the display. Show enough method for someone to verify your choice.
Why is my mean still different after I clear the filter?
Check for manually hidden rows, changed source ranges, numeric text, blanks, zero entries, duplicates, old cached output or an accidentally deleted row. Recalculate a few values by hand to isolate the cause. The problem may no longer be the filter.
Should teachers teach formulas before the concept of mean?
It is usually more productive to establish what a mean represents and which observations are included before introducing function syntax. Once the learner can explain numerator and denominator, the spreadsheet becomes a way to scale up a familiar operation. A formula memorised without that meaning is fragile.
Can I use the filtered average to prove a programme works?
Not by itself. You need a defensible comparison design, appropriate populations, reliable measurements and awareness of alternative explanations. Filtering can support exploration but cannot replace evidence of causal effect.
The final checkpoint: write a complete data sentence
Before submitting a spreadsheet result, complete this sentence: ‘The mean of [exactly which observations], measured in [units], using [inclusion rule], was [value], based on [count] valid numeric records.‘ If you cannot fill the brackets, there is a problem worth solving before polishing the chart. If you can, the formula and the inference both become easier to inspect.
The small spreadsheet lesson leads to a large 21st-century habit: whenever you see an average online, ask average of what? That one question turns a number into a statement with boundaries. It is a cheerful form of scepticism, because it makes statistics more usable instead of less trustworthy.
Next reads in eduKate’s connected learning library
Why a Histogram Can Change Its Story When You Change the Bin Width explains grouping and distribution display, not row-visibility rules. Why a Spreadsheet Can Put 10 Before 2 owns text-versus-number sorting. Why a School Survey Can Change When One Question Moves owns questionnaire-order effects. This article owns the population membership that spreadsheet averaging uses when rows are hidden or filtered.
Sources and boundaries
- Microsoft Support: SUBTOTAL function. Primary source for function codes 1/101, hidden versus filtered rows and behaviour in vertical ranges.
- Microsoft Support: AVERAGEIF function. Describes criterion-based arithmetic means and relevant syntax.
- Microsoft Support: AGGREGATE function. Offers a different route for aggregate calculations under specified options, especially with hidden rows or error conditions; this article does not assume students need its advanced forms.
- Google Docs Editors Help: SUBTOTAL. Google Sheets reference for the SUBTOTAL function and its numeric operation codes.
- Education Endowment Foundation: Guidance Reports, including mathematics guidance. Supports the broader practice of explicitly linking representations and concepts; it is not cited as a field trial of this particular spreadsheet lesson.
Factual note: Every numerical dataset in the article is constructed for teaching. No real pupils or school science measurements are being reported. The five-row arithmetic is checked explicitly, and application-specific instructions should be confirmed against the software version in use.